Incomplete recovery using the Logminer tool in Oracle

Before we go through the incomplete recovery case walkthrough, we need to learn how to use the Logminer tool. The tool mainly digs the redo log to find out the DDL or DML operations made at a point in time (including: Point-in-time, DataBlock SCN, SQL statement)

1. Analysis of DML

Sql> select * from SCOTT.TB01;

Id

----------

1

2

3

3 Rows selected.

Sql> Delete from scott.tb01;

3 rows deleted.

Sql> commit;

Commit complete.

sql> INSERT into SCOTT.TB01 values (111);

1 row created.

sql> INSERT into SCOTT.TB01 values (222);

1 row created.

sql> INSERT into SCOTT.TB01 values (333);

1 row created.

Sql> commit;

Commit complete.

Sql> select * from SCOTT.TB01;

Id

----------

111

222

333

3 Rows selected.

(1) View the current log group

Sql> select * from V$log;

group# thread# sequence# BYTES members archived STATUS first_change#

---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------

1 1 1 104857600 3 YES INACTIVE 846224 2012-03-22 15:46:28

4 1 0 104857600 3 YES unused 0

3 1 3 104857600 3 NO current 847894 2012-03-22 16:30:10

2 1 2 104857600 3 YES INACTIVE 846225 2012-03-22 15:47:06

Sql> alter system archive Log current;--archive the current log group

System altered.

Sql> select * from V$log;

group# thread# sequence# BYTES members archived STATUS first_change#

---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------

1 1 5 104857600 3 NO current 849696 2012-03-22 17:36:01

4 1 4 104857600 3 YES ACTIVE 849665 2012-03-22 17:34:34

3 1 3 104857600 3 YES ACTIVE 847894 2012-03-22 16:30:10

2 1 2 104857600 3 YES INACTIVE 846225 2012-03-22 15:47:06

4 rows selected.

Above case 1***********************************

2. Enable LOGMNR

(1) Add Database supplemental log

17:13:47 sql> ALTER DATABASE add supplemental log data; --Error not added after