How to use LogMiner to Restore Data
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.
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'
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 | >||
| >||
Query V$LOGMNR_CONTENTS | >||
2> FROM v$logmnr_contents 3> WHERE username = 'JHUNTER'; | >||
USERNAME | >SQL_REDO | >SQL_UNDO | >
| >
JHUNTER.DEPT(DEPTNO,DNAME,LOC) VALUES (50,'IT','PITTSBURGH'); | >
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.
