官方文档中关于控制文件这一段有一句话：
If the actual datafile corresponding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actual datafile. If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then you cannot use the rename operation to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile.
感到有点疑惑，于是做了这个实验。
实验步骤如下：
备份控制文件，创建新的表空间，将表空间offline/不对表空间做操作，关闭，用旧的控制文件替代现有的/重建控制文件，打开nomount，将表空间online/删除表空间
1.备份控制文件
SQL> alter database backup controlfile to 'C:\app\sunzj\oradata\orcl\control.bkp';
Database altered
此法备份的是二进制文件
SQL> alter database backup controlfile to trace;
Database altered
此法导出的创建controlfile的sql语句，也就是说，控制文件中保存的其他信息并没有在这个方法中备份（这里有点疑问，以后谈）
2.创建新的表空间
SQL> create tablespace archer_01
2 datafile 'C:\APP\SUNZJ\ORADATA\ORCL\ARCHER01.dbf' size 500k;
Tablespace created
SQL> create table archer1
2 ( id number(1)
3 )
4 tablespace archer_01;
3.表空间offline
SQL> alter tablespace archer_01 offline;
Tablespace altered
试着往archer1中插入数据
SQL> insert into archer1 values(1);
insert into archer1 values(1)
ORA-01542: 表空间 'ARCHER_01' 脱机, 无法在其中分配空间
4.数据库关闭
5.用第一步中备份的二进制文件替换之前的控制文件
先将个目录下的控制文件移至另一目录
启动数据库至mount
SQL> alter database mount;
ORA-00205: error in identifying control file, check alert log for more info
看一下告警日志
Sat Jul 13 16:07:52 2013
ORA-00210: ???????????
ORA-00202: ????: ''C:\APP\SUNZJ\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL''
ORA-27041: ??????OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-00210: ???????????
ORA-00202: ????: ''C:\APP\SUNZJ\ORADATA\ORCL\CONTROL01.CTL''
ORA-27041: ??????
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。 这里为什么会有问号？乱码？
将备份的二进制文档改名放至目录下
注意，这里需要将文件放至control_files定义的所有目录下
alter database mount
数据库已更改
6.查看数据库数据文件的状态，主要判断archer01的状态
SQL> select * from v$DATAFILE;
好吧，数据文件在数据字典中没有显示了
查看了下官方文档，文档中是指重建控制文件会出现主题描述的情况 （事后证明这里出错了）
所以用步骤1中方法二生成的trace文件，提取出其中的sql
SQL>@F:\control.sql
这里执行完后，数据库处于mount状态
发现在v$datafile中也是没有archer01的显示。
感觉很怪，先尝试打开数据库
alter database open;
SQL> select * from v$datafile; FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ --------------------- 1 7 2010/4/2 13:1 0 1 SYSTEM READ WRITE 1125652 2013/7/13 17:18 0 0 0 713031680 87040 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0 0 0 NO 0 0 2 2161 2010/4/2 13:1 1 2 ONLINE READ WRITE 1125652 2013/7/13 17:18 0 0 0 555745280 67840 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0 0 0 NO 0 0 3 938215 2010/4/2 14:2 2 3 ONLINE READ WRITE 1125652 2013/7/13 17:18 0 0 0 94371840 11520 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0 0 0 NO 0 0 4 18017 2010/4/2 13:1 4 4 ONLINE READ WRITE 1125652 2013/7/13 17:18 0 0 0 5242880 640 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0 0 0 NO 0 0 5 1124188 6 5 OFFLINE DISABLED 1124813 0 1124813 2013/7/13 1 0 0 0 0 0 8192 C:\APP\SUNZJ\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005 0 4294967295 UNKNOWN 0 0 0 NO 0 0
好吧，竟然是打开后，才会出现这个MISSING0005（这里的0005就是文件号）
7.重命名
SQL> alter database rename file 'C:\APP\SUNZJ\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005' to 'C:\APP\SUNZJ\ORADATA\ORCL\ARCHER01.DBF';
Database altered
8.表空间online
SQL> alter tablespace archer_01 online;
Tablespace altered
SQL> select * from v$datafile; FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ --------------------- 1 7 2010/4/2 13:1 0 1 SYSTEM READ WRITE 1125652 2013/7/13 17:18 0 0 0 713031680 87040 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0 0 0 NO 0 0 2 2161 2010/4/2 13:1 1 2 ONLINE READ WRITE 1125652 2013/7/13 17:18 0 0 0 555745280 67840 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0 0 0 NO 0 0 3 938215 2010/4/2 14:2 2 3 ONLINE READ WRITE 1125652 2013/7/13 17:18 0 0 0 94371840 11520 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0 0 0 NO 0 0 4 18017 2010/4/2 13:1 4 4 ONLINE READ WRITE 1125652 2013/7/13 17:18 0 0 0 5242880 640 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0 0 0 NO 0 0 5 1124188 2013/7/13 15: 6 5 ONLINE READ WRITE 1127501 2013/7/13 17:45 0 1124813 1127501 2013/7/13 1 516096 63 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\ARCHER01.DBF 0 8192 UNKNOWN 0 0 0 NO 0 0
9.这次不把表空间offline，重复一下4开始的操作
SQL> select * from v$datafile; FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ --------------------- 1 7 2010/4/2 13:1 0 1 SYSTEM READ WRITE 1127825 2013/7/13 17:47 0 0 0 713031680 87040 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0 0 0 NO 0 0 2 2161 2010/4/2 13:1 1 2 ONLINE READ WRITE 1127825 2013/7/13 17:47 0 0 0 555745280 67840 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0 0 0 NO 0 0 3 938215 2010/4/2 14:2 2 3 ONLINE READ WRITE 1127825 2013/7/13 17:47 0 0 0 94371840 11520 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0 0 0 NO 0 0 4 18017 2010/4/2 13:1 4 4 ONLINE READ WRITE 1127825 2013/7/13 17:47 0 0 0 5242880 640 0 8192 C:\APP\SUNZJ\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0 0 0 NO 0 0 5 1124188 6 5 RECOVER READ WRITE 0 0 1127866 2013/7/13 1 0 0 0 0 0 8192 C:\APP\SUNZJ\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005 0 4294967295 UNKNOWN 0 0 0 NO 0 0
可以发现，启动后，相应的file处于recover状态，
改完名后，尝试把表空间online一下
SQL> alter tablespace archer_01 online;
alter tablespace archer_01 online
ORA-01113: 文件 5 需要介质恢复
ORA-01110: 数据文件 5: 'C:\APP\SUNZJ\ORADATA\ORCL\ARCHER01.DBF'
SQL> recover datafile 5;
完成介质恢复
SQL> alter tablespace archer_01 online;
Tablespace altered
看来即使表空间不处于offline和只读模式，还是可以通过数据文件的介质恢复来重新使用的。
官方文档如下：
If the actual datafile corresponding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actual datafile.
If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then you cannot use the rename operation to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile.
查看了一下，这样说的原因，在于创建控制文件的脚本中多了resetlogs，所以无法进行介质恢复。网上有很多照这段话翻译，没有联系前文，直接说非脱机和只读模式表空间不能恢复必须删除，真是让人汗颜。
要说明一下，这里数据文件需要介质恢复的根本原因是控制文件中的scn和数据文件中的scn不同。两者要同步才能打开该文件，一般情况下控制文件中的scn是现有值。可以通过一下语句进行查询
控制文件： select file#，checkpoint_change# from v$datafile where file#=5;
数据文件： select file#，checkpoint_change# from v$datafile_header where file#=5；
参考文档：
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control004.htm
http://www.itpub.net/forum.php?mod=viewthread&tid=1660804&highlight=&page=1
