Use of logminer log analysis tools

1. Install logminer:
To install the logminer tool, you must first run the following two scripts,
$ ORACLE_HOME/rdbms/admin/dbmslm. SQL
$ ORACLE_HOME/rdbms/admin/dbmslmd. SQL.
Both scripts must run as sys users.

******************* **********
2. Create a data dictionary file
Alter system set utl_file_dir = 'd: \ oracle \ logmnr 'scope = spfile;
Restart the database to make the newly added parameters take effect, and then create a data dictionary file:
Execute dbms_logmnr_d.build (dictionary_filename => 'dirary ary. ora ', dictionary_location => 'd: \ oracle \ logmnr', Options => dbms_logmnr_d.store_in_flat_file );

3. Create a list of log files to be analyzed
Execute dbms_logmnr.add_logfile (logfilename => 'C: \ 4103814.dbf', Options => dbms_logmnr.new );
Execute dbms_logmnr.add_logfile (logfilename => 'C: \ 4103815.dbf ', Options => dbms_logmnr.addfile );

4. Use logminer for log analysis
Execute dbms_logmnr.start_logmnr (dictfilename => 'd: \ oracle \ logmnr \ dirctionary. ora ');
**************************************** *******************************

* ************ Store dictionary files in redo files (this method is recommended without restarting the database) *****************************
2. Create a data dictionary to redo File
Execute dbms_logmnr_d.build (Options => dbms_logmnr_d.store_in_redo_logs );

3. Create a list of log files to be analyzed
Execute dbms_logmnr.add_logfile (logfilename => 'C: \ 4103814.dbf', Options => dbms_logmnr.new );
Execute dbms_logmnr.add_logfile (logfilename => 'C: \ 4103815.dbf ', Options => dbms_logmnr.addfile );

4. Use logminer for log analysis
Execute dbms_logmnr.start_logmnr (Options => dbms_logmnr_d.store_in_redo_logs );
**************************************** **************************************** ************************

5. Observe the analysis results
Create Table temp as select * from V $ logmnr_contents;

6. Terminate the log analysis transaction. At this time, the PGA memory area is cleared and the analysis results no longer exist.
Execute dbms_logmnr.end_logmnr ();

7. Other considerations

We can use the logminer log analysis tool to analyze the duplicate log files generated by other database instances, not just the redo logs files of the database instances where logminer is installed. Note the following when using logminer to analyze other database instances:

1. logminer must use the dictionary files generated by the database instance to be analyzed, instead of the dictionary files generated by the database where logminer is installed. In addition, ensure that the character set of the logminer database to be installed is the same as that of the database to be analyzed.

2. the database platform to be analyzed must be the same as the database platform where logminer is currently located. That is to say, if the file to be analyzed is generated by the Oracle 8i running on the UNIX platform, logminer must also be run on an oracle instance running on a UNIX platform, rather than on other Microsoft NT. Of course, the hardware conditions of the two are not necessarily the same.

3. The logminer log analysis tool can only analyze products after Oracle 8. For products before Oracle 8, this tool is powerless.

8. When using logmnr for Oracle 10 Gb, you must set Supplemental log data in advance.
Alter database add Supplemental log data (primary key, unique index) columns;

Delete Supplemental log data
Alter database drop Supplemental log data (primary key, unique index) columns;