如何重建RAC的控制文件

在下面的一些情况下，可能需要重建控制文件：o 所有控制文件都已损坏或丢失
o 没有针对控制文件的备份或者备份已损坏下面是针对RAC环境下重建控制文件的具体过程，包括两个例子。一个是以noresetlogs模式来重建控制文件，一个是以resetlogs模式来重建控制文件。如果redo logs都存在而且没有被损坏，那么可以采用noresetlogs。使用resetlogs会将所有redo log清空而且重置log sequence为1.在RAC上重建控制文件与单实例有一些小区别：在重建控制文件前必须设置cluster_database=false，而且只启动一个实例来执行操作，否则会报错
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode重建完控制文件后，需要再将cluster_database设为true.TESTCASE1
---------------------------
用noresetlog模式重建控制文件过程：1.首先生成重建控制文件的脚本：
SQL> alter database backup controlfile to trace; Database altered. 2. 所生成的控制文件的脚本会在udump下：SQL> show parameter user_dump_dest NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/Oracle/diag/rdbms/racdb/RACDB1/trace数据库的Alert log中也会详细输出这个文件的路径和名字：
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/RACDB1_ora_10076.trc

（注意：例子中使用到的具体脚本，比如创建控制文件和添加临时数据文件的命令都在上面生成的trace文件中，其它步骤和命令也基本都在这个trace中）。3. 停止所有数据库实例：
[oracle@rac1 trace]$ srvctl stop database -d RACDB

4. 用noresetlog重建控制文件：
[oracle@rac1 trace]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 11:23:44 2014 Copyright （c） 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; <==启动一个实例
ORACLE instance started. Total System Global Area 739065856 bytes
Fixed Size 2232032 bytes
Variable Size 549454112 bytes
Database Buffers 184549376 bytes
Redo Buffers 2830336 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 （
9 "+DATA/racdb/onlinelog/group_1.261.783272805",
10 "+RECO/racdb/onlinelog/group_1.257.783272807"
11 ） SIZE 50M BLOCKSIZE 512,
12 GROUP 2 （
13 "+DATA/racdb/onlinelog/group_2.262.783272807",
14 "+RECO/racdb/onlinelog/group_2.258.783272809"
15 ） SIZE 50M BLOCKSIZE 512,
16 GROUP 3 （
17 "+DATA/racdb/onlinelog/group_3.269.804115405",
18 "+RECO/racdb/onlinelog/group_3.261.804115405"
19 ） SIZE 50M BLOCKSIZE 512,
20 GROUP 4 （
21 "+DATA/racdb/onlinelog/group_4.270.804115405",
22 "+RECO/racdb/onlinelog/group_4.263.804115407"
23 ） SIZE 50M BLOCKSIZE 512
24 -- STANDBY LOGFILE
25 DATAFILE
26 "+DATA/racdb/datafile/system.256.783272707",
27 "+DATA/racdb/datafile/sysaux.257.783272707",
28 "+DATA/racdb/datafile/undotbs1.258.783272707",
29 "+DATA/racdb/datafile/users.259.783272707",
30 "+DATA/racdb/datafile/example.264.783272831",
31 "+DATA/racdb/datafile/undotbs2.265.783273081"
32 CHARACTER SET AL32UTF8
33 ;
CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode <============由于没有设置cluster_database=false，所以报错需要将设置cluster_database=false：
SQL> alter system set cluster_database=false scope=spfile; System altered. SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down. SQL> startup nomount;
ORACLE instance started. Total System Global Area 739065856 bytes
Fixed Size 2232032 bytes
Variable Size 549454112 bytes
Database Buffers 184549376 bytes
Redo Buffers 2830336 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 （
9 "+DATA/racdb/onlinelog/group_1.261.783272805",
10 "+RECO/racdb/onlinelog/group_1.257.783272807"
11 ） SIZE 50M BLOCKSIZE 512,
12 GROUP 2 （
13 "+DATA/racdb/onlinelog/group_2.262.783272807",
14 "+RECO/racdb/onlinelog/group_2.258.783272809"
15 ） SIZE 50M BLOCKSIZE 512,
16 GROUP 3 （
17 "+DATA/racdb/onlinelog/group_3.269.804115405",
18 "+RECO/racdb/onlinelog/group_3.261.804115405"
19 ） SIZE 50M BLOCKSIZE 512,
20 GROUP 4 （
21 "+DATA/racdb/onlinelog/group_4.270.804115405",
22 "+RECO/racdb/onlinelog/group_4.263.804115407"
23 ） SIZE 50M BLOCKSIZE 512
24 -- STANDBY LOGFILE
25 DATAFILE
26 "+DATA/racdb/datafile/system.256.783272707",
27 "+DATA/racdb/datafile/sysaux.257.783272707",
28 "+DATA/racdb/datafile/undotbs1.258.783272707",
29 "+DATA/racdb/datafile/users.259.783272707",
30 "+DATA/racdb/datafile/example.264.783272831",
31 "+DATA/racdb/datafile/undotbs2.265.783273081"
32 CHARACTER SET AL32UTF8
33 ; Control file created. SQL> select * from v$log; <===确认redo log，thread#是正确的 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 11 52428800 512 2 NO
CURRENT 6876599 08-JAN-13 2.8147E+14 2 1 10 52428800 512 2 NO
INACTIVE 6825446 07-JAN-13 6876599 08-JAN-13 3 2 7 52428800 512 2 NO
INACTIVE 6877338 08-JAN-13 6960724 08-JAN-13
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
4 2 6 52428800 512 2 NO
INACTIVE 6815353 07-JAN-13 6877338 08-JAN-13SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required SQL> alter system archive log all; System altered. SQL> alter database open; Database altered. SQL> ALTER TABLESPACE TEMP ADD TEMPFILE "+DATA/racdb/tempfile/temp.263.783272821"
2 SIZE 39845888 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; 《==别忘了添加原来的临时文件到临时表空间中Tablespace altered. 6.将cluster_database设为true :
SQL> alter system set cluster_database=true scope=spfile; System altered. SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options 7. 启动所有实例：
[oracle@rac1 trace]$ srvctl start database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2TESTCASE2
---------------------------
用resetlogs模式重建控制文件Test Process: 1.首先生成重建控制文件的脚本：
SQL> alter database backup controlfile to trace; Database altered. 2. 数据库的Alert log中也会详细输出这个文件的路径和名字：
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/RACDB1_ora_5649.trc 3. 设置cluster_database=false：
SQL> alter system set cluster_database=false scope=spfile;
System altered.否则，在重建控制文件的时候会报下面的错误：CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode 4. 停止所有数据库实例：
[oracle@rac1 trace]$ srvctl stop database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is not running on node rac2 5. 用resetlogs模式重建控制文件：[oracle@rac1 trace]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 12:45:25 2013 Copyright （c） 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount;
ORACLE instance started. Total System Global Area 739065856 bytes
Fixed Size 2232032 bytes
Variable Size 549454112 bytes
Database Buffers 184549376 bytes
Redo Buffers 2830336 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 （
9 "+DATA/racdb/onlinelog/group_1.261.783272805",
10 "+RECO/racdb/onlinelog/group_1.257.783272807"
11 ） SIZE 50M BLOCKSIZE 512,
12 GROUP 2 （
13 "+DATA/racdb/onlinelog/group_2.262.783272807",
14 "+RECO/racdb/onlinelog/group_2.258.783272809"
15 ） SIZE 50M BLOCKSIZE 512
16 -- STANDBY LOGFILE
17 DATAFILE
18 "+DATA/racdb/datafile/system.256.783272707",
19 "+DATA/racdb/datafile/sysaux.257.783272707",
20 "+DATA/racdb/datafile/undotbs1.258.783272707",
21 "+DATA/racdb/datafile/users.259.783272707",
22 "+DATA/racdb/datafile/example.264.783272831",
23 "+DATA/racdb/datafile/undotbs2.265.783273081"
24 CHARACTER SET AL32UTF8
25 ; Control file created.SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; <=========必须使用UNTIL CANCEL，否则数据库无法open
ORA-00279: change 6976933 generated at 01/08/2013 12:45:12 needed for thread 1
ORA-00289: suggestion : +RECO
ORA-00280: change 6976933 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

接下来需要？？加其他thread，因为用resetlogs重建controlfile只是增加了thread为1的redo log：
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
2 GROUP 3 （
3 "+DATA/racdb/onlinelog/group_3.269.804115405",
4 "+RECO/racdb/onlinelog/group_3.261.804115405"
5 ） SIZE 50M BLOCKSIZE 512 REUSE,
6 GROUP 4 （
7 "+DATA/racdb/onlinelog/group_4.270.804115405",
8 "+RECO/racdb/onlinelog/group_4.263.804115407"
9 ） SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD LOGFILE THREAD 2
*
ERROR at line 1:
ORA-01276: Cannot add file +DATA/racdb/onlinelog/group_3.269.804115405. File
has an Oracle Managed Files file name.
对于ASM，使用了OMF命名规则时不能指定具体的文件名，只需要指定diskgroup名即可：SQL> ALTER DATABASE ADD LOGFILE THREAD 2
2 GROUP 3 （
3 "+DATA",
4 "+RECO"
5 ） SIZE 50M BLOCKSIZE 512 REUSE,
6 GROUP 4 （
7 "+DATA",
8 "+RECO"
9 ） SIZE 50M BLOCKSIZE 512 REUSE; Database altered. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> ALTER TABLESPACE TEMP ADD TEMPFILE "+DATA/racdb/tempfile/temp.263.783272821"
2 SIZE 39845888 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; Tablespace altered. 6. 设置cluster_database=true :
SQL> alter system set cluster_database=true scope=spfile; System altered. SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. 7. 启动所有实例：[oracle@rac1 trace]$ srvctl start database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2 更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址