Introduction to the application of Oracle LogMiner analysis tools

We all know that Oracle LogMiner is used as a log analysis tool for products integrated in Oracle8i and Oracle9i databases. Through this tool, we can clearly analyze all transaction changes in redo related logs and archive logs, and can accurately determine the specific time and SCN value of various DML and DDL operations.

The purpose of analyzing redo logs and archive logs is to restore data lost due to misoperations.

LogMiner can be used:

① Determine the time when the data logic is damaged. For example, after you execute the drop table and truncate table commands and other misoperations, you can use LogMiner to accurately locate the specific time for these misoperations. Note: Oracle8i can only restore DML operations, but cannot restore DDL operations. Oracle9i can restore DDL and DML operations.

② Tracking the transaction changes performed by the user. Using Oracle LogMiner can not only track various DML operations and DDL operations performed by users, but also obtain data changes.

③ Track the DML operations of a table. You can use LogMiner to track all transaction changes in the table.

The following uses Oracle8i (8.1.5) for Windows 2000/NT as an example to describe how to use Oracle LogMiner to analyze redo logs and archive logs. All operations performed in this article are verified in this environment. The database name used in this article is phj, and Oracle_sid is phj.

I. Log Operation Mode

Oracle databases have two log management modes: Non-archive mode and archive mode. If no log operation mode is specified when you create or install an Oracle database, NOARCHIVELOG is not in archive mode by default ). After the database is created, you can modify the operation mode of the database log as needed.

1. Non-archive mode NOARCHIVELOG)

Non-archive mode refers to the log operation mode that does not save redo logs. This log operation mode can only be used to protect instance failures, such as system power-off), but cannot be used to protect the physical files in the database for media failure ).

The non-archive mode works as follows: assume that the database has only two log groups, the current log group is log group 1, and the log serial number is 1. When the transaction changes fill the log group for a moment, the system switches to log group 2, and the LGWR process writes the transaction changes to the log group, and the log serial number changes to 2. When the transaction changes fill the log group 2, the system automatically switches back to log group 1. At this time, the log serial number changes to 3, and the transaction changes corresponding to log serial number 3 will overwrite the transaction changes corresponding to log serial number 1, and so on.

2. ARCHIVELOG in archive Mode)

Archive mode refers to the operation mode for saving the redo log content to the archive log. In this log operation mode, the ARCH process copies the redo log content to the archive log when switching logs. For example, if the database contains only two log groups, the LGWR process first writes transaction changes to log group 1, and the log serial number is 1;

When the transaction changes fill the log group for a moment, the system will automatically switch to log group 2 and write the transaction changes to log group 2. At this time, the log serial number is changed to 2, at the same time, the background process ARCn will save the content of log group 1 to archive log file 1;

When transaction changes fill in log group 2, the system automatically switches back to log group 1 and writes transaction changes to log group 1. At this time, the log serial number changes to 3, at the same time, the background process ARCn will save the content of log Group 2 to archive log file 2, and so on.

3. Change the log operation mode to automatic archiving mode.

① Modify the database parameter file and add the following parameters after the parameter file:

 
 

  
  
log_archive_start = true 
  
  
log_archive_dest_1 = "location=C:\Oracle\phj\archive" 
  
  
log_archive_format = "T%TS%S.ARC" 
 
 

Note: The ARCH process is automatically started when the first parameter is used to restart the database. The second parameter indicates the location where the archive log is stored. The third parameter indicates the file name format of the archived log, % T takes the redo thread number, and % S takes the log serial number;

② Restart and load the database

 
 

  
  
sqlplus internal/oracle@phj  
  
  
shutdown immediate  
  
  
startup mount pfile=%Oracle_Home%\database\initphj.ora  
  
  
alter database archivelog  
  
  
alter database open 
 
 

4. Generate archive logs

The following uses the DDL and DML operations of the TMP table as an example to describe the whole process of using Oracle LogMiner to analyze redo logs and archived logs. First, execute the following statements to execute DDL and DML operations on table TMP and generate archive logs:

 
 

  
  
sqlplus internal/oracle@phj  
  
  
create table tmp(name varchar2(10),no number(3));  
  
  
alter system switch logfile;  
  
  
insert into tmp values(‘liming’,112);  
  
  
update tmp set no=200;  
  
  
commit;  
  
  
alter system switch logfile;  
  
  
delete from tmp;  
  
  
alter system switch logfile; 
 
 

The preceding operations generate three consecutive log files.