Use of PFILE and SPFILE in Oracle

Before 9i, Oracle used pfile to store the initialization parameter settings. These parameters were read when the instance was started. Any modification requires restarting the instance to take effect, with spfile, you can use alter system or alter session to dynamically modify the parameters that can be dynamically modified. All changes can take effect immediately, you can choose to apply the changes only to the current instance or to the spfile at the same time. This allows all modifications to the spfile to be completed in the command line. We can choose not to manually modify the initialization parameter file, which greatly reduces the occurrence of human errors.

SPFILE is a binary file that can be backed up using RMAN. In this way, Oracle also incorporates the parameter file into Backup recovery management. In addition to PFILE for the first time to start the database (and then create SPFILE Based on PFILE), we do not need PFILE. We strongly recommend that you use spfile to store and maintain initialization parameter settings using its new features.

1. Create a SPFILE

By default, ORACLE uses PFILE to start the database. SPFILE must be created by PFILE. The newly created SPFILE will take effect the next time the database is started. create spfile requires SYSDBA or SYSOPER permissions:

Syntax:

Create spfile [= 'spfile-name'] from pfile [= 'pfile-name']

Example:

SQL> create spfile from pfile;

By default, spfile is created to the default directory of the system.

(Unix: $ ORACLE_HOME/dbs; NT: $ ORACLE_HOME \ database)

If the SPFILE already exists, the following error is returned during creation:

SQL> create spfile from pfile;

Create spfile from pfile

ERROR is located in row 1st:

ORA-32002: Unable to create SPFILE used by routine

To determine whether SPFILE is used, you can use the SHOW command to display parameter settings. If the following result returns a null value, you are using pfile:

SQL> SHOW PARAMETER spfile

NAME TYPE VALUE

------------------------------

Spfile string % ORACLE_HOME % \ DATABASE \ SPFILE % ORACLE_SID %. ORA

1 2 3 Next Page