原文链接：http://www.itpub.net/326938.html

起个题目：关于数据文件恢复
这是一个假设的例子（给出的条件并不一定都须用上）：
周二晚，有一个 联机拷贝os文件的脚本，依次执行下面伪代码

select 'cp ' ||name || '......' from v$datafile;

select 'alter tablespace '||tablespace_name || 'end backup;' from dba_tablespaces;

spool off

then run the script;

但是不幸，在备份过程中失败，只有部分文件备份成功，但是dba并不知道。

周三，dba新加了一个数据文件

周四，控制文件损坏，dba手工创建了控制文件

周五，磁盘损坏，恰好丢失了所有控制文件和新加的这个数据文件（该数据文件中有非常重要的期望恢复的数据）其他数据文件都。检查备份，结果发现原来的备份只有部分数据文件拷贝成功然后备份意外终止不再继续执行下面任何代码。 很不幸，备份出来的文件也在这个磁盘上坏了。

在这种情况下，请问，该数据文件是否能恢复，如能，请描述过程，如不能，请阐述理由。

测试过程：

一、周二：DBA备份了控制文件，对表空间users和test进行备份，但是备份失败，没有继续执行接下的脚本。BITI在这里备份没有正常结束的意思是给以后的恢复增加难， 事实上这一步的用处不大，在后面的恢复过程中也没增加难度，只要把置于备份状态的数据文件进行恢复就可将其变至非备份状态。

SQL> alter database backup controlfile to 'e:ctl.bak';

Database altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> alter tablespace test begin backup;

Tablespace altered.

二、周三：DBA为表空间TEST新加了一个数据文件：

SQL> alter tablespace test add datafile 'd:oracleoradatademotest02.dbf' size 5M;

Tablespace altered.

三、周四：用户插入数据并提交，此时发生了INSTANCE CRASH，丢失了全部控制文件。

SQL> create table t (a int) tablespace test;

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown abort
ORACLE instance shut down.

SQL> host del d:oracleoradatademo*.ctl

在CRASH后，DBA启动数据库发现控制文件全部丢失后，手工创建了控制文件并进行相应恢复：

SQL> startup
ORACLE instance started.

Total System Global Area   55646712 bytes
Fixed Size                   453112 bytes
Variable Size              37748736 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

SQL> create controlfile reuse database DEMO noresetlogs archivelog
  2  logfile
  3  'd:oracleoradatademoredo01.log' size 5M,
  4  'd:oracleoradatademoredo02.log' size 5M
  5  datafile
  6  'd:oracleoradatademosystem01.dbf' ,
  8  'd:oracleoradatademoundotbs01.dbf',
  9  'd:oracleoradatademousers01.dbf',
 10  'd:oracleoradatademousers02.dbf',
 11  'd:oracleoradatademousers03.dbf',
 12  'd:oracleoradatademotest01.dbf',
 13  'd:oracleoradatademotest02.dbf'
 14  character set zhs16gbk;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

这时候我们可以发现用户提交的数据已恢复成功，且原来置于备份状态的数据文件状态已经改变了。

SQL> select * from t;

         A
----------
         1

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ----------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE             217167 12-3?  -05
         4 NOT ACTIVE             217167 12-3?  -05
         5 NOT ACTIVE             217167 12-3?  -05
         6 NOT ACTIVE             217171 12-3?  -05
         7 NOT ACTIVE                  0

7 rows selected.

四、周五：再次发生CRASH，丢失了全部控制文件和新增加的数据文件：

SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown abort
ORACLE instance shut down.

SQL> host del d:oracleoradatademo*.ctl

SQL> host del d:oracleoradatademotest02.dbf

SQL> startup
ORACLE instance started.

Total System Global Area   55646712 bytes
Fixed Size                   453112 bytes
Variable Size              37748736 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

将备份的控制文件进行复原：

SQL> host copy e:ctl.bak d:oracleoradatademocontrol01.ctl
已复制         1 个文件。

SQL> host copy e:ctl.bak d:oracleoradatademocontrol02.ctl
已复制         1 个文件。

SQL> host copy e:ctl.bak d:oracleoradatademocontrol03.ctl
已复制         1 个文件。

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01991: invalid password file 'D:oracleora92DATABASEPWDdemo.ORA'

SQL> host del D:oracleora92DATABASEPWDdemo.ORA

SQL> host orapwd file=D:oracleora92DATABASEPWDdemo.ORA password=admin entries=5

开始对数据库进行恢复，并指定一个当前联机日志进行恢复：

SQL> recover database using backup controlfile;
ORA-00279: change 216889 generated at 03/12/2005 17:25:13 needed for thread 1

Specify log: {=suggested | filename | AUTO | CANCEL}
d:oracleoradatademoredo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 7: 'D:ORACLEORADATADEMOTEST02.DBF'

ORA-01112: media recovery not started

恢复过程中控制文件已经认到新添加了的数据文件，我们进行重新创建：

SQL> alter database create datafile 'D:ORACLEORADATADEMOTEST02.DBF' as 'D:O
RACLEORADATADEMOTEST02.DBF';
alter database create datafile 'D:ORACLEORADATADEMOTEST02.DBF' as 'D:ORACLE
ORADATADEMOTEST02.DBF'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"D:ORACLEORADATADEMOTEST02.DBF"

SQL> select name,status from v$datafile;

NAME                                     STATUS
---------------------------------------- -------
D:ORACLEORADATADEMOSYSTEM01.DBF      SYSTEM
D:ORACLEORADATADEMOUNDOTBS01.DBF     ONLINE
D:ORACLEORADATADEMOUSERS01.DBF       ONLINE
D:ORACLEORADATADEMOUSERS02.DBF       ONLINE
D:ORACLEORADATADEMOUSERS03.DBF       ONLINE
D:ORACLEORADATADEMOTEST01.DBF        ONLINE
D:ORACLEORA92DATABASEUNNAMED00007    RECOVER

7 rows selected.

SQL> alter database create datafile 'D:ORACLEORA92DATABASEUNNAMED00007' as '
D:ORACLEORADATADEMOTEST02.DBF';

Database altered.

新添加的数据文件已经创建完毕，这时候开始对数据库进行恢复：

SQL> recover database using backup controlfile;
ORA-00279: change 217203 generated at 03/12/2005 17:30:02 needed for thread 1
ORA-00289: suggestion : D:ORACLEORADATADEMOARCHIVE1.ARC
ORA-00280: change 217203 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
d:oracleoradatademoredo01.log
ORA-00279: change 237249 generated at 03/12/2005 17:34:21 needed for thread 1
ORA-00289: suggestion : D:ORACLEORADATADEMOARCHIVE2.ARC
ORA-00280: change 237249 for thread 1 is in sequence #2
ORA-00278: log file 'd:oracleoradatademoredo01.log' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
d:oracleoradatademoredo02.log
Log applied.
Media recovery complete.
SQL> select name,status from v$datafile;

NAME                                     STATUS
---------------------------------------- -------
D:ORACLEORADATADEMOSYSTEM01.DBF      SYSTEM
D:ORACLEORADATADEMOUNDOTBS01.DBF     ONLINE
D:ORACLEORADATADEMOUSERS01.DBF       ONLINE
D:ORACLEORADATADEMOUSERS02.DBF       ONLINE
D:ORACLEORADATADEMOUSERS03.DBF       ONLINE
D:ORACLEORADATADEMOTEST01.DBF        ONLINE
D:ORACLEORADATADEMOTEST02.DBF        ONLINE

7 rows selected.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ----------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE             217167 12-3?  -05
         4 NOT ACTIVE             217167 12-3?  -05
         5 NOT ACTIVE             217167 12-3?  -05
         6 NOT ACTIVE             217171 12-3?  -05
         7 NOT ACTIVE                  0

7 rows selected.

五、打开数据库，验证恢复成功（如果是生产数据库的话此时需马上进行一次全备份）：

SQL> alter database open resetlogs;

Database altered.

SQL> select * from t;

         A
----------
         1
         2