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 environment of MTS cannot use Logminer.
Second, the operation 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, add UTL_FILE_DIR = / U01 / Arch B in the initctc.ora file Depending on Database Oracle> SQLPlus / Nolog SQL> Conn / as Sysdba Sql> Shutdown Immediate SQL> Statup
2. Generate a data dictionary file, which 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 the Logminer analysis. SQL> Begin 2 dbms_logmnr.end_logmnr; 3 end; 4 /
Third, the 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. 3, V $ logmnr_parameters It is used to display the parameters of the logMNR 4, V $ logmnr_logs it is used to display log list information for analysis. (Full text)
