SQL> Conn Sys / SYS AS SYSDBA is connected. SQL> @c: /oracle/ora92/rdbms/admin/dbmslmd.sql
The process has been created.
There is no error.
Authorized success.
The PL / SQL process has been successfully completed.
The package has been created.
SQL> @c: /oracle/ora92/rdbms/admin/dbmslms.sql
The package has been created.
There is no error.
Authorized success.
SQL> EXECUTE DBMS_LOGMNR_D.BUILD (Dictionary_FileName => 'V816Dict.ora', Dictionary_Location => 'E: / ORACLE / LOGS');
SQL> EXECUTE DBMS_LOGMNR.ADD_Logfile (logfilename => 'E: /oracle/oradata/ncdbpx/redo01.log' ,Options=> dbms_logmnr.new);
PL / SQL Procedure SuccessFully Completed.
SQL> EXECUTE DBMS_LOGMNR.Add_logfile (logfilename => 'E: /oracle/oradata/ncdbpx/redo02.log' ,Options=> dbms_logmnr.addfile);
PL / SQL Procedure SuccessFully Completed.
SQL> EXECUTE DBMS_LOGMNR.Add_logfile (logfilename => 'E: /oracle/oradata/ncdbpx/redo03.log' ,Options=> dbms_logmnr.addfile);
PL / SQL Procedure SuccessFully Completed.
SQL> EXECUTE DBMS_LOGMNR.Start_logmnr (DictFileName => 'E: / ORACLE/logs/v816dict.ora');
PL / SQL Procedure SuccessFully Completed.
SQL> SELECT SQL_REDO from V $ logmnr_contents where upper (operation) = 'delete' and upper (SQL_REDO) LIKE '% GPRS_SER%' 2 /
No rows selected
SQL> Edwrote File Afiedt.buf
1 * SELECT SQL_REDO from V $ logmnr_contents where Upper (Operation) = 'create' and upper (sql_redo) Like '% EMP%' SQL> /
No rows selected
-------------------------------------------------- ------------------------------------
The main purpose of the Logminer's main purpose Logminer tools are: (1) Tracking the database changes: the trace database can be tracked by the database without affecting the performance of the online system. (2) Return the changes in the database: Retreat the specific change data, reduce the execution of Point-in-Time Recovery. (3) Optimization and expansion programs: The data growth mode can be analyzed by analyzing data in the log file.
Oracle9i Logminer Enhancements (1) Support for more data / storage types: link / migration rows, Cluster table operations, Direct Path Insert, and DDL operations. (2) Extract and use the data dictionary: Now the data dictionary can not only extract into an external file, but also directly extract the redo log stream, which provides the data dictionary snapshot at the time in the log stream, so Offline analysis can be implemented. (3) Allows the DML operation to group transactions: You can set the commcted_data_only option in start_logmnr () to implement a group of DML operations, which will return the transaction in the order of SCN. (4) Support SCHEMA change: In the state of the database, if the DDL_DICT_TRACKING option of logminer is used, Oracle9i's logminer will automatically compare the initial log stream and the current system's data dictionary, and return the correct DDL statement, and will automatically Scout and mark the difference between the current data dictionary and the initial log stream, so even if the table involved in the original log stream has been changed or no longer exist, the logminer will return the correct DDL statement. (5) Ability to record more column information in the log: For example, the UPDATE operation not only records the updated line, but also captures more updated operation information. (6) Support value-based query: Oracle9i logminer starts supporting queries based on actual data-based data based on metadata (operation, object, etc.) queries. For example, it involves a salary table, and now it is now possible to detect the employee salary from 1000 to the original update statement of 2000, and only all update statements can be selected before. Logminer installation To install the Logminer tool, you must first run the following two script files as SYS users: (1) $ oracle_home / rdbms / admin / dbmslsm.sql is used to create a DBMS_LOGMNR package, which is used to analyze log files. (2) $ ORACLE_HOME / RDBMS / Admin / DBMSLSMD.SQL is used to create a DBMS_LogMnR_D package that is used to create a data dictionary file.
Logminer Tools (1) Creating a Data Dictionary File (Data-Dictionary) The purpose of creating a data dictionary is to let the logminer reference to the actual names for them in the internal data dictionary, rather than 16 within the system. The data dictionary file is a text file that is created using the package dbms_logmnr_d. First, in the init.ra initialization parameter file, the location of the data dictionary file is specified, that is, add a parameter UTL_FILE_DIR, which is the directory of the data dictionary file in the server. Such as: UTL_FILE_DIR = E: / Oracle / logs; restart the database, make the new parameters take effect, then create a data dictionary file: Connect Sysexecute dbms_logmnr_d.build (Dictionary_FileName => 'v816dict.ora', Dictionary_location => 'E: / Oracle / Logs');
(2) Creating a list of log files to be analyzed Oracle's re-log files are divided into two, online and offline archive log files, and the list of the two different log files is discussed below. 1 Analysis of online reconciliation log files ● Create a list Execute dbms_logmnr.add_logfile (logfilename => 'e: /oracle/oradata/sxf/redo01.logwl' ,Options=> dbms_logmnr.new); ● Add other log files to list Execute dbms_logmnr .add_logfile (logfilename => 'e: /oracle/oradata/sxf/redo02.logMnr.AddFile); 2 Analyze Offline Log File ● Create List Execute DBMS_LogmnR.Add_logfile (logfilename =>' E: / Oracle /oradata/sxf/archive/ARCARC09108.001',Options=>dbms_logmnr.new);● adding additional log files to the list EXECUTE dbms_logmnr.add_logfile (LogFileName => 'E: / Oracle / oradata / sxf / archive / ARCARC09109. 001 ', options => dbms_logmnr.addfile; (3) Log analysis using logminer 1 No restrictions EXECUTE DBMS_LOGMNR.Start_Logmnr (DictFileName =>' E: / Oracle/logs/v816dict.ora '); 2 Have restriction conditions For several different parameters in the process dbms_ logmnr.start_logmnr, you can narrow down the scope of the log file. By setting the start time and termination time parameters can limit only a log of only a certain time range.
(4) Observation Analysis (V $ logmnr_contents) Dynamic performance view V $ logmnr_contents contains all information obtained after Logminer analysis. Views related to log analysis also: 1V $ logmnr_dictionary: Query the data dictionary file used. 2V $ logmnr_parameters: Query the parameters set by the current Logminer. 3V $ logmnr_files: Query the log file for analysis. 4V $ logmnr_contents: The content of the log file.
3. Other Precautions (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 analytical database platform must be the same as the database platform where the current Logminer is located. (3) Logminer log analysis tools can only analyze the products after Oracle 8.
