While setting up GoldenGate, we wished there was a way to say “create extract myextract begin scn “, or something similar. However, there is no supported way to get this (of which I am aware at least, but would gladly be proven wrong :))<\/p>\n
We up ended doing what is below. This essentially returns the Redo Byte Address (RBA) required by GoldenGate to start an extract from an exact position in an Oracle transaction log. It takes the current database SCN, and scans the current transaction for each thread to get the highest RBA where the SCN corresponding to the transaction is just under the SCN you fetched. I know this isn’t perfect, but in conjunction with HANDLECOLLISIONS, it will alleviate most data issues.<\/p>\n
This is applicable only to classic capture, not an integrated capture process.<\/p>\n
\r\nset serveroutput on size unlimited\r\ndeclare\r\n l_scn number;\r\n l_sequence number;\r\nbegin\r\n l_scn := dbms_flashback.get_system_change_number;\r\n for r in (select member\r\n from v$logfile lf\r\n where exists (select sequence#,\r\n thread#\r\n from v$log l\r\n where status = 'CURRENT'\r\n and lf.group# = l.group#)) loop\r\n dbms_logmnr.add_logfile(logfilename => r.member);\r\n end loop;\r\n dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.no_rowid_in_stmt);\r\n for r in (select thread#,\r\n max(rbablk * 512 + rbabyte) rba\r\n from v$logmnr_contents\r\n where scn <= l_scn\r\n and thread# != 0\r\n group by thread#) loop\r\n select sequence# into l_sequence from v$log where thread# = r.thread# and status = 'CURRENT';\r\n dbms_output.put_line('Thread# = ' || r.thread# || ', Sequence# = ' || l_sequence || ' and RBA = ' || r.rba);\r\n end loop;\r\nend;\r\n\/\r\n<\/pre>\nThis will output something similar to what is below...<\/p>\n
\r\nSQL> set serveroutput on size unlimited\r\nSQL> declare\r\n 2 l_scn number;\r\n 3 l_sequence number;\r\n 4 begin\r\n 5 l_scn := dbms_flashback.get_system_change_number;\r\n 6 for r in (select member\r\n 7 from v$logfile lf\r\n 8 where exists (select sequence#,\r\n 9 thread#\r\n 10 from v$log l\r\n 11 where status = 'CURRENT'\r\n 12 and lf.group# = l.group#)) loop\r\n 13 dbms_logmnr.add_logfile(logfilename => r.member);\r\n 14 end loop;\r\n 15 dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.no_rowid_in_stmt);\r\n 16 for r in (select thread#,\r\n 17 max(rbablk * 512 + rbabyte) rba\r\n 18 from v$logmnr_contents\r\n 19 where scn <= l_scn\r\n 20 and thread# != 0\r\n 21 group by thread#) loop\r\n 22 select sequence# into l_sequence from v$log where thread# = r.thread# and status = 'CURRENT';\r\n 23 dbms_output.put_line('Thread# = ' || r.thread# || ', Sequence# = ' || l_sequence || ' and RBA = ' || r.rba);\r\n 24 end loop;\r\n 25 end;\r\n 26 \/\r\nThread# = 1, Sequence# = 370 and RBA = 22833344\r\nThread# = 2, Sequence# = 377 and RBA = 30479088\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\n<\/pre>\nYou can then create your extract with something like...<\/p>\n
\r\ncreate extract myextract;\r\n<\/pre>\n..and alter it to start from exactly where you want...<\/p>\n
\r\nGGSCI (CMHLDECOMDB01) 2> alter cmhext, extseqno 370, extrba 22833344, thread 1\r\nEXTRACT altered.\r\n\r\n\r\nGGSCI (CMHLDECOMDB01) 3> alter cmhext, extseqno 377, extrba 30479088, thread 2\r\nEXTRACT altered.\r\n\r\n\r\nGGSCI (CMHLDECOMDB01) 4> info cmhext\r\n\r\nEXTRACT CMHEXT Initialized 2012-04-30 13:41 Status STOPPED\r\nCheckpoint Lag 00:01:16 (updated 00:00:02 ago)\r\nLog Read Checkpoint Oracle Redo Logs\r\n 2012-04-30 13:40:16 Thread 1, Seqno 370, RBA 22833344\r\nLog Read Checkpoint Oracle Redo Logs\r\n 2012-04-30 13:37:27 Thread 2, Seqno 377, RBA 30479088\r\n\r\n\r\nGGSCI (CMHLDECOMDB01) 5>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"While setting up GoldenGate, we wished there was a way to say “create extract myextract begin scn “, or something similar. However, there is no supported way to get this (of which I am aware at least, but would gladly…<\/p>\n