Oracle基于时间的不完全恢复

实验时间：2011年10月14日　环境：RED HAT4，Oracle 10.0.20

基于时间的不完全恢复，难点之一是找出恢复的时间点。如果用户记得当然很多，但很多时候，用户是不知道的，或者不能说知道。如果用户不知道，可以借助于LOG MINIER工具的确定。

一、假定场景
　　用户误删一个重要的表（SCOTT用户的EMP表），并且清空了回收站。当然此种情况也可以使用闪回技术来恢复，这里使用不完全恢复方法进行恢复。

二、场景模拟
以SCOTT用户登录，删除表，新建一个表。
SQL> conn scott/oracle
Connected.
SQL> drop table emp purge;

Table dropped.

SQL> create table dept_new as select * from dept;

Table created.

不完全恢复后，EMP表应该存在，DEPT_NEW表应该不存在

为了更加接近实际情况，再手动切换几次日志。
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

三、恢复过程
1、设置LOG MINER用的参数
SQL> conn / as sysdba
Connected.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/oracle/oracle/product/10
                                                 .2.0/db_1/dbs/spfileorcl.ora
SQL> alter system set utl_file_dir='/tmp/logmnr' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              62917068 bytes
Database Buffers           62914560 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /tmp/logmnr
验证一下，设置成功，当然，对应的目录要存在。

2、创建数据字典
SQL> exec dbms_logmnr_d.build('dictora','/tmp/logmnr');

PL/SQL procedure successfully completed.

SQL> !ls -l /tmp/logmnr
total 22992
-rw-r--r--  1 oracle oinstall 23509170 Oct 14 22:20 dictora
创建成功后，可以看到对应的文件

3、添加要分析的日志（包括联机日志和归档日志）
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         35   52428800          1 YES INACTIVE                915247 14-OCT-11
         2          1         36   52428800          1 NO  CURRENT                 935795 14-OCT-11
         3          1         34   52428800          1 YES INACTIVE                915244 14-OCT-11

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /home/oracle/oracle/product/oradata/orcl/redo03.log          NO
         2         ONLINE  /home/oracle/oracle/product/oradata/orcl/redo02.log          NO
         1 STALE   ONLINE  /home/oracle/oracle/product/oradata/orcl/redo01.log          NO
        
SQL> exec dbms_logmnr.add_logfile('/home/oracle/oracle/product/oradata/orcl/redo02.log',-
> dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oracle/product/oradata/orcl/redo01.log',-
> dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oracle/product/oradata/orcl/redo03.log',-
> dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

再加入两组归档日志
SQL> exec dbms_logmnr.add_logfile('/home/oracle/oracle/product/archive/1_33_760842868.dbf',-
> dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oracle/product/archive/1_32_760842868.dbf',-
> dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

4、开始分析，根据分析结果确定时间
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/tmp/logmnr/dictora');

PL/SQL procedure successfully completed.
字典文件名称就是前面的目录，加上字典文件名称

SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') sj,
  2  scn,sql_redo from V$logmnr_contents
  3  where upper(sql_redo) like 'DROP TABLE EMP%';

SJ                         SCN SQL_REDO
------------------- ---------- ------------------------------------------------------------
2011-10-14 22:04:56     915163 drop table emp purge;
由以上SQL可以确定了删除表的时间，以及SCN号。

5、具体的恢复过程
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !cp /disk2/oracle/coldbak/*.dbf $ORACLE_BASE/oradata/orcl/

SQL> startup mount
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              67111372 bytes
Database Buffers           58720256 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database until time '2011-10-14 22:04:56';
ORA-00279: change 869963 generated at 09/26/2011 03:49:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_22_760842868.dbf
ORA-00280: change 869963 for thread 1 is in sequence #22


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 890420 generated at 09/26/2011 03:58:51 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_23_760842868.dbf
ORA-00280: change 890420 for thread 1 is in sequence #23
ORA-00278: log file '/home/oracle/oracle/product/archive/1_22_760842868.dbf' no longer needed for this recovery


ORA-00279: change 910592 generated at 09/26/2011 04:14:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_24_760842868.dbf
ORA-00280: change 910592 for thread 1 is in sequence #24
ORA-00278: log file '/home/oracle/oracle/product/archive/1_23_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912028 generated at 09/26/2011 04:39:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_25_760842868.dbf
ORA-00280: change 912028 for thread 1 is in sequence #25
ORA-00278: log file '/home/oracle/oracle/product/archive/1_24_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912030 generated at 09/26/2011 04:39:56 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_26_760842868.dbf
ORA-00280: change 912030 for thread 1 is in sequence #26
ORA-00278: log file '/home/oracle/oracle/product/archive/1_25_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912033 generated at 09/26/2011 04:40:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_27_760842868.dbf
ORA-00280: change 912033 for thread 1 is in sequence #27
ORA-00278: log file '/home/oracle/oracle/product/archive/1_26_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912035 generated at 09/26/2011 04:40:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_28_760842868.dbf
ORA-00280: change 912035 for thread 1 is in sequence #28
ORA-00278: log file '/home/oracle/oracle/product/archive/1_27_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912037 generated at 09/26/2011 04:40:04 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_29_760842868.dbf
ORA-00280: change 912037 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/oracle/product/archive/1_28_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912039 generated at 09/26/2011 04:40:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_30_760842868.dbf
ORA-00280: change 912039 for thread 1 is in sequence #30
ORA-00278: log file '/home/oracle/oracle/product/archive/1_29_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912041 generated at 09/26/2011 04:40:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_31_760842868.dbf
ORA-00280: change 912041 for thread 1 is in sequence #31
ORA-00278: log file '/home/oracle/oracle/product/archive/1_30_760842868.dbf' no longer needed for this recovery


ORA-00279: change 912152 generated at 09/26/2011 04:46:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_32_760842868.dbf
ORA-00280: change 912152 for thread 1 is in sequence #32
ORA-00278: log file '/home/oracle/oracle/product/archive/1_31_760842868.dbf' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

简述一下具体过程：关库，转储备份的数据文件，打开数据库的MOUNT状态，使用recover database until time '2011-10-14 22:04:56'命令进行不完全恢复，
使用alter database open resetlogs命令打开数据库

6、检查
SQL> conn scott/oracle;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
RECOVER_TEST                   TABLE
RECOVER_TEST2                  TABLE
EMP_BAK                        TABLE

7 rows selected.
好了，该存在的表EMP存在，不该存在的表DEPT_NEW不存在。

7、关于resetlogs参数
切换几次重做日志，查询一下，可以看到由此带来的相应变化；日志的SEQUENCE#从１重新开始编了，RESETLOGS_CHANGE#变成一个新的数字了。
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          2   52428800          1 YES INACTIVE                915564 14-OCT-11
         2          1          4   52428800          1 NO  CURRENT                 915569 14-OCT-11
         3          1          3   52428800          1 YES INACTIVE                915566 14-OCT-11

SQL> select name,status,thread#,sequence#,resetlogs_change# from v$archived_log;

NAME                                                                             S    THREAD#  SEQUENCE# RESETLOGS_CHANGE#
-------------------------------------------------------------------------------- - ---------- ---------- -----------------
/home/oracle/oracle/product/archive/1_33_760842868.dbf                           A          1         33            690028
/home/oracle/oracle/product/archive/1_34_760842868.dbf                           A          1         34            690028
/home/oracle/oracle/product/archive/1_35_760842868.dbf                           A          1         35            690028
/home/oracle/oracle/product/archive/1_36_760842868.dbf                           A          1         36            690028
/home/oracle/oracle/product/archive/1_1_764549776.dbf                            A          1          1            915159
/home/oracle/oracle/product/archive/1_2_764549776.dbf                            A          1          2            915159
/home/oracle/oracle/product/archive/1_3_764549776.dbf                            A          1          3            915159