|
By default, Oracle uses parameter settings in the server parameter file to
control database resources. The traditional client side parameter file (init.ora)
can also be used; however, Oracle recommends that you use the server
parameter file because it is easier to use and manage.
The server parameter file (also called SPFILE) is in a single location where
all the necessary parameters are defined and stored. The defined parameter
values are applicable for all the instances in the cluster. The SPFILE
permits dynamic changes without requiring you to bring down the instance.
You can still use the client side parameter file to manage parameter
settings in Real Application Clusters; however, administrative convenience
is sacrificed and the advantage of dynamic change is lost. By default, if
you do not specify PFILE in your STARTUP command, Oracle will use a server
parameter file.
Server Parameter File
A server parameter file is basically a repository for initialization
parameters. Initialization parameters stored in a SPFILE are persistent,
meaning any parameter changes made while an instance is running can persist
across instance shutdown and startup. In this way, all the initialization
parameters manually updated by ALTER SYSTEM SET commands become persistent.
It also provides a basis for the Oracle database server to self-tune.
Another advantage, particularly for multi-instance RAC systems, is that a
single copy of the parameter file can be used by all instances. Even though
a single file is used to specify parameters, it has different format styles
to support both the common values for all instances, as well as the specific
values for an individual instance.
A server parameter file is initially built from the traditional text
initialization parameter file, using the create SPFILE statement. It is a
binary file that cannot be browsed or edited with a text editor. Oracle
provides other interfaces for viewing and modifying parameter settings.
At system startup, the default behavior of the STARTUP command is to read a
SPFILE to obtain initialization parameter settings. If the STARTUP command
doesn’t have a PFILE clause, it reads the SPFILE from a location specified
by the operating system. If you choose to use the traditional text
initialization parameter file, you must specify the PFILE clause when
issuing the STARTUP command.
Setting the Server Parameter File Values
Use the SID designator to set instance-specific parameter values in the
server parameter file. For settings across the database, use a ‘*’, and for
a specific instance, set the prefix with SID as indicated below.
*.OPEN_CURSORS=500 # For database-wide
setting
RACDB1.OPEN_CURSORS=1000 # For RACDB1 instance
Note that even though open_cursors is set at 500 for all instances in
the first entry, the value of 1000 remains in effect for the SID ‘RACDB1’.
Some initialization parameters are dynamic since they can be modified using
the ALTER SESSION or ALTER SYSTEM statement while an instance is running.
Use the following syntax to dynamically alter initialization parameters:
ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]
Use the SET clause of the ALTER SYSTEM statement to set or change
initialization parameter values. Additionally, the SCOPE clause specifies
the scope of a change as described below:
SCOPE = SPFILE
(For both static and dynamic parameters, changes are recorded in the spfile,
to be given effect in the next restart.)
SCOPE = MEMORY
(For dynamic parameters, changes are applied in memory only. No static
parameter change is allowed.)
SCOPE = BOTH
(For dynamic parameters, the change is applied in both the server parameter
file and memory. No static parameter change is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When
specified, the change is effective only for future sessions. Now, Let us
look at some examples.
The following statement affects all instances. However, the values are only
effective for the current instances, they are not written to binary SPFILE.
ALTER SYSTEM SET OPEN_CURSORS=2000 SID='*'
SCOPE=MEMORY;
The next statement resets the value for the instance ‘RACDB1’. At this
point, the database-wide setting becomes effective for SID of RACDB1.
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE
sid='RACDB1';
To reset a parameter to its default value throughout the cluster database,
use the command:
ALTER SYSTEM RESET OPEN_CURSORS
SCOPE=SPFILE sid=’*’;
Creating a Server Parameter File
The server parameter file is initially created from a text initialization
parameter file (init.ora). It must be created prior to its use in the
STARTUP command. The create SPFILE statement is used to create a server
parameter file. The following example creates a server parameter file from
an initialization parameter file.
CREATE SPFILE FROM PFILE='/u01/oracle/product/920/dbs/initRAC1.ora';
Below is another example that illustrates creating a server parameter file
and supplying a name.
CREATE SPFILE='/u01/oracle/product/920/dbs/racdb_spfile.ora'
FROM PFILE='/u01/oracle/product/920/dbs/init.ora';
Exporting the Server Parameter File
We can export the server parameter file to create a traditional text
initialization parameter file. This would be useful for:
- Creating backups of the server parameter file.
- For diagnostic purposes to list all of the parameter values currently
used by an instance.
- Modifying the server parameter file by first exporting it, editing the
output file, and then recreating it.
The following example creates a text initialization parameter file from the
server parameter file:
CREATE PFILE FROM SPFILE;
The example below creates a text initialization parameter file from a server
parameter file, where the names of the files are specified:
CREATE PFILE='/u01/oracle/product/920/dbs/racdb_init.ora'
FROM SPFILE='/u01/oracle/product/dbs/racdb_spfile.ora';
Refer to ‘Oracle 9i Database Reference’ for all the parameters that can be
changed with an ALTER SYSTEM command.
 |
If you like Oracle tuning, check out my latest book "Oracle
Tuning: The Definitive Reference".
It's 980 pages of hard-core tuning insights, tips and
scripts, and you can buy it direct from the publisher for 30%-off. |
|
