Oracle Study之--Oracle RAC重建控制文件

Oracle Study之--Oracle RAC重建控制文件

系统环境：

操作系统： AIX5.3

Cluster： Oracle 10gR2 CRS

Oracle： Oracle 10gR2

在RAC环境下重建控制文件和在单实例上类似，只是有些步骤需要注意：

[oracle@aix211 ~]$cat mkln.sh ln -s /dev/rsystem           /u01/app/oracle/oradata/prod/system01.dbfln -s /dev/rsysaux           /u01/app/oracle/oradata/prod/sysaux01.dbfln -s /dev/rusers            /u01/app/oracle/oradata/prod/users01.dbfln -s /dev/rundotbs1         /u01/app/oracle/oradata/prod/undotbs01.dbf ln -s /dev/rundotbs2         /u01/app/oracle/oradata/prod/undotbs02.dbfln -s /dev/rtemp             /u01/app/oracle/oradata/prod/temp01.dbfln -s /dev/rcontrol1_1         /u01/app/oracle/oradata/prod/control01.ctlln -s /dev/rcontrol2_2         /u01/app/oracle/oradata/prod/control02.ctlln -s /dev/rcontrol3_3         /u01/app/oracle/oradata/prod/control03.ctlln -s /dev/rredo1_1          /u01/app/oracle/oradata/prod/log11.logln -s /dev/rredo1_2          /u01/app/oracle/oradata/prod/log12.logln -s /dev/rredo2_1          /u01/app/oracle/oradata/prod/log21.logln -s /dev/rredo2_2          /u01/app/oracle/oradata/prod/log22.logln -s /dev/rindex            /u01/app/oracle/oradata/prod/index01.dbfln -s /dev/rspfile           /u01/app/oracle/oradata/prod/spfile01ln -s /dev/rexample          /u01/app/oracle/oradata/prod/example01.dbf

Database存储在在RAW上。

1、首先在一个节点备份controlfile

[oracle@aix201 ~]$sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 23 16:16:07 2015Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL> select status from v$instance;STATUS------------OPENSQL> alter database backup controlfile to trace;Database altered.

2、查看控制文件的trace备份（udump）

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 192    MAXLOGMEMBERS 3    MAXDATAFILES 1024    MAXINSTANCES 32    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/prod/log11.log'  SIZE 50M,  GROUP 2 '/u01/app/oracle/oradata/prod/log12.log'  SIZE 50M,  GROUP 3 '/u01/app/oracle/oradata/prod/log21.log'  SIZE 50M,  GROUP 4 '/u01/app/oracle/oradata/prod/log22.log'  SIZE 50M-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/prod/system01.dbf',  '/u01/app/oracle/oradata/prod/undotbs01.dbf',  '/u01/app/oracle/oradata/prod/sysaux01.dbf',  '/u01/app/oracle/oradata/prod/users01.dbf',  '/u01/app/oracle/oradata/prod/example01.dbf',  '/u01/app/oracle/oradata/prod/undotbs02.dbf'CHARACTER SET ZHS16GBK;

3、关闭database，启动其中一个instance到弄mount

SQL> startup nomount;ORACLE instance started.Total System Global Area  612368384 bytesFixed Size                  2022832 bytesVariable Size             184549968 bytesDatabase Buffers          423624704 bytesRedo Buffers                2170880 bytesSQL> @/home/oracle/cr_ctr.sqlCREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG*ERROR at line 1:ORA-01503: CREATE CONTROLFILE failedORA-12720: operation requires database is in EXCLUSIVE modeSQL> show parameter clusterNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cluster_database                     boolean     TRUEcluster_database_instances           integer     2cluster_interconnects                string---创建失败，原因是在RAC下控制文件时处于共享（share）模式，需启动到独立（exclusive）模式，才能重建；修改cluster_database 为false，然后重建

重新建立控制文件：

SQL> alter system set cluster_database =false scope=spfile;System altered.SQL> startup nomountORACLE instance started.Total System Global Area  612368384 bytesFixed Size                  2022832 bytesVariable Size             184549968 bytesDatabase Buffers          423624704 bytesRedo Buffers                2170880 bytesSQL> show parameter clusterNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cluster_database                     boolean     FALSEcluster_database_instances           integer     1cluster_interconnects                stringSQL> @/home/oracle/cr_ctr.sqlControl file created.告警日志：alter.log:Mon Mar 23 16:41:00 2015CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 192    MAXLOGMEMBERS 3    MAXDATAFILES 1024    MAXINSTANCES 32    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/prod/log11.log'  SIZE 50M,  GROUP 2 '/u01/app/oracle/oradata/prod/log12.log'  SIZE 50M,  GROUP 3 '/u01/app/oracle/oradata/prod/log21.log'  SIZE 50M,  GROUP 4 '/u01/app/oracle/oradata/prod/log22.log'  SIZE 50M-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/prod/system01.dbf',  '/u01/app/oracle/oradata/prod/undotbs01.dbf',  '/u01/app/oracle/oradata/prod/sysaux01.dbf',  '/u01/app/oracle/oradata/prod/users01.dbf',  '/u01/app/oracle/oradata/prod/example01.dbf',  '/u01/app/oracle/oradata/prod/undotbs02.dbf'CHARACTER SET ZHS16GBKMon Mar 23 16:41:00 2015WARNING: Default Temporary Tablespace not specified in CREATE DATABASE commandDefault Temporary Tablespace will be necessary for a locally managed database in future releaseWARNING: You are creating/reusing datafile /u01/app/oracle/oradata/prod/control01.ctl.WARNING: Oracle recommends creating new datafiles on devices with zero offset. The command "/usr/sbin/mklv -y LVname -T O -w n -s n -r n VGname NumPPs" can be used. Please contact Oracle customer support for more details.WARNING: You are creating/reusing datafile /u01/app/oracle/oradata/prod/control01.ctl.WARNING: Oracle recommends creating new datafiles on devices with zero offset. The command "/usr/sbin/mklv -y LVname -T O -w n -s n -r n VGname NumPPs" can be used. Please contact Oracle customer support for more details.WARNING: You are creating/reusing datafile /u01/app/oracle/oradata/prod/control02.ctl.WARNING: Oracle recommends creating new datafiles on devices with zero offset. The command "/usr/sbin/mklv -y LVname -T O -w n -s n -r n VGname NumPPs" can be used. Please contact Oracle customer support for more details.WARNING: You are creating/reusing datafile /u01/app/oracle/oradata/prod/control02.ctl.WARNING: Oracle recommends creating new datafiles on devices with zero offset. The command "/usr/sbin/mklv -y LVname -T O -w n -s n -r n VGname NumPPs" can be used. Please contact Oracle customer support for more details.Setting recovery target incarnation to 1Mon Mar 23 16:41:05 2015Successful mount of redo thread 1, with mount id 286981148Mon Mar 23 16:41:05 2015Completed: CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 192    MAXLOGMEMBERS 3    MAXDATAFILES 1024    MAXINSTANCES 32    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/prod/log11.log'  SIZE 50M,  GROUP 2 '/u01/app/oracle/oradata/prod/log12.log'  SIZE 50M,  GROUP 3 '/u01/app/oracle/oradata/prod/log21.log'  SIZE 50M,  GROUP 4 '/u01/app/oracle/oradata/prod/log22.log'  SIZE 50M-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/prod/system01.dbf',  '/u01/app/oracle/oradata/prod/undotbs01.dbf',  '/u01/app/oracle/oradata/prod/sysaux01.dbf',  '/u01/app/oracle/oradata/prod/users01.dbf',  '/u01/app/oracle/oradata/prod/example01.dbf',  '/u01/app/oracle/oradata/prod/undotbs02.dbf'CHARACTER SET ZHS16GBK

4、重建成功，启动到open

SQL> select status from v$instance;STATUS------------MOUNTEDSQL> alter database open;Database altered.添加临时表空间数据文件：SQL> select name from v$tempfile;no rows selectedSQL> select tablespace_name from dba_tablespaces;TABLESPACE_NAME------------------------------SYSTEMUNDOTBS1SYSAUXTEMPUSERSUNDOTBS2EXAMPLE7 rows selected.SQL> alter tablespace temp add   2  tempfile '/u01/app/oracle/oradata/prod/temp01.dbf' size 100m reuse;Tablespace altered.SQL>  select name from v$tempfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/prod/temp01.dbf

5、修改cluster_database参数，启动所有instance

SQL> alter system set cluster_database =true scope=spfile;System altered.

启动所有Instance，如果所有instance启动成功，则controlfile重建成功。