DBA_Oracle LogMiner analyzes redo and archive logs (CASE) (Oracle records any operation logs and how to analyze logs), dba_oraclelogminer

DBA_Oracle LogMiner analyzes redo and archive logs (CASE) (Oracle records any operation logs and how to analyze logs), dba_oraclelogminer

2014-08-19 BaoXinjian

I. Summary

Oracle LogMiner is a very useful and free analysis tool that allows you to easily obtain the specific content in Oracle redo log files (archived log files, it is composed of a group of PL/SQL packages and some dynamic views.

The log file stores all the data for database recovery and records every change in the database structure, that is, all DML statements for database operations.

The Logminer tool can be used to analyze online logs or offline log files. It can be used to analyze duplicate log files in its own database, or to analyze duplicate log files in other databases.

1. Main purpose:

• (1) tracking database changes: You can track database changes offline without affecting the performance of the online system.
• (2). Roll Back database changes: Roll back specific changes to reduce point-in-time recovery ing.
• (3). Optimization and expansion plan: You can analyze the data in the log file to analyze the data growth mode.

2. Two important packages

• DBMS_LOGMNR
• DBMS_LOGMNR_D

3. Four important views

• V $ LOGMNR_DICTIONARY ------- query the data dictionary files used
• V $ LOGMNR_PARAMETERS ------- query the parameters set by the current LogMiner
• V $ LOGMNR_LOGS ------- query and analyze log files
• V $ LOGMNR_CONTENTS ------- Log File Content

4. DBMS_LOGMNR.start_logmnr can define the range of Parameter Differentiation analysis.

• SCN
• Timestamp

 

Ii. Case-create a test table and insert data into it to analyze whether redo logs are recorded

1. Install LogMiner

To install the logminer tool, you must first run the following three scripts:

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sqlSQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sqlSQL> @$ORACLE_HOME/rdbms/admin/dbmslms.sql

Both scripts must run as SYS users. 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.

The process and view are as follows:

 

2. Create a data dictionary file

Create directory utlfile AS '/opt/oracle/oradata/gavinsit/logmnr' alter system set utl_file_dir = '/opt/oracle/oradata/gavinsit/logmnr' scope = spfile; restart

The LogMiner tool is actually created by two new PL/SQL built-in packages (DBMS_LOGMNR and DBMS _ LOGMNR_D) and four V $ dynamic performance views (views are created when LogMiner is started using DBMS_LOGMNR.START_LOGMNR).

Before using the LogMiner tool to analyze the redo log file, you can use the DBMS_LOGMNR_D package to export the data dictionary as a text file.

This dictionary file is optional, but if it is not available, the part of the data dictionary (such as the table name and column name) in the statement interpreted by LogMiner) and numeric values are in hexadecimal format, which we cannot directly understand.

 

3. View redo logs in the database

 

4. Create a table and add a record. Check whether the redo log exists through analysis and confirm the SCN.

 

5. Add the log file to be analyzed and use LogMiner for log analysis.

BEGIN   DBMS_LOGMNR_D.build (      dictionary_filename   => 'dictionary.ora',      dictionary_location   => '/opt/oracle/oradata/gavinsit/logmnr'   );   DBMS_LOGMNR.add_logfile ('/opt/oracle/oradata/gavinsit/redo01.log',                            DBMS_LOGMNR.NEW);   DBMS_LOGMNR.add_logfile ('/opt/oracle/oradata/gavinsit/redo02.log',                            DBMS_LOGMNR.ADDFILE);   DBMS_LOGMNR.add_logfile ('/opt/oracle/oradata/gavinsit/redo03.log',                            DBMS_LOGMNR.ADDFILE);   DBMS_LOGMNR.start_logmnr (      DictFileName   => '/opt/oracle/oradata/gavinsit/logmnr/dictionary.ora'   );END;

 

6. view the dynamic view results

6.1 View data dictionary files

6.2 View analysis parameters

6.3 view the log file for analysis

6.4 SQL statements traced back to table Creation

6.5 SQL statement for data insertion

Insert into "SYS ". "OBJ $" ("OBJ #", "DATAOBJ #", "OWNER #", "NAME", "NAMESPACE", "SUBNAME", "TYPE #", "CTIME", "MTIME", "STIME", "STATUS", "REMOTEOWNER", "LINKNAME", "FLAGS", "OID $", "SPARE1 ", "SPARE2", "SPARE3", "SPARE4", "SPARE5", "SPARE6") values ('20170', '20160', 'bxj _ TEST_LOGMINER ', '1', NULL, '2', TO_DATE ('18-AUG-14 ', 'dd-MON-RR'), TO_DATE ('18-AUG-14 ', 'dd-MON-RR '), TO_DATE ('18-AUG-14 ', 'dd-MON-RR'), '1', NULL, NULL, '0', NULL, '6', '1 ', '0', NULL );

 

7. Cancel Analysis

Execute dbms_logmnr.end_logmnr;

 

********************Author: Bao Xin********************

 

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm

What tools can be used to open oracle archive log files?

Brother,
Oracle archive log files are binary files.
We cannot view or modify such files in the normal way.
We recommend that you do not touch it.

Role of oracle redo logs and the entire process from generation to archive

Simply put, the redo log records the changes generated by the oracle dmlddl when the database runs in archive mode.
When the redo log is full or the manual/automatic log switch is performed, the redo log is archived to generate an archive log.
Essentially, the archived log and redo log have the same content,
The number of redo logs is defined according to the log group. Multiple redo logs are written and overwritten cyclically. Archived logs are not overwritten once archived.
Archive logs are used to restore the database.
You can use logminer to analyze archived and redo logs to view database changes and recovery.