The application of Oracle Logminer tools in practical work

The Logminer tool reproduces Oracle-executed SQL by analyzing specific content in Oracle Redo log files (archived log files), in particular, the tool can analyze all DML (INSERT, UPDATE, DELETE, etc.) statements for database operations. In addition, some necessary rollback SQL statements can be analyzed. This tool is especially useful for debugging, auditing, or fallback a particular transaction.

The Logminer analysis tool is actually composed of a set of PL/SQL packages and some dynamic views, which are published as part of the Oracle database and are a completely free tool. However, this tool is somewhat more complex to use than other Oracle built-in tools, mainly because the tool does not provide any graphical user interface (GUI). This article describes the use of the tool in real-world work.

　First, the use of Logminer

The log file holds all data for database recovery and records every change to the database structure, that is, all DML statements that operate on the database.

The Logminer tool can be used to analyze the online or to analyze offline log files, which can be used to analyze the redo log files of their own databases, or to parse other database redo log files.

　　Second, the use of Logminer tools

Due to misoperation, maintenance personnel to the Database SWT table error Update operation, the affected records about nearly million. Unable to recover from backup because the backup is too old. The following is an instance of recovering data using Logminer:

1. Protect the site and choose a recovery method

Around 13:30 from the maintenance staff learned that the misoperation is done between the 11:00~12:00, immediately suspend Archivelog backup. Checking the archive file in the archive directory time includes the above time period.

The initial determination of Logminer method to recover, check the database utl_file_dir parameters, found that the parameters are not initially configured, and can not be modified dynamically, can only be analyzed by Onlinecatalog method.

2. Select Archive log file

Check the time of Redolog, found that has exceeded the time of misoperation, you can use the archive log for analysis, check the/ARCH01 archive log time, select 1_63266.DBF,1_63267.DBF,1_63268.DBF as the Analysis object:

$sqlplus '/ASSYSDBA '

Sql>executedbms_logmnr.add_logfile (-

Logfilename=> "/arch01/1_63266.dbf", options=>dbms_logmnr.new);

Pl/sqlproceduresuccessfullycompleted.

Sql>executedbms_logmnr.add_logfile (-

Logfilename=> "/arch01/1_63267.dbf", options=>dbms_logmnr.addfile);

Pl/sqlproceduresuccessfullycompleted.

Sql>executedbms_logmnr.add_logfile (-

Logfilename=> "/arch01/1_63268.dbf", options=>dbms_logmnr.addfile);

Pl/sqlproceduresuccessfullycompleted.

3. Performing an archive file analysis

SQL>EXECUTEDBMS_LOGMNR. START_LOGMNR (-

options=>dbms_logmnr.dict_from_online_catalog-

Starttime=>to_date (' 06-06-200511:00:00 ',-

' Dd-mm-yyyyhh24:mi:ss '),-

Endtime=>to_date (' 06-06-200512:00:00 ',-

"Dd-mm-yyyyhh24:mi:ss");

Pl/sqlproceduresuccessfullycompleted.

4. Generate an Undo script

sql>setlinesize800

sql>setpagesize2000

Sql>setheadoff

sql>spool/arch01/undosql.sql.0606

Sql>selectsql_undofromv$logmnr_contentswhereseg_name
= ' SWT ' ' andusername= ' MODI ';

SQL>EXECUTEDBMS_LOGMNR.END_LOGMNR ();

Sql>spooloff

5. Analyze the undo script and execute the undo script.

6. Verify the business operation after the undo script executes.

　Iii. Summary

By recovering from the above, it avoids having to recover one of the tables previously only by recovering the entire tablespace, greatly shortening the time and scope of the business affected.

Application of Oracle Logminer tools in practice