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

系统环境：

操作系统： AIX5.3

Cluster： Oracle 10gR2 CRS

Oracle： Oracle 10gR2

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

[oracle@aix211~]$catmkln.shln-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.dbfln-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'/assysdba'SQL*Plus:Release10.2.0.1.0-ProductiononMonMar2316:16:072015Copyright(c)1982,2005,Oracle.Allrightsreserved.Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-64bitProductionWiththePartitioning,RealApplicationClusters,OLAPandDataMiningoptionsSQL>selectstatusfromv$instance;STATUS------------OPENSQL>alterdatabasebackupcontrolfiletotrace;Databasealtered.

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

CREATECONTROLFILEREUSEDATABASE"PROD"NORESETLOGSNOARCHIVELOGMAXLOGFILES192MAXLOGMEMBERS3MAXDATAFILES1024MAXINSTANCES32MAXLOGHISTORY292LOGFILEGROUP1'/u01/app/oracle/oradata/prod/log11.log'SIZE50M,GROUP2'/u01/app/oracle/oradata/prod/log12.log'SIZE50M,GROUP3'/u01/app/oracle/oradata/prod/log21.log'SIZE50M,GROUP4'/u01/app/oracle/oradata/prod/log22.log'SIZE50M--STANDBYLOGFILEDATAFILE'/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'CHARACTERSETZHS16GBK;

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

SQL>startupnomount;ORACLEinstancestarted.TotalSystemGlobalArea612368384bytesFixedSize2022832bytesVariableSize184549968bytesDatabaseBuffers423624704bytesRedoBuffers2170880bytesSQL>@/home/oracle/cr_ctr.sqlCREATECONTROLFILEREUSEDATABASE"PROD"NORESETLOGSNOARCHIVELOG*ERRORatline1:ORA-01503:CREATECONTROLFILEfailedORA-12720:operationrequiresdatabaseisinEXCLUSIVEmodeSQL>showparameterclusterNAMETYPEVALUE-----------------------------------------------------------------------------cluster_databasebooleanTRUEcluster_database_instancesinteger2cluster_interconnectsstring---创建失败，原因是在RAC下控制文件时处于共享（share）模式，需启动到独立（exclusive）模式，才能重建；修改cluster_database为false，然后重建

重新建立控制文件：

SQL>altersystemsetcluster_database=falsescope=spfile;Systemaltered.SQL>startupnomountORACLEinstancestarted.TotalSystemGlobalArea612368384bytesFixedSize2022832bytesVariableSize184549968bytesDatabaseBuffers423624704bytesRedoBuffers2170880bytesSQL>showparameterclusterNAMETYPEVALUE-----------------------------------------------------------------------------cluster_databasebooleanFALSEcluster_database_instancesinteger1cluster_interconnectsstringSQL>@/home/oracle/cr_ctr.sqlControlfilecreated.告警日志：alter.log:MonMar2316:41:002015CREATECONTROLFILEREUSEDATABASE"PROD"NORESETLOGSNOARCHIVELOGMAXLOGFILES192MAXLOGMEMBERS3MAXDATAFILES1024MAXINSTANCES32MAXLOGHISTORY292LOGFILEGROUP1'/u01/app/oracle/oradata/prod/log11.log'SIZE50M,GROUP2'/u01/app/oracle/oradata/prod/log12.log'SIZE50M,GROUP3'/u01/app/oracle/oradata/prod/log21.log'SIZE50M,GROUP4'/u01/app/oracle/oradata/prod/log22.log'SIZE50M--STANDBYLOGFILEDATAFILE'/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'CHARACTERSETZHS16GBKMonMar2316:41:002015WARNING:DefaultTemporaryTablespacenotspecifiedinCREATEDATABASEcommandDefaultTemporaryTablespacewillbenecessaryforalocallymanageddatabaseinfuturereleaseWARNING:Youarecreating/reusingdatafile/u01/app/oracle/oradata/prod/control01.ctl.WARNING:Oraclerecommendscreatingnewdatafilesondeviceswithzerooffset.Thecommand"/usr/sbin/mklv-yLVname-TO-wn-sn-rnVGnameNumPPs"canbeused.PleasecontactOraclecustomersupportformoredetails.WARNING:Youarecreating/reusingdatafile/u01/app/oracle/oradata/prod/control01.ctl.WARNING:Oraclerecommendscreatingnewdatafilesondeviceswithzerooffset.Thecommand"/usr/sbin/mklv-yLVname-TO-wn-sn-rnVGnameNumPPs"canbeused.PleasecontactOraclecustomersupportformoredetails.WARNING:Youarecreating/reusingdatafile/u01/app/oracle/oradata/prod/control02.ctl.WARNING:Oraclerecommendscreatingnewdatafilesondeviceswithzerooffset.Thecommand"/usr/sbin/mklv-yLVname-TO-wn-sn-rnVGnameNumPPs"canbeused.PleasecontactOraclecustomersupportformoredetails.WARNING:Youarecreating/reusingdatafile/u01/app/oracle/oradata/prod/control02.ctl.WARNING:Oraclerecommendscreatingnewdatafilesondeviceswithzerooffset.Thecommand"/usr/sbin/mklv-yLVname-TO-wn-sn-rnVGnameNumPPs"canbeused.PleasecontactOraclecustomersupportformoredetails.Settingrecoverytargetincarnationto1MonMar2316:41:052015Successfulmountofredothread1,withmountid286981148MonMar2316:41:052015Completed:CREATECONTROLFILEREUSEDATABASE"PROD"NORESETLOGSNOARCHIVELOGMAXLOGFILES192MAXLOGMEMBERS3MAXDATAFILES1024MAXINSTANCES32MAXLOGHISTORY292LOGFILEGROUP1'/u01/app/oracle/oradata/prod/log11.log'SIZE50M,GROUP2'/u01/app/oracle/oradata/prod/log12.log'SIZE50M,GROUP3'/u01/app/oracle/oradata/prod/log21.log'SIZE50M,GROUP4'/u01/app/oracle/oradata/prod/log22.log'SIZE50M--STANDBYLOGFILEDATAFILE'/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'CHARACTERSETZHS16GBK

4、重建成功，启动到open

SQL>selectstatusfromv$instance;STATUS------------MOUNTEDSQL>alterdatabaseopen;Databasealtered.添加临时表空间数据文件：SQL>selectnamefromv$tempfile;norowsselectedSQL>selecttablespace_namefromdba_tablespaces;TABLESPACE_NAME------------------------------SYSTEMUNDOTBS1SYSAUXTEMPUSERSUNDOTBS2EXAMPLE7rowsselected.SQL>altertablespacetempadd2tempfile'/u01/app/oracle/oradata/prod/temp01.dbf'size100mreuse;Tablespacealtered.SQL>selectnamefromv$tempfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/prod/temp01.dbf

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

SQL>altersystemsetcluster_database=truescope=spfile;Systemaltered.

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