an example of recreating controlfile with offline and read only datafile

假如数据库中存在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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26478664/viewspace-712015/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26478664/viewspace-712015/