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.logINACTIVE         /u01/app/oracle/oradata/orcl/redo02.logINACTIVE         /u01/app/oracle/oradata/orcl/redo01.logSQL> select * from t1;        ID NAME---------- --------------------         1 jacob1         2 jacob2         4 jacob4         5 jacob5         3 jacob3SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1136339SQL> delete from t1;5 rows deleted.SQL> commit;Commit complete.SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1136355SQL> 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.logINACTIVE         /u01/app/oracle/oradata/orcl/redo02.logINACTIVE         /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 a50SQL> col sql_undo for a50SQL> 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>begin2     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