開始這篇文章前需要先仔細閱讀一下兩篇文章：

《如何重建RAC的控制檔案？》
https://blogs.oracle.com/Database4CN/entry/%E5%A6%82%E4%BD%95%E9%87%8D%E5%BB%BArac%E7%9A%84%E6%8E%A7%E5%88%B6%E6%96%87%E4%BB%B6

《RAC環境單例項啟動資料庫收到ORA-29702報錯》
http://space.itpub.net/23135684/viewspace-757653

下面的實驗有兩個前提：
1).需要RAC環境下單例項啟動Oracle資料庫。
2).RAC叢集服務正常啟動。

[root@rhel1 bin]# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

[root@rhel1 bin]# ./crsctl check crs

Failure 1 contacting CSS daemon

Cannot communicate with CRS

Cannot communicate with EVM

[root@rhel1 bin]# ./crsctl check crs

CSS appears healthy

CRS appears healthy

EVM appears healthy

[root@rhel1 bin]# su - oracle

[oracle@rhel1 ~]$ sql

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Apr 2 16:11:00 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> alter database backup controlfile to trace as '/tmp/control.txt';

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

修改引數檔案，去掉RAC相關的引數，修改後參考如下內容：

*.audit_file_dest='/u01/app/oracle/admin/ractest/adump'

*.background_dump_dest='/u01/app/oracle/admin/ractest/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/u01/ractest/control01.ctl','/u01/ractest/control02.ctl','/u01/ractest/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/ractest/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='ractest'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ractestXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=142606336

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sga_target=429916160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/ractest/udump'

SQL> startup nomount

ORACLE instance started.

Total System Global Area 432013312 bytes

Fixed Size 2096824 bytes

Variable Size 125829448 bytes

Database Buffers 297795584 bytes

Redo Buffers 6291456 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "RACTEST" RESETLOGS NOARCHIVELOG

2 MAXLOGFILES 192

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 1024

5 MAXINSTANCES 32

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u02/ractest/redo01.log' SIZE 50M,

9 GROUP 2 '/u02/ractest/redo02.log' SIZE 50M

10 DATAFILE

11 '/u02/ractest/sysaux01.dbf',

12 '/u02/ractest/undotbs01.dbf',

13 '/u02/ractest/system01.dbf',

14 '/u02/ractest/users01.dbf',

15 '/u02/ractest/undotbs02.dbf'

16 CHARACTER SET ZHS16GBK

17 ;

Control file created.

注意對資料檔案位置和線上Redo日誌檔案位置的調整。

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

這個錯誤是由於重建RESETLOG模式的控制檔案中並沒有包含建立thread 2 redo日誌的內容。

SQL> recover database using backup controlfile;
這是control.txt backup controlfile中包含的需要執行的命令。

ORA-00279: change 977014 generated at 04/02/2013 16:11:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/db_1/dbs/arch1_18_783765282.dbf

ORA-00280: change 977014 for thread 1 is in sequence #18

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2

2 GROUP 3 '/u02/ractest/redo03.log' SIZE 50M REUSE,

3 GROUP 4 '/u02/ractest/redo04.log' SIZE 50M REUSE;

Database altered.

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 1: '/u02/ractest/sysaux01.dbf'

SQL> recover database using backup controlfile until cancel;
這是《如何重建RAC的控制檔案？》文章列出的RESETLOG模式建立控制檔案必須執行的命令。

ORA-00279: change 977014 generated at 04/02/2013 16:11:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/db_1/dbs/arch1_18_783765282.dbf

ORA-00280: change 977014 for thread 1 is in sequence #18

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel;

ORA-00308: cannot open archived log 'cancel;'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

資料庫順利開啟。

如果只以單例項執行該資料庫，那麼可以執行以下命令禁用thread 2：

SQL> alter database disable thread 2;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/ractest/temp01.dbf'

2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.
請不要忘記新增新的臨時檔案到臨時表空間中。

另外，執行alter database backup controlfile to trace生成的控制檔案trace非常具有參考價值，有必要仔細閱讀，按照內容引導一步一步的操作是非常好的習慣！下面貼出的是這篇文章的例子對應的control.txt檔案內容：

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="ractest"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=2

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/db_1/dbs/arch'

-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script. file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

-- Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "RACTEST" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u02/ractest/redo01.log' SIZE 50M,

GROUP 2 '/u02/ractest/redo02.log' SIZE 50M,

GROUP 3 '/u02/ractest/redo03.log' SIZE 50M,

GROUP 4 '/u02/ractest/redo04.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u02/ractest/sysaux01.dbf',

'/u02/ractest/undotbs01.dbf',

'/u02/ractest/system01.dbf',

'/u02/ractest/users01.dbf',

'/u02/ractest/undotbs02.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/db_1/dbs/arch1_1_716804664.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/db_1/dbs/arch1_1_783765282.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/ractest/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

--

-- Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "RACTEST" RESETLOGS NOARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u02/ractest/redo01.log' SIZE 50M,

GROUP 2 '/u02/ractest/redo02.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u02/ractest/sysaux01.dbf',

'/u02/ractest/undotbs01.dbf',

'/u02/ractest/system01.dbf',

'/u02/ractest/users01.dbf',

'/u02/ractest/undotbs02.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/db_1/dbs/arch1_1_716804664.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/db_1/dbs/arch1_1_783765282.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Create log files for threads other than thread one.

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 3 '/u02/ractest/redo03.log' SIZE 50M REUSE,

GROUP 4 '/u02/ractest/redo04.log' SIZE 50M REUSE;

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/ractest/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

--

下面是RAC環境下多例項啟動Oracle資料庫重建控制檔案的例子：

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 432013312 bytes

Fixed Size 2096824 bytes

Variable Size 142606664 bytes

Database Buffers 281018368 bytes

Redo Buffers 6291456 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "RACTEST" RESETLOGS NOARCHIVELOG

2 MAXLOGFILES 192

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 1024

5 MAXINSTANCES 32

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u02/ractest/redo01.log' SIZE 50M,

9 GROUP 2 '/u02/ractest/redo02.log' SIZE 50M

10 DATAFILE

11 '/u02/ractest/sysaux01.dbf',

12 '/u02/ractest/undotbs01.dbf',

13 '/u02/ractest/system01.dbf',

'/u02/ractest/users01.dbf',

14 15 '/u02/ractest/undotbs02.dbf'

16 CHARACTER SET ZHS16GBK

17 ;

CREATE CONTROLFILE REUSE DATABASE "RACTEST" RESETLOGS NOARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-12720: operation requires database is in EXCLUSIVE mode

SQL> show parameter cluster

NAME TYPE VALUE

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

cluster_database boolean TRUE

cluster_database_instances integer 2

cluster_interconnects string

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 432013312 bytes

Fixed Size 2096824 bytes

Variable Size 142606664 bytes

Database Buffers 281018368 bytes

Redo Buffers 6291456 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "RACTEST" RESETLOGS NOARCHIVELOG

2 MAXLOGFILES 192

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 1024

5 MAXINSTANCES 32

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u02/ractest/redo01.log' SIZE 50M,

GROUP 2 '/u02/ractest/redo02.log' SIZE 50M

9 10 DATAFILE

11 '/u02/ractest/sysaux01.dbf',

12 '/u02/ractest/undotbs01.dbf',

13 '/u02/ractest/system01.dbf',

14 '/u02/ractest/users01.dbf',

15 '/u02/ractest/undotbs02.dbf'

16 CHARACTER SET ZHS16GBK

17 ;

Control file created.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 977438 generated at 04/02/2013 16:23:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/db_1/dbs/arch1_1_811700008.dbf

ORA-00280: change 977438 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2

2 GROUP 3 '/u02/ractest/redo03.log' SIZE 50M REUSE,

3 GROUP 4 '/u02/ractest/redo04.log' SIZE 50M REUSE;

Database altered.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/ractest/temp01.dbf'

2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> show parameter cluster

NAME TYPE VALUE

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

cluster_database boolean FALSE

cluster_database_instances integer 1

cluster_interconnects string

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 432013312 bytes

Fixed Size 2096824 bytes

Variable Size 142606664 bytes

Database Buffers 281018368 bytes

Redo Buffers 6291456 bytes

Database mounted.

Database opened.

之後RAC兩個例項順利啟動。

--end--

RAC環境下單例項啟動Oracle資料庫重建控制檔案案例

相關文章