PFILE/SPFILE in RAC for parameter change

[Chucks_k]

DB: 10.2.0.1 2 node RAC on ASM
O/S: RHEL 4

Hi all,

I have to make a parameter change to increase the sga in our RAC db. I noticed that the system was using a pfile for each instance but seems like there is an SPFILE present there as well on the O/S pointing to an spfile in ASM.

SQL> select count(*) from v$spparameter where value is not null;

  COUNT(*)
----------
         0

I have to increase the sga_target so do i need to (for each instance) :
1/ shutdown the instance
2/ modify sga_target in the pfile
3/ startup

The above should work? However I am unsure why the spfile is not working properly (or not being used). If i do:

SQL> create pfile='/home/oracle/init.ora' from spfile;
create pfile='/home/oracle/init.ora' from spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

The above is probably because the spfile is in ASM. There is an spfile on the O/S for each node, when i do:

[oracle@mgdb2 dbs]$ strings spfilebof21.ora
SPFILE='+LOG/bof2/spfilebof2.ora'

I am a bit confused on how the spfile works with RAC, i understand it is placed in the ASM so all instances can access it.

Thanks in advance!

Chucks

[hrishy]

Hi

Whats the output of

show parameter spfile

regards
Hrishy

[LKBrwn_DBA]

You are correct, in RAC with ASM the spfile is placed in the ASM so all instances can access it.

You only need to issue the ALTER SYSTEM SET ... SCOPE=SPFILE once to modify the parameter and then restart the instances.

.

[Chucks_k]

Hi guys,

Output of show spfile:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

Above confirms its not being used, so would ALTER SYSTEM SET ... SCOPE=SPFILE work. Also SGA_MAX_SIZE is set to 2GB (altough its not in any of the pfiles). So if i shutdown the instances, edit the SGA_TARGET to higher value and restart the instance the new values should be reflected.

[davey23uk]

first - create spfile from pfile
restart db
ensure it is being used
get rid of any pfiles hanging around
alter system scope = spfile for what you want to change
restart