Server PFILE

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.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.