Oracle Database Admin (DBA) Tutorial: Initialization parameter files (PFILE and SPFILE)

PFILE

• A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally resides on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.
• Inside the PFILE are a number of database settings called parameters. These parameters help the Oracle programs know how to start. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and where certain database files already exist.

SPFILE

• An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore. 

• It can be backed up by RMAN (Oracle’s backup and recovery software) every time a change is made or when the database is backed up, which means it’s easier to recover. 

• SPFILES allow you to make dynamic changes to parameters that are persistent

Managing Initialization Parameters Using a Server Parameter File (SPFILE)

1. Creating a Server Parameter File 

• CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';
• CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM PFILE='/u01/oracle/dbs/test_init.ora'; 

1. Using SQL*Plus to View Parameters

SQL> SELECT name , value FROM V$PARAMETER;

NAME                   VALUE

lock_name_space        2

processes                 150

sessions                   170

timed_statistics       TRUE

timed_os_statistics    0

SQL>SHOW PARAMETER SHARED_POOL_SIZE

NAME                    TYPE        VALUE

shared_pool_size    big integer       0

The SCOPE clause specifies the scope of a change as follows:

• SCOPE=SPFILE: The change is applied in the server parameter file only. No change is made to the current instance. For both dynamic and static parameters, the change is effective at the next startup of the instance.
• SCOPE=MEMORY: The change is applied in memory only. For dynamic parameters, the effect is immediate but not persistent because the spfile is not updated.
• SCOPE=BOTH: The change is applied in both the server parameter file and memory.

SQL> ALTER SESSION SET NLS_DATE_FORMAT ='mon dd yyyy';

Session altered.

SQL> SELECT SYSDATE FROM dual;

SYSDATE

Jun 12 2007