The Server Parameter File in a Real Application Clusters Environment

PURPOSE
-------

The purpose of this bulletin is to demonstrate the usage of the server
parameter file, a new feature in 9i, in a Real Application Clusters
environment.


SCOPE & APPLICATION
-------------------

This Article is for database administrators of ORAC environments looking to
implement the server parameter file feature in their Application Clusters
environment.


THE SERVER PARAMETER FILE IN A REAL APPLICATION CLUSTERS ENVIRONMENT
--------------------------------------------------------------------

In 9i there is a new feature called the server parameter file that will allow
you to store a binary copy of your initialization parameter file. This feature
will allow you to make permanent dynamic changes to parameters within the
database while allowing you to change certain parameters in memory only. For
Oracle Real Application Clusters you can use one server parameter file and
share it between instances.


Creating a server parameter file in a Real Application Clusters Environment:

1. Create a text parameter file that seperates parameters between global and
instance specific parameters. For the instance specific parameters, specify
SID.parameter and for the global parameters just use parameter=. For example:

# Example of Global Parameters

db_name = "V9I"
control_files = ("/ora9i/oradata/V9I/v910ctl1", "/ora9i/oradata/V9I/v910ctl2")
parallel_server=true

# Example of Instance Specific Parameters for Node 1:

V9IM.instance_name = V9IM
V9IM.instance_number = 1
V9IM.thread =1
V9IM.rollback_segments = (rbs01_t1, rbs02_t1, rbs03_t1, rbs04_t1, rbs05_t1)

# Example of Instance Specific Parameters for Node 2:

V9IZ.instance_name = V9IZ
V9IZ.instance_number = 2
V9IZ.thread =2
V9IZ.rollback_segments = (rbs01_t2, rbs02_t2, rbs03_t2, rbs04_t2, rbs05_t2)

2. Create a raw partition for your spfile using platform specific commands.

3. Start up the instance if it is not already started and issue the following
statement to create your spfile on your raw partition:

create spfile='/dev/vx/rdsk/Stripe12/v910init'
from pfile='/ora9i/oradata/V9I/pfile/spfileV9I.ora';

4. For easy manageability you can create an initSID.ora file in your local
$ORACLE_HOME/dbs directory that points to your spfile:

spfile=/dev/vx/rdsk/Stripe12/v910init

5. At this point you should be able to start up using your spfile:

SQL> shutdown immediate
SQL> startup

6. You can then backup the file you used to create your spfile to avoid
confusion:

% mv /ora9i/oradata/V9I/pfile/spfileV9I.ora /ora9i/oradata/V9I/pfile/spfile.bak


Creating a pfile from your spfile:

With the spfile feature you can dump the contents of your spfile back into a
text initialization file. For Example:

create pfile='/ora9i/oradata/V9I/pfile/spfileV9I.ora'
from spfile='/dev/vx/rdsk/Stripe12/v910init';

You can use this feature to backup your spfile.


Viewing parameters in the spfile:

select from v$spparameter;


Changing spfile parameters dynamically:

You can change spfile parameters with alter system commands. You can specify
the scope to determine whether to change the parameter in memory, in your
spfile, or both. Some static parameters like db_name will not allow you to
change the parameter in memory. You can also define the SID to change
instance specific paramters.

o scope can be set to memory, spfile, or both
o sid can be set to define one specific instance

For example:

SQL> alter system set job_queue_processes=30 scope=both sid='V9IZ';

To reset parameters back to their default settings you can issue:

SQL> alter system reset job_queue_processes scope=both sid='V9IZ';