当我们不小心误操作致使数据库数据丢失、改变时， 需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对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.
以上就是本文的全部内容，希望大家可以喜欢。