您的位置: 首页 > IT文章 > 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

分类: IT文章 • 2023-11-08 09:45:52

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

View Code

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

相关推荐