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.
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.
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.
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
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 listed below.
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 the following command.
ALTER SYSTEM RESET OPEN_CURSORS SID='*' SCOPE='SPFILE';
In a Real Application Cluster (RAC) environment node-specific parameters can be set using the
ALTER SYSTEM SET OPEN_CURSORS=500 SID='SID1' SCOPE='SPFILE';
Hope this helps. Regards Tim...