当我们不小心误操作致使数据库数据丢失、改变时， 需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对oracle日志进行分析，而获得无操作的精确时间点。
oracle db提供了一个分析日志包logmnr
logminer 工具的使用
-------对redo log 进行挖掘，找出在某个时间点所作的DDL 或DML 操作（包括：时间点、datablock scn 、sql语句）
实验测试
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.
以上就是本文的全部内容，希望大家可以喜欢。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！
如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
狼山资源网 Copyright www.pvsay.com
暂无“如何使用log miner分析oracle日志”评论...
P70系列延期，华为新旗舰将在下月发布
3月20日消息，近期博主@数码闲聊站 透露，原定三月份发布的华为新旗舰P70系列延期发布，预计4月份上市。
而博主@定焦数码 爆料，华为的P70系列在定位上已经超过了Mate60，成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢？
根据目前爆料的消息来看，华为P70系列将推出三个版本，其中P70和P70 Pro采用了三角形的摄像头模组设计，而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智，但辨识度绝对拉满。