恢复表空间或数据文件与使用控制文件测试

不论是RMAN还是手动恢复均是使用数据文件的备份来应用归档前滚，即你无法恢复到数据文件备份以前的状态；相对数据文件，控制文件是可以恢复到之前状态的，如使用当前控制文件对数据库进行时间点恢复。即你要看数据文件备份的时间点来评估最早恢复时间

在恢复表空间或数据文件时与使用的控制文件有以下关系：

1. 恢复创建的表空间或数据文件

1. 数据文件备份 --> 控制文件 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句
2. 数据文件备份 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句 --> 控制文件

前者控制文件不包含创建的表空间或数据文件，而后者包含

1. 恢复DROP的表空间或数据文件

1. 数据文件备份 --> 控制文件 --> 执行了DROP TABELSPACE或DROP DATAFILE语句
2. 数据文件备份 --> 执行了DROP TABELSPACE或DROP DATAFILE语句 --> 控制文件

前者控制文件包含DROP的表空间或数据文件，而后者不包含

结论：

1.1手动恢复需要创建空文件，1.2/2.1手动直接恢复即可

1.1/1.2/2.1使用RMAN直接恢复即可

2.2无法恢复

• RMAN恢复测试

只做以下两个测试，另外两个场景是肯定可以恢复的

测试1：

数据文件备份 --> 控制文件 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句

备份全库

RMAN> backup database include current controlfile;

创建表空间与测试表

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/orcl/tools01.dbf' size 20m;

SQL> create table t1(id number) tablespace tools;

SQL> insert into t1 values(111);

SQL> commit;

执行恢复

RMAN> startup mount

RMAN> set dbid 1589350487

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

查看测试数据

SQL> select * from t1; -- 111

SQL> select file_name,TABLESPACE_NAME from dba_data_files;

FILE_NAME      TABLESPACE_NAME

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

/u01/app/oracle/oradata/orcl/tools01.dbf   TOOLS

测试2：

数据文件备份 --> 执行了DROP TABELSPACE或DROP DATAFILE语句 --> 控制文件

创建表空间与测试表

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/orcl/tools01.dbf' size 20m;

SQL> create table t1(id number) tablespace tools;

SQL> insert into t1 values(111);

SQL> commit;

备份全库

RMAN> backup database;

删除表空间

SQL> select current_scn from v$database;        --    5344394

SQL> drop tablespace tools including contents and datafiles;

执行恢复

RMAN> startup mount

RMAN> restore database;

RMAN> recover database until scn 5344394;

RMAN> alter database open resetlogs;

查看测试表

SQL> select * from t1;

ORA-00376: file 22 cannot be read at this time

ORA-01111: name for data file 22 is unknown - rename to correct file

ORA-01110: data file 22:

'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00022'

SQL> select file_name,TABLESPACE_NAME from dba_data_files;

FILE_NAME            TABLESPACE_NAME

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

/u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00022    TOOLS

SQL> select file#,NAME from v$datafile;

FILE#  NAME

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

22    /u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00022

SQL> select file#,name from v$datafile_header;

FILE#  NAME

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

22

$ ll /u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00022

ls: cannot access /u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00022: No such file or directory

说明RESTORE只还原当前控制文件包括的SCHEMA，即上面根本未还原TOOLS表空间

The control file must contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged data file was added to the database)

• 手动恢复测试  

手动恢复与RMAN恢复的适用性是相同的，即无法恢复控制文件未包括的已DROP数据文件

测试1：

数据文件备份 --> 控制文件 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句

备份全库

SQL> Alter database begin backup;

$ cp -r orcl orclbak

SQL> Alter database end backup;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/cf.bak' REUSE;

创建表空间与测试表

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/orcl/tools01.dbf' size 20m;

SQL> create table t1(id number) tablespace tools;

SQL> insert into t1 values(111);

SQL> commit;

执行恢复

SQL> startup nomount

SQL> show parameter control

NAME                              VALUE

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

control_files /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl

$ cp /u01/app/oracle/cf.bak /u01/app/oracle/oradata/orcl/control01.ctl

$ cp /u01/app/oracle/cf.bak /u01/app/oracle/oradata/orcl/control02.ctl

SQL> alter database mount;

$ cp orclbak/*.dbf orcl

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 5349219 generated at 07/15/2021 17:20:00 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_15/o1_mf_1_1_%u_.arc

ORA-00280: change 5349219 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 24: '/u01/app/oracle/oradata/orcl/tools01.dbf'

SQL> select name from  v$datafile;

NAME

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

/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00024

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00024' AS '/u01/app/oracle/oradata/orcl/tools01.dbf';

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 5349375 generated at 07/15/2021 17:24:01 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_15/o1_mf_1_1_%u_.arc

ORA-00280: change 5349375 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

Log applied.

Media recovery complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> SELECT * FROM T1;   --111

SQL> select name from  v$datafile;

NAME

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

/u01/app/oracle/oradata/orcl/tools01.dbf

测试2：

数据文件备份 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句 --> 控制文件

备份全库

SQL> Alter database begin backup;

$ cp -r orcl orclbak

SQL> Alter database end backup;

创建表空间与测试表

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/orcl/tools01.dbf' size 20m;

SQL> create table t1(id number) tablespace tools;

SQL> insert into t1 values(111);

SQL> commit;

执行恢复

SQL> startup mount

$ cp orclbak/*.dbf orcl

SQL> RECOVER DATABASE;

SQL> alter database open;

SQL> SELECT * FROM T1;   --111

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/orcl/tools01.dbf