/ * LogMiner tools * /
The Logminer tool is actually built by two new PL / SQL ((DBMS_LOGMNR and DBMS_ LOGMNR_D) and four V $ dynamic performance views (view is created when using process dbms_logmnr.start_logmnr to start logminer)
1. Interpret the data dictionary, create a logminer dictionary file If we have change in the database we want to analyze, the data dictionary affecting the library has changed, and the dictionary file is required to recreate the dictionary file. Another situation is to analyze the reproduction log of another database file, you must also regenerate the data dictionary files that have been analyzed over again.
a. Turn off the database b. Initialization parameters Set UTL_FILE_DIR ('f: / Oracle / Database'), specify the location of the data dictionary file c. Open the database D.SQL> show user; user is "sys"
SQL> EXECUTE DBMS_LOGMNR_D.BUILD ('Dict.ora', 'f: / Oracle / Database');
The PL / SQL process has been successfully completed.
If there is no dbms_logmnr_d package, you need to perform @d: /oracle/ora81/rdbms/admin/dbmslmd.sql.
If SQL> execute dbms_logmnr_d.build ('wlcdbdict.ora', 'd: / oracle') is executed; becoming the following error: begin dbms_logmnr_d.build ('wlcdbdict.ora', 'd: / oracle'); end; * error Located on the 1st line: ORA-06532: The subscript beyond the limit ORA-06512: in "sys.dbms_logmnr_d", line 793 ORA-06512: In line 1, you need to perform the following steps to avoid errors:
i. Modify D: /Oracle/ora81/rdbms/admin/dbmslmd.sql file Open the dbms_logmnr_d package, change the value of type col_desc_array is varray (513) of col_description this array of II. Log in to SQL * Plus> Connect Sys / Change_on_install III as SYS / CHANGE_ON_INSTALL. Execute SQL * Plus> @d: /oracle/ora81/rdbms/admin/dbmslmd.sql
If you want to use the logminer in the Oracle 8 database, copy the RDBMS / Admin / DBMSLMD.sql in the Oracle 9i installation directory to the corresponding subdirectory in the Oracle 8 database, then execute the file, you can create DBMS_LOGMNR and DBMS_LOGMNR_D packets, then use The same approach creates a Logminer dictionary file.
2. Specify the redo log file used to analyze
a. Use the new option to create a new redo log file list SQL> execute dbms_logmnr.add_logfile (logfilename => 'f: /oracle/oradata/backup/arc1/arch_1_38.arc' ,Options=> dbms_logmnr.new);
The PL / SQL process has been successfully completed.
b. Use the addFile option to add other redo log files SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => 'f: /oracle/oradata/backup/arc1/rCH_1_37.arc' ,Options=> dbms_logmnr.addfile); PL / The SQL process has been successfully completed.
c. Use the removefile option to delete unwanted redo log files SQL> Execute dbms_logmnr.add_logfile (logfilename => 'f: /oracle/oradata/backup/arc1/rch_1_38.arc' ,Options=> dbms_logmnr.removefile);
The PL / SQL process has been successfully completed.
You can query V $ logmnr_logs to get those log files that have been added to the log list.
3. Run Logminer
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (DictFileName => 'f: /oracle/database/dict.ora');
[Q] execute execute dbms_logmnr.start_logmnr (DictFileName => 'DictFileName') prompted ORA-01843: Invalid month, this is what causes [A] We analyzed start_logmnr package PROCEDURE start_logmnr (startScn IN NUMBER default 0, endScn IN NUMBER default 0 STARTTIME IN DATE DEFAULT TO_DATE ('01-Jan-1988 ',' DD-MON-YYYY '), endtime in date default to_date ('01-jan-2988', 'DD-MON-YYYY'), DictFileName In Varchar2 Default '', Options in binary_integer default 0); You can know if to_date ('01-jan-1988 ',' DD-MON-YYYY ') failed, will result in the above error, so the solution can be 1, ALTER session set nls_language = American2, perform Execute dbms_logmnr.start_logms_logmnr.start_logmnr (DictFileName => ", startTime => to_date ('01 -01-1988 ',' DD-MM-YYYY '), using similar to the following method. EndTime => to_date ('01 -01-2988 ',' DD-MM-YYYY ');
a. Perform time-based filtering (narrow the scope to analyze the log file, only the log of September 18, 2001) SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (DictFileName => f: /oracle/database/dict.ora ', StartTime => to_date ('2001-9-18 00:00:00', 'YYYY-MM-DD HH24: MI: SS') endtime => to_date ('' 2001-9-18 23:59:59 ", 'YYYY-MM-DD HH24: MI: SS'));
b. Perform SCN-based filtering (setting start SCN and scope to SCN to limit the range to analyze log) SQL> Execute dbms_logmnr.start_logmnr (DictFileName => f: /oracle/database/dict.ora ', startscn => 20 , Endscn => 50); 4. Output analysis results to v $ logmnr_contents view so far, we have analyzed the contents of the recovery log file. Dynamic performance view V $ logmnr_contents contains all information obtained by Logminer analysis.
SELECT OPERATION, SQL_REDO from V $ logmnr_contents;
If we just want to know the operation of a user for a table, you can get the following SQL query, which can get all the work made by the user Hanzo to Table Salry.
SQL> SELECT SQL_REDO from V $ logmnr_contents where username = 'hanzo' and tablename = 'Salary'; 1
The analysis results in V $ logMnR_Contents exist only in our running process 'dbms_logmrn.start_logmnr' this session. This is because all Logminer storage is in SGA memory. Once the database is restarted, the analysis results will be lost, and the Logminer must be re-run to get the analysis results again.
5. End Logminer Clear the content in V $ logmnr_logs, v $ logmnr_contents, v $ logmnr_parmeters, end analysis.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
The PL / SQL process has been successfully completed.
6. Other considerations
You can use the LogMiner log analysis tool to analyze the reproduction log files generated by other database instances, not just to analyze the Redo logs files of the database instance of the Logminer itself. Note when using Logminer to analyze other database instances:
1. Logminer must use a dictionary file generated by the database discharge, not a dictionary file generated by the database of logminer, and must ensure that the character set of the Logminer database is the same as the character set of the analytical database.
2. The analysis database platform must be the same as the database platform where the current logminer is located, that is, if we want to analyze the file generated by Oracle 8i running on the UNIX platform, you must also act in a UNIX platform. Run the logminer on the other, such as running the logminer as Microsoft NT. Of course, the hardware conditions of both are not exactly the same.
3. Logminer is the new package provided by Oracle in Oracle8i, and Oracle8 does not use this package; but Oracle8 can also be used in Logminer. Simply copy Oracle8.1.6 (dbmslogmnr.sql, prvtlogmnr.sql, prvtlogmnr.plb), Oracle 8.1.6 (dbmslmd.sql, dbmslm.sql, prvtlm.plb) files on the machine where ORACLE8 is located and implemented Scripts can be. This can also be used in Oracle8.
