Managing Initialization Parameters Using a Server Parameter File (SPFILE)

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file(PFILE). For better manageability, you can choose to maintain initialization parameters in a binary server parameter file (SPFILE) that is persistent across database startup and shutdown.



The server parameter file (SPFILE) is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings in a SPFILE.

You use the CREATE SPFILE statement to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

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 re-create the same server parameter file that is currently being used by the instance.

You can create a server parameter file (SPFILE) from an existing text initialization parameter file or from memory. Creating the SPFILE from memory means copying the current values of initialization parameters in the running instance to the SPFILE.

The example below illustrates creating a server parameter file with a name and location.

CREATE SPFILE='/db/oracle/dbs/spfileDB_SID.ora'  FROM PFILE='/db/oracle/dbs/initDB_SID.ora';

It can create SPFILE directly from memory with

CREATE SPFILE FROM MEMORY;

You can specify the name of the SPFILE with

CREATE SPFILE = 'spfile_name' FROM MEMORY;

Setting or Changing Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values.

The optional SCOPE clause specifies the scope of a change as described in the following table:

SCOPE = SPFILE
The change is applied in the server parameter file only.
The effect is as follows:
- No change is made to the current instance.
- For both dynamic and static parameters, the change is effective at the next startup and is persistent.
- This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORY
The change is applied in memory only.
The effect is as follows:
- The change is made to the current instance and is effective immediately.
- 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:
- The change is made to the current instance and is effective immediately.
- For dynamic parameters, the effect is persistent because the server parameter file is updated.
 -For static parameters, this specification is not allowed

You can set the parameter in SPFILE with:

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=2 SCOPE=SPFILE;

You can clean/reset Initialization Parameter Values with:

ALTER SYSTEM RESET "SEC_MAX_FAILED_LOGIN_ATTEMPTS" SCOPE=BOTH;

You can view current value of the parameters in SQLPlus with command

SHOW PARAMETERS

If you want to see some specific parameter/s/ value please use SQLPlus command:

SHOW PARAMETER spfile.

i.e. SHOW PARAMETER parameter_name/pattern


Origin: Oracle