Using Oracle logminor to retrieve committed DML Oct 11, 2011
Logminer is a utility that can read Oracle REDO log files. REDO log files contain all committed transaction.
In a previous tip, I talked about using the APPEND hint with INSERT statement. If you followed the discussion about APPEND, Oracle appends the inserted transactions to the end of the Data files while skipping the process of adding the inserted (and committed) rows into the REDO logfile.
In this tip, we will try to use logminer just to prove if Oracle really skips writing to log files when the APPEND hint is used.
Let us start
SQL> CREATE TABLE b (F1 NUMBER);
Table Created
SQL>insert /*+ append */ into b select 200 from dual;
It is worth noting at this stage that i you try to SELECT data inserted with APPEND hint before committing, Oracle will not give you back the results
SQL> select * from b;
select * from b
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
Commit complete.
Now, i will also INSERT a row without APPEND
SQL> insert into b select 300 from dual;
1 row created.
SQL> commit;
Commit complete.
Let us now explore the contents of the log files using logminor
Connect / as sysdba
We will find out the name of the current REDO LOG file which will become the subject of our investigation
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
-------- -------- --------- -------- -------- --- ---------------- -------------
---------
1 1 125 52428800 1 NO INACTIVE 3971753 12-OCT-11
2 1 126 52428800 1 NO CURRENT 3973216
12-OCT-11
3 1 124 52428800 1 NO INACTIVE 3968473 12-OCT-11
The second row is the current redo log file. To get the name , we issue another
Query againt the data dictionary
SQL> select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
-------- ------- -------
------------------------------------------------------------ ---
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG NO
2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
NO
1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG NO
therefore, the redo log of interest is REDO02.log
once the redo log is identified, we will start Redo log mining session.
You can follow the following steps
1) build a file based dictionary to be used by logminor. Note that the first argument is the dictionary name and the second argument is its path. Note you will probably need to set the UTL_FILE_DIR parameter in order to allow oracle to access the OS file system.
begin
dbms_logmnr_d.build ('dictionary.ora','c:\oracle\product\');
end;
/
PL/SQL procedure successfully completed.
2) Add the log file under investigation to the mining session
SQL>begin
dbms_logmnr.add_logfile( logfilename
=>
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG');
end;
/
PL/SQL procedure successfully completed.
3) Start mining session
begin
dbms_logmnr.start_logmnr(
dictfilename => 'c:\oracle\product\dictionary.ora');
end;
/
PL/SQL procedure successfully completed.
4) now you can extract the REDO log information from the V$logmnr_contents dynamic table
SQL> select sql_redo, sql_undo
from v$logmnr_contents
where seg_name = 'B';
SQL_REDO
----------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------
create table b (F1 number);
insert into "SCOTT"."B"("F1") values ('300');
delete from "SCOTT"."B" where "F1" = '300' and ROWID = 'AAANWyAAEAAAClFAAA';
Note that the insert /*+ append */ into b select 200 from dual;
5) End logmining session
SQL> exec dbms_logmnr.end_logmnr;
under SQL_REDO, due to the append, while the insert into b select 300 from dual;
appears in the REDO log. IF you are new to logminor, it is worth noting that the under SQL_UNDO, you will find a statement the you can use in order remove the effect of the original INSERT statement.
Hope you have enjoyed this tip
Ammar Sajdi