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…

11.2 Oracle Restart – Changing Hostname

It has taken me a good deal longer that I would have expected to change the hostname on some cloned VMs that are home to Oracle 11.2 databases running on ASM with Oracle Restart.

I was clearly not alone in thinking that it would just be a case of running localconfig delete/localconfig add, as I have done in the past for hosts running Oracle database 10.2 with ASM for storage. However, things have changed and having done a fair amount of searching using both MetaLink (My Oracle Support) and Google it appears that there isn’t much out there yet in the form of clear instructions. The best I could find was My Oracle Support article ID 887658.1, which got me moving in the right direction.

So, in the hope of saving others the time I have lost working this out, here are the steps I used to rename a Linux host running Oracle Database 11.2 with ASM in an Oracle Restart configuration. The steps have been written for an installation that splits the ownership of the “Grid Infrastructure” and the database between a user named grid and a user named oracle respectively. It is important who you run at least some of these commands as, so please check which user you are before each command. Or more likely, if things don’t look to be working then check which user you ran the command as before going further. It is easy to backtrack if you do get it wrong.

Remove the existing configuration as root (with ORACLE_HOME set to the grid home):

[root@old-host grid]# $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl -delete

* In some of my earlier attempts I stopped everything gracefully, but it does not appear to be necessary.

Change the value of HOSTNAME in the network file using the editor of your choice.

[root@old-host ~]# vi /etc/sysconfig/network

Update /etc/hosts as appropriate

[root@old-host ~]# vi /etc/hosts

Change the IP address in ifcfg-<interface> as required

[root@old-host ~]# vi /etc/sysconfig/network-scripts/ifcfg-eth0

Change the hostname in the listener configuration file

[root@old-host ~]# vi $ORACLE_HOME/network/admin/listener.ora

* Clearly you don’t need to be root to do the previous command, but as reboot is next it doesn’t seem worth switching user.

Reboot to pickup hostname change

When the host has restarted run roothas.pl as root (with ORACLE_HOME set to the grid home)

[root@new-host grid]# $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

The output should look something like:

2009-12-19 15:10:36: Checking for super user privileges
2009-12-19 15:10:36: User has super user privileges
2009-12-19 15:10:36: Parsing the host name
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node new-host successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting

2009/12/19 15:11:58     /u01/app/grid/product/11.2.0/grid/cdata/new-host/backup_20091219_151158.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

Exit from root user.

You now need to add the listener, ASM, database and services (if applicable) into the Oracle Restart configuration.

[grid@new-host ~]$ srvctl add listener
[grid@new-host ~]$ srvctl add asm -d '/dev/sd[a-h]1'
[grid@new-host ~]$ srvctl start listener
[grid@new-host ~]$ srvctl start asm

* Your asm_diskstring, specified with the -d option to “srvctl add asm”, with almost certainly be different!

Note that the diskgroups are not automatically added back to the Oracle Restart configuration (as shown by the output of “srvctl status” below) and must be manually started:

[grid@new-host ~]$ srvctl status diskgroup -g data
PRCR-1001 : Resource ora.DATA.dg does not exist

[grid@new-host ~]$ sqlplus /nolog
SQL> conn / as sysasm
Connected.
SQL> alter diskgroup data mount;

Diskgroup altered.

SQL> exit

[grid@new-host ~]$ srvctl status diskgroup -g data
Disk Group data is running on new-host
[grid@new-host ~]$

Adding the database to Oracle Restart:

[oracle@new-host ~]$ srvctl add database -d ora11gr2 -o $ORACLE_HOME -n ora11gr2 -p +DATA/ora11gr2/spfileora11gr2.ora -a DATA
[oracle@new-host ~]$ srvctl config database -d ora11gr2
Database unique name: ora11gr2
Database name: ora11gr2
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: grid
Spfile: +DATA/ora11gr2/spfileora11gr2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
[oracle@new-host ~]$ srvctl start database -d ora11gr2
[oracle@new-host ~]$ srvctl status database -d ora11gr2
Database is running.
[oracle@new-host ~]$

That should be it. The only other place to update the home name is in the tnsnames.ora file.