因为重建控制文件要丢失stop scn，并且数据文件里面没有存有表空间read only的信息。
数据字典里面存有但是OPEN前是无法读取的。所以最好能按照backup 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 Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "O11203" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/o11203/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/o11203/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/o11203/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/o11203/system01.dbf',
'/u01/app/oracle/oradata/o11203/sysaux01.dbf',
'/u01/app/oracle/oradata/o11203/streams_tbs.dbf',
'/u01/app/oracle/oradata/o11203/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/u01/app/oracle/oradata/o11203/users01.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/app/oracle/oradata/o11203/temp0101.dbf'
     SIZE 1048576000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/o11203/temp01.dbf'
     SIZE 104857600 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/oradata/o11203/temp0102.dbf'
     SIZE 104857600 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

如果重建时，包含了read only的数据文件，就要麻烦一点点

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
****************************************
*** zhangqiaoc Test Env ****************
****************************************

ORACLE instance started.

Total System Global Area 1068994560 bytes
Fixed Size                  2235080 bytes
Variable Size             796919096 bytes
Database Buffers          264241152 bytes
Redo Buffers                5599232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "O11203" NORESETLOGS FORCE LOGGING ARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
    MAXINSTANCES 8
5    6      MAXLOGHISTORY 292
7 LOGFILE
8    GROUP 1 '/u01/app/oracle/oradata/o11203/redo01.log' SIZE 50M BLOCKSIZE 512,
9    GROUP 2 '/u01/app/oracle/oradata/o11203/redo02.log' SIZE 50M BLOCKSIZE 512,
10    GROUP 3 '/u01/app/oracle/oradata/o11203/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13    '/u01/app/oracle/oradata/o11203/system01.dbf',
14    '/u01/app/oracle/oradata/o11203/sysaux01.dbf',
15    '/u01/app/oracle/oradata/o11203/streams_tbs.dbf',
16    '/u01/app/oracle/oradata/o11203/undotbs02.dbf',
17    '/u01/app/oracle/oradata/o11203/users01.dbf'     <== read only tablespace
18 CHARACTER SET ZHS16GBK
19 ;

Control file created.

SQL> RECOVER DATABASE
ORA-00279: change 29862501 generated at 10/07/2013 09:39:19 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_10_07/o1_mf_1_151_%u_.
arc
ORA-00280: change 29862501 for thread 1 is in sequence #151

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_10_07/o1_mf_1_151_%u_
.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_10_07/o1_mf_1_151_%u_
.arc'
ORA-27037: unable to obtain file status         <== 需要从read only开始的日志问题，这些文件已经不存在了
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/o11203/users01.dbf'

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database datafile 4 online;        <== 必须重新online表空间后在online数据文件
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/o11203/users01.dbf'

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter database datafile 4 online;

Database altered.

ITPub博客

含read only表空间的数据库的控制文件重建

最新文章