Tuesday, October 15, 2013

GoldenGate not able to stop extract...

I was trying to stop Extract Process and I was getting this error (not going to blog more on this but just some commands).
There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT EPCS04, FORCESTOP command.
Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 36376, SCN 69.4281137724 (300633881148),                             RBA 1572231184
Redo Thread 2, Redo Log Sequence Number 35408, SCN 69.4272252393 (300624995817),                             RBA 838206480.

Basically, there are some long running transactions (Active) in process and thus its not able to stop.

 send extract epcs04, showtrans
The above command shows the transaction which is active with more info.

Find out the transaction using:
select s.saddr,s.sid, s.username, s.osuser, s.status, s.program, t.start_time
  from v$session s, v$transaction t 
 where s.saddr = t.ses_addr
 order by t.start_time;

Skip the transaction: 
SEND EXTRACT , SKIPTRANS
could lose data if there are subsequent operations in this txn that need to be captured.

Force GG to "capture" the txn as-is: 
SEND EXTRACT , FORCETRANS
remains open as far as the database is concerned, but GG writes it to the trail & will no longer track it in the logs. Data could be lost if more operations occur that would need to be captured.

Or Try this:
ggsci> send extract EPCS04, report
Check the report file under dirrpt/ and find out long running transaction id:
eg.,
WARNING OGG-01027  Long Running Transaction: XID 1090.15.75000, Items 0, Extract EPCS04, Redo Thread 1, SCN 98.2643258821 (423550053829), Redo Seq #123329, Redo RBA 2649730064.

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 = (
                 select --sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action
                        sql_id --prev_sql_id
                   from gv$session
                  where taddr = (
                                 select addr from gv$transaction where xidusn=1090 --this is the first part of XID from the report file
                                )
                )

No comments:

Post a Comment