写在前面，在研究Oracle logmnr 的时候看到 http://www.askmaclean.com/archives/dbms_logmnr-unsupported-sqlredo.html 的文章，其中有一句
不知道maclean测试的是哪个版本的数据库，我测试的情况是可以的。
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看是否启用 supplemental log
SQL> select supplemental_log_data_min from v$database; SUPPLEME
--------
NO SQL>
创建测试实例
[oracle@localhost ~]$ sqlplus scott/tiger SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 11 02:00:49 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set linesize 180;
SQL> set pagesize 80;
SQL> select table_name from user_tables; TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
DEPT_2 SQL> create table emp2 as select * from emp where 1 =2; Table created. SQL> insert into emp2 select * from emp; 14 rows created. SQL> commit; Commit complete. SQL> delete from emp where deptno =30; 6 rows deleted. SQL> commit; Commit complete.
查看测试结果
SQL> begin 2 dbms_logmnr.add_logfile(logfilename=>'/u01/app/flash_recovery_area/ORCL/archivelog/2014_10_11/o1_mf_1_12_b3kwq3qz_.arc',options=>dbms_logmnr.NEW);
3 dbms_logmnr.add_logfile(logfilename=>'/u01/app/flash_recovery_area/ORCL/archivelog/2014_10_11/o1_mf_1_11_b3kv67v1_.arc',options=>dbms_logmnr.ADDFILE);
4 end;
5 / PL/SQL procedure successfully completed. SQL> select sql_redo from v$logmnr_contents t where t.seg_name ='EMP2';
select sql_redo from v$logmnr_contents t where t.seg_name ='EMP2'
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents SQL> execute dbms_logmnr.start_logmnr(options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only); PL/SQL procedure successfully completed. SQL> select sql_redo from v$logmnr_contents t where t.seg_name ='EMP2'; SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create table emp2 as select * from emp where 1 =2;
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','SMITH','CLERK','',TO_DATE('17-DEC-80', 'DD-MON-RR'),'',NULL,'');
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','ALLEN','SALESMAN','',TO_DATE('20-FEB-81', 'DD-MON-RR'),'','','30
'); insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','WARD','SALESMAN','',TO_DATE('22-FEB-81', 'DD-MON-RR'),'','',''
); insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','JONES','MANAGER','',TO_DATE('02-APR-81', 'DD-MON-RR'),'',NULL,'')
; SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','MARTIN','SALESMAN','',TO_DATE('28-SEP-81', 'DD-MON-RR'),'','',''); insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','BLAKE','MANAGER','',TO_DATE('01-MAY-81', 'DD-MON-RR'),'',NULL,'')
; insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','CLARK','MANAGER','',TO_DATE('09-JUN-81', 'DD-MON-RR'),'',NULL,'')
; insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','SCOTT','ANALYST','',TO_DATE('19-APR-87', 'DD-MON-RR'),'',NULL,'')
; SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','KING','PRESIDENT',NULL,TO_DATE('17-NOV-81', 'DD-MON-RR'),'',NULL,'');
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','TURNER','SALESMAN','',TO_DATE('08-SEP-81', 'DD-MON-RR'),'','',''
); insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','ADAMS','CLERK','',TO_DATE('23-MAY-87', 'DD-MON-RR'),'',NULL,'');
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','JAMES','CLERK','',TO_DATE('03-DEC-81', 'DD-MON-RR'),'',NULL,'');
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','FORD','ANALYST','',TO_DATE('03-DEC-81', 'DD-MON-RR'),'',NULL,'');
insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('','MILLER','CLERK','',TO_DATE('23-JAN-82', 'DD-MON-RR'),'',NULL,''); 15 rows selected.
同样 对于david 的文章貌似也存在描述的不妥的情况
http://blog.****.net/tianlesoftware/article/details/6554674
不知道是不是也是测试的环境不同。
还是说我的测试具有偶然性。
经过测试至少可以证明几件事情：（基于当前测试环境 Oracle 11.2）
1.必须要创建utl_file_dir，创建之后必须重启数据库。
2.即便是没有启用supplemental log，在没有其他更好的办法的时候还是可以尝试logmnr恢复特定表的特定数据。