12c单独挖掘某PDB的日志logminer

柠檬
已于 2022-03-28 01:30:17 修改
阅读量437
点赞数
分类专栏： oracle 文章标签： oracle
于 2022-03-24 02:29:18 首次发布
原文链接：https://blog.csdn.net/qq_21127313/article/details/80624291
版权
oracle 专栏收录该内容
99 篇文章 1 订阅
订阅专栏
参考https://blog.csdn.net/qq_21127313/article/details/80624291
    SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /arch/
    Oldest online log sequence     8
    Next log sequence to archive   11
    Current log sequence           11
    SQL> alter session set container=orclpdb1;
     
    Session altered.
     
    SQL> create tablespace TS_orclpdb1 datafile '/data/ORCL/6DEA766238A20B38E0536538A8C0818E/datafile/test.dbf' size 10m;
     
    Tablespace created.
     
    SQL> alter user test default tablespace TS_orclpdb1;
     
    User altered.
     
    SQL> alter user test quota unlimited on TS_orclpdb1;
     
    User altered.
     
    SQL> create table test.test_orclpdb1
      2  (num number,
      3   text varchar2(10))
      4  tablespace TS_orclpdb1;
     
    Table created.
     
    SQL> insert into test.test_orclpdb1 values (1,'test 1');
    insert into test.test_orclpdb1 values (2,'test 2');
     
    1 row created.
     
    SQL> insert into test.test_orclpdb1 values (3,'test 3');
     
    1 row created.
     
    SQL>
    1 row created.
     
    SQL> insert into test.test_orclpdb1 values (4,'test 4');
     
    1 row created.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> delete test.test_orclpdb1 where num = 2;
    commit;
    1 row deleted.
     
    SQL>
     
    Commit complete.
     
    SQL> update test.test_orclpdb1 set text='UPDATE 3' where num=3;
     
    1 row updated.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> select * from test.test_orclpdb1;
     
           NUM TEXT
    ---------- ----------
             1 test 1
             3 UPDATE 3
             4 test 4
     
    SQL> alter session set container=orclpdb2;
     
    Session altered.
     
    SQL> create tablespace TS_orclpdb1 datafile '/data/ORCL/6DEA8C7BFE4F106FE0536538A8C0D779/datafile/test.dbf' size 10m;
     
    Tablespace created.
     
    SQL> create user test identified by test default tablespace TS_orclpdb1;
     
    User created.
     
    SQL> alter user test quota unlimited on TS_orclpdb1;
     
    User altered.
     
    SQL> create table test.test_orclpdb2
      2  (num number,
      3  text varchar2(10))
      4  tablespace TS_orclpdb1;
     
    Table created.
     
    SQL> insert into test.test_orclpdb2 values (1,'test 1');
    insert into test.test_orclpdb2 values (2,'test 2');
     
    1 row created.
     
    SQL>
    1 row created.
     
    SQL> insert into test.test_orclpdb2 values (3,'test 3');
     
    1 row created.
     
    SQL> insert into test.test_orclpdb2 values (4,'test 4');
     
    1 row created.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> update test.test_orclpdb2 set text='ERROR';
     
    4 rows updated.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> select * from test.test_orclpdb2;
     
           NUM TEXT
    ---------- ----------
             1 ERROR
             2 ERROR
             3 ERROR
             4 ERROR
     
    SQL> alter session set container=cdb$root;
     
    Session altered.
     
    SQL> alter system archive log current;
     
    System altered.
     
    SQL> alter session set container=orclpdb1;
     
    Session altered.
     
    SQL> create directory scripts as '/home/oracle/dba/scripts';
     
    Directory created.
     
    SQL> exec dbms_logmnr_d.build('minerorclpdb1.dic','SCRIPTS', dbms_logmnr_d.store_in_flat_file);
     
    PL/SQL procedure successfully completed.
     
    SQL> alter session set container=orclpdb2;
     
    Session altered.
     
    SQL> create directory scripts as '/home/oracle/dba/scripts';
     
    Directory created.
     
    SQL> exec dbms_logmnr_d.build('minerorclpdb2.dic','SCRIPTS', dbms_logmnr_d.store_in_flat_file);
     
    PL/SQL procedure successfully completed.
     
    SQL> alter session set container=cdb$root;
     
    Session altered.
     
    SQL> begin
      2  sys.dbms_logmnr.add_logfile(logfilename=>'/arch/1_10_971686879.dbf', options=>sys.dbms_logmnr.addfile);
      3  sys.dbms_logmnr.add_logfile(logfilename=>'/arch/1_11_971686879.dbf', options=>sys.dbms_logmnr.addfile);
      4  end;
      5  /
     
    PL/SQL procedure successfully completed.
     
    SQL> BEGIN
      2  DBMS_LOGMNR.start_logmnr (
      3  dictfilename => '/home/oracle/dba/scripts/minerorclpdb1.dic',
      4  options => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
      5  END;
      6  /
     
    PL/SQL procedure successfully completed.
     
    SQL> select sql_redo from  v$logmnr_contents  where sql_redo like '%TEST_ORCLPDB%';
     
    SQL_REDO
    --------------------------------------------------------------------------------
    insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"
    ,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$
    ","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6","SIGNATURE","SPARE7","SP
    ARE8","SPARE9","DFLCOLLID","CREAPPID","CREVERID","CREPATCHID","MODAPPID","MODVER
    ID","MODPATCHID","SPARE10","SPARE11","SPARE12","SPARE13","SPARE14") values ('732
    78','73278','107','TEST_ORCLPDB1','1',NULL,'2',TO_DATE('08-JUN-18', 'DD-MON-RR')
    ,TO_DATE('08-JUN-18', 'DD-MON-RR'),TO_DATE('08-JUN-18', 'DD-MON-RR'),'1',NULL,NU
    LL,'0',NULL,'6','1','107',NULL,NULL,NULL,NULL,'0','0','0','16382',NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
     
    insert into "TEST"."TEST_ORCLPDB1"("NUM","TEXT") values ('1','test 1');
     
    SQL_REDO
    --------------------------------------------------------------------------------
    insert into "TEST"."TEST_ORCLPDB1"("NUM","TEXT") values ('2','test 2');
    insert into "TEST"."TEST_ORCLPDB1"("NUM","TEXT") values ('3','test 3');
    insert into "TEST"."TEST_ORCLPDB1"("NUM","TEXT") values ('4','test 4');
    delete from "TEST"."TEST_ORCLPDB1" where "NUM" = '2' and "TEXT" = 'test 2' and R
    OWID = 'AAAR4+AATAAAACHAAB';
     
    update "TEST"."TEST_ORCLPDB1" set "TEXT" = 'UPDATE 3' where "TEXT" = 'test 3' an
    d ROWID = 'AAAR4+AATAAAACHAAC';
     
     
    7 rows selected.
     
    SQL> begin
      2  
      3  sys.dbms_logmnr.end_logmnr;
      4  
      5  end;
      6  /
     
    PL/SQL procedure successfully completed.
     
    SQL> begin
      2  sys.dbms_logmnr.add_logfile(logfilename=>'/arch/1_10_971686879.dbf', options=>sys.dbms_logmnr.addfile);
      3  sys.dbms_logmnr.add_logfile(logfilename=>'/arch/1_11_971686879.dbf', options=>sys.dbms_logmnr.addfile);
      4  end;
      5  /
     
    PL/SQL procedure successfully completed.
     
    SQL> BEGIN
      2  DBMS_LOGMNR.start_logmnr (
      3  dictfilename => '/home/oracle/dba/scripts/minerorclpdb2.dic',
      4  options => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
      5  END;
      6  /
     
    PL/SQL procedure successfully completed.
     
    SQL> select sql_redo from  v$logmnr_contents  where sql_redo like '%TEST_ORCLPDB%';
     
    SQL_REDO
    --------------------------------------------------------------------------------
    insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"
    ,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$
    ","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6","SIGNATURE","SPARE7","SP
    ARE8","SPARE9","DFLCOLLID","CREAPPID","CREVERID","CREPATCHID","MODAPPID","MODVER
    ID","MODPATCHID","SPARE10","SPARE11","SPARE12","SPARE13","SPARE14") values ('732
    77','73277','107','TEST_ORCLPDB2','1',NULL,'2',TO_DATE('08-JUN-18', 'DD-MON-RR')
    ,TO_DATE('08-JUN-18', 'DD-MON-RR'),TO_DATE('08-JUN-18', 'DD-MON-RR'),'1',NULL,NU
    LL,'0',NULL,'6','1','107',NULL,NULL,NULL,NULL,'0','0','0','16382',NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
     
    insert into "TEST"."TEST_ORCLPDB2"("NUM","TEXT") values ('1','test 1');
     
    SQL_REDO
    --------------------------------------------------------------------------------
    insert into "TEST"."TEST_ORCLPDB2"("NUM","TEXT") values ('2','test 2');
    insert into "TEST"."TEST_ORCLPDB2"("NUM","TEXT") values ('3','test 3');
    insert into "TEST"."TEST_ORCLPDB2"("NUM","TEXT") values ('4','test 4');
    update "TEST"."TEST_ORCLPDB2" set "TEXT" = 'ERROR' where "TEXT" = 'test 1' and R
    OWID = 'AAAR49AAUAAAACHAAA';
     
    update "TEST"."TEST_ORCLPDB2" set "TEXT" = 'ERROR' where "TEXT" = 'test 2' and R
    OWID = 'AAAR49AAUAAAACHAAB';
     
    update "TEST"."TEST_ORCLPDB2" set "TEXT" = 'ERROR' where "TEXT" = 'test 3' and R
    OWID = 'AAAR49AAUAAAACHAAC';
     
    SQL_REDO
    --------------------------------------------------------------------------------
     
    update "TEST"."TEST_ORCLPDB2" set "TEXT" = 'ERROR' where "TEXT" = 'test 4' and R
    OWID = 'AAAR49AAUAAAACHAAD';
     
     
    9 rows selected.
     
    SQL> begin
      2  
      3  sys.dbms_logmnr.end_logmnr;
      4  
      5  end;
      6  /
     
    PL/SQL procedure successfully completed.
原文链接：https://blog.csdn.net/qq_21127313/article/details/80624291
柠檬
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
12c单独挖掘某PDB的日志logminer

参考https://blog.csdn.net/qq_21127313/article/details/80624291 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /arch/ Oldest online log sequence
复制链接

扫一扫
专栏目录