1、生成重建controlfile的指令碼

SQL> alter database backup controlfile to trace;

Database altered.
2、檢視指令碼位置
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/jzh
/jzh1/trace
3、關閉資料庫
[oracle@jzh1 ~]$ srvctl stop database -d jzh
4、啟動資料庫至nomount狀態(啟動一個例項即可)
[oracle@jzh1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 17 15:40:07 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size 2228384 bytes
Variable Size 905973600 bytes
Database Buffers 520093696 bytes
Redo Buffers 8093696 bytes
5、執行前面生成的指令碼
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/jzh/jzh1/trace/jzh1_ora_4989.trc
指令碼在上面的trace檔案中
SQL> CREATE CONTROLFILE REUSE DATABASE "JZH" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '+DATA/jzh/onlinelog/group_1.271.880001913',
10 '+ARCH/jzh/onlinelog/group_1.261.880001915'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
13 '+DATA/jzh/onlinelog/group_2.268.880001917',
14 '+ARCH/jzh/onlinelog/group_2.271.880001919'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 3 (
17 '+DATA/jzh/onlinelog/group_3.260.880005747',
18 '+ARCH/jzh/onlinelog/group_3.260.880005753'
19 ) SIZE 50M BLOCKSIZE 512,
20 GROUP 4 (
21 '+DATA/jzh/onlinelog/group_4.264.880005761',
22 '+ARCH/jzh/onlinelog/group_4.259.880005763'
23 ) SIZE 50M BLOCKSIZE 512
24 -- STANDBY LOGFILE
25 DATAFILE
26 '+DATA/jzh/datafile/system.261.880001919',
27 '+DATA/jzh/datafile/sysaux.265.880001965',
28 '+DATA/jzh/datafile/undotbs1.272.880001991',
29 '+DATA/jzh/datafile/undotbs2.269.880002027',
30 '+DATA/jzh/datafile/users.262.880002029'
31 CHARACTER SET AL32UTF8;
CREATE CONTROLFILE REUSE DATABASE "JZH" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode----------&gt該錯誤是由於沒有設定cluster_database=false
SQL> show parameter cluster_database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shu immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1436389376 bytes
Fixed Size 2228384 bytes
Variable Size 905973600 bytes
Database Buffers 520093696 bytes
Redo Buffers 8093696 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JZH" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '+DATA/jzh/onlinelog/group_1.271.880001913',
10 '+ARCH/jzh/onlinelog/group_1.261.880001915'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
'+DATA/jzh/onlinelog/group_2.268.880001917',
'+ARCH/jzh/onlinelog/group_2.271.880001919'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'+DATA/jzh/onlinelog/group_3.260.880005747',
13 14 15 16 17 18 '+ARCH/jzh/onlinelog/group_3.260.880005753'
19 ) SIZE 50M BLOCKSIZE 512,
20 GROUP 4 (
21 '+DATA/jzh/onlinelog/group_4.264.880005761',
22 '+ARCH/jzh/onlinelog/group_4.259.880005763'
23 ) SIZE 50M BLOCKSIZE 512
24 -- STANDBY LOGFILE
25 DATAFILE
26 '+DATA/jzh/datafile/system.261.880001919',
27 '+DATA/jzh/datafile/sysaux.265.880001965',
28 '+DATA/jzh/datafile/undotbs1.272.880001991',
29 '+DATA/jzh/datafile/undotbs2.269.880002027',
30 '+DATA/jzh/datafile/users.262.880002029'
31 CHARACTER SET AL32UTF8;
Control file created.
6、確認redo log
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 69 52428800 512 2 NO CURRENT 972789 17-JUN-15 2.8147E+14 17-JUN-15
2 1 68 52428800 512 2 NO INACTIVE 952423 18-MAY-15 952425 18-MAY-15
3 2 3 52428800 512 2 NO INACTIVE 952462 18-MAY-15 972466 17-JUN-15
4 2 4 52428800 512 2 NO INACTIVE 972466 17-JUN-15 981652 17-JUN-15
7、recover database
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
8、開啟資料庫
SQL> alter system archive log all;

System altered.

SQL> alter database open
2 ;
Database altered.
9、新增原tempfile至temp表空間
SQL> select name from v$tempfile;
no rows selected
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
6 rows selected.
SQL> alter tablespace temp add tempfile '+DATA/jzh/tempfile/TEMP.267.880001997';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/jzh/tempfile/temp.267.880001997
10、設定cluster_database為true
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL>
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11、啟動所有例項
[oracle@jzh1 ~]$ srvctl start database -d jzh
[oracle@jzh1 ~]$ srvctl status database -d jzh
Instance jzh1 is running on node jzh1
Instance jzh2 is running on node jzh2
controlfile 重建完成！

RAC環境重建控制檔案

相關文章