Using DBMS_LOGMNR with GoldenGate logdump

I honestly don’t know if we will ever use this, but I thought it was useful enough to document.

One of the things I struggled with early in our GoldenGate testing was what an RBA was. The term was flipped around in the documentation, sometimes with an EXT on the front (EXTRBA), sometimes not. Regardless, it was confusing to me because Oracle also uses the term RBA for “Redo Byte Address”.

The bottom line is that an RBA in GoldenGate is simply the current read position of the source data, whatever that may be.

For a “regular” extract, it would be the “normal” Oracle RBA, or exact position in the redo log from which the Goldengate process is extracting Oracle database transactions for replication. For a datapump extract, it is the byte position (or read checkpoint) in the proprietary GoldenGate trail file that is being written by the “regular” extract process. For a replicat, it would be the byte position (or read checkpoint) in the proprietary GoldenGate trail file that is being written by the datapump extract process on the source.

If for whatever reason, you need to tie the position in a “regular” trail file to what is in the Oracle database, you can do what is below.

Get the transaction of interest in the trail file using the GoldenGate logdump utility…

Logdump 539 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   142  (x008e)   IO Time    : 2012/04/23 10:10:46.000.000
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        113       AuditPos   : 25834512
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012/04/23 10:10:46.000.000 Insert               Len   142 RBA 2733
Name: ATL.ORDER_DETAILS
After  Image:                                             Partition 4   G  s
 0000 0028 0000 0024 3333 3335 3965 6166 2d65 3465 | ...(...$33359eaf-e4e
 302d 3462 6636 2d39 3431 342d 3438 6438 3337 3666 | 0-4bf6-9414-48d8376f
 6338 3337 0001 0028 0000 0024 3635 3730 6161 6437 | c837...(...$6570aad7
 2d33 6337 382d 3430 6361 2d61 3639 322d 3335 6431 | -3c78-40ca-a692-35d1
 3430 3465 3062 6333 0002 0028 0000 0024 6333 6437 | 404e0bc3...(...$c3d7
 6561 6332 2d33 3138 362d 3436 6135 2d38 3433 652d | eac2-3186-46a5-843e-
 6333 6336 6135 3534 6439 3661 0003 0006 0000 0002 | c3c6a554d96a........

GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4145 5030 4141 4641 4141 424d 5741 4165 0001 | AAAEP0AAFAAABMWAAe..
TokenID x4c 'L' LOGCSN           Info x00  Length    7
 3636 3539 3638 34                                 | 6659684
TokenID x36 '6' TRANID           Info x00  Length    9
 3331 2e31 372e 3539 37                            | 31.17.597

Logdump 540 >

We then take the sequence (AuditRBA) and Oracle RBA (AuditPos) for this change and find it in the archived redo log using DBMS_LOGMNR. Please note that a redo block in Oracle is 512 bytes (in Linux, at least), so we multiply the RBABLK column for a given change by 512 and add the “trailing” RBABYTE, or number of bytes into that block, at which the change is recorded.

SQL> select name from v$archived_log where sequence# = 113;

NAME
--------------------------------------------------------------------------------
+DATA/express/archivelog/2012_04_23/thread_1_seq_113.475.781351919

SQL> exec dbms_logmnr.add_logfile(logfilename => '+DATA/express/archivelog/2012_04_23/thread_1_seq_113.475.781351919');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.no_rowid_in_stmt);

PL/SQL procedure successfully completed.

SQL> select scn from v$logmnr_contents where rbablk * 512 + rbabyte =  25834512;

       SCN
----------
   6659681
   6659681

SQL>

It is interesting to note that the SCN recorded in the trail file is for the commit on the transaction. The transaction itself was recorded with an SCN three whole numbers lower.

SQL> select sql_redo from v$logmnr_contents where scn = 6659681;

SQL_REDO
--------------------------------------------------------------------------------
set transaction read write;
insert into "ATL"."ORDER_DETAILS"("ID","ORDER_ID","PRODUCT_ID","QUANTITY") value
s ('33359eaf-e4e0-4bf6-9414-48d8376fc837','6570aad7-3c78-40ca-a692-35d1404e0bc3'
,'c3d7eac2-3186-46a5-843e-c3c6a554d96a','63');

SQL> select sql_redo from v$logmnr_contents where scn = 6659684;

SQL_REDO
--------------------------------------------------------------------------------
commit;

SQL>