Monthly Archives: November 2013

GoldenGate – SCN translation

This entry was posted in GoldenGate Oracle on by

On GoldenGate when you do “SEND <extract> status” you get a SCN field which does not correspond to the database SCN:
GGSCI> send e_test status
Sending STATUS request to EXTRACT E_TEST …
EXTRACT E_TEST (PID 9372244)
Current status: Recovery complete: Processing data
Current read position:
Redo thread #: 1
Sequence #: 198315
RBA: 335376
Timestamp: 2013-11-11 16:11:57.000000
SCN: 78.924145222
Current write position:
Sequence #: 1548
RBA: 401683
Timestamp: 2013-11-11 16:12:31.811533
Extract Trail: /acfs/goldengate/dirdat/test/tt


SQL> select current_scn from v$database;
CURRENT_SCN
———————-
335931612762


To translate that SCN into the database SCN there is the calculation to make:


78 * (2^32) + 924145222 = 335931594310or with a hexadecimal translation:
78 to hexa        ==> 4E
924145222 to hexa ==> 37155646

Concatenate both and then
4E37155646 to decimal ==> 335931594310

Well, with the most recent versions of GoldenGate you can simply to “INFO <extract>” and you get the database SCN between parentheses.

GGSCI> info e_test
EXTRACT  E_TEST  Last Started 2013-11-13 08:08   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                  2013-11-13 09:57:40  Seqno 200447, RBA 16256592
                  SCN 78.1163096680 (336170545768)

Goldengate – Refresh single table

This entry was posted in GoldenGate Oracle on by

With the recent versions of GoldenGate >11.2 you can see the current SCN on with the extract process is reading, even when it is stopped:

GGSCI> INFO E_TEST
EXTRACT  E_TEST  Last Started 2013-11-13 03:50   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                   2013-11-13 09:32:34  Seqno 508717, RBA 4366336
                   SCN 78.1156360444 (336163809532)

This makes it easiear to refresh a single table on a GoldenGate replication, if you can allow a small delay. There can be this need if you discover that it does not match anymore, or if you need to add a new table to the configuration. For that you do, in case of a Oracle to Oracle replication:
1 – Stop extract process and do INFO <extract> (see above). Note down the SCN between parentheses.
2 – Add the table to the parameter file (or remove an existing TABLEEXCLUDE on extract process or MAPEXCLUDE on replicat process) 
3 – Export the table from the source with FLASHBACK_SCN parameter pointing to the SCN taken from parameter 1.
4 – Make sure the replicat process has no delay with the command LAG <replicat>. You should get “At EOF, no more records to process.”
GGSCI> LAG R_TEST
Sending GETLAG request to REPLICAT R_TEST …
Last record lag: 4 seconds.
At EOF, no more records to process.
5 – Import the table at the destination (with TABLE_EXISTS=REPLACE, if needed)
6 – Re-start the extract process.
You can make the operation simpler if you have a database link between destination and source. In that case you skip the step 3 and use directly the FLASHBACK_SCN on step 5:
impdp NETWORK_LINK=source_db TABLES=test.table1 EXCLUDE=TRIGGER