Question

如何重建RAC的控制文件

Answer 1

在下面的一些情况下，可能需要重建控制文件：

o 所有控制文件都已损坏或丢失
o 没有针对控制文件的备份或者备份已损坏
o 或者在一些情况下，Oracle Support建议这么做

下面是针对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

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 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" 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 ;