使用logminer恢复delete数据

按照官方文档做：

在session1：

SQL> create table tab1(id number ,name varchar2(20)); 

Table created.

SQL> begin 
  2  for i in 1..100 loop 
  3  insert into TAB1 values(i,'test'||i); 
  4  
  5  commit;
  6  end loop; 
  7  end; 
  8  / 

PL/SQL procedure successfully completed.

SQL> select count(*) from TAB1;

  COUNT(*)
----------
       100

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; 

Session altered.

SQL> select sysdate from dual; 

SYSDATE
--------------------
03-FEB-2010 10:00:37

SQL> delete from tab1; 

100 rows deleted.

SQL> commit;

Commit complete.

SQL> /

Commit complete.

SQL> select sysdate from dual;

SYSDATE
--------------------
03-FEB-2010 10:00:50

SQL> select count(*) from TAB1;

  COUNT(*)
----------
         0

然后再session2：

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '03-FEB-2010 10:00:00', ENDTIME => '03-FEB-2010 10:02:50',options=> DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +DBMS_LOGMNR.CONTINUOUS_MINE); 

PL/SQL procedure successfully completed.

SQL> 
SQL> create table mycontents as select * from v$logmnr_contents;

Table created.

SQL> execute dbms_logmnr.end_logmnr(); 

PL/SQL procedure successfully completed.

奇怪的现象发生了：

SQL> SELECT COUNT(*)
  2  FROM mycontents 
  3  WHERE seg_owner='SCOTT' 
  4  AND table_name='TAB1' AND operation='DELETE'; 

  COUNT(*)
----------
        68

知道答案了，原来没有打开SUPPLEMENTAL_LOG_DATA_MIN

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

打开试一下：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

然后就ok了，后面就不罗嗦了

使用logminer注意事项：
1.要在archive log mode才能使用
2.尽量掌握操作的准确时间
3.table_name等要使用大写
4.ENDTIME最好写迟一些，增加1-2分钟

补充一下使用logminer的要求：
Requirements

The following are requirements for the source and mining database, the data dictionary, and the redo log files that LogMiner will mine:

*

Source and mining database
o

Both the source database and the mining database must be running on the same hardware platform.
o

The mining database can be the same as, or completely separate from, the source database.
o

The mining database must run the same version or a later version of the Oracle Database software as the source database.
o

The mining database must use the same character set (or a superset of the character set) used by the source database.
*

LogMiner dictionary
o

The dictionary must be produced by the same source database that generates the redo log files that LogMiner will analyze.
*

All redo log files:
o

Must be produced by the same source database.
o

Must be associated with the same database RESETLOGS SCN.
o

Must be from a release 8.0 or later Oracle Database. However, several of the LogMiner features introduced as of release 9.0.1 work only with redo log files produced on an Oracle9i or later database. See Supported Databases and Redo Log File Versions.

LogMiner does not allow you to mix redo log files from different databases or to use a dictionary from a different database than the one that generated the redo log files to be analyzed.

Note:
You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.

When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;


If the query returns a value of YES or IMPLICIT, minimal supplemental logging is enabled. See Supplemental Logging for complete information about supplemental logging


参考文献：How to Recover from delete on a table using 10g logminer (by example)? [ID 390537.1]
Manual B14215-01 "Oracle Database Utilities 10g Release 2 (10.2)"