Using Oracle LogMiner
I just gave an Oracle backup & recovery workshop for a customer, talking about Oracle in general, DataPump and Recovery Manager (RMAN). At the end we came across LogMiner to find a certain SCN of a DDL statement. As I did not use LogMiner for a while, lets review this great tool.
LogMiner allows you to look inside your redo logs and review DML and DDL statements.
Setting up LogMiner
Check if you have configured a directory where Oracle can read and write (this are not the directories created with CREATE DIRECTORY):
SQL> show parameter utl_file_dir NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ utl_file_dir string
Value is empty? Set Oracle parameter UTL_FILE_DIR in (server) parameter file, bounce the instance and run utlfile.sql:
$ sqlplus / as sysdba SQL> alter system set utl_file_dir = '/tmp' scope = spfile; SQL> shutdown SQL> startup SQL> @?/rdmbs/admin/utlfile
Next you need to setup LogMiner dictionary four your instance to map object ids to names and provide human-readable insight to the logs — if you don’t build it you see something like this:
SQL> select sql_redo from v$logmnr_contents; SQL_REDO -------------------------------------------------------------------------------- set transaction read write; insert into UNKNOWN.Objn:25128(Col[1]) values (HEXTORAW('c102')); commit;
instead of:
SQL> select sql_redo from v$logmnr_contents; SQL_REDO -------------------------------------------------------------------------------- set transaction read write; insert into test1 values (1); commit; $ sqlplus / as sysdba
Build the dictionary with DBMS_LOGMNR_D.BUILD:
SQL> exec dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location => '/tmp'); PL/SQL procedure successfully completed.
LogMiner views
What data dictionary views do we have got?
SQL> select view_name from all_views where view_name like '%LOGMNR%'; VIEW_NAME ------------------------------ V_$LOGMNR_CALLBACK V_$LOGMNR_CONTENTS V_$LOGMNR_DICTIONARY V_$LOGMNR_DICTIONARY_LOAD V_$LOGMNR_LATCH V_$LOGMNR_LOGFILE V_$LOGMNR_LOGS V_$LOGMNR_PARAMETERS V_$LOGMNR_PROCESS V_$LOGMNR_REGION V_$LOGMNR_SESSION V_$LOGMNR_STATS V_$LOGMNR_TRANSACTION DBA_LOGMNR_LOG DBA_LOGMNR_PURGED_LOG DBA_LOGMNR_SESSION 13 rows selected.
I will just use V$LOGMNR_CONTENTS in this blog entry. See V$LOGMNR_CONTENTS in Oracle Database Reference 11g Release 2 (11.2) for an explanation of all columns in this view.
A simple session with LogMiner
Take a look at the available logfile by querying V$LOGFILE and V$ARCHIVED_LOG:
SQL> select sequence#,name,status,deleted from v$archived_log; SEQUENCE# NAME S DEL ---------- --------------------------------------------------------------------------- - --- 7 /u04/app/oracle/orafra/PROD/archivelog/2011_05_11/o1_mf_1_7_6wnn7thl_.arc A NO 8 /u04/app/oracle/orafra/PROD/archivelog/2011_05_11/o1_mf_1_8_6wnt0o61_.arc A NO 9 /u04/app/oracle/orafra/PROD/archivelog/2011_05_11/o1_mf_1_9_6wnt1fr5_.arc A NO [...] 40 /u04/app/oracle/orafra/PROD/archivelog/2011_05_13/o1_mf_1_40_6wsqps23_.arc A NO 41 /u04/app/oracle/orafra/PROD/archivelog/2011_05_13/o1_mf_1_41_6wsqsccn_.arc A NO 42 /u04/app/oracle/orafra/PROD/archivelog/2011_05_13/o1_mf_1_42_6wsqsgxv_.arc A NO SQL> select f.group#,l.sequence#,f.member from v$logfile f inner join v$log l on f.group# = l.group#; GROUP# SEQUENCE# MEMBER ---------- ---------- ---------------------------------------------------------------------- 1 43 /u02/app/oracle/oradata/PROD/onlinelog/o1_mf_1_6wlfqxho_.log 1 43 /u02/app/oracle/oradata/PROD/onlinelog/o1_mf_1_6wlfqxww_.log 2 42 /u02/app/oracle/oradata/PROD/onlinelog/o1_mf_2_6wlfr060_.log 2 42 /u02/app/oracle/oradata/PROD/onlinelog/o1_mf_2_6wlfr21y_.log
We can see that logs from sequence #7 up to #42 are archived and they are NOT DELeted, so they are available for analysis. The actual log sequence #43 is available in redo log group #1.
Start LogMiner
At first add the logfiles you want to analyze, take care: first logfile is added with option DBMS_LOGMNR.NEW, others with DBMS_LOGMNR.ADDFILE. I want to analyze the last four log sequences (3 archived, 1 online log sequence):
SQL> exec dbms_logmnr.add_logfile(logfilename => '/u04/app/oracle/orafra/PROD/archivelog/2011_05_13/o1_mf_1_40_6wsqps23_.arc', options => dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename => '/u04/app/oracle/orafra/PROD/archivelog/2011_05_13/o1_mf_1_41_6wsqsccn_.arc', options => dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename => '/u04/app/oracle/orafra/PROD/archivelog/2011_05_13/o1_mf_1_42_6wsqsgxv_.arc', options => dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename => '/u02/app/oracle/oradata/PROD/onlinelog/o1_mf_1_6wlfqxho_.log', options => dbms_logmnr.addfile); PL/SQL procedure successfully completed.
Start a session using the previously created dictionary logfile for the instance:
SQL> exec dbms_logmnr.start_logmnr(dictfilename => '/tmp/dictionary.ora'); SQL> select scn, sql_redo from v$logmnr_contents where ... ;
Alternatively you can start a session and give some contraints on time or SCN:
SQL> exec dbms_logmnr.start_logmnr(dictfilename => '/tmp/dictionary.ora' 2 , starttime => to_date('01-Feb-2011 08:00:00', 'DD-MON-YYYY HH:MI:SS') 3 , endtime => to_date('01-Feb-2011 10:00:00', 'DD-MON-YYYY HH:MI:SS'));
LogMiner shows only DML statements and transaction control from redo and undo. DDL (i.e. DROP TABLE) is a DML on data dictionary items! To find DDL statements you have to query for DML against SYS.TAB$ for example.
SQL> column seg_name format a25 trunc SQL> col seg_name format a10 SQL> col seg_owner format a20 SQL> col table_space format a15 SQL> col operation format a11 SQL> select seg_name , seg_type , seg_owner , table_name , table_space , operation , scn from v$logmnr_contents where operation != 'INTERNAL' and seg_name in ('COL$','OBJ$','TAB$') group by seg_name , seg_type , seg_owner , table_name , table_space , operation , scn order by scn; SEG_NAME SEG_TYPE SEG_OWNER TABLE_NAME TABLE_SPACE OPERATION SCN ---------- ---------- -------------------- -------------------------------- --------------- ----------- ---------- OBJ$ 2 SYS OBJ$ SYSTEM INSERT 407518 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407521 OBJ$ 2 SYS OBJ$ SYSTEM INSERT 407524 OBJ$ 2 SYS OBJ$ SYSTEM INSERT 407528 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407535 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407539 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407543 OBJ$ 2 SYS OBJ$ SYSTEM INSERT 407594 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407596 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407598 OBJ$ 2 SYS OBJ$ SYSTEM UPDATE 407600
End your session with DBMS_LOGMNR.END_LOGMNR or just start a new one with DBMS_LOGMNR.START_LOGMNR.
SQL> exec dbms_logmnr.end_logmnr;
I can’t find a dropped table with LogMiner
Maybe you’re using the Flashback feature and the table was moved into recycle bin:
SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on
The recyclebin is turned on. So dropped tables will not be dropped, they are renamed:
SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST BIN$oyQvIAiLn73gQCi8yGgVVw==$0 TABLE 2011-05-13:09:23:36
Recover it using the FLASHBACK TABLE command:
SQL> flashback table ttt to before drop;
Finding a log sequence by time
To find certain statements you need to locate the needed logfile(s). At first you may know the time when a statement was issued. The data dictionary views V$ARCHVIED_LOG and V$BACKUP_REDOLOG give a lot of information on what log sequence and SCNs (system change numbers) belong to which time frame:
SQL> set linesize 132 SQL> col name format a70 SQL> alter session set nls_date_format = 'DD.MM.YY HH24:MI'; SQL> select name, sequence#, first_change#, first_time from v$archived_log; [...] NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME ---------------------------------------------------------------------- ---------- ------------- -------------- 35 270339 15.02.11 15:04 36 270523 15.02.11 15:08 37 275453 15.02.11 15:26 38 278957 15.02.11 15:28 39 283483 15.02.11 16:37 40 283681 15.02.11 16:43 /u02/app/oracle/fra/AOC/archivelog/2011_02_16/o1_mf_1_41_6or9x348_.arc 41 283705 15.02.11 16:43 /u02/app/oracle/fra/AOC/archivelog/2011_02_16/o1_mf_1_42_6orb7r4w_.arc 42 337380 16.02.11 20:38 /u02/app/oracle/fra/AOC/archivelog/2011_02_17/o1_mf_1_43_6otb8y65_.arc 43 337619 16.02.11 20:43 /u02/app/oracle/fra/AOC/archivelog/2011_02_18/o1_mf_1_44_6ox4wn28_.arc 44 397223 17.02.11 14:56 /u02/app/oracle/fra/AOC/archivelog/2011_02_18/o1_mf_1_45_6ox4xqth_.arc 45 450667 18.02.11 16:43 39 rows selected.
This query shows us that log sequences from 41 to 45 are available on disk in the FRA (flash recovery area). Our online redo logs are:
SQL> set linesize 132 SQL> col member format a70 SQL> alter session set nls_date_format = 'DD.MM.YY HH24:MI:SS'; SQL> select l.group#, sequence#, first_change#, first_time, min(member) member from v$log l inner join v$logfile f on l.group# = f.group# group by l.group#, sequence#, first_change#, first_time; GROUP# SEQUENCE# FIRST_CHANGE# FIRST_TIME MEMBER ---------- ---------- ------------- ----------------- ------------------------------------------------------- 2 46 450705 18.02.11 16:43:51 /u02/app/oracle/fra/AOC/onlinelog/o1_mf_2_6om6m588_.log 1 45 450667 18.02.11 16:43:15 /u02/app/oracle/fra/AOC/onlinelog/o1_mf_1_6om6m1tr_.log
Use LogMiner like shown above to take a look into the log. Now I want to see the statements, look at the column SQL_REDO in V$LOGMNR_CONTENTS:
SQL> select sql_redo from v$logmnr_contents where scn=407518; SQL_REDO --------------------------------------------------------------------------------------------------------------------------- commit; set transaction read write; insert into "SYS"."SCHEDULER$_EVENT_LOG"("LOG_ID","LOG_DATE","TYPE#","NAME","OWNER","CLASS_ID","OPERATION","STATUS","USER_NAME","CLI ENT_ID","GUID","DBID","FLAGS","CREDENTIAL","DESTINATION","ADDITIONAL_INFO") values ('99',TO_TIMESTAMP_TZ('12-MAY-11 10.00.06.423812 PM +02:00'),'66','DRA_REEVALUATE_OPEN_FAILURES','SYS','12166','RUN','SUCCEEDED',NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL); insert into "SYS"."SCHEDULER$_JOB_RUN_DETAILS"("LOG_ID","LOG_DATE","REQ_START_DATE","START_DATE","RUN_DURATION","INSTANCE_ID","SESSI ON_ID","SLAVE_PID","CPU_USED","ERROR#","ADDITIONAL_INFO","CREDENTIAL","DESTINATION") values ('99',TO_TIMESTAMP_TZ('12-MAY-11 10.00.0 6.536282 PM +02:00'),NULL,TO_TIMESTAMP_TZ('12-MAY-11 10.00.03.047298 PM EUROPE/VIENNA'),TO_DSINTERVAL('+000 00:00:03'),'1','160,747' ,'22112',TO_DSINTERVAL('+000 00:00:00.08'),'0',NULL,NULL,NULL); insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWN ER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('13098',NULL,'0','ORA$AT_OS_OPT_SY_7',' 1',NULL,'66',TO_DATE('12-MAY-11', 'DD-MON-RR'),TO_DATE('12-MAY-11', 'DD-MON-RR'),TO_DATE('12-MAY-11', 'DD-MON-RR'),'1',NULL,NULL,'0' ,NULL,'6','65535','0',NULL,NULL,NULL);
If you need to backout that change, query the column SQL_UNDO:
SQL_UNDO --------------------------------------------------------------------------------------------------------------------------- delete from "SYS"."SCHEDULER$_EVENT_LOG" where "LOG_ID" = '99' and "LOG_DATE" = TO_TIMESTAMP_TZ('12-MAY-11 10.00.06.423812 PM +02:00 ') and "TYPE#" = '66' and "NAME" = 'DRA_REEVALUATE_OPEN_FAILURES' and "OWNER" = 'SYS' and "CLASS_ID" = '12166' and "OPERATION" = 'RU N' and "STATUS" = 'SUCCEEDED' and "USER_NAME" IS NULL and "CLIENT_ID" IS NULL and "GUID" IS NULL and "DBID" IS NULL and "FLAGS" = '0 ' and "CREDENTIAL" IS NULL and "DESTINATION" IS NULL and ROWID = 'AAABbfAACAAAAzOAAO'; delete from "SYS"."SCHEDULER$_JOB_RUN_DETAILS" where "LOG_ID" = '99' and "LOG_DATE" = TO_TIMESTAMP_TZ('12-MAY-11 10.00.06.536282 PM +02:00') and "REQ_START_DATE" IS NULL and "START_DATE" = TO_TIMESTAMP_TZ('12-MAY-11 10.00.03.047298 PM EUROPE/VIENNA') and "RUN_DURA TION" = TO_DSINTERVAL('+000 00:00:03') and "INSTANCE_ID" = '1' and "SESSION_ID" = '160,747' and "SLAVE_PID" = '22112' and "CPU_USED" = TO_DSINTERVAL('+000 00:00:00.08') and "ERROR#" = '0' and "ADDITIONAL_INFO" IS NULL and "CREDENTIAL" IS NULL and "DESTINATION" IS NULL and ROWID = 'AAABbjAACAAAAztAAI'; delete from "SYS"."OBJ$" where "OBJ#" = '13098' and "DATAOBJ#" IS NULL and "OWNER#" = '0' and "NAME" = 'ORA$AT_OS_OPT_SY_7' and "NAM ESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '66' and "CTIME" = TO_DATE('12-MAY-11', 'DD-MON-RR') and "MTIME" = TO_DATE('12-MAY -11', 'DD-MON-RR') and "STIME" = TO_DATE('12-MAY-11', 'DD-MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NU LL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '65535' and "SPARE3" = '0' and "SPARE4" IS NULL and "SPARE 5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAEg1AAL';
Optimize
It’s best to create a (temporary) table of the contents of v$logmnr_contents to save time and performance when querying the logs:
SQL> create table mylog as select ... from v$logmnr_contents;
Take care
It’s very important to end the LogMiner session with DBMS_LOGMNR.END_LOGMNR, as you will get an ORA-600 error when logging off:
SQL> exec dbms_logmnr.end_logmnr();
Trying to use LogMiner concurrently from another session will give a ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents.
See MOS Note 62508.1 “The LogMiner Utility” for details.
Recover the database using RMAN
After finding the correct log sequence or SCN use it with RMAN’s UNTIL-clause like this:
RMAN> recover database until scn 220123;
or
RMAN> recover database until logseq 43;
Resources
19 Using LogMiner to Analyze Redo Log Files in Oracle Database Utilities 11g Release 2 (11.2)
