如何使用log miner分析oracle日志
当我们不小心误操作致使数据库数据丢失、改变时，需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对oracle日志进行分析，而获得无操作的精确时间点。
oracledb提供了一个分析日志包logmnr
logminer工具的使用
-------对redolog进行挖掘，找出在某个时间点所作的DDL或DML操作（包括：时间点、datablockscn、sql语句）
实验测试
SQL>selectnamefromv$archived_log; NAME -------------------------------------------------- /oracle/arch1/1_2_883536782.dbf SQL> SQL> SQL>deletefromscott.t1; 576rowsdeleted. SQL>altersystemarchivelogcurrent; Systemaltered. SQL>createtablescott.t6asselect*fromscott.emp; Tablecreated. SQL>altersystemarchivelogcurrent; Systemaltered. SQL>selectnamefromv$archived_log; NAME -------------------------------------------------- /oracle/arch1/1_2_883536782.dbf /oracle/arch1/1_3_883536782.dbf /oracle/arch1/1_4_883536782.dbf
--启动logminer添加要分析的日志
SQL>executedbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_2_883536782.dbf',options=>dbms_logmnr.new); PL/SQLproceduresuccessfullycompleted.
--添加需要分析的日志
SQL>executedbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_3_883536782.dbf',options=>dbms_logmnr.addfile); PL/SQLproceduresuccessfullycompleted. SQL>executedbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_4_883536782.dbf',options=>dbms_logmnr.addfile) PL/SQLproceduresuccessfullycompleted.
--执行logminer
SQL>executedbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); PL/SQLproceduresuccessfullycompleted.
--查询分析结果
SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';
Sessionaltered.
SQL>selectusername,scn,timestamp,sql_redofromv$logmnr_contentswhereseg_name='T1';
USERNAMESCNTIMESTAMP
-----------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
0'and"COMM"ISNULLand"DEPTNO"='10'andROWID='AAAVbSAAFAAAACXABi';
SYS14945452015-06-2804:24:44
deletefrom"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
0'and"COMM"ISNULLand"DEPTNO"='10'andROWID='AAAVbSAAFAAAACXABj';
SYS14945452015-06-2804:24:44
deletefrom"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
。。。。。
--结束logminer分析
SQL>executedbms_logmnr.end_logmnr; PL/SQLproceduresuccessfullycompleted.
以上就是本文的全部内容，希望大家可以喜欢。