I’m still hopeful that someone can point out where I’ve gone wrong with this, but I think I’ve covered all bases… “srvctl -p spfile_path” doesn’t work as documented.
UPDATE: This issue has turned out to be the result of default permissions not accommodating the 2 user approach of “oracle” (for database) and “grid” (for Cluster Infrastructure) – More details here.
The Oracle documentation makes is pretty clear that you can specify a non-default location for an spfile by using the “-p spfile_path” option to either srvctl add database or srvctl modify database:
Starting Up with SRVCTL with a Non-Default Server Parameter File
srvctl add database
srvctl modify database
I started having problems when adding a freshly cloned (using Grid Control) database to Oracle Restart. I wanted to put the spfile into ASM so used the following to get it there:
SQL> create pfile from spfile; File created. SQL> create spfile='+DATA/gg01/spfilegg01.ora' from pfile; File created. SQL>
I then added the database to Oracle Restart using:
[oracle@gg01 ~]$ srvctl add database -d gg01 -o $ORACLE_HOME -n gg01 -p +DATA/gg01/spfilegg01.ora -a DATA
Interestingly Oracle Restart didn’t know the state of the database as show below:
[oracle@gg01 admin]$ srvctl status database -d gg01 Database is not running.
So I stopped the database via SQL*Plus and attempted to start it using srvctl when I hit an error:
[oracle@gg01 dbs]$ srvctl start database -d gg01 PRCR-1079 : Failed to start resource ora.gg01.db CRS-2674: Start of 'ora.gg01.db' on 'gg01' failed
After playing around and getting frustrated with the fact that setting SRVM_TRACE=TRUE didn’t give me anything to go on, I finally got srvctl to start my database from the spfile in ASM…
One important thing to note is that the method I used to get the spfile into ASM left me with a pfile and an spfile in $ORACLE_HOME/dbs. This meant that as soon as I cleared the spfile from the srvctl configuration as shown below the database could be started:
[oracle@gg01 ~]$ srvctl modify database -d gg01 -p ' ' [oracle@gg01 ~]$ srvctl config database -d gg01 Database unique name: gg01 Database name: Oracle home: /u01/app/oracle/product/11.2.0/db_1 Oracle user: grid Spfile: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: Services: [oracle@gg01 ~]$ srvctl start database -d gg01
* Note that you have to specify quoted whitespace (any amount will do) to clear the spfile from the configuration otherwise you’ll get an error:
[oracle@gg01 ~]$ srvctl modify database -d gg01 -p PRKO-2388 : Command line option -p should be followed by a value. [oracle@gg01 ~]$ srvctl modify database -d gg01 -p '' PRKO-2003 : Invalid command line option value: [oracle@gg01 ~]$ srvctl modify database -d gg01 -p ' '
Using SRVM_TRACE you can see that the whitespace is removed to give an empty string:
[main] [ 2009-12-20 17:28:44.455 EST ] [CRSNative.register:1141] Name: 'SPFILE', Value: ''
It’s also interesting to note you can set SRVM_TRACE to anything you like, TRUE/FALSE/X all seem to give the same result and therefore you must use unset SRVM_TRACE to disable the tracing.
From looking at another 11.2 Oracle Restart database, created with DBCA, I found that there was an init.ora file in $ORACLE_HOME/dbs that had the SPFILE=<path to spfile> point to the spfile file alias in ASM, i.e.:
[oracle@gg01 ~]$ cat $ORACLE_HOME/dbs/initgg01.ora SPFILE='+DATA/gg01/spfilegg01.ora'
Clearly having a pfile that points to an spfile in ASM isn’t anything new, but without it using srvctl -p doesn’t seem to work!
With no spfile set via srvctl and no pfile or spfile in $ORACLE_HOME/dbs
[oracle@gg01 ~]$ srvctl start database -d gg01 PRCR-1079 : Failed to start resource ora.gg01.db ORA-01078: failure in processing system parameters CRS-2674: Start of 'ora.gg01.db' on 'gg01' failed
With spfile set via srvctl and no pfile or spfile in $ORACLE_HOME/dbs
[oracle@gg01 ~]$ srvctl modify database -d gg01 -p +DATA/gg01/spfilegg01.ora [oracle@gg01 ~]$ srvctl start database -d gg01 PRCR-1079 : Failed to start resource ora.gg01.db CRS-2674: Start of 'ora.gg01.db' on 'gg01' failed
With spfile set via srvctl and pfile point to the spfile in ASM
[oracle@gg01 ~]$ cat $ORACLE_HOME/dbs/initgg01.ora SPFILE='+DATA/gg01/spfilegg01.ora' [oracle@gg01 ~]$ srvctl start database -d gg01
But, what if I remove the spfile from the srvctl configuration…
[oracle@gg01 ~]$ srvctl modify database -d gg01 -p ' ' [oracle@gg01 ~]$ srvctl start database -d gg01
It still starts, so what’s the point in adding it to the srvctl configuration?
Any suggestions/comments gratefully received…