Friday, July 16, 2010

Oracle 11gR2 RAC SPfile Issue

It seems in Oracle 11gR2 whenever srvctl is used to start the database it actively replaces the init*.ora entry in the ORACLE_HOME/dbs directory. But if the spfile was later recreated in ASM it will have a different filename and entry added during srvctl start (taken from OCR) is nolonger valid and shows a error message that is not relavent to the cause of the error.
Here are some observations.

on 10.2.0.4
srvctl srvctl config database -d livedb -a
tbxdb1 livedb2 /opt/oracle/app/oracle/product/10.2.0/db_1
tbxdb2 livedb1 /opt/oracle/app/oracle/product/10.2.0/db_1
DB_NAME: null
ORACLE_HOME: /opt/oracle/app/oracle/product/10.2.0/db_1
SPFILE: +DATA/livedb/spfilelivedb.ora
DOMAIN: domain.com
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED

more initlivedb2.ora
SPFILE='+DATA/livedb/spfilelivedb.ora'
on 11.1.0.7
srvctl config database -d livedb -a
278071-dbclus1 livedb1 /opt/dm/app/oracle/product/11.1.0/db_1
278072-dbclus2 livedb2 /opt/dm/app/oracle/product/11.1.0/db_1
DB_UNIQUE_NAME: livedb
DB_NAME: livedb
ORACLE_HOME: /opt/dm/app/oracle/product/11.1.0/db_1
SPFILE: +DATA/livedb/spfilelivedb.ora
DOMAIN: domain.com
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED

more initlivedb1.ora
SPFILE='+DATA/livedb/spfilelivedb.ora'
on 11.2.0.1
srvctl config database -d clusdb
Database unique name: clusdb
Database name:
Oracle home: /opt/app/oracle/product/11.2.0/clusdb
Oracle user: oracle
Spfile: +DATA/clusdb/spfileclusdb.ora_1277909842526
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: clusdb
Database instances: clusdb1
Disk Groups: DATA,FLASH
Services:
Database is administrator managed

more initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora_1277909842526' # line added by Agent
From the outputs above it could be seen that in 11.2 real spfile location is referred not the ASM alias.

But the real spfile name has changed due to recreation
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileclusdb.ora => +DATA/CLUSDB/PARAMETERFILE/
spfile.265.723730605
When trying to start the database with srvctl following error is thrown
srvctl start database -d clusdb
PRCR-1079 : Failed to start resource ora.clusdb.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.clusdb.db' on 'hpc1' failed
CRS-2632: There are no more servers to try to place resource 'ora.clusdb.db' on that would satisfy its placement policy
One way to solve the problem is to edit the init*.ora file to point to spfiledbname.ora in the ASM instance and start the database with sqlplus.
vi initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora'
sqlplus / as sysdba
sql>startup
will bring up the database but in subsequent srvctl start will add the original entry to the init*.ora file. Content of the ORACLE_HOME/dbs directory
ls
hc_clusdb1.dat initclusdb1.ora orapwclusdb orapwclusdb1 snapcf_clusdb1.f
more initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora'
Only one init*.ora file and it's content is shown. Stop and start the database with srvctl
srvctl stop database -d clusdb
ls
hc_clusdb1.dat initclusdb1.ora orapwclusdb orapwclusdb1 snapcf_clusdb1.f

srvctl start database -d clusdb
PRCR-1079 : Failed to start resource ora.clusdb.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.clusdb.db' on 'hpc1' failed
CRS-2632: There are no more servers to try to place resource 'ora.clusdb.db' on that would satisfy its placement policy

ls
hc_clusdb1.dat initclusdb1.ora initclusdb1.ora.bak.hpc1 orapwclusdb orapwclusdb1 snapcf_clusdb1.f

more initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora_1277909842526' # line added by Agent
As seen from the above outputs the existing init file is backed up and a new one created with the original spfile location by the agent.

Best way to fix it is by using srvctl modify to add the generic spfile location.
srvctl modify database -d clusdb -p +DATA/clusdb/spfileclusdb.ora
srvctl config database -d clusdb
Database unique name: clusdb
Database name:
Oracle home: /opt/app/oracle/product/11.2.0/clusdb
Oracle user: oracle
Spfile: +DATA/clusdb/spfileclusdb.ora
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: clusdb
Database instances: clusdb1
Disk Groups: DATA,FLASH
Services:
Database is administrator managed
Whenever spfile is recreated new spfile could be linked to the ASM alias thus making the init*.ora file content valid all the time.

On metalink the error messages CRS-2632, CRS-2674, ORA-01078 has also been linked to metalink notes 1069254.1, 889845.1, 1071324.1, 1088754.1 with regard to 11gR2 issues.