LogMiner – Pluggable Database – EzDBA

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;
2 comments

yasser dessouki says:

September 22, 2016 at 10:22 am

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

- Carlos Magno says:
  
  September 22, 2016 at 8:42 pm
  
  Hi Yasser, as we discussed by email.
  All those kinf of operation must be done at CDB level.
  
  Thank you.
  
  LikeLike
EzDBA – BLOG

BLOG for Oracle DBAs

LogMiner – Pluggable Database

2 comments

Leave a Reply Cancel reply

LogMiner – Pluggable Database

Share this:

Like this:

Related

2 comments

Leave a Reply Cancel reply
