Using LogMiner

Here are the steps to test LogMiner functionality.

LogMiner, first introduced in Oracle 8i, is used to analyze redo and archive logs. Here are the steps I preformed to test LogMiner functionality. The demonstration was executed on Win2K and Oracle8i.

  1. Add parameter to init.ora and restart database
    UTL_FILE_DIR = C:\oracle\admin\testlog
    
  2. Build dictionary
    Oracle8i Enterprise Edition Release 8.1.7.4.1 - 
    Production JServer Release 8.1.7.4.1 - Production
    
    MDINH@TEST> set serveroutput on
    MDINH@TEST> BEGIN
      2    sys.dbms_logmnr_d.build
      3      ( DICTIONARY_FILENAME =>'dictionary.ora',
      4        DICTIONARY_LOCATION => 'C:\oracle\admin\testlog');
      5  END;
      6  /
    LogMnr Dictionary Procedure started
    LogMnr Dictionary File Opened
    TABLE: OBJ$ recorded in LogMnr Dictionary File
    TABLE: TAB$ recorded in LogMnr Dictionary File
    TABLE: COL$ recorded in LogMnr Dictionary File
    TABLE: SEG$ recorded in LogMnr Dictionary File
    TABLE: UNDO$ recorded in LogMnr Dictionary File
    TABLE: UGROUP$ recorded in LogMnr Dictionary File
    TABLE: TS$ recorded in LogMnr Dictionary File
    TABLE: CLU$ recorded in LogMnr Dictionary File
    TABLE: IND$ recorded in LogMnr Dictionary File
    TABLE: ICOL$ recorded in LogMnr Dictionary File
    TABLE: LOB$ recorded in LogMnr Dictionary File
    TABLE: USER$ recorded in LogMnr Dictionary File
    TABLE: FILE$ recorded in LogMnr Dictionary File
    TABLE: PARTOBJ$ recorded in LogMnr Dictionary File
    TABLE: PARTCOL$ recorded in LogMnr Dictionary File
    TABLE: TABPART$ recorded in LogMnr Dictionary File
    TABLE: INDPART$ recorded in LogMnr Dictionary File
    TABLE: SUBPARTCOL$ recorded in LogMnr Dictionary File
    TABLE: TABSUBPART$ recorded in LogMnr Dictionary File
    TABLE: INDSUBPART$ recorded in LogMnr Dictionary File
    TABLE: TABCOMPART$ recorded in LogMnr Dictionary File
    TABLE: INDCOMPART$ recorded in LogMnr Dictionary File
    Procedure executed successfully - LogMnr Dictionary Created
    
    PL/SQL procedure successfully completed.
    
  3. Add some data to table
    MDINH@TEST> select * from t1; 
    
    no rows selected 
    
    MDINH@TEST> insert into t1 values(1,'michael'); 
    
    1 row created. 
    
    MDINH@TEST> insert into t1 values(2,'erika'); 
    
    1 row created. 
    
    MDINH@TEST> commit; 
    
    Commit complete. 
    
  4. Take a look at the current redo log
    MDINH@TEST> BEGIN 
      2    sys.dbms_logmnr.add_logfile 
      3      ( LOGFILENAME => 'C:\ORADATA\TESTREDO03A.DBS', 
      4        OPTIONS => SYS.DBMS_LOGMNR.NEW); 
      5  END; 
      6  / 
    
    PL/SQL procedure successfully completed. 
    
    MDINH@TEST> BEGIN 
      2    sys.dbms_logmnr.start_logmnr 
      3       ( DICTFILENAME => 'C:\oracle\admin\testlog\dictionary.ora' ); 
      4  END; 
      5  
      6  / 
    
    PL/SQL procedure successfully completed. 
    
    MDINH@TEST> set linesize 500 
    MDINH@TEST> select sql_undo 
      2  from v$logmnr_contents 
      3  where sql_undo like '%MDINH%T1%' 
      4  order by timestamp desc; 
    
  5. This gives you the sql to undo the insert
    SQL_UNDO 
    ----------------------------------------------------------------------------- 
    delete from "MDINH"."T1" 
       where "T1_ID" = 2 and "NAME" = 'erika' and ROWID = 'AAAEwLAAEAAAAfCAAA'; 
    delete from "MDINH"."T1" 
       where "T1_ID" = 1 and "NAME" = 'michael' and ROWID = 'AAAEwLAAEAAAAfCAAC'; 
    
    MDINH@TEST> update t1 set name='erika dinh'; 
    
    2 rows updated. 
    
    MDINH@TEST> commit; 
    
    Commit complete. 
    
    MDINH@TEST> select sql_undo 
      2  from v$logmnr_contents 
      3  where sql_undo like '%MDINH%T1%' 
      4  order by timestamp desc; 
    
    6. Here is the command to undo the update
    SQL_UNDO 
    --------------------------------------------------------------------------- 
    update "MDINH"."T1" set "NAME" = 'erika' where ROWID = 'AAAEwLAAEAAAAfCAAA';
    update "MDINH"."T1" set "NAME" = 'michael' where ROWID = 'AAAEwLAAEAAAAfCAAC'; 
    delete from "MDINH"."T1" 
       where "T1_ID" = 2 and "NAME" = 'erika' and ROWID = 'AAAEwLAAEAAAAfCAAA'; 
    delete from "MDINH"."T1" 
       where "T1_ID" = 1 and "NAME" = 'michael' and ROWID = 'AAAEwLAAEAAAAfCAAC'; 
    
    MDINH@TEST> SELECT * FROM T1; 
    
         T1_ID NAME 
    ---------- ------------------------------ 
             2 erika dinh 
             1 erika dinh 
    
    MDINH@TEST>  
    

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in April 2003

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close