Oracle tracking user operation experience the power of logminer

The SQL interface is used to query redo logs.

Purpose:
1. You can use LogMiner to recover the deleted data by mistake. Although you can also use RMAN to recover the data, it is troublesome to transfer the data and query the data before using the backup; logMiner is relatively simple;

2. The post-audit function can be performed. LOG logs contain all the information for tracking any DML and DDL, And you can know the execution sequence and who will execute it;

Procedure

1. Adjust the supplemental status of the database
2. Create a database-Level Trigger to record customer login information
3. Install logminer
4. Create a logminer data dictionary. (If you do not create the data yourself, you can analyze the data online. Instead, you can see not the original SQL,
SQL statements formatted by Oracle)
5. Add logs to be analyzed
6. Analyze logs
7. view the result (v $ logmnr_contents)

1. Check the client information in v $ logmnr_contents to see if logminer is supported.

Select name, supplemental_log_data_min from v $ database

We need to set supplemental_log_data_min to on

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

Enabling method:

Alter database add supplemental log data;

Closing Method

Alter database drop supplemental log data;

This parameter is recorded in the control file. If you re-create the Database Control file, we need to reset it.

2. Create a basic trigger for the database:

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/

Now, 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
Session_info: Check the Client ip address.

3. Install logminer:

Iv. Analysis

Exec dbms_logmnr.start_logmnr (dictfilename => 'C: \ oracle \ logs \ dictionary. ora ');

Exec dbms_logmnr.start_logmnr;

Example:
Select timestamp, commit_timestamp, table_space, session #, serial #, username, session_info, SQL _redo, operation, table_name, seg_name, seg_owner
From v $ logmnr_contents
Where seg_owner = upper ('Scott)
And operation = upper ('insert ');

Vi. End Analysis
SQL> execute dbms_logmnr.end_logmnr;

We recommend that you use non-system tablespace to store LogMiner information:
Exec DBMS_LOGMNR_D.SET_TABLESPACE ('logmnrts $ ');

1. logminer is actually composed of two pl/SQL built-in packages (dbms_logmnr and dbms_logmnr_d) and four v $ dynamic performance views.

V $ logmnr_logs
V $ logmnr_contents
V $ logmnr_parameters

To install logminer, run the pl/SQL script as the sys administrator.

SQL> conn/as sysdba

SQL> @/rdbms/admin/dbmslm. SQL

SQL> @/rdbms/admin/dbmslmd. SQL

2. Modify the utl_file_dir parameter.

This parameter is a static parameter. It takes effect only when the database is modified.

SQL> alter system set utl_file_dir = 'C: \ logminerlog' scope = spfile;

The system has been changed.

SQL> host mkdir c: \ logminerlog

SQL> exec dbms_logmnr_d.build (dictionary_filename => 'logminer', dictionary_location => 'C: \ logminerlog ');

The PL/SQL process is successfully completed.

SQL> begin
2 dbms_logmnr_d.build (dictionary_filename => 'logminer', dictionary_location => 'C: \ logminerlog ');
3 end;
4/

The PL/SQL process is successfully completed.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
-----------------------------------------------------------------------------
Utl_file_dir string c: \ logminerlog

SQL> exec dbms_logmnr_d.build (dictionary_filename => 'dictionary. ora ', dictionary_location => 'C: \ logminerlog ');

The PL/SQL process is successfully completed.

 

SQL> exec dbms_logmnr.add_logfile (logfilename => 'd: \ app \ Gavin \ oradata \ orcl \ redo03.log ', options => dbms_logmnr.new );

The PL/SQL process is successfully completed.

SQL> exec dbms_logmnr.add_logfile (logfilename => 'd: \ app \ Gavin \ oradata \ orcl \ redo01.log ', options => dbms_logmnr.addfile );

The PL/SQL process is successfully completed.

To reduce the data volume of the view and accelerate the analysis time, you can delete the analyzed logs from the view.

SQL> exec dbms_logmnr.add_logfile (logfilename => 'd: \ app \ Gavin \ oradata \ orcl \ redo03.log ', options => dbms_logmnr.removefile );

The PL/SQL process is successfully completed.

SQL> exec dbms_logmnr.add_logfile (logfilename => 'd: \ app \ Gavin \ oradata \ orcl \ redo01.log ', options => dbms_logmnr.removefile );

The PL/SQL process is successfully completed.