Overview

This document is a reading note from the official Oracle documentation Oracle Database Utilities 11g Release 2 (11.2) E22490-05, and it mainly introduces several examples of log mining using logmnr.

Environment Information

Operating System Version

Microsoft Windows Version 6.1.7601

Database Version

Oracle 11g Release 11.2.0.1.0

Preparation Work

Create a test table. All subsequent experiments are based on this test table.

  1. Copy some data from the emp table of the scott user.
1
2
3
SQL> create table scott.tab_emp as 
select ename,sal from scott.emp;
Table created.
  1. View the data in the test table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select * from scott.tab_emp;
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
14 rows selected.

Log Mining Examples

View Changes in the Most Recently Archived Redo Log File

  1. Locate the path of the most recently archived redo log file.
    Suppose the operation we want to check just occurred, and the log is recorded in the latest archived log file.
1
2
3
4
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
--------------------------------------------------------------------------------
E:\ORACLE\ARCH_LOG\ARC0000000021_0831311361.0001
  1. Specify the log file list to be analyzed.
1
2
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'E:\ORACLE\ARCH_LOG\ARC0000000021_0831311361.0001',OPTIONS=>DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
  1. Start log mining.
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
  1. View the v$logmnr_contents.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- --------------------------------------------------------------------------------
   1075293 SYS      ALTER TABLE "SCOTT"."TAB_EMP" RENAME TO "BIN$tQR5Z7zsQLWDs5RPAA3kFg==$0" ;
   1075296 SYS      drop table scott.tab_emp AS "BIN$tQR5Z7zsQLWDs5RPAA3kFg==$0" ;
   1075320 SYS      create table scott.tab_emp as select ename,sal from scott.emp;
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('SMITH','800');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('ALLEN','1600');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('WARD','1250');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('JONES','2975');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('MARTIN','1250');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('BLAKE','2850');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('CLARK','2450');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('SCOTT','3000');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('KING','5000');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('TURNER','1500');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('ADAMS','1100');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('JAMES','950');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('FORD','3000');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('MILLER','1300');
  1. End the log mining session.
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

View Committed Changes in the Most Recently Archived Redo Log File

In the previous example, the data analyzed included uncommitted SQL statements. In this example, we will only query the committed SQL statements.

  1. View the data in the test table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select * from scott.tab_emp;
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
14 rows selected.
  1. Execute SQL statements.
1
2
3
4
5
6
7
8
9
10
SQL> delete from scott.tab_emp where ename in ('TURNER','ADAMS');
2 rows deleted.
SQL> rollback;
Rollback complete.
SQL> update scott.tab_emp set sal=8888 where ename='SCOTT';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
  1. Locate the path of the most recently archived redo log file.
1
2
3
4
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
------------------------------------------------------
E:\ORACLE\ARCH_LOG\ARC0000000023_0831311361.0001
  1. Add the log file to be analyzed.
1
2
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'E:\ORACLE\ARCH_LOG\ARC0000000023_0831311361.0001',OPTIONS=>DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
  1. Start analyzing the logs.
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
  1. View the v$logmnr_contents.
1
2
3
4
5
6
7
8
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- ----------------------------------------------------------------------------------------------------------------
   1082062 UNKNOWN  delete from "SCOTT"."TAB_EMP" where "ENAME" = 'TURNER' and "SAL" = '1500' and ROWID = 'AAASNgAAEAAAAJ7AAJ';
   1082062 UNKNOWN  delete from "SCOTT"."TAB_EMP" where "ENAME" = 'ADAMS' and "SAL" = '1100' and ROWID = 'AAASNgAAEAAAAJ7AAK';
   1082088 UNKNOWN  insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('ADAMS','1100');
   1082088 UNKNOWN  insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('TURNER','1500');
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP" set "SAL" = '8888' where "SAL" = '3000' and ROWID = 'AAASNgAAEAAAAJ7AAH';

As seen, this analysis includes the rollback logs as well. If you only wish to analyze committed transactions, the COMMITTED_DATA_ONLY parameter must be added.

  1. Analyze committed transactions.
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
PL/SQL procedure successfully completed.
  1. View the v$logmnr_contents.
1
2
3
4
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- ----------------------------------------------------------------------------------------------------
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP" set "SAL" = '8888' where "SAL" = '3000' and ROWID = 'AAASNgAAEAAAAJ7AAH';

Here, the uncommitted DELETE statement has been filtered out, and only the committed UPDATE statement is shown.

Format Reconstructed SQL Statements

In the previous examples, the analyzed SQL statements were not formatted, making them difficult to read and analyze, especially when searching through a large number of SQL logs. By specifying the PRINT_PRETTY_SQL parameter, you can format the output SQL statements. However, note that this parameter may prevent the reconstructed statements from being executed directly.

  1. Analyze the log and format the SQL output.
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
  1. View the v$logmnr_contents.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- ---------------------------------------------------------------------
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP"
                      set
                        "SAL" = 8888
                      where
                        "SAL" = 3000 and
                        ROWID = 'AAASNgAAEAAAAJ7AAH';
SQL> SELECT scn,username,sql_undo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_UNDO
---------- -------- ----------------------------------------------------------------------
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP"
                      set
                        "SAL" = 3000
                      where
                        "SAL" = 8888 and
                        ROWID = 'AAASNgAAEAAAAJ7AAH';

Using LogMiner Directory in Redo Log Files

In the previous examples, the log mining was conducted on the same database that generated the logs.

  1. Analyze the log and format the SQL output.
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.

Additional Information

If you find that some statements cannot be analyzed or are incomplete, refer to Oracle’s official explanation:

  • Ensure that database was in minimum supplemental logging at the time that the redo information was created；
  • Ensure that all archive redo logs containing the necessary redo information have been added to the LogMiner session.

That is, ensure the database is running in Supplemental Log Data mode, and the relevant logs have been added to the log mining queue.

How to ensure the database is running in Supplemental Log Data mode:

1
2
3
4
5
6
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES