There is a document that answers the question if any tag was set without clarifying what tag was set specifically:
Using Logminer to Identify if a Non-NULL Tag Has Been Set Using DBMS_STREAMS.SET_TAG (Doc ID 740574.1)
Besides, LogDump can be used to display the tag information when GoldenGate is in use: GGSTOKEN.
My experiments revealed that the required information can be obtained using X$LOGMNR_CONTENTS.DUMP_INFO as the example below demonstrates.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | SYS@PDB> grant create session, create table, select_catalog_role to tc identified by tc;Grant succeeded.SYS@PDB>SYS@PDB> grant execute on dbms_streams_adm to tc;Grant succeeded.SYS@PDB>SYS@PDB> alter user tc quota 100m on users;User altered.SYS@PDB>SYS@PDB> conn tc/tc@pdbConnected.TC@PDB>TC@PDB>TC@PDB> select current_scn from v$database;CURRENT_SCN----------- 2221571TC@PDB>TC@PDB> create table t(id int, s varchar2(30)) 2 segment creation immediate;Table created.TC@PDB>TC@PDB> exec dbms_streams_adm.set_tag(hextoraw('112233'))PL/SQL procedure successfully completed.TC@PDB>TC@PDB> insert into t values (1, 'tag_1');1 row created.TC@PDB> select dbms_transaction.local_transaction_id from dual;LOCAL_TRANSACTION_ID--------------------------------------------------------------------------------3.29.732TC@PDB> commit;Commit complete.TC@PDB>TC@PDB> exec dbms_streams_adm.set_tag(hextoraw('deadbeef'))PL/SQL procedure successfully completed.TC@PDB>TC@PDB> insert into t values (11, 'tag_1');1 row created.TC@PDB> insert into t values (12, 'tag_1');1 row created.TC@PDB> select dbms_transaction.local_transaction_id from dual;LOCAL_TRANSACTION_ID--------------------------------------------------------------------------------8.26.689TC@PDB> commit;Commit complete.TC@PDB>TC@PDB> select current_scn from v$database;CURRENT_SCN----------- 2221592 |
| TRANSACTION | TAG |
|---|---|
| 3.29.732 | 112233 |
| 8.26.689 | DEADBEEF |
The following query shows only the redo opcode 5.20 (Transaction Audit) rows for these transactions:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | SQL> col log_file old_v log_fileSQL>SQL> select member log_file 2 from v$log l, 3 v$logfile f 4 where l.status = 'CURRENT' 5 and f.group# = l.group#;LOG_FILE--------------------------------------------------------------------------------/opt/oracle/oradata/ORCLCDB/redo02.logSQL>SQL> exec dbms_logmnr.add_logfile('&log_file.')PL/SQL procedure successfully completed.SQL>SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog)PL/SQL procedure successfully completed.SQL> select xidusn, xidslt, xidsqn, 2 dump_info 3 from x$logmnr_contents 4 where (xidusn, xidslt, xidsqn) in ((3,29,732),(8,26,689)) 5 and scn between 2221571 and 2221592 6 and component_id = 5 7 and opcode = 20; XIDUSN XIDSLT XIDSQN DUMP_INFO ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 3 29 732 CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000 session number = 264 serial number = 29041 transaction name = version 318767104 audit sessionid 24 Client Id = login username = TC REPL MARKER: 06 00 09 00 00 00 01 00 03 11 22 33 XIDUSN XIDSLT XIDSQN DUMP_INFO ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 8 26 689 CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000 session number = 264 serial number = 29041 transaction name = version 318767104 audit sessionid 24 Client Id = login username = TC REPL MARKER: 06 00 0a 00 00 00 01 00 04 de ad be ef |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | SQL> select xidusn, xidslt, xidsqn, repl_marker, 2 utl_raw.substr(repl_marker, 10, to_number(utl_raw.substr(repl_marker, 9, 1), 'xxxxxxxxxx')) tag, 3 dump_info 4 from ( 5 select xidusn, xidslt, xidsqn, 6 hextoraw(replace(regexp_substr(dump_info, 'REPL MARKER:.(.*).', 1, 1, 'n', 1), ' ')) repl_marker, 7 dump_info 8 from x$logmnr_contents 9 where (xidusn, xidslt, xidsqn) in ((3,29,732),(8,26,689)) 10 and scn between 2221571 and 2221592 11 and component_id = 5 12 and opcode = 20); XIDUSN XIDSLT XIDSQN REPL_MARKER TAG DUMP_INFO ---------- ---------- ---------- -------------------------- ---------- ---------------------------------------------------------------------------------------------------- 3 29 732 060009000000010003112233 112233 CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000 session number = 264 serial number = 29041 transaction name = version 318767104 audit sessionid 24 Client Id = login username = TC REPL MARKER: 06 00 09 00 00 00 01 00 03 11 22 33 XIDUSN XIDSLT XIDSQN REPL_MARKER TAG DUMP_INFO ---------- ---------- ---------- -------------------------- ---------- ---------------------------------------------------------------------------------------------------- 8 26 689 06000A000000010004DEADBEEF DEADBEEF CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000 session number = 264 serial number = 29041 transaction name = version 318767104 audit sessionid 24 Client Id = login username = TC REPL MARKER: 06 00 0a 00 00 00 01 00 04 de ad be ef |
I was using a vanilla 19.3 database on Linux for these experiments.
