Persistent Initialization Parameters

In previous versions, alterations of server parameters using the ALTER SYSTEM SET command would only remain until the server was restarted. In the same way, if the server was started using as local parameter file, the changes in the file could not be made to persist between shutdowns. Oracle9i introduces the concept of persistent initialization parameters:

PFILE
SPFILE
Database Startup
Parameter Scope

PFILE

A PFILE is a traditional text based init.ora parameter file. Typically this resides on the server in the $ORACLE_BASE/admin/SID/pfile directory, with a symbolic link pointing to it from the $ORACLE_HOME/dbs directory. In addition, a DBA may keep copies of this file on their local PC to allow remote startup:

SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP PFILE=C:LocalInit.ora

A PFILE is necessary in order to create a SPFILE to enable persistent initialization parameters.

If you already have a SPFILE, a PFILE can be generated from it using one of the following:

CREATE PFILE FROM SPFILE;
CREATE PFILE FROM SPFILE = 'production.ora';
CREATE PFILE = '$ORACLE_HOME/dbs/my_pfile.ora' FROM SPFILE;
CREATE PFILE = '$ORACLE_HOME/dbs/my_pfile.ora' FROM SPFILE = '$ORACLE_HOME/dbs/my_spfile.ora';

If the SPFILE name is not specified Oracle will look for the default SPFILE. If this is not present an error will be returned. If the PFILE name is not specified Oracle will use a platform specific default name.

Once the PFILE is produced you can edit it and use it to create a new or modified SPFILE.

SPFILE

A SPFILE, Server Parameter File, is a server managed binary file that Oracle uses to hold persistent initialization parameters. If a parameter is changed using the ALTER SYSTEM SET command Oracle will apply this parameter change to the current SPFILE. Since the database uses this file during startup all parameter changes persist between shutdowns.

A SPFILE is created from a PFILE using one of the following commands:

SQL> CREATE SPFILE FROM PFILE = '$ORACLE_HOME/dbs/my_pfile.ora';
SQL> CREATE SPFILE = '$ORACLE_HOME/dbs/my_spfile.ora' FROM PFILE = '$ORACLE_HOME/dbs/my_pfile.ora';

If the SPFILE is not specified Oracle will assume you are creating a default SPFILE. If a default SPFILE already exists Oracle will overwrite it. If the SPFILE is specified a non-default SPFILE will be created. The named SPFILE must not be the same as a SPFILE that was used to start the current instance. Oracle recommends that you leave it to decide on the name and location of the SPFILE.

Database Startup

There are now several ways of starting the database:

-- Use the default SPFILE
STARTUP

-- Use non-default SPFILE
STARTUP PFILE = singleline_init.ora

-- Use traditional text parameter file
STARTUP PFILE = init.ora

If a non-default SPFILE is required, the PFILE parameter must point to a text based initialization file that contains a single line in the following fomat:

SPFILE = $ORACLE_HOME/dbs/my_spfile.ora

Parameter Scope

The scope of the ALTER SYSTEM SET command can be defined using the following:

ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH];

The actions of the scope parameters are:

BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.
SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.
MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.

A parameter value can be reset to the default using:

ALTER SYSTEM RESET OPEN_CURSORS SID='*' SCOPE='SPFILE';

In a Real Application Cluster (RAC) environment node-specific parameters can be set using the SID parameter:

ALTER SYSTEM SET OPEN_CURSORS=500 SID='SID1' SCOPE='SPFILE';

Hope this helps. Regards Tim...

Back to the Top.