oracle dbms_logmnr,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:\Databases\O102\arc\O102__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.