Logminer is a tool that every DBA should be familiar. Due to the incomplete recovery of users a day, because you can't determine which time is done, this is very good. Large difficulties, lost data cannot be completely recovered. Logminer can help you determine the exact time of this misoperation. My test environment is AIX4.3.3 operating system, Oracle9.2.0.3 test library. 1. Some of Logminer 1, logminer can help you determine the specific time and SCN number of various DMLs, DDL operations sent in a certain period of time, which is based on archive log files and online log files. 2, it can only be used in Oracle8i and later versions, but it can analyze Oracle8 logs. 3, Oracle8i can only be used to analyze DML operations, and to Oracle9i can analyze DDL operations. 4, Logminer does not support indexing organization tables, long, LOB, and collection types. 5, the MTS environment can also use the logminer. Second, step 1, set the location where the data file stored for the logminer analysis is the process of setting the UTL_FILE_DIR parameter, my example: A, join in the initctC.ora file As next to UTL_FILE_DIR = / U01 / Arch B, get the database Oracle> SQLPLUS / NOLOG SQL> Conn / AsMediate SQL> SHUTDOWN IMMEDIT SQL> Statup 2, generating a data dictionary file, is done by dbms_logmnr_d.build (). 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, it must Match the setting of UTL_FILE_DIR. Where Dictionary_FileName refers to the name of the dictionary file that is stored, the name can be arbitrarily taken. 3. Create a log analysis table A. Create a log analysis table database must start the database to the MOUNT status in the Mount or Nomount state. sqlplus / nolog sql> conn / as sysdba sql> shutdown immediate sql> starup mount b, create 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 / where Options has three values, dbms_logmnr.new is used to build a log analysis table; dbms_logmnr.addfile is used to join log files used to analyze DBMS_LOGMNR.RemoveFile is used to remove log files used to analyze. 4, add a log file used to analyze.
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 transfer this file from log analysis table Except for analysis. 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, starting the LogMiner to analyze. SQL> Begin 2 dbms_logmnr.start_logmnr (3 DictFileName => '/u01/rCh/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 / That is the analysis of 12:15 to 15:40 on May 1, 2003 And put the analysis results in the data dictionary for query. There are two parameters StartSCN (start SCN) and ENDSCN (Termination SCN). 6. View the results of the log analysis, by query V $ logmnr_contents can query A, view DML operation, 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 operation, SQL_REDO refers to actual operation, SQL_UNDO refers to the opposite operation for cancellation . B, view DDL operation, example: SELECT TIMSTAMP, SQL_REDO from V $ logmnr_contents where Upper (SQL_REDO) Like '% truncate%'; 7, end LogMiner analysis. SQL> Begin 2 dbms_logmnr.end_logmnr; 3 End; 4/3, data dictionary related to Logminer. 1, V $ loglist It is used to display some information of the history log file 2, V $ logmnr_dictionary because logmnr can have multiple dictionary files, which is used to display this information.
