Oracle Parameter Changes

Oracle Parameter changes:

Scenario:

Oracle Parameters            Current        Proposed

db_cache_size                   10048M        17920M

sga_target                                 21G               29G

sga_max_size                            22G               30G

 

you want to change above oracle parameters to its proposed value.

There are 2 ways of changing Oracle Parameters:

1. By editing an initialization parameter file (init<SID>.ora)
2. By issuing an ALTER SYSTEM SET ... SCOPE=SPFILE statement to update a server parameter file (spfile).

  

First Take a backup of an existing pfile & spfile.

1. Editing Pfile:

   Steps:

• Create Pfile from SPFILE. 

          SQL> create pfile from spfile;

          It  creates a  file init<SID>.ora inside /$ORACLE_HOME/dbs  directory.

• Login as ora<SID > user & edit Pfile in vi editor.

          vi init<SID>.ora
          Change parameters as per your requirement.
          Save & close it. (:wq!)

• Login in to SQL plus & shutdown the database.
• Start database using Pfile.

         SQL> startup pfile='$ORACLE_HOME/dbs/init<SID>.ora';

• Now  create SPfile from Pfile.

          SQL> create spfile from pfile;

• Shutdown database.

          SQL> shutdown immediate;

• Start database with SPFILE.

          SQL> startup;

2. Using ALTER  SYSTEM SET commands:

Steps:

• First check Parameter and its actual value:

           SQL> show parameter <parameter name>;

           

• Change parameter value using ALTER SYSTEM set command (SPfile):

          SQL> ALTER SYSTEM SET <Parameter name> = <value> scope = <spfile/Memory/Both> sid = ‘*’; 

• Shutdown database.

          SQL> shutdown immediate;

• Start database with SPFILE.

          SQL> startup;


Whereas,

Scope:

MEMORY: 

The change is immediate but will not be available after next startup or reboot  of the instance.

SPFILE: 

The change will be effective in SPFILE only and will be available after next startup or reboot

BOTH: 

The change is effective for both MEMORY and SPFILE and will be available after next startup also.
 

Default is BOTH

Sid:

Applicable for RAC database.

This is to specify the instance where you want to make that change.

Sid=’*’ means change in the parameter will takes place for all instances after restart.

Default is ‘*’

SAPPFPAR:

• Run sappfpar to see how the pools should be set (ipc/shm_psize_40 and ipc/shm_psize_10)
• Login as <SID>adm

          sappfpar check pf=< profile path>:

          

Output will be a report containing error with solution.

 e.g:

<SID>adm > sappfpar check pf=/sapmnt/<SID>/profile

================================================================================

==   Checking profile:     /sapmnt/<SID>/profile

================================================================================

***ERROR: Size of shared memory pool 40 too small

================================================================

SOLUTIONS: (1) Locate shared memory segments outside of pool 40

               with parameters like: ipc/shm_psize_<key> =0

SOLUTION: Increase size of shared memory pool 40

          with parameter: ipc/shm_psize_40 =486000000

Shared memory disposition overview

================================================================ 

• As per adviced solution adjust the parameter value.

• Restart the system