Wednesday, May 5, 2010

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.





     

No comments: