Logmnr的简单使用

Oracle Logmnr的用途:

     对redo log file 和archive log file进行分析挖掘，可以用来修正用户的误操作，还可以用来进行一些简单的审计。

oracle 11g的logmnr默认是安装好的，不需要运行dbmslm.sql和dbmslmd.sq。一下是logmnr简单应用的过程。

SQL> select a.status,b.member from v$log a,v$logfile b where a.group#=b.group# ;

STATUS           MEMBER
---------------- --------------------------------------------------
CURRENT          /u01/app/oracle/oradata/orcl/redo03.log
INACTIVE         /u01/app/oracle/oradata/orcl/redo02.log
INACTIVE         /u01/app/oracle/oradata/orcl/redo01.log

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 jacob1
         2 jacob2
         4 jacob4
         5 jacob5
         3 jacob3

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1136339

SQL> delete from t1;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1136355

SQL> select a.status,b.member from v$log a,v$logfile b where a.group#=b.group# ;

STATUS           MEMBER
---------------- --------------------------------------------------
CURRENT          /u01/app/oracle/oradata/orcl/redo03.log
INACTIVE         /u01/app/oracle/oradata/orcl/redo02.log
INACTIVE         /u01/app/oracle/oradata/orcl/redo01.log

----此处是关键
SQL> alter database add supplemental log data;

Database altered.

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo03.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog,startscn=>1136339,endscn=>1136355);

PL/SQL procedure successfully completed.

SQL> col operation for a15 
SQL> col sql_redo for a50
SQL> col sql_undo for a50
SQL> select operation,sql_redo,sql_undo from v$logmnr_contents;

OPERATION       SQL_REDO                                           SQL_UNDO
--------------- -------------------------------------------------- --------------------------------------------------
DELETE          delete from "JACOB"."T1" where "ID" = '1' and "NAM insert into "JACOB"."T1"("ID","NAME") values ('1',
                E" = 'jacob1' and ROWID = 'AAAR7kAAFAAAACHAAB';    'jacob1');

DELETE          delete from "JACOB"."T1" where "ID" = '2' and "NAM insert into "JACOB"."T1"("ID","NAME") values ('2',
                E" = 'jacob2' and ROWID = 'AAAR7kAAFAAAACHAAC';    'jacob2');

DELETE          delete from "JACOB"."T1" where "ID" = '4' and "NAM insert into "JACOB"."T1"("ID","NAME") values ('4',
                E" = 'jacob4' and ROWID = 'AAAR7kAAFAAAACHAAD';    'jacob4');

DELETE          delete from "JACOB"."T1" where "ID" = '5' and "NAM insert into "JACOB"."T1"("ID","NAME") values ('5',
                E" = 'jacob5' and ROWID = 'AAAR7kAAFAAAACHAAE';    'jacob5');

OPERATION       SQL_REDO                                           SQL_UNDO
--------------- -------------------------------------------------- --------------------------------------------------

DELETE          delete from "JACOB"."T1" where "ID" = '3' and "NAM insert into "JACOB"."T1"("ID","NAME") values ('3',
                E" = 'jacob3' and ROWID = 'AAAR7kAAFAAAACHAAL';    'jacob3');

                                
SQL>begin
2     insert into "JACOB"."T1"("ID","NAME") values (1,'jacob1');
3     insert into "JACOB"."T1"("ID","NAME") values (2,'jacob2');
4     insert into "JACOB"."T1"("ID","NAME") values (4,'jacob4');
5     insert into "JACOB"."T1"("ID","NAME") values (5,'jacob5');
6     insert into "JACOB"."T1"("ID","NAME") values (3,'jacob3');
7 end;
  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 jacob1
         2 jacob2
         4 jacob4
         5 jacob5
         3 jacob3