srvctl -p option

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…

About these ads

11 thoughts on “srvctl -p option

  1. 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

  2. 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.

  3. 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

  4. 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?

  5. 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.
    ;)

  6. Pingback: srvctl -p option part II « ORAganism

  7. 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

  8. 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.

  9. 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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s