ORACLE9i备份恢复小实验系列之七：使用备份的控制文件进行不完全恢复

恢复时，使用备份的控制文件
The current time is 12:00 p.m. on March 9, 2002.
The tablespace containing the EMPLOYEES table has been dropped.
The error occurred around 11:45 a.m.
Many employee records were updated this morning, but not since 11:00 a.m.
Backups are taken every night.

我制造的情景

SQL> create table scott.test2(n1 number) tablespace users;
SQL> alter system switch logfile;
SQL> insert into scott.test2 values(1);
SQL> commit;
SQL> alter system switch logfile;
SQL> drop tablespace users including contents and datafiles;
SQL> create table scott.test3(n1 number) tablespace tools;
SQL> insert into scott.test3 values(1);
SQL> commit;
SQL> alter system switch logfile;

现在需要把 test2给找回来

SQL> shutdown immediate;
OS: 把datafile和control file都copy回来

打开 D:\oracle\admin\ora\bdump\oraALET.log

找到最近一次drop tablespace
Sun Dec 02 16:12:20 2007
Deleted file D:\ORACLE\ORADATA\ORA\USERS01.DBF
Completed: drop tablespace users including contents and dataf

应将数据库恢复到
2007-12-02:16:12:19 的样子

SQL> startup
提示日志不匹配
ORA-00314: 日志 1 (线程 1)，预计序号  与  不匹配
ORA-00312: 联机日志 1 线程 1: 'D:\ORACLE\ORADATA\ORA\REDO01.LOG'

SQL> recover database until time '2004-09-25:23:26:53' using backup controlfile;
ORA-00279: 更改 271494 (在 12/02/2007 01:25:39 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\ORACLE\ORADATA\ORA\AWU7.ARC''
ORA-00280: 更改 271494 对于线程 1 是按序列 # 7 进行的

指定日志: {=suggested | filename | AUTO | CANCEL}

ORA-00279: 更改 271598 (在 12/02/2007 15:57:13 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\ORACLE\ORADATA\ORA\AWU8.ARC''
ORA-00280: 更改 271598 对于线程 1 是按序列 # 8 进行的
ORA-00278: 此恢复不再需要日志文件 'D:\ORACLE\ORADATA\ORA\AWU7.ARC'''

指定日志: {=suggested | filename | AUTO | CANCEL}

ORA-00326: 日志在更改 271616 开始，需要更早的更改 271598
ORA-00334: 归档日志: 'D:\ORACLE\ORADATA\ORA\AWU8.ARC'''

SQL> alter database open resetlogs;
SQL> select * from scott.test2;       
                              
             N1                    
     ----------                                                         
              1                    
            
SQL> select * from scott.test3;
select * from scott.test3
                    *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在