dbms_logmnr Unsupported SQLREDO

Question:

Try Testing Oracle Logminer

 

SQL> create table maclean (t1 varchar2(100)) tablespace users;

Table created.

SQL> insert into maclean values (‘MACLEAN’);

1 row created.

SQL> commit;

Commit complete.

after start logmnr:
sql> …add log file ….
sql> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
sql> select sql_redo from v$logmnr_contents;
create table maclean (t1 varchar2(100)) tablespace users;
Unsupported
commit;
In the system, some statement will got sqltext from sqlredo column and other is ‘Unsupported’. The got sqltext have INSERT, DELETE, UPDATE; and ‘Unsupported’ sql have INSERT, DELETE, UPDATE too.

 

Answer:

Have you enabled supplemental logging prior to mining the redo / archive logs ? If not then please enable the supplemental logging.

To enable minimal supplemental logging execute the following SQL statement:
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

If you had not enabled supplemental logging earlier then the redo logs will not contain sufficient information to mine the logs. Additionally logminer will not always populate all the fields of the v$logmnr_contents this is because the redo may/may not have all the information that we need for every column. Adding Supplemental Logging will help in more info being logged in the redo being generated, helping populate more values.

 

Exactly, supplemental logging is mandatory, Oracle recommends that you at least enable minimal supplemental logging for LogMiner. By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging prior to generating log files which will be analyzed by LogMiner.
Once supplemental logging is enabled the log file generated later on will be elgible for log miner.

 

You must enable at least database minimal supplemental logging prior to generate log files which will be analyzed by LogMiner.

 

 

Solution
connect / as sysdba

-- enable minimal supplemental logging at database level
alter database add supplemental log data;

select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

-- switch logfile to get a new fresh archived log
alter system switch logfile;

connect test/test

-- insert some rows into table
insert into emp values (3, 'Text 3');
insert into emp values (4, 'Text 4');
commit;

connect / as sysdba

-- switch again logfile to get a minimal redo activity
alter system switch logfile;

-- mine the last written archived log
exec dbms_logmnr.add_logfile ('D:DatabasesO102arcO102__1__24__676053397.ARC', options => dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
select operation, sql_redo from v$logmnr_contents where seg_name = 'EMP';

select operation, sql_redo from v$logmnr_contents where seg_name = 'EMP';

OPERATION
--------------------------------
SQL_REDO
------------------------------------------------------------------------------
INSERT
insert into "TEST"."EMP"("EMPNO","EMPNAME") values ('3','Text 3');

INSERT
insert into "TEST"."EMP"("EMPNO","EMPNAME") values ('4','Text 4');

2 rows selected.

How to setup Oracle Streams Bi-Directional

构建Oracle双向流复制是一个十分复杂的过程,我写这个文档的目的是尽量有条理地列出所需做的工作,帮助DBA更有效的建设流复制环境。

1.以scott模式为复制示例,一般只要在创建数据库时选择了安装sample schema,都会存在该scott模式;至少保证源库中存在该schema,以便可以初始化到目标库中。

2.在源和目标2个数据库中创建strmadmin流管理用户,当然你也可以选用其他名字。同时在2个库中都要创建streams使用的表空间,以便让logmnr使用它:
[Read more…]

沪ICP备14014813号

沪公网安备 31010802001379号