Oracle Golden gate - Long running transactions while stopping an extract

Oracle Golden gate - Long running transactions while stopping an extract ?

Below is how you can find it out and kill if needed.


GGSCI (ggnode1) 7>send extract EXRAJ, showtrans duration 20 MIN

Sending showtrans request to EXTRACT EXRAJ ...

Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 74058, SCN 1586.3900894393 (6815719025849), RBA 104066064
Redo Thread 2, Redo Log Sequence Number 79546, SCN 1586.3894494112 (6815712625568), RBA 1458358288

------------------------------------------------------------
XID:                  1630.17.201166
Items:                1       
Extract:              EXRAJ   
Redo Thread:          1     
Start Time:           2013-05-10:21:43:50 
SCN:                  1586.3900894393 (6815719025849) 
Redo Seq:             74058
Redo RBA:             104066064         
Status:               Running           


------------------------------------------------------------
XID:                  9068.29.296116
Items:                1       
Extract:              EXRAJ   
Redo Thread:          2     
Start Time:           2013-05-10:21:13:50 
SCN:                  1586.3894494112 (6815712625568) 
Redo Seq:             79546
Redo RBA:             1458358288         
Status:               Running           


------------------------------------------------------------
XID:                  8805.6.296139
Items:                1       
Extract:              EXRAJ   
Redo Thread:          2     
Start Time:           2013-05-10:21:24:33 
SCN:                  1586.3896755063 (6815714886519) 
Redo Seq:             79547
Redo RBA:             791874576         
Status:               Running           



22:09:08 SQL> select * from gv$transaction where xidusn=8805;

   INST_ID ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS           START_TIME           START_SCNB START_SCNW START_UEXT START_UBAFIL
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------
START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG SPA REC NOU PTX NAME                                                                   PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN
------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------- ---------- ---------- ---------- ----------
PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DATE       DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ---------- ---------- ------------- ----------------
PRV_XID          PTX_XID
---------------- ----------------
         2 0000001BFF3DEBD8       8805          6     296139        856     125452      27952          5 ACTIVE           05/10/13 21:24:33    3896755063       1586          4          856
      125452        27952            5 0000001C84587F40       7683 NO  NO  NO  NO                                                                                  0          0          0          0
         0          0          0          0          1          1          5          0          0          0 10-MAY-13                0          0 6.8157E+12             0 22650006000484CB
0000000000000000 0000000000000000



22:09:38 SQL> select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='0000001BFF3DEBD8';

  SID    SERIAL# EVENT                          MACHINE         SQL_ID        SECONDS_IN_WAIT PREV_SQL_ID   MODULE         PROGRAM
----- ---------- ------------------------------ --------------- ------------- --------------- ------------- -------------- ------------------------------------------------
ACTION
----------------------------------------------------------------
 9871        167 SQL*Net message from client    client07                               2710 7zwu0n8myp5vn occ-raj-jar occworker@client07 (TNS V1-V3)
                                                                                                          
20f669b057722


22:09:43 SQL> select
hash_value, address,
executions,buffer_gets, disk_reads,
round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,
round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
last_load_time,
module,
sql_fulltext
from v$sqlarea
where sql_id='&sql_id';

Enter value for sql_id: 7zwu0n8myp5vn

HASH_VALUE ADDRESS          EXECUTIONS BUFFER_GETS DISK_READS   AVG_GETS   AVG_DISK LAST_LOAD_TIME      MODULE
---------- ---------------- ---------- ----------- ---------- ---------- ---------- ------------------- -------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
 669685620 0000001A4E4E8840   16774570    43243144       5728        2.6          0 10-MAY-13           occ-raj-jarers
INSERT INTO user_info (uid, part_key, t_id, uname) VALUES (:uid, :part_key, :t_id) /*Truncated the sql*/


you will see the following error message in ggserr.log file

2013-05-10 22:15:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle):  send EXRAJ showtrans duration 45m.
2013-05-10 22:15:16  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Command received from GGSCI: showtrans duration 45m.
Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:20  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:26  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): send EXRAJ showtrans duration 90m.
2013-05-10 22:15:28  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Command received from GGSCI: showtrans duration 90m.


select logon_time,status,LAST_CALL_ET from gv$session where sid=9871 and inst_id=2;

LOGON_TIME          STATUS   LAST_CALL_ET
------------------- -------- ------------
05/10/2013 19:26:36 INACTIVE         3226

or you can use:


set echo on
set timing on
col sid format 999999
col serial# format 999999
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
select  t.start_time,t.status TSTATUS, s.status SSTATUS,s.sid, s.serial# ,s.machine , s.sql_id,s.prev_sql_id,s.process,t.XIDUSN||'.'||t.XIDSLOT||'.'||t.XIDSQN XID from gv$transaction t, gv$session s  where t.addr=s.taddr and t.inst_id=s.inst_id and t.start_date < (sysdate-1/142) order  by t.start_time;


-- if needed, you can go ahead and kill the sql (if it is not of a major impact)

alter system kill session '9871,167';