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;

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',
  2  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',
  2  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',
  2  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', 
  2  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

Chapter 19 Using LogMiner to Analyze Redo Log Files in Oracle Database Utilities 11g Release 2 (11.2)

This entry was posted in Databases, System Administration and tagged , . Bookmark the permalink.