Server Parameter File (SPFILE)
SHOW PARAMETERS This SQL*Plus command displays the currently in use parameter values.
CREATE PFILE This SQL statement creates a text initialization parameter file from the binary server parameter file.
V$PARAMETER This view displays the currently in effect parameter values.
V$PARAMETER2
This view displays the currently in effect parameter values. It is
easier to distinguish list parameter values in this view because each
list parameter value appears as a row.
V$SPPARAMETER This
view displays the current contents of the server parameter file. The
view returns NULL values if a server parameter file is not being used by
the instance.
A
server parameter file is initially built from a traditional text
initialization parameter file using the CREATE SPFILE statement. It is a
binary file that cannot be browsed or edited using 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
server parameter file to obtain initialization parameter settings. The
STARTUP command with no PFILE clause, reads the server parameter file
from an operating system specific location. If you choose to use the
traditional text initialization parameter file, you must specify the
PFILE clause when issuing the STARTUP command. Explicit instructions for
starting an instance using a server parameter file are contained in
Starting Up a Database.
How to check if an instance was started using a PFILE or an SPFILE
Use the following query on a started instance (nomount, mount or open):
SQL> select count(*) from v$spparameter where value is not null;
If the result of this query is zero, the instance was started using a
PFILE. If the result is a non-zero value, the instance was started using
an SPFILE.
Explanation:When an instance has been started using a
server side parameter file (SPFILE), which is a new feature in Oracle
Server 9.0.1, the v$spparameter contains NOT NULL values for several
parameters. When an instance has been started using the 'classic' PFILE,
this view only contains NULL values.
Creating Server Parameter Files (SPFILE)
The
server parameter file must initially be created from a traditional text
initialization parameter file. It must be created prior to its use in
the STARTUP command. The CREATE SPFILE statement is used to create a
server parameter file. You must have the SYSDBA or the SYSOPER system
privilege to execute this statement.
The following example creates a server parameter file from initialization parameter file /disk01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.
CREATE SPFILE FROM PFILE='/disk01/app/oracle/product/10.0.1/dbs/init.ora';
Another example, below, illustrates creating a server parameter file and supplying a name.
CREATE SPFILE='/disk01/app/oracle/product/10.0.1/dbs/test_spfile.ora'
FROM PFILE='/disk01/app/oracle/product/10.0.1/dbs/init.ora';
The
server parameter file is always created on the machine running the
database server. If a server parameter file of the same name already
exists on the server, it is overwritten with the new information.
Oracle
recommends that you allow the database server to default the name and
location of the server parameter file. This will ease administration of
your database. For example, the STARTUP command assumes this default
location to read the parameter file.
When
the server parameter file is created from the initialization parameter
file, comments specified on the same lines as a parameter setting in the
initialization parameter file are maintained in the server parameter
file. All other comments are ignored.
The
CREATE SPFILE statement can be executed before or after instance
startup. However, if the instance has been started using a server
parameter file, an error is raised if you attempt to recreate the same
server parameter file that is currently being used by the instance.
Setting or Changing Initialization Parameter Values
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 in the following table:
Scope/Description
SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is effective at the next startup and is persistent.
For
static parameters, the behavior is the same as for dynamic parameters.
This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:
For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
For static parameters, this specification is not allowed.
SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:
For dynamic parameters, the effect is immediate and persistent.
For static parameters, this specification is not allowed.
Exporting Server Parameter Files (SPFILE)
You
can export a server parameter file to create a traditional text
initialization parameter file. Reasons for doing this include:
Creating backups of the server parameter file
For
diagnostic purposes, listing all of the parameter values currently used
by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS
command or selecting from the V$PARAMETER or V$PARAMETER2 views.
Modifying of the server parameter file by first exporting it, editing the output file, and then recreating it.
The exported file can also be used to start up an instance using the PFILE option.
The
CREATE PFILE statement is used to export a server parameter file. You
must have the SYSDBA or the SYSOPER system privilege to execute this
statement. The exported file is created on the database server machine.
It contains any comments associated with the parameter in the same line
as the parameter setting.
The following example creates a text initialization parameter file from the server parameter file:
CREATE PFILE FROM SPFILE;
Because
no names were specified for the files, a platform-specific name is used
for the initialization parameter file, and it is created from the
platform-specific default server parameter file.
The
following example creates a text initialization parameter file from a
server parameter file where the names of the files are specified:
CREATE PFILE='/disk01/oracle/dbs/tv_init.ora'
FROM SPFILE='/disk01/oracle/dbs/tv_spfile.ora';
Errors and Recovery for the Server Parameter Files (SPFILE)
If
an error occurs while reading the server parameter file (during startup
or an export operation), or while writing the server parameter file
during its creation, the operation terminates with an error reported to
the user.
If
an error occurs while reading or writing the server parameter file
during a parameter update, the error is reported in the alert file and
all subsequent parameter updates to the server parameter file are
ignored. At this point, you have the following options:
Shutdown the instance, recover the server parameter file, then restart the instance
Continue to run without caring that subsequent parameter updates will not be persistent