Quick way to approximate the RBA for a given SCN

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 :))

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.

This is applicable only to classic capture, not an integrated capture process.

set serveroutput on size unlimited
declare
  l_scn number;
  l_sequence number;
begin
  l_scn := dbms_flashback.get_system_change_number;
  for r in (select member
              from v$logfile lf
              where exists (select sequence#,
                                   thread#
                              from v$log l
                              where status = 'CURRENT'
                                and lf.group# = l.group#)) loop
    dbms_logmnr.add_logfile(logfilename => r.member);
  end loop;
  dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.no_rowid_in_stmt);
  for r in (select thread#,
                   max(rbablk * 512 + rbabyte) rba
              from v$logmnr_contents
              where scn <= l_scn
                and thread# != 0
              group by thread#) loop
    select sequence# into l_sequence from v$log where thread# = r.thread# and status = 'CURRENT';
    dbms_output.put_line('Thread# = ' || r.thread# || ', Sequence# = ' || l_sequence || ' and RBA = ' || r.rba);
  end loop;
end;
/

This will output something similar to what is below...

SQL> set serveroutput on size unlimited
SQL> declare
  2    l_scn number;
  3    l_sequence number;
  4  begin
  5    l_scn := dbms_flashback.get_system_change_number;
  6    for r in (select member
  7                from v$logfile lf
  8                where exists (select sequence#,
  9                                     thread#
 10                                from v$log l
 11                                where status = 'CURRENT'
 12                                  and lf.group# = l.group#)) loop
 13      dbms_logmnr.add_logfile(logfilename => r.member);
 14    end loop;
 15    dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.no_rowid_in_stmt);
 16    for r in (select thread#,
 17                     max(rbablk * 512 + rbabyte) rba
 18                from v$logmnr_contents
 19                where scn <= l_scn
 20                  and thread# != 0
 21                group by thread#) loop
 22      select sequence# into l_sequence from v$log where thread# = r.thread# and status = 'CURRENT';
 23      dbms_output.put_line('Thread# = ' || r.thread# || ', Sequence# = ' || l_sequence || ' and RBA = ' || r.rba);
 24    end loop;
 25  end;
 26  /
Thread# = 1, Sequence# = 370 and RBA = 22833344
Thread# = 2, Sequence# = 377 and RBA = 30479088

PL/SQL procedure successfully completed.

SQL>

You can then create your extract with something like...

create extract myextract;

..and alter it to start from exactly where you want...

GGSCI (CMHLDECOMDB01) 2> alter cmhext, extseqno 370, extrba 22833344, thread 1
EXTRACT altered.


GGSCI (CMHLDECOMDB01) 3> alter cmhext, extseqno 377, extrba 30479088, thread 2
EXTRACT altered.


GGSCI (CMHLDECOMDB01) 4> info cmhext

EXTRACT    CMHEXT    Initialized   2012-04-30 13:41   Status STOPPED
Checkpoint Lag       00:01:16 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2012-04-30 13:40:16  Thread 1, Seqno 370, RBA 22833344
Log Read Checkpoint  Oracle Redo Logs
                     2012-04-30 13:37:27  Thread 2, Seqno 377, RBA 30479088


GGSCI (CMHLDECOMDB01) 5>