GoldenGate: Using SKIPTRANS

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.

About these ads

2 comments

  1. 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.

    1. 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Thoughts from James H. Lui

If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.

bdt's oracle blog

Sharing experience (by Bertrand Drouvot)

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Vishal desai's Oracle Blog

Just another WordPress.com weblog

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: