Ogg入门--产生lag超时，查询当前事务的方法

1.在产生lag的时候，可以通过以下过程，查询相应的事务信息：当前在dayu中有两个到dayurpt的正向同步操作，一个dayurpt向dayu的逆向同步操作，因此通过进程的lag at列可以看到是正向还是逆向超时。

 

GGSCI (pdayudb1) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPEdayu     00:00:00      00:00:08    --投递进程（dayu向dayurpt） 
EXTRACT     RUNNING     DPEdayu2    00:00:00      00:00:04    --投递进程（dayu向dayurpt）
EXTRACT     RUNNING     EXTdayu     00:00:02      00:00:09    --抽取进程（dayu向dayurpt）
EXTRACT     RUNNING     EXTdayu2    00:00:01      00:00:09    --抽取进程（dayu向dayurpt）
REPLICAT    RUNNING     REPRPT      00:00:00      00:00:04    --复制进程（dayurpt向dayu）

 

2.当产生lag超时时，在源端和目标端执行以下语句，查询进程正在处理的事务：

 

GGSCI (pdayudb1) 3> send EXTdayu,showtrans duration 30MIN      --EXTdayu对应抽取进程中的group列，duration时间可以自定义

Sending SHOWTRANS request to EXTRACT EXTdayu ...
Oldest redo log files necessary to restart Extract are:
Redo Thread 1, Redo Log Sequence Number 51709, SCN 1415.2885425305 (6080264149145), RBA 16461328
Redo Thread 2, Redo Log Sequence Number 39994, SCN 1415.2875260169 (6080253984009), RBA 842256
------------------------------------------------------------
XID:                  61.22.3342277         
Items:                1        
Extract:              EXTdayu   
Redo Thread:          1      
Start Time:           2018-08-21:15:51:20  
SCN:                  1415.2885425305 (6080264149145)   
Redo Seq:             51709
Redo RBA:             16461328            
Status:               Running             
------------------------------------------------------------
XID:                  62.13.3443042         
Items:                1        
Extract:              EXTdayu   
Redo Thread:          1      
Start Time:           2018-08-21:15:51:36  
SCN:                  1415.2885458813 (6080264182653)   
Redo Seq:             51709
Redo RBA:             19875344            
Status:               Running             
------------------------------------------------------------
XID:                  96.1.3376159          
Items:                1        
Extract:              EXTdayu   
Redo Thread:          1      
Start Time:           2018-08-21:16:07:29  
SCN:                  1415.2887044768 (6080265768608)   
Redo Seq:             51710
Redo RBA:             126284304           
Status:               Running             
------------------------------------------------------------
XID:                  115.7.1917708         
Items:                5        
Extract:              EXTdayu   
Redo Thread:          1      
Start Time:           2018-08-21:16:07:29  
SCN:                  1415.2887045100 (6080265768940)   
Redo Seq:             51710
Redo RBA:             126365712           
Status:               Running             
------------------------------------------------------------
XID:                  25.32.4959465         
Items:                1        
Extract:              EXTdayu   
Redo Thread:          2      
Start Time:           2018-08-21:14:08:26  
SCN:                  1415.2875260169 (6080253984009)   
Redo Seq:             39994
Redo RBA:             842256              
Status:               Running             
------------------------------------------------------------
XID:                  118.8.2473829         
Items:                1        
Extract:              EXTdayu   
Redo Thread:          2      
Start Time:           2018-08-21:15:47:48  
SCN:                  1415.2885041729 (6080263765569)   
Redo Seq:             39999
Redo RBA:             825375248           
Status:               Running             
------------------------------------------------------------
XID:                  30.8.6925867          
Items:                1        
Extract:              EXTdayu   
Redo Thread:          2      
Start Time:           2018-08-21:15:51:10  
SCN:                  1415.2885404779 (6080264128619)   
Redo Seq:             40000
Redo RBA:             117596176           
Status:               Running             
------------------------------------------------------------
XID:                  106.0.4764803         
Items:                1        
Extract:              EXTdayu   
Redo Thread:          2      
Start Time:           2018-08-21:15:51:12  
SCN:                  1415.2885409608 (6080264133448)   
Redo Seq:             40000
Redo RBA:             118817808           
Status:               Running 

 

3.根据以上XID中标红的xidusn定位事务的address，即taddr。切换到oracle，以sysdba连接到数据库，执行以下语句；

 

select taddr from gv$transaction where xidusn=106;

 

4.根据taddr定位事务的sql_id。

select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='';