重建控制文件与 datafile offline,tablespace read only

转自：http://blog.sina.com.cn/s/blog_465a4a1e0100nvuv.html

假如数据库中存在offline 的datafile，read only 的Tablespace，这时重建控制文件，控制文件中是否会有关于offline datafile, read only tablespace的信息呢？
要如何恢复呢？

SQL> alter database datafile 3 offline;
数据库已更改。
SQL> alter tablespace users read only;
表空间已更改。
SQL> alter database backup controlfile to trace;
数据库已更改。
---其实Trace file中已经有了恢复的方法：
Trace file:
# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ICE" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORA92\ORADATA\ICE\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORA92\ORADATA\ICE\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORA92\ORADATA\ICE\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORA92\ORADATA\ICE\SYSTEM01.DBF',
'D:\ORA92\ORADATA\ICE\UNDOTBS01.DBF',
'D:\ORA92\ORADATA\ICE\TEST01.DBF'
CHARACTER SET AL32UTF8
;
---注意存在offline的datafile的信息，但是不存在read only tablespace(datafile)的信息.
# Take files offline to match current control file.
ALTER DATABASE DATAFILE 'D:\ORA92\ORADATA\ICE\TEST01.DBF' OFFLINE;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00004'
TO 'D:\ORA92\ORADATA\ICE\USER01.DBF';
# Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
# No tempfile entries found to add.

----假如是offline drop的datafile会有什么不同吗？

SQL> alter tablespace users read write;
表空间已更改。
SQL> alter database datafile 3 online;
alter database datafile 3 online
*
ERROR 位于第 1 行:
ORA-01113: 文件 3 需要介质恢复
ORA-01110: 数据文件 3: 'D:\ORA92\ORADATA\ICE\TEST01.DBF'
SQL> recover datafile 3;
完成介质恢复。
SQL> alter database datafile 3 online;
数据库已更改。
SQL> alter database datafile 3 offline drop;
数据库已更改。
SQL> alter tablespace users read only;
表空间已更改。
SQL> alter database backup controlfile to trace;
数据库已更改。

# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ICE" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORA92\ORADATA\ICE\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORA92\ORADATA\ICE\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORA92\ORADATA\ICE\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORA92\ORADATA\ICE\SYSTEM01.DBF',
'D:\ORA92\ORADATA\ICE\UNDOTBS01.DBF',
'D:\ORA92\ORADATA\ICE\TEST01.DBF'
CHARACTER SET AL32UTF8
;
---offline drop跟offline 生成的Trace file中有关控制文件的内容没什么不同，offline drop的datafile同样包括在控制文件里。
# Take files offline to match current control file.
ALTER DATABASE DATAFILE 'D:\ORA92\ORADATA\ICE\TEST01.DBF' OFFLINE DROP;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00004'
TO 'D:\ORA92\ORADATA\ICE\USER01.DBF';
# Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
# No tempfile entries found to add.