如何重建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-13
SQL> 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 rac2
TESTCASE2
---------------------------
用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: {
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-1312916/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Controlfile 重建控制檔案 noresetlogs, resetlogs..
- oracle快速拿到重建控制檔案語句的方法二Oracle
- RAC控制檔案恢復（三種不同情況）
- 重建共享(db或asm)密碼檔案 in Oracle 19c RAC-20220209ASM密碼Oracle
- 【RMAN】Oracle中如何備份控制檔案？備份控制檔案的方式有哪幾種？Oracle
- Oracle RAC引數檔案管理Oracle
- 惡意軟體PE檔案重建指南
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- Oracle 控制檔案Oracle
- Oracle RAC修改引數檔案位置Oracle
- Oracle RAC NFS掛載檔案系統OracleNFS
- 2.6.4 指定控制檔案
- 12c複製 RAC ASM中的密碼檔案到檔案系統ASM密碼
- Oracle RAC 環境 引數檔案的啟動順序Oracle
- 與控制檔案有關的恢復
- 控制檔案損壞處理
- ORACLE 控制檔案（Control Files）概述Oracle
- git移除已經加入版本控制的檔案Git
- [20210224]控制檔案序列號滿的分析.txt
- 與控制檔案有關的恢復(二)
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- ORACLE RAC ASM資料檔案遷移OMF檔案報錯ORA-01276解決OracleASM
- 記一次Oracle RAC for aix 儲存雙控鎖盤導致ASM控制檔案損壞恢復OracleAIASM
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- windwos檔案控制代碼數限制
- 把“點檔案”放到版本控制中
- [20210225]控制檔案序列號滿的恢復.txt
- oracle 控制檔案及引數檔案何時自動備份Oracle
- 如何有效控制專案成本？
- 內外網檔案交換，如何控制使用者的文件使用許可權？
- 清除Oracle控制檔案中的歸檔資訊v$archived_logOracleHive
- 【RAC】Oracle RAC如何修改心跳網路Oracle
- ORACLE11GR2 RAC檔案系統變更成ASM EXTEND RAC及高可用測試OracleASM
- 如何識別檔案的真假
- Python如何處理檔案的?Python
- rman恢復控制檔案的一個小錯誤
- 控制檔案不一致引發的“血案”
- 使用git不希望檔案被版本控制Git
- Oracle 控制檔案損壞解決方案Oracle