How to skip Orphan Transactions in Golden Gate

• when  golden gate  extract  is  restarted  It will  start to  scan at the recovery checkpoint  archive

This  means  that there is  an open transaction  in 1013793 archive

GGSCI  2> info E_CBDS showch

EXTRACT E_CBDS Last Started 2010-05-03 03:07 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Redo Logs
2010-05-04 11:59:26 Thread 1, Seqno 1013813, RBA 542140976

Current Checkpoint Detail:

Read Checkpoint #1
Oracle RAC Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 1013526
RBA: 13032976
Timestamp: 2010-05-03 03:02:53.000000
SCN: 48.3433707770 (209592137978)
Redo File: /cbdspredo02/redo_1_02.dbf

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Thread #: 1
Sequence #: 1013793
RBA: 529800208
Timestamp: 2010-05-04 09:41:20.000000
SCN: 48.3631197264 (209789627472)
Redo File: Not Avaliable

• I checked the  open transactions  in Golden Gate The oldest transaction is  688.6.2315775
  
  GGSCI (kamino) 7> send E_CBDS showtrans
  
  Sending SHOWTRANS request to EXTRACT E_CBDS ...
  Oldest redo log files necessary to restart Extract are:
  Redo Thread 1, Redo Log Sequence Number 1013634, SCN 48.3548506190 (209706936398), RBA 482658832
  
  ------------------------------------------------------------
  XID: 688.6.2315775
  Items: 62523375
  Extract: E_CBDS
  Redo Thread: 1
  Start Time: 2010-05-03:22:25:21
  SCN: 48.3548506190 (209706936398)
  Redo Seq: 1013634
  Redo RBA: 482658832
  Status: Running
  
  ....
  ....
• I checked all the transaction from database  but  the  oldest transaction stared   05/04/10 09:35:06  so  transaction XID: 688.6.2315775   is  mismatched  anyway 
  
  SQL>SELECT s.inst_id,
  
  s.sid,s.serial#,t.start_time,
  s.status
  FROM GV$session s, GV$transaction t, GV$rollstat r
  WHERE s.saddr=t.ses_addr
  and t.xidusn=r.usn
  and s.inst_id=t.inst_id
  and t.inst_id=r.inst_id
  order by t.start_time;
  
  1 10689 2949 05/04/10 09:35:06 INACTIVE
  
  2 10657 2782 05/04/10 09:44:40 ACTIVE
  2 10819 4957 05/04/10 09:49:20 INACTIVE
  2 10054 65352 05/04/10 09:50:22 ACTIVE
  2 10626 2361 05/04/10 09:55:16 ACTIVE
  2 10624 5782 05/04/10 10:00:15 ACTIVE
• I will skip this transaction BUT BE CAREFULL this  means  inconsistency   with the target replication side Before  doing that I will  analyze  the transaction  XID: 688.6.2315775 in archive   1013634 with logminer (Refer  to  this Logminer  article  for  detail).I will  replicate  this transaction manullay to the  target  database
  
  begin
  
  sys.dbms_logmnr.add_logfile (logfilename => 'arch_1013634_1_461937508.arc',options=>sys.dbms_logmnr.new);
  sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
  end;
  /
  
  create table serdar.logminer_table as select * from V$LOGMNR_CONTENTS;
  
  --XID: 688.6.2315775
  select * from serdar.logminer_table where xidusn=688 and xidslt=6 and xidsqn=2315775;
• Then last step I will  first  dump and  skip the transaction
  
  GGSCI  1>send E_CBDS showtrans 688.6.2315775  file tran_688.6.2315775.dmp detail
  
  GGSCI  2> SEND EXTRACT E_CBDS , SKIPTRANS 688.6.2315775 THREAD 1
  
  
  Sending SKIPTRANS request to EXTRACT E_CBDS ...
  
  Are you sure you sure you want to skip transaction [XID 688.6.2315775, Redo Thread 1, Start Time 2010-05-03:22:25:21, SCN 48.3548506190 (209706936398)]? (y/n)y
  Sending SKIPTRANS request to EXTRACT E_CBDS ...
  Transaction [XID 688.6.2315775, Redo Thread 1, Start Time 2010-05-03:22:25:21, SCN 48.3548506190 (209706936398)] skipped.