Practice LogMiner on an Oracle 8i Database

Steps:

1. Edit init.ora to include the following parameter 
   utl_file_dir=/ora01/orabkup/GENQA/utl

2. Verify utl_file_dir 
   SQL>connect / as sysdba
   SQL>show parameter utl
    
   NAME                TYPE    VALUE
   ------------------- ------- ------------------------------
   utl_file_dir        string  /ora01/orabkup/GENQA/utl

3. create logminer packages (As SYS)

   SQL>@?/rdbms/dbmslmd.sql
   Package created.
   Package body created.
   No errors.
   Grant succeeded.

4. Perform some user activities

   SQL> select sysdate from dual;
   17-JAN-2007 11:53:44

   SQL> conn nbk9lsj/nbk9lsj
   SQL> create table t1 as select * from all_objects where rownum < 3;
   SQL> update t1 set owner=lower(owner); 
   SQL> update t2 set object_id = object_id +9000;
   SQL> commit;

   SQL> select sysdate from dual;
   17-JAN-2007 11:58:09

5. Use LogMiner (conn as sysdba)
5.1 Create a log miner dictionary file:

   execute sys.dbms_logmnr_d.build( -
   dictionary_filename =>'mylog_dict.dic', -
   dictionary_location => '/ora01/orabkup/GENQA/utl');

5.2. Create a list of logs
   execute dbms_logmnr.add_logfile( -
   logfilename => '/ora01/oraredo/GENQA/genqa_redo01a.log', -
   options => dbms_logmnr.new);

   execute dbms_logmnr.add_logfile( -
   logfilename => '/ora01/oraredo/GENQA/genqa_redo02a.log', -
   options => dbms_logmnr.addfile);

   execute dbms_logmnr.add_logfile( -
   logfilename => '/ora01/oraredo/GENQA/genqa_redo03a.log', -
   options => dbms_logmnr.addfile);

   Note: this database has only three onling log files. Add them all to the list

5.3 Start the log miner

   SQL> execute dbms_logmnr.start_logmnr( -
   dictfilename => '/ora01/orabkup/GENQA/utl/mylog_dict.dic', -
   starttime => to_date('17-JAN-2007 11:53:44', 'DD-MON-YYYY HH:MI:SS'), - 
   endtime => to_date('17-JAN-2007 11:58:09', 'DD-MON-YYYY HH:MI:SS')); 

5.4 View the output via the v$logmnr_contents table. .
   col timestamp format a11
   col sql_redo format a30
   col sql_undo format a30   
   SQL> select timestamp,sql_redo, sql_undo
   from v$logmnr_contents
   where username='NBK9LSJ';


   TIMESTAMP   SQL_REDO                       SQL_UNDO
   ----------- ------------------------------ ------------------------------
   17-JAN-2007 set transaction read write;
    11:56:56
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OWN update "NBK9LSJ"."T1" set "OWN
    11:56:56   ER" = 'sys' where ROWID = 'AAA ER" = 'SYS' where ROWID = 'AAA
        CmjAAaAAAAADAAA';              CmjAAaAAAAADAAA';
    
   17-JAN-2007
    11:56:56
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OWN update "NBK9LSJ"."T1" set "OWN
    11:56:56   ER" = 'sys' where ROWID = 'AAA ER" = 'SYS' where ROWID = 'AAA
        CmjAAaAAAAADAAB';              CmjAAaAAAAADAAB';
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OBJ update "NBK9LSJ"."T1" set "OBJ
    11:57:27   ECT_ID" = 91290 where ROWID =  ECT_ID" = 1290 where ROWID = '
        'AAACmjAAaAAAAADAAA';          AAACmjAAaAAAAADAAA';
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OBJ update "NBK9LSJ"."T1" set "OBJ
    11:57:27   ECT_ID" = 91251 where ROWID =  ECT_ID" = 1251 where ROWID = '
        'AAACmjAAaAAAAADAAB';          AAACmjAAaAAAAADAAB';
    
   17-JAN-2007 commit;
    11:57:28
    
5.5 End the log miner
   SQL> execute dbms_logmnr.end_logmnr;