Oracle Log Analyzer Tool Logminer use

Oracle Log Analyzer Tool Logminer use

1. Set Date format

Alter system set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ' scope=spfile;

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

2. Add Supplemental Log

If the database needs to use Logminer, it should be added, only after this log is added to capture DML

ALTER DATABASE ADD Supplemental LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

3. Open Archive

Alter system set log_archive_dest_1= ' Location=e:\arch ' Scope=both;

Shutdown immediate

Startup Mount

ALTER DATABASE Archivelog;

ALTER DATABASE open;

4. Install Logminer Tools

To install the Logminer tool, you must first run the following two scripts, both of which must be run as SYS users. The first of these scripts is used to create a DBMS_LOGMNR package that analyzes the log files. The second script is used to create the Dbms_logmnr_d package, which is used to create the data dictionary file.

1. $ORACLE _home\rdbms\admin\dbmslm.sql

2. $ORACLE _home\rdbms\admin\dbmslmd.sql

3. $ORACLE _home\rdbms\admin\dbmslms.sql

@e:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmslm.sql

The package has been created.

The authorization was successful.

The synonym has been created.

@e:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmslmd.sql

The package has been created.

The synonym has been created.

@e:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmslms.sql

Package created

No mistakes.

The authorization was successful.

5. Using the Logminer tool

5.1. Set parameter Utl_file_dir

A data dictionary file is a text file that is created using package dbms_logmnr_d. If the table in the database that we are analyzing changes, the data dictionary that affects the library changes, and the dictionary file needs to be recreated. Another scenario is that when you analyze a redo log for another database file, you also have to regenerate the data dictionary file of the parsed database.

In Oracle8i, first in the Init.ora initialization parameter file, specify the location of the data dictionary file, which is to add a parameter utl_file_dir, which is the directory where the data dictionary files are placed in the server. Such as:

Utl_file_dir = (e:\test)

After oracle9i, it is recommended to use SPFile to dynamically adjust parameters;

Sql> Show parameter SPFile;

NAME TYPE VALUE

------------------------------------ ----------- ---------- --------------------

SPFile string E:\APP\ADMINISTRATOR\PRODUCT\1

1.2.0\dbhome_1\database\spfile

ORCL. ORA

Sql> alter system set utl_file_dir= ' E:\test ' scope=spfile;

The system has changed.

Shutdown immediate

sql> Startup force