如何使用log miner分析oracle日志

日常生活中，我们经常因为操作失误丢失数据或者是更改数据，如何进行恢复呐?这篇文章就是帮助我们解决这一困扰,需要的朋友可以参考下

当我们不小心误操作致使数据库数据丢失、改变时， 需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对oracle日志进行分析，而获得无操作的精确时间点。

oracle db提供了一个分析日志包logmnr

logminer 工具的使用

-------对redo log 进行挖掘，找出在某个时间点所作的DDL 或DML 操作(包括：时间点、datablock scn 、sql语句)

实验测试

1. SQL>selectnamefromv$archived_log;
2. NAME--------------------------------------------------
3. /oracle/arch1/1_2_883536782.dbf
4. SQL>SQL>
5. SQL>deletefromscott.t1;576rowsdeleted.
6. SQL>altersystemarchivelogcurrent;Systemaltered.
7. SQL>createtablescott.t6asselect*fromscott.emp;Tablecreated.
8. SQL>altersystemarchivelogcurrent;Systemaltered.
9. SQL>selectnamefromv$archived_log;
10. NAME--------------------------------------------------
11. /oracle/arch1/1_2_883536782.dbf/oracle/arch1/1_3_883536782.dbf
12. /oracle/arch1/1_4_883536782.dbf

--启动log miner添加要分析的日志

1. SQL>executedbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_2_883536782.dbf',options=>dbms_logmnr.new);
2. PL/SQLproceduresuccessfullycompleted.

--添加需要分析的日志

1. SQL>executedbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_3_883536782.dbf',options=>dbms_logmnr.addfile);
2. PL/SQLproceduresuccessfullycompleted.
3. SQL>executedbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_4_883536782.dbf',options=>dbms_logmnr.addfile)
4. PL/SQLproceduresuccessfullycompleted.

--执行log miner

1. SQL>executedbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
2. PL/SQLproceduresuccessfullycompleted.

--查询分析结果

1. SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';Sessionaltered.
2. SQL>selectusername,scn,timestamp,sql_redofromv$logmnr_contentswhereseg_name='T1';USERNAMESCNTIMESTAMP
3. -----------------------------------------------------------SQL_REDO
4. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------deletefrom"SCOTT"."T1"where"EMPNO"='7782'and"ENAME"='CLARK'and"JOB"='MANAGER'and"MGR"='7839'and"HIREDATE"=TO_DATE('1981-06-0900:00:00','yyyy-mm-ddhh24:mi:ss')and"SAL"='245
5. 0'and"COMM"ISNULLand"DEPTNO"='10'andROWID='AAAVbSAAFAAAACXABi';
6. SYS14945452015-06-2804:24:44deletefrom"SCOTT"."T1"where"EMPNO"='7839'and"ENAME"='KING'and"JOB"='PRESIDENT'and"MGR"ISNULLand"HIREDATE"=TO_DATE('1981-11-1700:00:00','yyyy-mm-ddhh24:mi:ss')and"SAL"='500
7. 0'and"COMM"ISNULLand"DEPTNO"='10'andROWID='AAAVbSAAFAAAACXABj';
8. SYS14945452015-06-2804:24:44deletefrom"SCOTT"."T1"where"EMPNO"='7844'and"ENAME"='TURNER'and"JOB"='SALESMAN'and"MGR"='7698'and"HIREDATE"=TO_DATE('1981-09-0800:00:00','yyyy-mm-ddhh24:mi:ss')and"SAL"='1
9. 。。。。。

--结束log miner 分析

1. SQL>executedbms_logmnr.end_logmnr;
2. PL/SQLproceduresuccessfullycompleted.

以上就是本文的全部内容，希望大家可以喜欢。