- Introduction
- Understanding the Server Parameter File (SPFILE)
- Setting Events in the SPFILE
- Migrating from PFILE to SPFILE
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Understanding the Server Parameter File (SPFILE)
Prior to Oracle9i, the initialization parameters for an Oracle instance were read during instance startup by looking at the init<sid>.ora file. This file is actually a client-side file because it should be available to any client tool (such as Oracle Enterprise Manager) that allows the facility to start and shut down a database instance. The problem with the init<sid>.ora file is that, for any change made to this file to become effective, the database instance has to be restarted (because the file is read-only during instance startup). As discussed in Part 1 of this series, in Oracle9i you can dynamically change the size of the SGA. In addition, Oracle9i allows you to record parameter settings for your database instance in a server initialization parameter file (referred to as the SPFILE). The server parameter file can be created during the installation process; it has the name SPFILE.ORA by default. Use of SPFILE is particularly valuable for Oracle9i Real Application Clusters, as it allows Oracle to perform self-tuning of the database by automatically modifying parameter settings in SPFILE.ORA.
Even though the traditional client-side parameter file can be used with Oracle9i, Oracle Corporation recommends that you use the server parameter file; otherwise, parameter changes made by Oracle as a result of auto-tuning are lost after instance shutdown.
In Oracle9i, if PFILE is not specified in your STARTUP command, by default Oracle uses the server parameter file. The initialization file is searched in the following order:
- $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
- $ORACLE_HOME/dbs/spfile.ora
- $ORACLE_HOME/dbs/init$ORACLE_SID.ora
NOTE
For UNIX, the platform-specific default location of SPFILE is $ORACLE_HOME/dbs. For Windows NT/2000, the location is $ORACLE_HOME\database.
It's possible to use an SPFILE in a non-default location and use it to start the database. This can be achieved by using the SPFILE parameter in the PFILE. For example, place the following line in the PFILE:
SPFILE=/database/mydir/spfilem901.ora
The simplest way to determine whether an SPFILE or a PFILE was used to start the database is to execute the following command from SQL*PLUS:
SQL>show parameter pfile NAME TYPE VALUE -------- ------------ --------------------------------------- spfile string /database/mydir/spfilem901.ora
The above output indicates the parameter file that was used to start this database instance.
The SPFILE is a binary file and should not be edited in the same manner that you would have used to edit a client-side initialization file (init<sid>.ora). Instead, the ALTER SYSTEM command should be used to modify the SPFILE. It's possible to use the same SPFILE for all the database instances on the same machine; you may even want to use a separate SPFILE for each instance on a machine. Using separate SPFILEs for each instance allows you to configure and tune each instance independently, whereas using the same SPFILE for all the instances simplifies maintenance. Both global as well as instance-specific parameters can be recorded in the SPFILE.
The following two clauses of the ALTER SYSTEM command are useful when working with SPFILE parameters:
SCOPE
This clause is used to specify the scope of parameter changes. Three possible values can be used with the SCOPE clause:
MEMORY
This setting indicates that the change is made in memory and takes effect immediately. The change persists until the database is shut down. If the database instance was started using a PFILE (that is, the init<sid>.ora client-side file), then this is the only option available with the scope. If a PFILE was used to start the database, this is the default setting.
SPFILE
This setting indicates that the change is made in the server parameter file. When changing the value of a static parameter, you must use this setting. The change takes effect when the database instance is restarted.
BOTH
This setting indicates that the change is made in memory as well as in the server parameter file. The change takes effect immediately and persists after the database is restarted. If an SPFILE was used to start the database, this is the default setting.
SID
This clause is relevant only in an Oracle9i Real Application Clusters environment. Oracle9i Real Application Clusters has three types of parameters:
Those that can have any valid value for any instance
Those that must have identical settings for all instances
Those that must have unique settings for each instance
It allows you to specify the SID of the instance where the value will take effect. If you want the change to affect all the instances of the database, specify SID='*' (on the other hand, if you want the change to affect a particular instance only, specify SID='instance_name'). If an instance other than the current instance is specified, Oracle sends a message to that instance to change the parameter value in its memory. Not specifying the SID clause results in Oracle assuming the SID of the current instance (if the instance was started with a PFILE) or SID='*' (if the instance was started up with an SPFILE).
The ALTER SYSTEM...RESET clause works complementary to the ALTER SYSTEM...SET clause in that it can be used to restore parameters to their default values.
Let's consider some examples to understand the SCOPE and SID clauses of the ALTER SYSTEM command.
SQL>alter system set job_queue_processes=10 scope=memory sid='*'
The above command sets job_queue_processes to 10 in memory and for all the instances on that machine.
SQL>alter system set job_queue_processes=20 scope=memory sid='M901'
The above command sets job_queue_processes to 20 in memory but only for the instance 'M901'. This overrides any prior setting of job_queue_processes for the M901 instance.
SQL>alter system reset job_queue_processes scope=memory sid='M901'
The above command restores the job_queue_processes parameter for the instance M901 to its default value. The job_queue_processes setting for all other instances of this database remain unchanged.