Talking about the use of Logminer

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, Logminer of some of the instructions


1, Logminer can help you determine the specific time of the various DML,DDL operations in the time and the SCN number, which is based on the archive log files and online log files.


2, it can only be used in oracle8i and later versions, but it analyzes Oracle8 logs.


3, oracle8i can only be used to parse DML operations, and the DDL operation is analyzed by oracle9i.


4, Logminer does not support indexed organization tables, Long, LOB, and collection types.


5, MTS environment also cannot use Logminer.





Two, operation steps


1, set the location of the data file for Logminer analysis


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, open database


Oracle>sqlplus/nolog


Sql>conn/as sysdba


Sql>shutdown Immediate


Sql>statup





2, generate the data dictionary file, is through 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.


where Dictionary_filename refers to the name of the dictionary file placed in place, 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, set up 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/


the options have three values, dbms_logmnr.new is used to build a log Analysis table, dbms_logmnr.addfile 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/


 


is used 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 opposite operation for cancellation.


 


B, view DDL operations, Example:


SELECT Timstamp,


Sql_redo


from V$logmnr_contents


WHERE Upper (Sql_redo) like '%truncate% ';





7, end of Logminer analysis.


Sql>begin


2 DBMS_LOGMNR.END_LOGMNR;


3 END;


4/





Third, data dictionary related to Logminer.


1, v$loglist It is used 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.





(end of full text)