srvctl -p option part II

This is an update on my earlier post “srvctl -p option“, which reported that srvctl modify database with the -p option does not work as documented. Thanks to Surachart for providing his findings in the comments and thanks to Neil for providing me with a chance to test this in another environment.

The key turned out to be the permissions on $ORACLE_HOME/dbs, but whilst the original problem has been solved another question has been raised…

As commented in the update on the original post I have installed 11.2 with both “grid” and “oracle” users. I had previously noticed that srvctl config shows the “Oracle user” as “grid”, but that the documentation for srvctl modify database -u states that the user should be the “Name of the Oracle user who owns the Oracle home directory”. Interestingly srvctl add database does not have a -u option, but does have a -o option to specify the full path to the $ORACLE_HOME (thanks to Pawel for pointing that out) which suggests (to me at least) that srvctl add database derives the “Oracle user” from the permissions on ORACLE_HOME.

Anyway, the crux of this is that allowing group write on $ORACLE_HOME/dbs allows srvctl modify database with the -p option to work as documented and shown below:

Removing the spfile configuration:

[oracle@gg01 ~]$ srvctl modify database -d gg01 -p ' '
[oracle@gg01 ~]$ srvctl config database -d gg01
Database unique name: gg01
Database name: gg01
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: DATA
Services:

Confirming groups for “grid” user and changing $ORACLE_HOME/dbs permissions:

[oracle@gg01 ~]$ id grid
uid=501(grid) gid=501(oinstall) groups=501(oinstall),500(dba),503(asmadmin),504(asmoper),505(asmdba)
[oracle@gg01 ~]$ chmod g+w /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@gg01 ~]$ ls -ld /u01/app/oracle/product/11.2.0/db_1/dbs
drwxrwxr-x 2 oracle oinstall 4096 Jan 14 22:22 /u01/app/oracle/product/11.2.0/db_1/dbs

Stopping the database and removing the pfile:

[oracle@gg01 ~]$ srvctl stop database -d gg01
[oracle@gg01 ~]$ mv /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora.bak

Setting the spfile parameter:

[oracle@gg01 ~]$ srvctl modify database -d gg01 -p '+DATA/gg01/spfilegg01.ora'
[oracle@gg01 ~]$ srvctl config database -d gg01
Database unique name: gg01
Database name: gg01
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: grid
Spfile: +DATA/gg01/spfilegg01.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:

Confirming that the pfile has not been created:

[oracle@gg01 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora
ls: /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora: No such file or directory

Starting the database and confirming that the pfile has been created:

[oracle@gg01 ~]$ srvctl start database -d gg01
[oracle@gg01 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora
-rw-r--r-- 1 grid   oinstall   58 Jan 14 22:32 /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora
[oracle@gg01 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora
SPFILE='+DATA/gg01/spfilegg01.ora' # line added by Agent
[oracle@gg01 ~]$

The part of all this that still confuses me is that the srvctl “Oracle user” for the database is set to “grid” rather than “oracle” when using srvctl add database. As I see it, either the permissions on $ORACLE_HOME/dbs is a bug or setting “grid” as the “Oracle user” is a bug because the two don’t work together with the srvctl -p option… Interestingly changing the “Oracle user” to “oracle” when a pfile pointing to the spfile already exists does allow the database to start, but trying to start a database with “Oracle user” set to “oracle”, having write permissions to $ORACLE_HOME/dbs, but no existing pfile in $ORACLE_HOME/dbs does not work (at least for me) as shown below:

[oracle@gg01 ~]$ srvctl stop database -d gg01
[oracle@gg01 ~]$ mv /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora.bak
[oracle@gg01 ~]$ su
Password: 
[root@gg01 oracle]# srvctl modify database -d gg01 -u oracle
[root@gg01 oracle]# exit
exit
[oracle@gg01 ~]$ ls -ld $ORACLE_HOME/dbs
drwxr-xr-x 2 oracle oinstall 4096 Jan 20 20:37 /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@gg01 ~]$ srvctl modify database -d gg01 -p ' '
[oracle@gg01 ~]$ srvctl modify database -d gg01 -p '+DATA/gg01/spfilegg01.ora'
[oracle@gg01 ~]$ srvctl config database -d gg01
Database unique name: gg01
Database name: gg01
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/gg01/spfilegg01.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services: 
[oracle@gg01 ~]$ srvctl status database -d gg01
Database is not running.
[oracle@gg01 ~]$ ls -l $ORACLE_HOME/dbs/initgg01.ora
ls: /u01/app/oracle/product/11.2.0/db_1/dbs/initgg01.ora: No such file or directory
[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
[oracle@gg01 ~]$ chmod g+w $ORACLE_HOME/dbs
[oracle@gg01 ~]$ su
Password: 
[root@gg01 oracle]# srvctl modify database -d gg01 -u grid
[root@gg01 oracle]# exit
exit
[oracle@gg01 ~]$ srvctl start database -d gg01
[oracle@gg01 ~]$ 

I think it is now time for me to leave this alone, although please feel free to comment.

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…