当我们不小心误操作致使数据库数据丢失、改变时， 需要对数据库对象做基于时间点的恢复，找到我们需要的数据，这个时间点不能认为精确确定，我们可以通过对oracle日志进行分析，而获得无操作的精确时间点。
创新互联公司是一家专业提供
肥西企业网站建设,专注与成都网站建设、
网站建设、
H5高端网站建设、小程序制作等业务。10年已为肥西众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。
oracle db提供了一个分析日志包logmnr
logminer 工具的使用
-------对redo log 进行挖掘，找出在某个时间点所作的DDL 或DML 操作（包括：时间点、datablock scn 、sql语句）
实验测试
SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/oracle/arch2/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/arch2/1_2_883536782.dbf
/oracle/arch2/1_3_883536782.dbf
/oracle/arch2/1_4_883536782.dbf
--启动log miner添加要分析的日志
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch2/1_2_883536782.dbf',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
--添加需要分析的日志
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch2/1_3_883536782.dbf',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch2/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 hh34: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 hh34: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 hh34: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 hh34:mi:ss') and "SAL" = '1
。。。。。
--结束log miner 分析
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
名称栏目：使用logminer分析oracle日志-创新互联
网页网址：
http://cdkjz.cn/article/doihhc.html
