Discussion on application of Logminer method _oracle

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.

My test environment is Aix4.3.3 's operating system, Oracle9.2.0.3 's 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, the establishment of a log analysis table
A, create 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, the establishment of the Log Analysis table, using 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/
The options have three values, dbms_logmnr.new is used to build a log Analysis table, Dbms_logmnr.addfile is used to add log files for analysis; dbms_ Logmnr.removefile is used to remove log files for profiling.


4, add the log file for analysis.
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 to remove this file from the Log Analysis table so that it is not parsed.
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/

Data dictionary related to Logminer.
1, v$loglist it to display some information about the history log file
2, v$logmnr_dictionary because LOGMNR can have more than one dictionary file, this view 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.