Installation and Use of Oracle log analysis tool LogMiner

Logminer is integrated with oracle as a related log analysis tool. We can use this tool to clearly analyze the changes of all things in related logs and archive logs, and can

Logminer is integrated with oracle as a related log analysis tool. We can use this tool to clearly analyze the changes of all things in related logs and archive logs, and can

Test environment:

OS: RedHat 5.5

DB: Oracle11g

Logminer is integrated with oracle as a related log analysis tool. We can use this tool to clearly analyze the changes of all things in related logs and archive logs, in addition, you can accurately determine the specific time and SCN values of various DML and DDL operations.
With logminer, we can implement:

1. determine the time of Logical Data corruption

2. Tracks user-performed transaction change operations

3. Track table DML operations

If you want to analyze the archive log, first modify the oracle archive log mode, and change the default archive path to our own path:

Start mount;

Alter database archivelog;

Alter database open;

Alter system set log_archive_dest_1 = 'location = d: \ oracle 'scope = spfile;

Alter system set log_archive_format = 'arch _ % t _ % s _ % r. trc' scope = spfile;

View the modified archive path:

Archive log list;

View archived logs:

Select name, dest_id from v $ archived_log;

If the query has not changed, restart the database.

Install logminer. To install logminer, We need to install the following packages:

$ ORACLE_HOME/rdbms/admin/dbmslm. SQL

$ ORACLE_HOME/rdbms/admin/dbmslmd. SQL

$ ORACLE_HOME/rdbms/admin/dbmslms. SQL

These scripts must be run by the sys user.

@ $ ORACLE_HOME/rdbms/admin/dbmslm. SQL

@ $ ORACLE_HOME/rdbms/admin/dbmslmd. SQL

@ $ ORACLE_HOME/rdbms/admin/dbmslms. SQL

To add a data dictionary, you must add the utl_file_dir parameter,

Alter system set utl_file_dir = '/home/oracle/dir' scope = spfile;

Add supplement logging

First View

Select name, supplemental_log_data_min from v $ database; whether it is yes

YES is enabled. session_info, username, and other information are recorded.
NO is disabled, and NO information such as sesion_info and username is recorded

Add

Alter database add supplemental log data;

Close

Alter database drop supplemental log data;

Restart the database so that the two parameters we just made take effect;

View data dictionary:

Show parameter utl;

Add data dictionary:

SQL> begin
2 dbms_logmnr_d.build (
3 dictionary_filename => 'logminer _ dict. dat ',
4 dictionary_location => '/home/oracle/logminer ');
5 end;
6/

PL/SQL procedure successfully completed.

Or:

Execute dbms_logmnr_d.build (dictionary_filename => 'logminer _ dict. dat ', dictionary_location =>'/home/oracle/logminer ');

Create a logon trigger:
SQL> create or replace trigger on_logon_tigger
2 after logon on database
3 begin
4 dbms_application_info.set_client_info (sys_context ('userenv', 'IP _ address '));
5 end;
6/

Trigger created.

We can see the IP address of the newly logged-on client in the CLIENT_INFO column of the V $ SESSION view. Now you can

Add an archive log file to be analyzed

SQL> begin
2 dbms_logmnr.add_logfile (
3 logfilename => '/home/oracle/arch/arch_6_75891_49_1.trc', options => dbms_logmnr.new );
4 end;
5/

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_logmnr.add_logfile (
3 logfilename => '/home/oracle/arch/arch_7_75891_49_1.trc ',
4 options => dbms_logmnr.addfile );
5 end;
6/

PL/SQL procedure successfully completed.

Switch to archive log:

Alter system switch logfile;

Enable analysis:

Execute dmbs_logmnr.start_logmnr (dictfilename = '/home/oracle/logminer/logminer_dict.dat ');

Or:

Execute dbms_logmnr.start_logmnr;

Query archived logs:

SQL> select name from v $ archived_log;

NAME
--------------------------------------------------------------------------------
/Home/oracle/arch/arch_6_75891_49_1.trc
/Home/oracle/arch/arch_7_75891_49_1.trc
/Home/oracle/arch/arch_8_75891_49_1.trc

To save space for pga, remove unnecessary logs after analyzing logs:

SQL> begin
2 dbms_logmnr.add_logfile (
3 logfilename => '/home/oracle/arch/arch_7_75891_49_1.trc ',
4 options => dbms_logmnr.removefile );
5 end;
6/

PL/SQL procedure successfully completed.

The query result is in v $ logmnr_contents;

Query operations on the database

Select scn, SQL _redo, timestamp from v $ logmnr. contents;

Disable Analysis

Execute dbms_logmnr.stop_logmnr;

It is best to use plsql for query.