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