How to use LogMiner to Restore Data

How to use LogMiner to Restore Data

Oracle8i provides a new tool to find out what Oracle is writing to the redo log files called LogMiner. The log files store all the data needed to perform a database recovery and record all changes (INSERT, UPDATE and DELETE) made to the database.

LogMiner is a set of PL/SQL packages and dynamic (V$) views. The packages are installed with the installation of the database. The views however will be created on the moment you startup LogMiner by using the DBMS_LOGMNR.START_LOGMNR procedure.

LogMiner can be used against Online or Offline log files from either the 'current' database or a 'foreign' database.

The most important package is DBMS_LOGMNR.START_LOGMNR. This one will create the necessary views you can query later on. To be able to read the views you will have to setup a meta-data file before starting LogMiner.

1.      Create meta-data file

Run the procedure DBMS_LOGMNR_D.BUILD as SYS.

  SQL> set serveroutput on

  SQL> CONNECT SYS

 

BEGIN

dbms_logmnr_d.build (DICTIONARY_FILENAME=>'Dictionary.ora’, 

DICTIONARY_LOCATION =>'//u01/app/oracle/common/utl_file', 

OPTIONS =>DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

END;

NOTE: DBMS_LOGMNR_D.BUILD uses the database package UTL_FILE. Therefore you need to add a parameter UTL_FILE_DIR in the init.ora file pointing to a directory on the server.

Eg. UTL_FILE_DIR = (/u01/app/oracle/common/utl_file)

2.      Create the list of 'to examine logfiles'

A.) Add the first logfile to the list:

  SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,LogFileName=>'/u03/app/oradata/SEBLMKT/redo_g01a.log');

B.) Add other logfiles to the list :

  SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'/u03/app/oradata/SEBLMKT/redo_g02a.log');

 

  SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'/u03/app/oradata/SEBLMKT/redo_g03a.log');

NOTE: You decide yourself which logfile, and how many logfiles you will examine. We suggest to create a list of just one logfile and to check the contents of this one before adding another one.

NOTE: You can remove a log file from the list by using 'dbms_logmnr.removefile' instead of using 'dbms_logmnr.addfile'

3.      Start LogMiner

Start LogMiner with no limitations

 SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'/u01/app/oracle/common/utl_file/Dictionary.ora');

Limit the search to a specific time range during which someone made his changes to the database

 SQL> EXECUTE

  dbms_logmnr.start_logmnr(DictFileName=>'/u01/app/oracle/common/utl_file/Dictionary.ora'

                     , StartTime=>TO_DATE('15-Jan-2000 08:00:00','DD-MON-RRRR HH:MI:SS')

                     , EndTime=>TO_DATE('15-Jan-2000 08:30:00','DD-MON-RRRR HH:MI:SS')

  );

 

Limit the search to committed data only

SQL> EXECUTE

  dbms_logmnr.start_logmnr(DictFileName=>'/u01/app/oracle/common/utl_file/Dictionary.ora' options=>dbms_logmnr.committed_data_only);

Parameters of "dbms_logmnr.start_logmnr":

·         StartScn Default 0,

·         EndScn Default 0,

·         StartTime Default '01-Jan-1988',

·         EndTime Default '01-Jan-2988',

·         DictFileName Default '',

·         Options Default 0 (= a debug flag, not yet being used)

4.      Examine view v$logmnr_contents

The results of LogMiner are stored in a private view called: v$logmnr_contents. It might be helpful to so a DESCRIBE on the view.

<><><><><><><><><><>

Original DML
INSERT INTO dept VALUES (50, 'IT','PITTSBURGH');
Query V$LOGMNR_CONTENTS
SQL> SELECT  username, sql_redo, sql_undo   2> FROM  v$logmnr_contents   3> WHERE username = 'JHUNTER';
USERNAME	SQL_REDO	SQL_UNDO
JHUNTER	INSERT INTO JHUNTER.DEPT(DEPTNO,DNAME,LOC) VALUES (50,'IT','PITTSBURGH');	delete from JHUNTER.DEPT where DEPT NO = 50 and DNAME = 'IT' and LOC = PITTSBURGH' and ROWID = 'AAAHhCAAn AAAAGJAAE';

Example 1: Query v$logmnr_contents

The above example (Example 1) is a typical query against v$logmnr_contents. The example tells us that the USERNAME 'JHUNTER' inserted a row into the table JHUNTER.DEPT. This is seen under the 'SQL_REDO' column. The view v$logmnr_contents also includes a column called 'SQL_UNDO' that includes the SQL needed to UNDO the transaction.

5.      Create a temporary table to make the query easier

create table JHUNTER.restore as
select to_char(timestamp,'HH24:MI.SS') "Time",table_name, sql_redo,sql_undo
from v$logmnr_contents
where sql_redo like 'delete from " JHUNTER "%' ;

 

6.      Final Remarks on LogMiner

exec dbms_logmnr.end_logmnr();

 

The output from LogMiner is the contents of the view 'v$logmnr_contents'. The output is only visible during the life of the session which runs the procedure 'dbms_logmrn.start_logmnr'. This is because all the LogMiner memory is in PGA memory, so it is neither visible to other sessions, nor is it persistent.

So the moment you logoff or you invoke the procedure 'dbms_logmnr.end_logmnr' the PGA will be cleared and the information is no longer available.

DBMS_LOGMNR and DBMS_LOGMNR_D call kernel C modules directly. There is very little of the functionality visible to the user in the dbmslogmnr.sql.