Comparison between Oracle LOGMNR mining logs and DUMP logs

Many people know that LOGMNR is used to analyze logs, but few use DUMP to analyze logs. This is because the information analyzed by LOGMNR is easy to read and understand.

But sometimes we still need to DUMP to analyze the log file, because it records more details and is more real. (In fact, the logs analyzed by General LOGMNR are not comprehensive)

After a LOGMNR log analysis, I found that the information mined was very strange. I queried the records analyzed by LOGMNR Based on ROWID and found that a ROWID had an INSERT or DELETE operation, but no UPDATE operation was performed,

In fact, there are indeed UPDATE operations based on business analysis (note that I have also thought about ROWID changes, such as row migration, however, according to the detection of SCN and ROWID, The ROWID has not changed at all.) So I began to doubt the correctness of the LOGMNR log analysis. I started a quiz to verify my thoughts. The quiz is as follows:

First, find the currently used log file

Select a. status, B. member from v $ log a, v $ logfile B where a. group # = B. group #;

The log file of the CURRENT activity is G: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ REDO03.LOG.

-- Create a test table

00:41:20 scott @ orcl> create table dump_a (id number, tt varchar2 (20 ));

The table has been created.

Used time: 00: 00: 00.40

-- Current SCN number

At 00:41:22 scott @ orcl> select dbms_flashback.get_system_change_number () a from dual;

A
-------------
1257368

Select 1 line.

-- Insert a data entry

00:42:47 scott @ orcl> insert into dump_a values (1, 'w ');

One row has been created.

Used time: 00: 00: 00.11

-- Update a piece of data
At 00:43:44 scott @ orcl> update dump_a set id = 2 where id = 1;

1 row updated.

Used time: 00: 00: 00.14

-- The current SCN number

At 00:43:52 scott @ orcl> select dbms_flashback.get_system_change_number () a from dual;

A
-------------
1267898

Select 1 line.

 

-- The above SCN number is used for DUMP logs.

1 2 3 4 Next Page