Shulou(Shulou.com)06/01 Report--

I. use

Tracking database changes: you can track database changes offline without affecting the performance of the online system.

Rollback database changes: rollback specific change data to reduce the execution of point-in-time recovery.

Optimization and expansion plan: the data growth pattern can be analyzed by analyzing the data in the log file.

II. Configuration precautions

Logminer must use the dictionary files generated by the database instance being analyzed, not the dictionary files generated by the database where logminer is installed, and must ensure that the character set of the installed logminer database is the same as that of the database being analyzed.

The database platform being analyzed must be the same as the current logminer database platform, that is, if the file we want to analyze is generated by Oracle 9i running on the UNIX platform, then we must also run logminer on an Oracle instance running on the UNIX platform, instead of running logminer on other platforms such as Microsoft NT. Of course, the hardware conditions of the two are not necessarily the same.

The logminer log analysis tool can only analyze products after Oracle 8, and it is powerless for products before 8.

III. Configuration

Source libraries: creating data dictionary files in

Execute dbms_logmnr_d.build (dictionary_filename = > 'dict.ora',dictionary_location = >' MY_DICTIONARY_DIR')

Transfer source database data dictionary files and logs that need to be analyzed to the analysis library

Analysis libraries: creating DBMS_ LOGMNR packages

@? / rdbms/admin/dbmslm.sql

Analysis libraries: creating related data dictionaries

@? / rdbms/admin/dbmslmd.sql

Analysis library: create a directory to specify where the analysis data is stored

Create directory my_dictionary_dir as'/ oradata/dictionary'

Analysis library: log analysis

Execute dbms_logmnr.add_logfile (options = > dbms_logmnr.new,logfilename = >'/ oradata/o1_mf_1_241618_fln656rl_.arc'); execute dbms_logmnr.add_logfile (options = > dbms_logmnr.addfile,logfilename= >'/ oradata/o1_mf_1_241619_fln653w6_.arc'); execute dbms_logmnr.add_logfile (options = > dbms_logmnr.addfile,logfilename= >'/ oradata/o1_mf_1_241620_fln653rc_.arc')

Analysis libraries: deleting log files that do not require analysis

Execute dbms_logmnr.add_logfile (options = > dbms_logmnr.removefile,logfilename = >'/ opt/oracle/ora92/rdbms/ARC00011.001')

Analysis library: launch LogMiner for analysis

Execute dbms_logmnr.start_logmnr (dictfilename = >'/ oradata/dictionary/dict.ora')

Analysis libraries: viewing analysis results

Select operation,sql_redo,sql_undo,TIMESTAMP from v$logmnr_contents where username='test' or table_name='TEST1' and timestamp > to_date ('2008-01-28 09MMMI hh34:mi:ss')

End analysis

Execute dbms_logmnr.end_logmnr; IV. View the view

VroomLOGMNRDICTIONARY-data dictionary file used by the query

Query the parameters set by the current LogMiner

Log Log File for query Analysis

Contents-contents of the log file.

5. Parameter definition parameter type default value meaning StartScn digital type (Number) 0 analysis of SCN ≥ StartScn log file part EndScn digital type (Number) 0 analysis of SCN ≤ EndScn log file part StartTime date type (Date) 1998-01-01 analysis redo log file part time stamp ≥ StartTime log file part EndTime date type (Date) 2988-01-01 analyze the date of timestamp ≤ EndTime in the redo log Log file part DictFileName character type (VARCHAR2) 0 dictionary file The file contains a snapshot of the database directory. The use of this file can make the analysis results understandable in text form. 6. Note

UTL_FILE_DIR is not configured here for the following reasons

This parameter has been expired.

After this parameter is configured, Logminner will report an error.

To sum up, starting from 12c, using Logminner to analyze the data suggests using the path pointed to by create directory

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.