Oracle9i New Feature Series: Server Parameter File

Server parameter file (SPFILE) is new in Oracle9i that could be used to replace your initialization file. While creating a database, you need to start with the traditional text initialization parameter file (initSID.ora). SPFILE feature lets the DBA change the parameter values without directly login to the server.

The SPFILE (spfile_SID.ora) is maintained on the server (on UNIX, $ORACLE_HOME/dbs directory) in a binary format by Oracle. You’re not supposed to edit this file. Changes to the SPFILE file are made using the ‘ALTER SYSTEM SET <parameter_name> = <value> SCOPE=SPFILE’ statement. The SCOPE=BOTH also changes the SPFILE.

Examples:

ALTER SYSTEM SET USER_DUMP_DEST=’/ora_dump/SID’ SCOPE=SPFILE;

ALTER SYSTEM SET SORT_AREA_SIZE=1048576 SCOPE=BOTH;

ALTER SYSTEM SET ROLLBACK_SEGMENTS=’’ SCOPE=SPFILE;

SPFILE can be generated from the traditional initSID.ora file using the CREATE SPFILE FROM PFILE statement.

To change a parameter that need to open the database if the database is down, you need to do the following:

1. STARTUP NOMOUNT
2. ALTER SYSTEM SET … SCOPE=SPFILE;
3. SHUTDOWN
4. STARTUP

Read, “Managing initialization parameters using a server parameter file” from Oracle documentation.