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

浏览数：25 / 时间：2015年06月12日

写在前面，在研究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

View Code

查看是否启用 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.

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_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 (‘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.