首页 > 代码库 > Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable

Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable

2024-07-26 15:56:18   217人阅读

写在前面，在研究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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
View Code

查看是否启用 supplemental log

SQL> select supplemental_log_data_min from v$database;SUPPLEME--------NOSQL>

创建测试实例

[oracle@localhost ~]$ sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 11 02:00:49 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> set linesize 180;SQL> set pagesize 80;SQL> select table_name from user_tables;TABLE_NAME------------------------------DEPTEMPBONUSSALGRADEDEPT_2SQL> 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.
View Code

 

查看测试结果

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_contentsSQL> 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 (‘7369‘,‘SMITH‘,‘CLERK‘,‘7902‘,TO_DATE(‘17-DEC-80‘, ‘DD-MON-RR‘),‘800‘,NULL,‘20‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7499‘,‘ALLEN‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘20-FEB-81‘, ‘DD-MON-RR‘),‘1600‘,‘300‘,‘30‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7521‘,‘WARD‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘22-FEB-81‘, ‘DD-MON-RR‘),‘1250‘,‘500‘,‘30‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7566‘,‘JONES‘,‘MANAGER‘,‘7839‘,TO_DATE(‘02-APR-81‘, ‘DD-MON-RR‘),‘2975‘,NULL,‘20‘);SQL_REDO------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7654‘,‘MARTIN‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘28-SEP-81‘, ‘DD-MON-RR‘),‘1250‘,‘1400‘,‘30‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7698‘,‘BLAKE‘,‘MANAGER‘,‘7839‘,TO_DATE(‘01-MAY-81‘, ‘DD-MON-RR‘),‘2850‘,NULL,‘30‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7782‘,‘CLARK‘,‘MANAGER‘,‘7839‘,TO_DATE(‘09-JUN-81‘, ‘DD-MON-RR‘),‘2450‘,NULL,‘10‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7788‘,‘SCOTT‘,‘ANALYST‘,‘7566‘,TO_DATE(‘19-APR-87‘, ‘DD-MON-RR‘),‘3000‘,NULL,‘20‘);SQL_REDO------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7839‘,‘KING‘,‘PRESIDENT‘,NULL,TO_DATE(‘17-NOV-81‘, ‘DD-MON-RR‘),‘5000‘,NULL,‘10‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7844‘,‘TURNER‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘08-SEP-81‘, ‘DD-MON-RR‘),‘1500‘,‘0‘,‘30‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7876‘,‘ADAMS‘,‘CLERK‘,‘7788‘,TO_DATE(‘23-MAY-87‘, ‘DD-MON-RR‘),‘1100‘,NULL,‘20‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7900‘,‘JAMES‘,‘CLERK‘,‘7698‘,TO_DATE(‘03-DEC-81‘, ‘DD-MON-RR‘),‘950‘,NULL,‘30‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7902‘,‘FORD‘,‘ANALYST‘,‘7566‘,TO_DATE(‘03-DEC-81‘, ‘DD-MON-RR‘),‘3000‘,NULL,‘20‘);insert into "SCOTT"."EMP2"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7934‘,‘MILLER‘,‘CLERK‘,‘7782‘,TO_DATE(‘23-JAN-82‘, ‘DD-MON-RR‘),‘1300‘,NULL,‘10‘);15 rows selected.
View Code

 

同样 对于david 的文章貌似也存在描述的不妥的情况

http://blog.csdn.net/tianlesoftware/article/details/6554674

不知道是不是也是测试的环境不同。

还是说我的测试具有偶然性。

经过测试至少可以证明几件事情：（基于当前测试环境 Oracle 11.2）

1.必须要创建utl_file_dir，创建之后必须重启数据库。

2.即便是没有启用supplemental log，在没有其他更好的办法的时候还是可以尝试logmnr恢复特定表的特定数据。

 

Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable


联系
我们