Oracle 9i and later version of the RDBMS have provided us with an increasing ability to dynamically control initialization parameters through the use of a SPFILE instead of an INIT file. By definition the differences between the two files are:

INIT File – OS text file that defines the initialization parameters for the database. You may be able to dynamically change initialization parameters in a database instance but the changes will not survive a restart of the database. The text init file must be updated manually for any changes to take affect upon restart.

SPFILE – OS binary file that defines the initialization parameters for the database. Dynamic changes to a database instance can be stored in the SPFILE to take affect upon restart and immediately. Non-Dynamic changes can be recorded to the SPFILE, to take affect at restart without having to enter OS layer.

This article will cover determining what you are using (an SPFILE or INIT file) and how oracle determines which to use.

Determination of what you are using:

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 21 19:31:43 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Use the following command to determine if you are using an spfile: show parameter {initalization_parameter}

In below example, you are using an SPFILE because the VALUE file is not null and defines an SPFILE:

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string
/opt/app/oracle/10.2.0/dbs/spfileorcl.ora

In this next example you are using an init file because the VALUE of SPFILE is null.

NAME TYPE VALUE
———————————— ———– ——————————
spfile string

This completes the determining the use of SPFILE or INIT file.

How Oracle handles SPFILE and INIT files during startup. (10g and higher)

1. First, Oracle looks for a ‘pfile={file and full path}’ clause in your start command and if it is present uses that file regardless of the existence of any other SPFILE or INIT file.

2. Second, Oracle looks for an spfile{sid}.ora file in the directory
$ORACLE_HOME/dbs and if found it will use it.

3. Finally, Oracle looks for an init{sid}.ora file in the directory
$ORACLE_HOME/dbs and if found it will use it.

4. If none of these exist, the Oracle instance will not start.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com