This post is just a short note documenting a procedure that isn’t done frequently or described anywhere else (to my knowledge).
Sometime last month, a customer asked for my help to change the SIDs on one of his RAC databases to match the new corporate standard. The database name matched the standard so we could leave that alone, but the SID needed to be changed.
Here’s what we did to change it (on 11gR2, but it should work on older versions too):
- Save a copy of SPFILE as pfile.
- Replace all SIDs in pfile.
- Stop database: srvctl stop database -d <dbname>.
On one node:
- Export ORACLE_SID=<new SID>.
- Rename init.ora file:
cp init<old sid>.ora init<new sid>.ora - Start instance no mount:
startup nomount pfile=/home/oracle/temp_pfile - Replace SPFILE with the modified version:
create spfile=’+DATADG01/<dbname>/spfile<dbname>.ora’ from pfile=’/home/oracle/temp_pfile’; - Stop the instance.
- Start the instance again.
- Make sure that it looks okay by running “select instance_name from v$instance”.
- Stop the instance.
Then:
- Rename init.ora on other cluster nodes.
- Unregister instances from srvctl:
srvctl remove instance -d <dbname> -i <old sid node 1>
srvctl remove instance -d <dbname> -i <old sid node 2>
srvctl remove instance -d <dbname> -i <old sid node 3> - Register new instances:
srvctl add instance -d <dbname> -i <new sid node 1> -n <hostname node 1>
srvctl add instance -d <dbname> -i <new sid node 2> -n <hostname node 2>
srvctl add instance -d <dbname> -i <new sid node 3> -n <hostname node 3>
Enjoy!
And of course, comment below if you think I forgot a step or know about a better way to do it.
5 Comments. Leave new
Shapira,
Actually in 11gR2, spile is in shared storage ASM, so need to change only one instance and start others using the modified spfile. No need the step (1. Rename init.ora on other cluster nodes), just shutdown and start … is it?
Thanks,
Wissem
At least on the systems I managed, although the SPFILE is on ASM, there is a PFILE “initSID.ora” that contains a single line:
SPFILE=+ASM/
As far as I know this line is mandatory, and if you change the path to the SPFILE in ASM (which we did), you will need to change it on all nodes.
If you just replace the existing SPFILE in the same location, but don’t create a new one, you can skip this space – regardless of whether or not ASM is involved.
I hope its clearer now :)
Hi Gwen,
I might be missing something with both the comment from Wissem and your response, but
I’m interested to know if “Spfile” had been set in the database configuration, i.e. the output of:
srvctl config database -d <DB_NAME> | grep “Spfile”
What I’ve observed is that if the spfile is defined for a database (in the OCR) then the pfile ($ORACLE_HOME/dbs/initSID.ora) is either created or updated/overwritten (as required) to set the appropriate SPFILE= line when each instance of a database is started. I’ve just verified this in 11.2.0.2 by both removing the pfile followed by a stop and start via srvctl; then modifying the path followed by a stop and start via srvctl. It’s worth noting that you get a “# line added by Agent” comment in the pfile. I assume “Agent” in this context is oraagent.bin.
The only situation I’ve seen this not work is where there has been separate ownership for GI (grid) and RDBMS (oracle) as I wrote about in https://oraganism.wordpress.com/2010/01/20/srvctl-p-option-part-ii/
In summary, use of “srvctl modify database -d <DB_NAME> -p” would save rename of pfiles, which I think is the point Wissem was making.
Ah, I see what you mean Martin.
Yes, if you are using spfile as part of the RAC resource, and you don’t want/need explicit control over the file name and location, you don’t have to do the little dance I did with the rename.
If you need remote access you should also rename password files on all nodes.