微客导航 » 文章资讯 » 如何使用log miner分析oracle日志

如何使用log miner分析oracle日志

2024-03-29 04:02:05 418

当我们不小心误操作致使数据库数据丢失、改变时，需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对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.

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

返回顶部
3162201930
czq8825@qq.com

如何使用log miner分析oracle日志

热门推荐

随机推荐