Thursday, September 2, 2010

Changing spfile location of RAC DATABASE

Last week we made some changes in the configuration (SGA & PGA) of our RAC DB using pfile. After open the each instance using pfile we executed below commands pfile with new configuration:
create spfile from pfile='.....';

As a result now each instance has different spfile in their default location ($ORACLE_HOME/dbs).
So we were planning to shift back spfile to a common location in ASM.

To do this we followed steps bellow:

1. Take backup of pfile & spfile

2. login to a instance(my case instance "dw1" of DB "dw") as sysdba

3. SQL> create pfile='/home/oracle/pfileaug31aug2010.ora' from spfile;

4. SQL> create spfile='+DATA1/dw/spfiledw.ora' from pfile='/home/oracle/pfileaug31aug2010.ora';

5. Then create a pfile in the default location($ORACLE_HOME/dbs/initSID.ora) having only the spfile location:
echo "SPFILE='+DATA1/axdw/spfiledw.ora'" > $ORACLE_HOME/dbs/initdw1.ora

6. delete the spfile in default location($ORACLE_HOME/dbs)

7. restart the current instance

8. Now repeat steps 5,6 & 7 for all other instances

9. Now while a instance starts
- it will look for spfile in the default location
- as no spfile is there it will look for pfile
- in pfile it will find the location of spfile and load init params for it

No comments:

Post a Comment