如何使用log miner分析oracle日志 |
|
当我们不小心误操作致使数据库数据丢失、改变时， 需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对oracle日志进行分析，而获得无操作的精确时间点 。 SQL> select name from v$archived_log; NAME -------------------------------------------------- /oracle/arch1/1_2_883536782.dbf SQL> SQL> SQL> delete from scott.t1; 576 rows deleted. SQL> alter system archive log current; System altered. SQL> create table scott.t6 as select * from scott.emp; Table created. SQL> alter system archive log current; System altered. SQL> select name from v$archived_log; NAME -------------------------------------------------- /oracle/arch1/1_2_883536782.dbf /oracle/arch1/1_3_883536782.dbf /oracle/arch1/1_4_883536782.dbf --启动log miner添加要分析的日志 SQL> execute dbms_logmnr.add_logfile(logfilename=>/oracle/arch1/1_2_883536782.dbf,options=>dbms_logmnr.new); PL/SQL procedure successfully completed. --添加需要分析的日志 SQL> execute dbms_logmnr.add_logfile(logfilename=>/oracle/arch1/1_3_883536782.dbf,options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> execute dbms_logmnr.add_logfile(logfilename=>/oracle/arch1/1_4_883536782.dbf,options=>dbms_logmnr.addfile) PL/SQL procedure successfully completed. --执行log miner SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); PL/SQL procedure successfully completed. --查询分析结果 SQL> alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss; Session altered. SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=T1; USERNAME SCN TIMESTAMP ------------------------------ ---------- ------------------- SQL_REDO -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- delete from "SCOTT"."T1" where "EMPNO" = 7782 and "ENAME" = CLARK and "JOB" = MANAGER and "MGR" = 7839 and "HIREDATE" = TO_DATE(1981-06-09 00:00:00, yyyy-mm-dd hh24:mi:ss) and "SAL" = 245 0 and "COMM" IS NULL and "DEPTNO" = 10 and ROWID = AAAVbSAAFAAAACXABi; SYS 1494545 2015-06-28 04:24:44 delete from "SCOTT"."T1" where "EMPNO" = 7839 and "ENAME" = KING and "JOB" = PRESIDENT and "MGR" IS NULL and "HIREDATE" = TO_DATE(1981-11-17 00:00:00, yyyy-mm-dd hh24:mi:ss) and "SAL" = 500 0 and "COMM" IS NULL and "DEPTNO" = 10 and ROWID = AAAVbSAAFAAAACXABj; SYS 1494545 2015-06-28 04:24:44 delete from "SCOTT"."T1" where "EMPNO" = 7844 and "ENAME" = TURNER and "JOB" = SALESMAN and "MGR" = 7698 and "HIREDATE" = TO_DATE(1981-09-08 00:00:00, yyyy-mm-dd hh24:mi:ss) and "SAL" = 1 。 。 。 。 。 --结束log miner 分析 SQL> execute dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed. 以上就是本文的全部内容，希望大家可以喜欢 。 |
