oracle11g日志挖掘

在线日志挖掘
创建一个用户，建表
SQL> create user chengzhuo identified by oracle;

User created.

SQL> grant dba to chengzhuo;

Grant succeeded.

SQL> conn chengzhuo/oracle
Connected.

SQL> create table t1 (id number,name varchar2(10));

Table created.


插入数据
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> insert into t1 values (1,'diyitiao');

1 row created.

SQL> alter system switch logfile;

System altered.

SQL> insert into t1 values (2,'diertiap');

1 row created.

SQL> commit;

Commit complete.


SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/redo03.log
/u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log

online日志挖掘
SQL>  execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD1/redo02.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL>  execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD1/redo03.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SQL> select sql_redo,sql_undo from  v$logmnr_contents where table_name ='T1' and operation='INSERT';

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
insert into "CHENGZHUO"."T1"("ID","NAME") values ('1','diyitiao');
delete from "CHENGZHUO"."T1" where "ID" = '1' and "NAME" = 'diyitiao' and ROWID
= 'AAAVpLAAEAAAAIPAAA';

insert into "CHENGZHUO"."T1"("ID","NAME") values ('2','diertiap');
delete from "CHENGZHUO"."T1" where "ID" = '2' and "NAME" = 'diertiap' and ROWID
= 'AAAVpLAAEAAAAIPAAB';

SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

归档日志挖掘
查看表是否处于归档模式
SQL> select table_name,logging  from user_tables;

TABLE_NAME                     LOG
------------------------------ ---
T1                             YES


查看数据库是否处于启用了补充日志（启动了，则每条update写redo时会附加列值唯一信息）
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUP SUP
--- ---
NO  NO

SQL> alter database add supplemental log data (primary key,unique index) columns;                                                                                       ;

Database altered.

SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUP SUP
--- ---
YES YES

创建一张表T2
SQL> conn chengzhuo/oracle
Connected.

SQL> create table t2 (id number,name varchar2(10));

Table created.

SQL> insert into t2 values (1,'aa');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into t2 values (2,'bb');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.
SQL>  select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/db_recovery_flash/PROD1/archivelog/2016_05_04/o1_mf_1_25_clm7g4g3_.arc
/u01/app/oracle/db_recovery_flash/PROD1/archivelog/2016_05_04/o1_mf_1_26_clm8yw0w_.arc
/u01/app/oracle/db_recovery_flash/PROD1/archivelog/2016_05_04/o1_mf_1_27_clm8zkgv_.arc

创建directory file，使用execute dbms_logmnr_d.build()
SQL> exec sys.dbms_logmnr_d.build(dictionary_filename=>'log.ora',dictionary_location =>'/u01/log/');
 
PL/SQL procedure successfully completed.

添加日志
SQL> exec sys.dbms_logmnr.add_logfile (logfilename  =>'/u01/app/oracle/db_recovery_flash/PROD1/archivelog/2016_05_04/o1_mf_1_26_clm8yw0w_.arc',options=>sys.dbms_logmnr.NEW);
 
PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.add_logfile (logfilename =>'/u01/app/oracle/db_recovery_flash/PROD1/archivelog/2016_05_04/o1_mf_1_27_clm8zkgv_.arc',options=>sys.dbms_logmnr.ADDFILE);
 
PL/SQL procedure successfully completed.

进行分析
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/u01/log/log.ora');

PL/SQL procedure successfully completed.

SQL> select sql_redo,sql_undo from  v$logmnr_contents where table_name ='T2';

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
create table t2 (id number,name varchar2(10));


insert into "CHENGZHUO"."T2"("ID","NAME") values ('1','aa');
delete from "CHENGZHUO"."T2" where "ID" = '1' and "NAME" = 'aa' and ROWID = 'AAA
VpjAAEAAAAIXAAA';

insert into "CHENGZHUO"."T2"("ID","NAME") values ('2','bb');
delete from "CHENGZHUO"."T2" where "ID" = '2' and "NAME" = 'bb' and ROWID = 'AAA

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
VpjAAEAAAAIXAAB';

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.



--END--

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30606744/viewspace-2091386/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30606744/viewspace-2091386/