重建控制檔案與 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.