基于日志挖掘的误操作不完全恢复思路

生成备份所有数据文件的脚本:
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||
'ho cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)||
'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files;
备份controlfile:
alter database backup controlfile to '/home/oracle/hotbk/ctl_after_reset.bk';
alter database backup controlfile to trace as '/home/oracle/hotbk/cre_ctl.sql';
备份成功后切换一次归档日志:
alter system switch logfile;

online redolog 很重要！！！

SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1079325

alter tablespace USERS begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/users01.dbf  /home/oracle/hotbk/
alter tablespace USERS end backup;
alter tablespace UNDOTBS1 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/undotbs01.dbf  /home/oracle/hotbk/
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/sysaux01.dbf  /home/oracle/hotbk/
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/system01.dbf  /home/oracle/hotbk/
alter tablespace SYSTEM end backup;
alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data01.dbf  /home/oracle/hotbk/
alter tablespace JF_DATA end backup;
alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data02.dbf  /home/oracle/hotbk/
alter tablespace JF_DATA end backup;
alter tablespace T2 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/t2a.dbf  /home/oracle/hotbk/
alter tablespace T2 end backup;

SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1079325
SQL> SELECT COUNT(*) FROM DAODAO;
  COUNT(*)
----------
    500002
SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1079325
SQL> SELECT COUNT(*) FROM DAODAO;
  COUNT(*)
----------
    500002
SQL> DELETE FROM DAODAO;
500002 rows deleted.
SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1079325

SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/archivelog
Oldest online log sequence     7
Next log sequence to archive   10
Current log sequence           10
SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1089560
SQL> drop table daodao purge;
Table dropped.
SQL> purge recyclebin;
Recyclebin purged.

挖掘dml语句需要将数据库置为追加日志数据模式:
默认只是记录ddl语句
alter database add SUPPLEMENTAL LOG data;

SQL> select group# ,status from v$log where status='CURRENT';
    GROUP# STATUS
---------- ----------------
         2 CURRENT
SQL> SELECT MEMBER FROM  V$LOGFILE WHERE GROUP#=2;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/mdnss/redo02.log
挖掘dml语句需要将数据库置为追加日志数据模式:
默认只是记录ddl语句
alter database add SUPPLEMENTAL LOG data;
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL>  exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo01.log',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/mdnss/redo03.log',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like '%drop%' and seg_name='DAODAO'  and seg_owner='SCOTT'

SQL> select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like '%drop%' and seg_name='DAODAO'  and seg_owner='SCOTT';
       SCN
----------
SQL_REDO
--------------------------------------------------------------------------------
   1089685
drop table daodao purge;
说明这个SCN号上面删除了这个对应的表所以得进行还原
SQL> select * from v$log;
SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1079325

alter tablespace USERS begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/users01.dbf  /home/oracle/hotbk/
alter tablespace USERS end backup;
alter tablespace UNDOTBS1 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/undotbs01.dbf  /home/oracle/hotbk/
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/sysaux01.dbf  /home/oracle/hotbk/
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/system01.dbf  /home/oracle/hotbk/
alter tablespace SYSTEM end backup;
alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data01.dbf  /home/oracle/hotbk/
alter tablespace JF_DATA end backup;
alter tablespace JF_DATA begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/jf_data02.dbf  /home/oracle/hotbk/
alter tablespace JF_DATA end backup;
alter tablespace T2 begin backup;
ho cp -v /u01/app/oracle/oradata/mdnss/t2a.dbf  /home/oracle/hotbk/
alter tablespace T2 end backup;
alter database backup controlfile to '/home/oracle/hotbk/ctl_after_reset.bk';
alter database backup controlfile to trace as '/home/oracle/hotbk/cre_ctl.sql';
SQL> select  CHECKPOINT_CHANGE#  from v$database;
CHECKPOINT_CHANGE#
------------------
           1079325
SQL> SELECT COUNT(*) FROM DAODAO;
  COUNT(*)
----------
    500002
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          9   52428800        512          1 YES
INACTIVE               1089570 31-JUL-13      1089575 31-JUL-13
         2          1         10   52428800        512          1 NO
CURRENT                1089575 31-JUL-13   2.8147E+14
         3          1          7   52428800        512          1 YES
INACTIVE               1089560 31-JUL-13      1089565 31-JUL-13
         4          1          8   52428800        512          1 YES
INACTIVE               1089565 31-JUL-13      1089570 31-JUL-13

select * from v$logfile
    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u01/app/oracle/oradata/mdnss/redo03.log
NO
         2         ONLINE
/u01/app/oracle/oradata/mdnss/redo02.log
NO
         1         ONLINE
/u01/app/oracle/oradata/mdnss/redo01.log
NO
         4         ONLINE
/u01/app/oracle/oradata/mdnss/redo09.log
NO
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@node1 hotbk]$ cp -v  ./*.dbf /u01/app/oracle/oradata/mdnss/
`./jf_data01.dbf' -> `/u01/app/oracle/oradata/mdnss/jf_data01.dbf'
`./jf_data02.dbf' -> `/u01/app/oracle/oradata/mdnss/jf_data02.dbf'
`./sysaux01.dbf' -> `/u01/app/oracle/oradata/mdnss/sysaux01.dbf'
`./system01.dbf' -> `/u01/app/oracle/oradata/mdnss/system01.dbf'
`./t2a.dbf' -> `/u01/app/oracle/oradata/mdnss/t2a.dbf'
`./undotbs01.dbf' -> `/u01/app/oracle/oradata/mdnss/undotbs01.dbf'
`./users01.dbf' -> `/u01/app/oracle/oradata/mdnss/users01.dbf'

[oracle@node1 ~]$ ls /home/oracle/hotbk
jf_data01.dbf  jf_data02.dbf  sysaux01.dbf