RAC环境下单实例启动Oracle数据库重建控制文件案例

           开始这篇文章前需要先仔细阅读一下两篇文章：

《如何重建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

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-757659/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-757659/