Log Miner on Multitenant 12c The following steps will show you some differences to use LogMiner with Pluggable databases. At this time i will use Flat File for the dictionary. References: http://docs.oracle.com/database /121/SUTIL/GUID-3417B738-374C-4EE3-B15C-3A66E01AE2B5.htm#SUTIL019 --> PDBS: select * from v$pdbs; CON_ID DBID NAME OPEN_MODE -------------------- -------------------- ------------------------- ---------- 2 1147477011 PDB$SEED READ ONLY 3 3291301321 PDB1 READ WRITE 5 4142533224 PDB3 READ WRITE --> Archive Status --> The time below will be used on DBMS_LOGMNR.start_logmnr 08:42:41 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data01/CDB/archives Oldest online log sequence 167 Next log sequence to archive 169 Current log sequence 169 --> Simulating some operations on PDBs alter session set container=pdb1; select name, open_mode from v$pdbs; NAME OPEN_MODE ---------------------------------------- ---------- PDB1 READ WRITE create table test.test_pdb1 (num number, text varchar2(10)) tablespace TS_PDB1; insert into test.test_pdb1 values (1,'test 1'); insert into test.test_pdb1 values (2,'test 2'); insert into test.test_pdb1 values (3,'test 3'); insert into test.test_pdb1 values (4,'test 4'); commit; delete test.test_pdb1 where num = 2; commit; update test.test_pdb1 set text='UPDATE 3' where num=3; commit; select * from test.test_pdb1; NUM TEXT -------------------- ---------- 1 test 1 3 UPDATE 3 4 test 4 alter session set container=pdb3; select name, open_mode from v$pdbs; NAME OPEN_MODE ---------------------------------------- ---------- PDB3 READ WRITE create table test.test_pdb3 (num number, text varchar2(10)) tablespace users; insert into test.test_pdb3 values (1,'test 1'); insert into test.test_pdb3 values (2,'test 2'); insert into test.test_pdb3 values (3,'test 3'); insert into test.test_pdb3 values (4,'test 4'); commit; update test.test_pdb3 set text='ERROR'; commit; select * from test.test_pdb3; NUM TEXT -------------------- ---------- 1 ERROR 2 ERROR 3 ERROR 4 ERROR -------------------------------- STARTING HERE --------------------------------- alter session set container=cdb$root; select name, open_mode from v$pdbs; NAME OPEN_MODE ---------------------------------------- ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB3 READ WRITE alter system switch logfile; --> The time below will be used on DBMS_LOGMNR.start_logmnr 09:04:13 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data01/CDB/archives Oldest online log sequence 173 Next log sequence to archive 175 Current log sequence 175 --> At this time we will work with archived logs below: /data01/CDB/archives/1_168_900491294.dbf /data01/CDB/archives/1_169_900491294.dbf /data01/CDB/archives/1_170_900491294.dbf /data01/CDB/archives/1_171_900491294.dbf /data01/CDB/archives/1_172_900491294.dbf /data01/CDB/archives/1_173_900491294.dbf /data01/CDB/archives/1_174_900491294.dbf Note: http://docs.oracle.com/database/121/SUTIL/GUID-EF8A932F-7E71-4995-893E-E583B90007C2.htm#SUTIL4227 Using a Flat File Dictionary in a CDB "You cannot take a dictionary snapshot for an entire CDB in a single flat file. You must be connected to a distinct PDB, and can take a snapshot of only that PDB in a flat file. Thus, when using a flat file dictionary, you can only mine the redo logs for the changes associated with the PDB whose data dictionary is contained within the flat file." 1.) Creating the dictionary PDB1 alter session set container=pdb1; create directory scripts as '/home/oracle/dba/scripts'; exec dbms_logmnr_d.build('minerPDB1.dic', 'SCRIPTS', dbms_logmnr_d.store_in_flat_file); !ls -ltr /home/oracle/dba/scripts/minerPDB1.dic -rw-r--r-- 1 oracle oinstall 44592353 May 17 10:14 /home/oracle/dba/scripts/minerPDB1.dic 2.) Creating the Dictionary PDB3 alter session set container=pdb3; create directory scripts as '/home/oracle/dba/scripts'; exec dbms_logmnr_d.build('minerPDB3.dic', 'SCRIPTS', dbms_logmnr_d.store_in_flat_file); !ls -ltr /home/oracle/dba/scripts/minerPDB3.dic -rw-r--r-- 1 oracle oinstall 44638808 May 17 10:16 /home/oracle/dba/scripts/minerPDB3.dic 3.) Including Archived Logs to be mined for PDB1: exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_168_900491294.dbf',Options=>dbms_logmnr.NEW); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_169_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_170_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_171_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_172_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_173_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_174_900491294.dbf',Options=>dbms_logmnr.ADDFILE); 4.) Starting the miner for PDB1: BEGIN DBMS_LOGMNR.start_logmnr ( dictfilename => '/home/oracle/dba/scripts/minerPDB1.dic', starttime => TO_DATE('17-MAY-2016 08:42:41', 'DD-MON-YYYY HH:MI:SS'), endtime => TO_DATE('17-MAY-2016 09:04:13', 'DD-MON-YYYY HH:MI:SS'), options => DBMS_LOGMNR.COMMITTED_DATA_ONLY); END; / 5.) Collecting Information for PDB1 desc v$logmnr_contents select count(*), TABLE_NAME FROM v$logmnr_contents GROUP BY TABLE_NAME; select count(*) from v$logmnr_contents where table_name like '%TEST_PDB%'; COUNT(*) -------------------- 7 col operation format a10 col sql_redo format a80 col sql_undo format a80 col username format a10 select username, operation, sql_redo, sql_undo from v$logmnr_contents where table_name ='TEST_PDB1';6.) Finishing for PDB1 exec DBMS_LOGMNR.END_LOGMNR; 7.) Including Archived Logs to be mined for PDB3: exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_168_900491294.dbf',Options=>dbms_logmnr.NEW); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_169_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_170_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_171_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_172_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_173_900491294.dbf',Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/data01/CDB/archives/1_174_900491294.dbf',Options=>dbms_logmnr.ADDFILE); 8.) Starting the miner for PDB3: BEGIN DBMS_LOGMNR.start_logmnr ( dictfilename => '/home/oracle/dba/scripts/minerPDB3.dic', starttime => TO_DATE('17-MAY-2016 08:42:41', 'DD-MON-YYYY HH:MI:SS'), endtime => TO_DATE('17-MAY-2016 09:04:13', 'DD-MON-YYYY HH:MI:SS'), options => DBMS_LOGMNR.COMMITTED_DATA_ONLY); END; / 9.) Collection Information for PDB3 desc v$logmnr_contents select count(*), TABLE_NAME FROM v$logmnr_contents GROUP BY TABLE_NAME; select count(*) from v$logmnr_contents where table_name like '%TEST_PDB%'; COUNT(*) -------------------- 9 col operation format a10 col sql_redo format a80 col sql_undo format a80 col username format a10 select username, operation, sql_redo, sql_undo from v$logmnr_contents where table_name ='TEST_PDB3';
10.) Finishing for PDB3 exec DBMS_LOGMNR.END_LOGMNR;
Advertisements
when i issue the command exec dbms_logmnr.add_logfile .. within a pluggable
i received the msg
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at “SYS.DBMS_LOGMNR”, line 68
ORA-06512: at line 1
LikeLike
Hi Yasser, as we discussed by email.
All those kinf of operation must be done at CDB level.
Thank you.
LikeLike