Using the LogMiner to search for transactions in the Oracle database
By: Date: February 27, 2016 Categories: LogMiner,Oracle

Oracle offers the dbms_logmnr package to look inside the archivelogs of the database. you can only view every DML with the LogMiner if you have previously enabled at least minimal supplemental logging. To verify if it is enabled you can run the following query:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
 SUPPLEME
 --------
 YES

 

Now let’s say we need to investigate what changes were made 4 days ago to the CUSTOMERS table between 11:20 and 11:27.

First we need to find out which archivelogs we need to look for:

SQL> SELECT TIMESTAMP_TO_SCN(to_timestamp('20/02/2016 11:20:00','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
 SCN
 ----------
 2722014

SQL> SELECT TIMESTAMP_TO_SCN(to_timestamp('20/02/2016 11:27:00','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
 SCN
 ----------
 2722182

Now that we have the first and last SCN we can connect with RMAN to check and restore the archivelogs.

Listing the archivelogs:

RMAN> list backup of archivelog from scn 2722014 until scn 2722182;

List of Archived Logs in backup set 3
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    21      2716806    20-FEB-16 2722045    20-FEB-16
 1    22      2722045    20-FEB-16 2722115    20-FEB-16
 1    23      2722115    20-FEB-16 2722245    20-FEB-16

Restoring the archivelogs to /export/home/oracle:

run
 {
 set archivelog destination to '/export/home/oracle';
 restore archivelog from scn 2722014 until scn 2722182;
 }

 

Now that we have the archivelogs we can start setting up the LogMiner by adding them:

SQL> BEGIN
 DBMS_LOGMNR.ADD_LOGFILE
 ('/export/home/oracle/1_21_882879408.dbf');
 END;
/

PL/SQL procedure successfully completed.

After adding all the required archivelogs (we can use the same command for all of the files) the LogMiner session can be started:

SQL> BEGIN
 DBMS_LOGMNR.START_LOGMNR
 ( options =>
 dbms_logmnr.dict_from_online_catalog );
 END;
 /

PL/SQL procedure successfully completed.

Before starting to search for transactions  check if we have all the archivelogs we needed:

SQL> select FILENAME from V$LOGMNR_LOGS;

FILENAME
 ----------------------------------------
 /export/home/oracle/1_21_882879408.dbf
 /export/home/oracle/1_22_882879408.dbf
 /export/home/oracle/1_23_882879408.dbf

 

From now on we can use the V$LOGMNR_CONTENTS view to look for any kind of DML or DDL operation:

SQL> set lines 300
col sql_redo format a60
select SCN,
to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') as timestamp,
sql_redo,
SESSION_INFO
from V$LOGMNR_CONTENTS
where SEG_NAME = 'CUSTOMERS'
order by scn;

       SCN TIMESTAMP            SQL_REDO                                                SESSION_INFO
---------- -------------------- ------------------------------------------------------- --------------------
   2716904 20-FEB-2016 11:23:11 insert into "SYS"."CUSTOMERS"("CUSTOMER_ID","CUSTOMER_N login_username=SYS c
                                AME","CITY") values ('1235','Customer2','London');      lient_info= OS_usern
                                                                                        ame=oracle Machine_n
                                                                                        ame=orasol1 OS_termi
                                                                                        nal=pts/1 OS_process
                                                                                        _id=4725 OS_program_
                                                                                        name=sqlplus@orasol1
                                                                                        (TNS V1-V3)

   2722042 20-FEB-2016 11:25:09 update "SYS"."CUSTOMERS" set "CITY" = 'Paris' where "CI login_username=SYS c
                                TY" = 'London' and ROWID = 'AAAWfEAABAAAYeBAAB';        lient_info= OS_usern
                                                                                        ame=oracle Machine_n
                                                                                        ame=orasol1 OS_termi
                                                                                        nal=pts/1 OS_process
                                                                                        _id=4725 OS_program_
                                                                                        name=sqlplus@orasol1
                                                                                        (TNS V1-V3)

 

We can see that even session specific information can be extracted by using the column SESSION_INFO.

Leave a Reply

Your email address will not be published. Required fields are marked *