I’m going to make this a quick post because I just came across this and wanted to document it. The environment is GoldenGate 11.2.1.0.5 on Linux 64bit, with replication between two 11gR2 Oracle Databases.
While attempting to stop an EXTRACT, I receive the error message below:
GGSCI (daily.planet.paper) 2> stop E_GG Sending STOP request to EXTRACT E_GG ... 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 E_GG, FORCESTOP command. Oldest redo log file necessary to restart Extract is: Redo Thread 1, Redo Log Sequence Number 26064, SCN 2568.3320032698 (11032796048826), RBA 34984976.
The quick workaround, in most cases, is to simply Skip the transaction that this extract is “stuck” on. Serdar Turgut has a good post on skipping an orphaned transaction here which helped me a lot!
First, let’s get the Transaction ID (XID) for the particular SCN number.
GGSCI (daily.planet.paper) 3> send extract E_GG, showtrans Sending SHOWTRANS request to EXTRACT E_GG ... Oldest redo log file necessary to restart Extract is: Redo Thread 1, Redo Log Sequence Number 26064, SCN 2568.3320032698 (11032796048826), RBA 34984976 ------------------------------------------------------------ XID: 10.29.328342 Items: 0 Extract: E_GG Redo Thread: 1 Start Time: 2014-05-28:10:52:14 SCN: 2568.3320032698 (11032796048826) Redo Seq: 26064 Redo RBA: 34984976 Status: Running
We can simply just skip it using the SKIPTRANS command.
GGSCI (daily.planet.paper) 4> send extract E_GG, skiptrans 10.29.328342 thread 1 Sending skiptrans request to EXTRACT E_GG ... Are you sure you sure you want to skip transaction [XID 10.29.328342, Redo Thread 1, Start Time 2014-05-28:10:52:14, SCN 2568.3320032698 (11032796048826)]? (y/n)y Sending skiptrans request to EXTRACT E_GG ... Transaction [XID 10.29.328342, Redo Thread 1, Start Time 2014-05-28:10:52:14, SCN 2568.3320032698 (11032796048826)] skipped.
Let’s make sure it worked.
GGSCI (daily.planet.paper) 5> send extract E_GG, showtrans Sending SHOWTRANS request to EXTRACT E_GG ... No transactions found Oldest redo log file necessary to restart Extract is: Redo Thread 1, Redo Log Sequence Number 26066, SCN 2568.3320047935 (11032796064063), RBA 7516688.
Great!
Now, we can proceed to stop it successfully.
GGSCI (daily.planet.paper) 6> stop E_GG Sending STOP request to EXTRACT E_GG ... Request processed.
Hope this helps!
Cheers.
Maaz Anjum's Blog 
Good post.
You need to check a couple of things before you can safely use SKIPTRANS. Firstly, the transaction has not already processed data to be replicated because you will lose it of you skip it. In this case, your “Items: 0” indicates that nothing has been processed. Secondly, the transaction is one that will never change data that you are replicating. Again, if you skip the transaction, you will lose all changes already made and any changes to be made by that transaction. If you know both of these conditions to be true then you can safely skip the transaction. Otherwise a safer thing to do is perform a manual bounded recovery checkpoint using SEND EXTRACT BRCHECKPOINT IMMEDIATE to reduce the startup time, then use FORCESTOP on the extract.
Paul,
You are correct! I should have taken the time to explain the pre-requisite checks one should go through before skipping a transaction. Your detailed comment is welcome and much appreciated 🙂
Cheers!