柔中带刚，刚中带柔，淫荡中富含柔和，刚猛中荡漾风骚，无坚不摧，无孔不入！
全部博文（1669）
分类： Oracle
2012-02-06 09:24:44
有关控制文件之前整理过一篇Blog，参考：
http://blog.csdn.net/tianlesoftware/article/details/4974440
这里主要是说明一下重建控制文件2个注意的地方：
1. 对只读表空间
假如存在一个只读的表空间，那么在重建控制文件之后，read-only的数据文件会重命名为MISSING00005的格式，最后是5位数字，这个数据根据file_id 对应。并且datafile 也会变成offline。
所以在重建控制文件之后，我们需要对只读文件的datafile 进行rename 操作，还原成原来的名称，并且修改其状态为online。具体操作示例有说明。
在重建控制文件之前需要留意datafile 的文件名称，如果有多个datafile，那么就需要注意其顺序。这个需要注意一下。
2. TEMP 表空间
重建控制文件之后，原来的临时表空间中没有数据文件，需要单独添加。我们可以从DBA_TABLESPACES视图中查看到TEMP 表空间，但是在v$tempfile视图中却查看不到datafile。所以必须要手工的添加temporary datafile。
一.表空间read-only示例
--查看相关的信息
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/gg2/users01.dbf
/u01/app/oracle/oradata/gg2/undotbs01.dbf
/u01/app/oracle/oradata/gg2/sysaux01.dbf
/u01/app/oracle/oradata/gg2/system01.dbf
--创建表空间
SQL> create tablespace dave datafile'/u01/app/oracle/oradata/gg2/dave01.dbf' size 20M autoextendoff,'/u01/app/oracle/oradata/gg2/dave02.dbf' size 20M autoextend off;
Tablespace created.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DAVE ONLINE
6 rows selected.
SQL> col file_name for a43
SQL> selectfile_name,status,online_status from dba_data_files;
FILE_NAME STATUS ONLINE_
---------------------------------------------------- -------
/u01/app/oracle/oradata/gg2/users01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/undotbs01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/sysaux01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/system01.dbf AVAILABLE SYSTEM
/u01/app/oracle/oradata/gg2/dave01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/dave02.dbf AVAILABLE ONLINE
6 rows selected.
--将表空间dave设置成只读
SQL> alter tablespace dave read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DAVE READ ONLY
6 rows selected.
SQL> selectfile_name,status,online_status from dba_data_files;
FILE_NAME STATUS ONLINE_
---------------------------------------------------- -------
/u01/app/oracle/oradata/gg2/users01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/undotbs01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/sysaux01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/system01.dbf AVAILABLE SYSTEM
/u01/app/oracle/oradata/gg2/dave01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/dave02.dbf AVAILABLE ONLINE
6 rows selected.
--将表空间设置成读写
SQL> alter tablespace dave read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DAVE ONLINE
6 rows selected.
SQL> selectfile_name,status,online_status from dba_data_files;
FILE_NAME STATUS ONLINE_
---------------------------------------------------- -------
/u01/app/oracle/oradata/gg2/users01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/undotbs01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/sysaux01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/system01.dbf AVAILABLE SYSTEM
/u01/app/oracle/oradata/gg2/dave01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/dave02.dbf AVAILABLE ONLINE
6 rows selected.
二. 重建控制文件 测试
2.1 先将表空间read-only
SQL> alter tablespace dave read only;
Tablespace altered.
2.2 将控制文件dump到trace
SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfileto trace;
Database altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc
2.3 查看trace 文件，取得控制文件创建的SQL 代码
--在这个trace文件里对我们的的影响写的很清楚：
gg3:/u01> cat /u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc
Trace file/u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
ORACLE_HOME =/u01/app/oracle/product/11.2.0.3/db_1
System name: Linux
Node name: gg3
Release: 2.6.18-164.el5xen
Version: #1 SMP Tue Aug 18 15:59:52 EDT 2009
Machine: x86_64
VM name: Xen Version: 3.1 (PVM)
Instance name: gg2
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 3700, image: oracle@gg3(TNS V1-V3)
…
*** 2012-02-03 14:15:26.571
-- The following are current System-scopeREDO Log Archival related
-- parameters and can be included in thedatabase initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="gg2"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE,NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
--LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRMNOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATENODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURENOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements,each of which creates a new
-- control file and uses it to open thedatabase. The first set opens
-- the database with the NORESETLOGS optionand should be used only if
-- the current versions of all online logsare available. The second
-- set opens the database with theRESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can becopied from the trace into
-- a script file, edited as necessary, andexecuted when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--以NORESETLOGS模式创建控制文件的详细步骤
--
-- The following commands will create a newcontrol file and use it
-- to open the database.
-- Data used by Recovery Manager will belost.
-- Additional logs may be required formedia recovery of offline
-- Use this only if the current versions ofall online logs are
-- available.
-- After mounting the created controlfile,the following SQL
-- statement will place the database in theappropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE"GG2" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/gg2/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/gg2/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/gg2/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/gg2/system01.dbf',
'/u01/app/oracle/oradata/gg2/sysaux01.dbf',
'/u01/app/oracle/oradata/gg2/undotbs01.dbf',
'/u01/app/oracle/oradata/gg2/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed toexisting filenames on
-- disk. Any one log file from each branchcan be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_762083164.dbf';
-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_765219083.dbf';
-- Recovery is required if any of thedatafiles are restored backups,
-- or if the last shutdown was not normalor immediate.
RECOVER DATABASE
-- Set Database Guard and/or SupplementalLogging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- All logs need archiving and a log switchis needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Files in read-only tablespaces are nownamed.
--注意这里，DBOPEN 之后，之前dave 表空间的数据文件名称发生了变化，我们需要重新对其进行rename，然后才可以onliedave 表空间。
ALTER DATABASE RENAME FILE 'MISSING00005'
TO'/u01/app/oracle/oradata/gg2/dave01.dbf';
ALTER DATABASE RENAME FILE 'MISSING00006'
TO'/u01/app/oracle/oradata/gg2/dave02.dbf';
-- Online the files in read-onlytablespaces.
ALTER TABLESPACE "DAVE" ONLINE;
-- Commands to add tempfiles to temporarytablespaces.
-- Online tempfiles have complete spaceinformation.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE'/u01/app/oracle/oradata/gg2/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ONNEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--以RESETLOGS模式重建控制文件的详细步骤
--
-- The following commands will create a newcontrol file and use it
-- to open the database.
-- Data used by Recovery Manager will belost.
-- The contents of online logs will be lostand all backups will
-- be invalidated. Use this only if onlinelogs are damaged.
-- After mounting the created controlfile,the following SQL
-- statement will place the database in theappropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE"GG2" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/gg2/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/gg2/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/gg2/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/gg2/system01.dbf',
'/u01/app/oracle/oradata/gg2/sysaux01.dbf',
'/u01/app/oracle/oradata/gg2/undotbs01.dbf',
'/u01/app/oracle/oradata/gg2/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed toexisting filenames on
-- disk. Any one log file from each branchcan be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_762083164.dbf';
-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_765219083.dbf';
-- Recovery is required if any of thedatafiles are restored backups,
-- or if the last shutdown was not normalor immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Set Database Guard and/or SupplementalLogging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Database can now be opened zeroing theonline logs.
ALTER DATABASE OPENRESETLOGS;
-- Files in read-only tablespaces are nownamed.
--同样需要单独处理只读表空间
ALTER DATABASE RENAME FILE 'MISSING00005'
TO'/u01/app/oracle/oradata/gg2/dave01.dbf';
ALTER DATABASE RENAME FILE 'MISSING00006'
TO'/u01/app/oracle/oradata/gg2/dave02.dbf';
-- Online the files in read-onlytablespaces.
ALTER TABLESPACE "DAVE" ONLINE;
-- Commands to add tempfiles to temporarytablespaces.
-- Online tempfiles have complete spaceinformation.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE'/u01/app/oracle/oradata/gg2/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ONNEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
*** 2012-02-03 14:15:33.992
Processing Oradebug command'tracefile_name'
*** 2012-02-03 14:15:33.996
Oradebug command 'tracefile_name' consoleoutput:
/u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc
gg3:/u01>
由此trace 文件可以确认，重建控制文件需要单独处理只读表空间和TEMP 表空间。
2.4 使用NORESETLOGS 模式重建控制文件
具体的操作步骤，在trace文件里有说明。
--DB 启动到nomount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1006305280 bytes
Fixed Size 2234600 bytes
Variable Size 650118936 bytes
Database Buffers 348127232 bytes
Redo Buffers 5824512 bytes
SQL>
--重建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE"GG2" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1'/u01/app/oracle/oradata/gg2/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2'/u01/app/oracle/oradata/gg2/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3'/u01/app/oracle/oradata/gg2/redo03.log' SIZE 50M BLOCKSIZE 512
11 --STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/gg2/system01.dbf',
14 '/u01/app/oracle/oradata/gg2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/gg2/undotbs01.dbf',
16 '/u01/app/oracle/oradata/gg2/users01.dbf'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
--控制文件的位置在初始化参数里指定。
--打开数据库
SQL> alterdatabase open;
Database altered.
--查看表空间和数据文件的状态：
SQL> colfile_name for a55
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DAVE READ ONLY
6 rows selected.
SQL> selectfile_id,file_name,status,online_status from dba_data_files;
FILE_ID FILE_NAME STATUS ONL
----------------------------------------------------------------- --------- ---
4/u01/app/oracle/oradata/gg2/users01.dbf AVAILABLE ONL
3/u01/app/oracle/oradata/gg2/undotbs01.dbf AVAILABLE ONL
2/u01/app/oracle/oradata/gg2/sysaux01.dbf AVAILABLE ONL
1/u01/app/oracle/oradata/gg2/system01.dbf AVAILABLE SYS
5/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00005 AVAILABLE OFF
6/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00006 AVAILABLE OFF
6 rows selected.
--注意这里，我们之前read only 的dave 表空间还是read only的，但是其对应的数据文件名称发生了改变，变成了MISSING00005，最后是5位数字，这个数据根据file_id对应。
同时注意这里的datafile 状态变成了offline。
--如果我们现在直接online 或者read write 表空间，都会报错：
SQL> altertablespace dave online;
alter tablespacedave online
*
ERROR at line 1:
ORA-01157: cannotidentify/lock data file 5 - see DBWR trace file
ORA-01111: namefor data file 5 is unknown - rename to correct file
ORA-01110: datafile 5:
'/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00005'
SQL> alter tablespace dave read write;
alter tablespace dave read write
*
ERROR at line 1:
ORA-01135: file 5accessed for DML/query is offline
ORA-01111: namefor data file 5 is unknown - rename to correct file
ORA-01110: datafile 5:
'/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00005'
也是提示我们需要对文件进行rename。
--现在对2个datafile 进行rename 操作：
SQL> ALTERDATABASE RENAME FILE 'MISSING00005'
2 TO'/u01/app/oracle/oradata/gg2/dave01.dbf';
Database altered.
SQL> ALTERDATABASE RENAME FILE 'MISSING00006'
2 TO'/u01/app/oracle/oradata/gg2/dave02.dbf';
Database altered.
--在将表空间online：
SQL> altertablespace dave read write;
alter tablespacedave read write
*
ERROR at line 1:
ORA-01135: file 5accessed for DML/query is offline
ORA-01110: datafile 5: '/u01/app/oracle/oradata/gg2/dave01.dbf'
因为之前的datafile 是offline的，所以我们直接修改表空间模式时，提示我们要访问的datafile 是offline的。
我们使用命令将表空间下的所有datafileonline，
SQL> altertablespace dave online;
Tablespace altered.
也可以使用alter database datafile 命令分次处理单个的datafile。
关于这个2个命令的区别参考我的blog：
ALTERDATABASE 与 ALTERTABLESPACE OFFLINE的区别
http://blog.csdn.net/tianlesoftware/article/details/4898800
现在我们就可以看到数据文件已经正常了：
SQL> select file_name,status,online_status from dba_data_files;
FILE_NAME STATUS ONLINE_
---------------------------------------------------------------- -------
/u01/app/oracle/oradata/gg2/users01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/undotbs01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/sysaux01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/system01.dbf AVAILABLE SYSTEM
/u01/app/oracle/oradata/gg2/dave01.dbf AVAILABLE ONLINE
/u01/app/oracle/oradata/gg2/dave02.dbf AVAILABLE ONLINE
但是我们的表空间还是read only 的状态：
SQL> selecttablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DAVE READ ONLY
6 rows selected.
--但是要注意，我们重建控制文件之后还需要添加Temp 表空间，虽然我们从dba_tablespaces视图里可以查看到TEMP 表空间，但是该表空确实没有数据文件的，我们需要单独添加。
SQL> select * from v$tempfile;
no rows selected
--所以为了系统的正常运行，需要给TEMP 表空间添加数据文件：
SQL> ALTERTABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/gg2/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ONNEXT 655360 MAXSIZE 32767M;
Tablespacealtered.
在次查询就ok了：
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/gg2/temp01.dbf
至此，我们重建控制文件的操作就算全部完成。
