Oracle: SPFile and PFiles

Oracle reads its parameters from a file called “SPFile”. You can't change it directly as it's a binary file. Additionally, Oracle can read the parameters from “PFiles”, which are regular text files.

To export the SPFiles to a PFile, just enter the following in SQLplus:

CREATE PFILE FROM SPFILE;

It will create a PFile ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora.

To import the parameters from the PFile to the SPFile, you have to boot the instance from the PFile and the write the SPFile. You have to enter the path to the PFile instead of ${PATH_TO_PFILE}.

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT PFILE='${PATH_TO_PFILE}'
CREATE SPFILE FROM PFILE;
SHUTDOWN IMMEDIATE;

If your PFile is located at /db/oracle/config/pfiles/initweb.ora, the the second line would be:

STARTUP NOMOUNT PFILE='/db/oracle/config/pfiles/initweb.ora'

The parameter DB_RECOVERY_FILE_DEST_SIZE specifies the size of the flash recovery area.

• For more inforamtion, see the documentation: 10.2 11.2

Example: Set flash recovery area to 8GB. The usage of SCOPE=BOTH changes the init param in memory and in the SPFile.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=8G SCOPE=BOTH SID='*';