How to Use LogMiner to analyze database logs (1)

This article uses examples to explain how to useLogMinerMethod to Analyze database logs.

◆ 1. Install LogMiner

The following two scripts run as SYSDBA)

@$ORACLE_HOME\rdbms\admin\dbmslm.sql;@ $ORACLE_HOME\rdbms\admin\dbmslmd.sql;

The first script is used to create the DBMS_LOGMNR package, which is used to analyze log files.

The second script is used to create the DBMS_LOGMNR_D package, which is used to create a data dictionary file.

◆ 2. Use the LogMiner Tool

The following describes in detail how to use the LogMiner tool.

1. Create a data dictionary file data-dictionary)

1) first, in the init. ora initialization parameter file, specify the location of the data dictionary file, that is, add the UTL_FILE_DIR parameter, which is the directory where the data dictionary file is placed on the server.

For example: UTL_FILE_DIR = ($ ORACLE_HOME \ logs), restart the database to make the newly added parameters take effect.

2) create a data dictionary file:

SQL> connect /as sysdbaSQL> execute dbms_logmnr_d.build(dictionary_filename => 'dict.ora',dictionary_location => 'G:\oracle\logs');PL/SQL procedure successfully completed

2. Create a list of log files to be analyzed:

1) Create an analysis list, that is, the log to be analyzed:

SQL> execute dbms_logmnr.add_logfile(LogFileName => 'G:\ORACLE\ORADATA\ORADBSP\REDO04.LOG',Options => dbms_logmnr.new);PL/SQL procedure successfully completeds

2) add one analytic log file at a time ):

SQL> execute dbms_logmnr.add_logfile(LogFileName => 'G:\ORACLE\ORADATA\ORADBSP\REDO05.LOG',Options => dbms_logmnr.ADDFILE);PL/SQL procedure successfully completed

3. Use logMiner for log analysis:

1) Unrestricted conditions: Use a data dictionary file to analyze all the content of the log file to be analyzed:

SQL> execute dbms_logmnr.start_logmnr(DictFileName => 'G:\oracle\logs\dict.ora');PL/SQL procedure successfully completed

2) restrictions:

You can use the scn number or time as the limit condition, or you can use it in combination to analyze the time in the log list from.

SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('20070228100000','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('20070228150000','yyyy-mm-dd hh24:mi:ss'),DictFileName => 'G:\oracle\logs\dict.ora');PL/SQL procedure successfully completed

The prototype of the dbms_logmnr.start_logmnr function is:

PROCEDURE start_logmnr(startScn INNUMBER default 0 ,endScnINNUMBER default 0,startTimeINDATE default '',endTime INDATE default '',DictFileNameINVARCHAR2 default '',Options INBINARY_INTEGER default 0 );

4. release memory after analysis:

SQL> execute dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed

5. Others:

1) Delete the log analysis file:

exec dbms_logmnr.add_logfile('G:\ORACLE\ORADATA\ORADBSP\REDO04.LOG',dbms_logmnr.removefile);