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.
|