RAC 11G ASM下修改spfile檔案

背景：

RAC 11g 資料庫啟動報錯

[oracle@rac1 ~]$ srvctl start database -d rac1db
PRCR-1079 : Failed to start resource ora.rac1db.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.rac1db.db' on 'rac1' failed
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.rac1db.db' on 'rac2' failed
CRS-2632: There are no more servers to try to place resource 'ora.rac1db.db' on that would satisfy its placement policy

[root@rac1 ~]# su - grid
[grid@rac1 ~]$ ls
arch  oradiag_grid
[grid@rac1 ~]$ crsctl status resource ora.rac1db.db -f
NAME=ora.rac1db.db
TYPE=ora.database.type
STATE=OFFLINE
TARGET=ONLINE
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=2
CARDINALITY_ID=0
CHECK_INTERVAL=1
CHECK_TIMEOUT=600
CLUSTER_DATABASE=true
CREATION_SEED=29
CURRENT_RCOUNT=0
DB_UNIQUE_NAME=rac1db
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_COUNT=0
FAILURE_HISTORY=
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=/u01/oracle/admin/rac1db/adump
GEN_USR_ORA_INST_NAME=
GEN_USR_ORA_INST_NAME@SERVERNAME(rac1)=rac1db1
GEN_USR_ORA_INST_NAME@SERVERNAME(rac2)=rac1db2
HOSTING_MEMBERS=
ID=ora.rac1db.db
INCARNATION=0
INSTANCE_FAILOVER=0
LAST_FAULT=0
LAST_RESTART=0
LAST_SERVER=
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ORACLE_HOME=/home/oracle/db
PLACEMENT=restricted
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=ora.rac1db
SPFILE=+DATA/rac1db/spfilerac1db.ora
START_DEPENDENCIES=hard(ora.DATA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform.:ora.ons,uniform.:ora.eons) pullup(ora.DATA.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STATE_CHANGE_VERS=0
STATE_DETAILS=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)
STOP_TIMEOUT=600
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=rac1db
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=
USR_ORA_INST_NAME@SERVERNAME(rac1)=rac1db1
USR_ORA_INST_NAME@SERVERNAME(rac2)=rac1db2
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.1.0

檢視  SPFILE=+DATA/rac1db/spfilerac1db.ora 發現 ASMCMD下面沒有這個檔案別名，需要重新建立

ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> pwd
+DATA/rac1db
ASMCMD> cd para*
ASMCMD> ls
spfile.336.807807355
ASMCMD> pwd
+DATA/rac1db/PARAMETERFILE

建立別名

ASMCMD> mkalias '+DATA/rac1db/PARAMETERFILE/spfile.336.807807355' '+DATA/rac1db/spfilerac1db.ora'
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilerac1db.ora
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    ARCHIVELOG/
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    TEMPFILE/
                                                 N    spfilerac1db.ora => +DATA/RAC1DB/PARAMETERFILE/spfile.336.807807355
ASMCMD> exit

啟動資料庫

[oracle@rac2 dbs]$ srvctl start database -d rac1db
[oracle@rac2 dbs]$

[oracle@rac2 dbs]$ /u01/grid/bin/crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1       
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1       
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1       
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1       
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1       
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE              
ora....network ora....rk.type ONLINE    ONLINE    rac1       
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE              
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1       
ora....SM1.asm application    ONLINE    ONLINE    rac1       
ora....C1.lsnr application    ONLINE    ONLINE    rac1       
ora.rac1.gsd   application    OFFLINE   OFFLINE              
ora.rac1.ons   application    ONLINE    ONLINE    rac1       
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1       
ora.rac1db.db  ora....se.type ONLINE    ONLINE    rac1       
ora....SM2.asm application    ONLINE    ONLINE    rac2       
ora....C2.lsnr application    ONLINE    ONLINE    rac2       
ora.rac2.gsd   application    OFFLINE   OFFLINE              
ora.rac2.ons   application    ONLINE    ONLINE    rac2       
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2       
ora.racdb.db   ora....se.type OFFLINE   OFFLINE              
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1       
[oracle@rac2 dbs]$

啟動成功

目的：修改SPFILE內容

1.先生成pfile，請指定目錄，防止覆蓋預設目錄eg./u01/tmp.ora

2.修改完成後，重新生成spfile

create spfile='+dg1' from pfile='/u01/tmp.ora' --其中+dg1是ASM的一個磁碟組

3.修改指向SPFILE

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DG1/devdb/spfiledevdb.ora

4.進入asmcmd命令介面看下上面這個SPFILE檔案

ASMCMD> ls -tl
Type Redund Striped Time Sys Name
Y TEMPFILE/
Y PARAMETERFILE/
Y ONLINELOG/
Y DATAFILE/
Y CONTROLFILE/
Y ARCHIVELOG/
N spfiledevdb.ora => +DG1/DEVDB/PARAMETERFILE/spfile.275.791775033 --可以看到是個別名

ASMCMD> pwd
+dg1/devdb/parameterfile
ASMCMD> ls
spfile.268.789967617
spfile.275.791775033
spfile.276.791775083
ASMCMD> ls -tl
Type Redund Striped Time Sys Name
PARAMETERFILE MIRROR COARSE AUG 20 01:00:00 Y spfile.276.791775083 --新生成SPFILE
PARAMETERFILE MIRROR COARSE AUG 20 01:00:00 Y spfile.275.791775033
PARAMETERFILE MIRROR COARSE AUG 20 01:00:00 Y spfile.268.789967617

5.先刪除別名

ASMCMD> rmalias spfiledevdb.ora

6.重新建立別名

ASM在mount狀態下

alter diskgroup dg1 add alias '+dg1/devdb/spfiledevdb.ora' for '+dg1/devdb/parameterfile/spfile.276.791775083 ';

或者

ASMCMD模式下

mkalias '+dg1/devdb/parameterfile/spfile.282.794797403' '+dg1/devdb/spfiledevdb.ora'

7.提示錯誤

ORA-15005: name "+dg1/devdb/spfiledevdb.ora" is already used by an existing

--解決關閉資料庫或將ASM例項MOUNT再修改，也有可能是名字衝突引起，先刪除別名--此項未詳細驗證；

ASMCMD命令參考