Hello,
I am trying to use LogMiner to view all DML against a table called 'incidents' inside a PDB called 'oem'.
However, I am only seeing DDL against the table and no DML.
Am I missing something? Here are some steps to reproduce:
# connect to the 'oem' PDB, then run some DDL:
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER SESSION SET container=oem;
SQL> ALTER TABLE incidents ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
# now run some DML:
SQL> select * from incidents;
I_ID SUBJECT
---------- ------------------------------
5 b
1 a
SQL> update incidents set i_id = 6 where i_id = 5;
1 row updated.
SQL> commit;
# now switch back to cdb$root and start LogMiner (in NOARCHIVELOG mode, so only mining the 3 redo logs):
SQL> ALTER SESSION SET container=cdb$root;
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/apps/oracle/oradata/emdbmw01b/redo01.log', -
OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/apps/oracle/oradata/emdbmw01b/redo02.log');
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/apps/oracle/oradata/emdbmw01b/redo03.log');
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
# now query the v$LOGMNR_CONTENTS view and notice only the DDL is listed:
SQL> SELECT SRC_CON_NAME, username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE SRC_CON_NAME = 'OEM' and sql_redo like '%incidents%'; 2
SRC_CON_NAME USR
------------------------------ ------------------------------
XID
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
OEM SYS
10.19.2507
ALTER TABLE incidents ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-=-
Why is the UPDATE missing? I can reproduce this issue on several different 12c instances , same behavior everywhere.
Please let me know if you need more information from me in order to solve this. Apologies if I am misunderstanding how LogMiner works with PDB's in 12c.