Use Oracle Logminer to view log files __oracle

Use of Logminer =========================================================== Author: Tolywang (http://tolywang.itpub.net)
Published in: 2005.01.19 11:03
Category: Oracle database Management
Source: http://tolywang.itpub.net/post/48/14593
---------------------------------------------------------------
Logminer is a tool that every DBA should be familiar with, when one day you need to do incomplete recovery due to the user's misoperation, because you are not sure which time to do this operation, so this is very difficult to restore your recovery, the lost data can not be fully restored back. And Logminer can help you determine the exact timing of this error operation.

The test environment is the Aix4.3.3 operating system, the Oracle9.2.0.3 test library.

A few explanations of logminer
1. Logminer can help you determine the specific time and SCN number of various DML,DDL operations that are issued at a certain time, depending on the archive log file and the online log file.
2, it can only be used in oracle8i and later versions, but it could analyze the Oracle8 log.
3, oracle8i can only be used to analyze DML operations, to Oracle9i to analyze DDL operations.
4. Logminer does not support index organization tables, Long, LOB, and collection types.
5, MTS Environment also can not use Logminer.

Second, the Operation steps
1, set up for the Logminer analysis of the data files stored location
This is the process of setting the Utl_file_dir parameter, my example:
A, add the following line to the Initctc.ora file
Utl_file_dir=/u01/arch
b, the database to be restarted
Oracle>sqlplus/nolog
Sql>conn/as SYSDBA
Sql>shutdown Immediate
Sql>statup

2, the generation of data dictionary files, through the Dbms_logmnr_d.build () to complete.
Sql> BEGIN
2 Dbms_logmnr_d.build (
3 dictionary_filename => ' Logminer_dict.dat ',
4 dictionary_location => '/u01/arch '
5);
6 end;
7/

Dictionary_location refers to the location where the Logminer data dictionary file is stored, and it must match the Utl_file_dir settings.
The dictionary_filename refers to the name of the dictionary file placed in the location, and the name can be arbitrarily taken.

3, create a Log Analysis table
  A, set up Log Analysis table database must be in mount or Nomount state, start database to mount state.
    sqlplus/nolog
    sql>conn/as sysdba
    sql> Shutdown immediate
    Sql>starup Mount
 
  B, create a log Analysis table, use Dbms_logmnr.add_ LogFile ()
    sql> BEGIN
     2     dbms_logmnr.add_ LogFile (
     3       options => dbms_logmnr.new,
      4       logfilename => '/u01/arch/arc_ctc_0503.arc '
     5    );
     6  end;
     7/      
    options have three values, Dbms_ Logmnr.new is used to build a log Analysis table, dbms_logmnr.addfile to add log files for parsing, and dbms_logmnr.removefile to remove log files for profiling.
       

4, add a log file for profiling.
    sql> BEGIN
     2     dbms_logmnr.add_logfile (
     3       Options => dbms_logmnr.addfile,
     4        logfilename => '/u01/arch/arc_ctc_0504.arc '
     5     );
     6  end;
     7/
 
  Use You can remove this file from the Log Analysis table and do not analyze it.
    sql> BEGIN
     2     dbms_logmnr.add_logfile (
     3       Options => dbms_logmnr.removefile,
     4       logfilename => '/u01/arch/arc_ctc_0503.arc '
     5     );
     6  end;
     7/  
    

5, start Logminer for analysis.
Sql> BEGIN
2 DBMS_LOGMNR.START_LOGMNR (
3 dictfilename => '/u01/arch/logminer_dict.dat ',
4 starttime => to_date (' 20030501 12:15:00 ', ' yyyymmdd hh24:mi:ss '),
5 endtime => to_date (' 20030501 15:40:30 ', ' yyyymmdd hh24:mi:ss ')
6);
7 End;
8/

This is to analyze the period from 12:15 to 15:40 on May 1, 2003, and put the results in the data dictionary for querying. There are also two parameter STARTSCN (start SCN number) and ENDSCN (terminating SCN) number.

6, view the results of the log analysis, through the query v$logmnr_contents can query to
A, view DML operations, example:
SELECT operation,
Sql_redo,
Sql_undo,
From V$logmnr_contents
WHERE seg_name = ' qiuyb ';

OPERATION Sql_redo Sql_undo
----------    --------------------------  --------------------------
INSERT inser into Qiuyb.qiuyb ... delete from Qiuyb.qiuyb ...

Where operation refers to the operation, Sql_redo refers to the actual operation, Sql_undo refers to the reverse operation for cancellation.

B, view DDL operations, Example:
SELECT Timstamp,
Sql_redo
From V$logmnr_contents
WHERE Upper (Sql_redo) like '%truncate% ';

7, the end of Logminer analysis.
Sql>begin
2 DBMS_LOGMNR.END_LOGMNR;
3 END;
4/

Third, a data dictionary associated with Logminer.
1, v$loglist           It is used to display some information about the history log file
2, v$ Logmnr_dictionary LOGMNR can have more than one dictionary file, which is used to display this information.
3, v$logmnr_parameters It is used to display LOGMNR parameters
4, v$logmnr_logs       It is used to display the log list information for analysis.