如何重建RAC的控制文件_sql

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

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 TYPEVALUE

------------------------------------ ----------- ------------------------------

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

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 Buffers2830336 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

8GROUP 1 (

9 '+DATA/racdb/onlinelog/group_1.261.783272805',

10 '+RECO/racdb/onlinelog/group_1.257.783272807'

11) SIZE 50M BLOCKSIZE 512,

12GROUP 2 (

13 '+DATA/racdb/onlinelog/group_2.262.783272807',

14 '+RECO/racdb/onlinelog/group_2.258.783272809'

15) SIZE 50M BLOCKSIZE 512,

16GROUP 3 (

17 '+DATA/racdb/onlinelog/group_3.269.804115405',

18 '+RECO/racdb/onlinelog/group_3.261.804115405'

19) SIZE 50M BLOCKSIZE 512,

20GROUP 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

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