PFILE/SPFILE in RAC for parameter change
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PFILE/SPFILE in RAC for parameter change

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    PFILE/SPFILE in RAC for parameter change

    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.

    Code:
    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:

    Code:
    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:

    Code:
    [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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Whats the output of

    show parameter spfile

    regards
    Hrishy

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Talking RAC it on...

    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.

    .
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Dec 2001
    Posts
    337
    Hi guys,

    Output of show spfile:

    Code:
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width