How to configure a new Integrated Extract to start at a historial old date and time. (Doc ID 2046831.1)

APPLIES TO:Oracle GoldenGate - Version 11.2.0.0.0 and later

Information in this document applies to any platform.

GOAL Detail steps to create an Integrated Extract to start mining from an earlier date/time

SOLUTIONIn order for a new IE to capture transactions earlier than that time, you need to search for a dictionary build that was created earlier than the historical time and register the IE to begin capture at a SCN of a dictionary build.

To find SCN for all previous dictionary builds issue:

SELECT first_change# FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND name IS NOT NULL AND status = 'A';

Convert the SCNs displayed to a timestamp that is closest to what you need but earlier, via

SELECT SCN_TO_TIMESTAMP(<SCN>) FROM DUAL;

Choose the timestamp that is earlier and closest to the time you want to start the new IE, and register the IE as follows:

dblogin userid <userid>, password <password>

register extract myinteg, database scn <SCN>

Then add IE as usual with a historical time as determined above.

add ext myinteg, integrated tranlog,  begin <yyyy-mm-dd hh:mm>

Please refer to the Reference manual for more details on ADD EXTRACT for configuring an Integrated Extract.

You can also change an existing IE to go to a timestamp earlier than when it was first configured, also known as first scn which can be obtained from GGSCI > INFO EXTRACT DETAIL display. Unregister this IE and register again with the above scn option. Then alter the extract to the timestamp as required.

Note all archive log files from the logminer build to the starting point of the extract must be available. If there are archive logs missing, the extract cannot start.

Example:

The current date is 21-Aug-2015 and we need to capture transactions using IE from 04-Aug-2015

SQL> SELECT first_change# FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND name IS NOT NULL AND status = 'A';

FIRST_CHANGE#

-------------

     18975095

     19465792

     19510832

SQL> SELECT SCN_TO_TIMESTAMP(18975095) FROM DUAL;

SCN_TO_TIMESTAMP(18975095)

--------------------------------------------------

04-AUG-15 02.15.57.000000000 PM

GGSCI> register extract myinteg, database scn 18975095

Extract myinteg successfully registered with database at SCN 18975095.

GGSCI> add ext myinteg, integrated tranlog,  begin 2015-08-04 03:00

EXTRACT added.

GGSCI> add exttrail dirdat/ie, ext myinteg

EXTTRAIL added.

Other Notes:

A new dictionary is built when you create a new database and generally for normal database operations you do not need to create anymore new dictionaries.

A ggsci> register extract database command creates a new dictionary and can thus be shared if there are existing IEs.

If you regularly creates a dictionary you enable new or existing IEs the ability to capture from a historical timestamp by

sql>execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

For example schedule this daily and you can go back each 24 hours.

If you need to go back further then the available listed dictionary SCNs you can restore archived logs if these logs contain dictionary builds and catalog these restored logs with RMAN. Do the SELECT to retrieve the dictionary build SCNs again and you may see more SCNs.

http://www.cndba.cn/redhat/article/2178

http://www.cndba.cn/redhat/article/2178