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…
Thank you for your point.
I’m curious with ‘ ‘
$ srvctl modify database -d orcl -p ‘ ‘
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
$ srvctl start database -d orcl
$ srvctl status database -d orcl
Instance orcl_1 is running on node rhel5-test
that’s a good thing, isn’t it?
Hi Surachart,
I guess it is good that you can start your database without specifying the parameter file, but what I wanted to do was use the -p option in order to specify a non-standard location for my parameter file.
Would I be correct in assuming that $ORACLE_HOME/dbs/initorcl.ora or $ORACLE_HOME/dbs/spfileorcl.ora exist on your machine?
Thank You
Good, if -p can store $ORACLE_HOME environment in Cluster 😉
$ srvctl modify database -d orcl -p $ORACLE_HOME/dbs/spfileorcl.ora
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: /oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
$ srvctl modify database -d orcl -p +DISK01/orcl/spfileorcl.ora
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DISK01/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
Hi Surachart,
In your first case the spfile is in the default location, so the behavour is no different regardless of whether you set the spfile_path using -p or not, i.e. the database will start. Have you tried moving the spfile to another (non-default) location? I’d be interested to know if you can make this work.
With respect to your second case, can you confirm that there is not a pfile (initorcl.ora) in $ORACLE_HOME/dbs with the line: SPFILE=’+DISK01/orcl/spfileorcl.ora’?
I have not had any problems with srvctl config database showing me that the spfile location is set, but rather with starting the database when all I have is a spfile (with srvctl config database showing me the same location) and no pfile pointing to it.
I look forward to more detail from you.
Thanks.
Thank You for your comment.
About -p ‘ ‘ , when we start database by “srvctl” – It seems read initSID.ora at $ORACLE_HOME/dbs/ PATH
If in initSID.ora wrong… that can not startup database.
TEST:
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: /tmp/SPFILEORCL
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
$ srvctl start database -d orcl
PRCR-1079 : Failed to start resource ora.orcl.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of ‘ora.orcl.db’ on ‘rhel5-test’ failed
CRS-2632: There are no more servers to try to place resource ‘ora.orcl.db’ on that would satisfy its placement policy
$ srvctl modify database -d orcl -p ‘ ‘
$ srvctl start database -d orcl
PRCR-1079 : Failed to start resource ora.orcl.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of ‘ora.orcl.db’ on ‘rhel5-test’ failed
CRS-2632: There are no more servers to try to place resource ‘ora.orcl.db’ on that would satisfy its placement policy
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
$ cat /oracle/product/11.2.0/dbhome_1/dbs/initorcl_1.ora
SPFILE=’/tmp/SPFILEORCL’ # line added by Agent
Go To… Back -)
$ srvctl modify database -d orcl -p +DISK01/orcl/spfileorcl.ora
$ srvctl status database -d orcl
Instance orcl_1 is running on node rhel5-test
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DISK01/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
$ srvctl modify database -d orcl -p ‘ ‘
$ srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl_1
Disk Groups:
Services:
Database is administrator managed
$ srvctl start database -d orcl
$ srvctl status database -d orcl
Instance orcl_1 is running on node rhel5-test
$ cat /oracle/product/11.2.0/dbhome_1/dbs/initorcl_1.ora
SPFILE=’+DISK01/orcl/spfileorcl.ora’ # line added by Agent
Hi Surachart,
I have done a bit more testing and it seems that no amount of running srvctl modify database with -p results in updates to the pfile. Do you actually get the “# line added by Agent” text? If so, then I guess it is time to start talking version numbers and other areas where our configuration might differ:
Enterprise Linux Enterprise Linux Server release 5.2 (Carthage) x86_64
Oracle Database 11g – 11.2.0.1.0
Oracle Grid Infrastructure – 11.2.0.1.0
Your database is RAC, right? Mine isn’t. Maybe this is a “feature” for non-RAC databases?
My Testing: Oracle 11gR2 on RHEL 5.3 (x86_64)
I use RAC, But I have one node now 😉
About “# line added by Agent” in initorcl_1.ora file, I’m not sure. But I found it’s changed, after Spfile didn’t be ‘ ‘ + started database…
By the way, Thank You for idea. This help me idea for Testing.
😉
Pingback: srvctl -p option part II « ORAganism
Hi,
I had the same issue with srvctl and an account used to own the grid infrastructure. Having fixed the permissions on $ORACLE_HOME/dbs and adding the spfile name to the config, still srvctl would not start the database. Then I noticed you had given the grid user the dba group. So I added that group to the account and srvctl started working. So thanks for the original posts.
But I double checked the docs and other guides I have, none say that the grid owner should be in the OSDBA group. What do you think, is this all an issue with adding a database not originally created and set-up via dbca? In my case I had migrated the database to 11.2, then I installed the grid infrastructure for a single instance.
Regards
Pete
This is a feature it seems, even on 11.2.0.3.
Experienced exactly the same issue today.
I already had an init.ora in OH/dbs (one without spfile= entry but containing many other standard parameters), but db would not start with srvctl despite the -p being also configured correctly to point to spfile in ASM.
Error experienced indicated that user grid tries to write to the init.ora, but cannot because it doesn’t have write permissions on the file (it is owned by oracle:oinstall, not grid:oinstall – oinstall has read only on the init.ora).
CRS-5010: Update of configuration file “/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl01.ora” failed: details at “(:CLSN00014:)” in “/u01/app/grid/product/11.2.0/grid/log/localhost/agent/ohasd/oraagent_grid/oraagent_grid.log”
Solution is as described in op to create a single entry init.ora in OH/dbs with spfile=
Adding -p to srvctl config database doesn’t make any difference – db won’t start with srvctl or crsctl without the init.ora having this spfile= line in it. Don’t know what the point of the -p in Oracle Restart configuration is.
unbelievable. I though I created the service after I deleted the spfile from dbs folder. Obviously i didn’t so … I had the same issue.
Thanks for your article!!
Thank you so much. This helped me downgrading Oracle Database 11.2.0.4 to 3