How to startup an Oracle Database Instance using spfile or pfile(init.ora) parameter file

This post explains which initialization file can be used to start an instance, depending upon if a spfile or init.ora file exists.

Before Oracle9i, Oracle instances were always started using a text file called an init[SID].ora. This file is by default located in the “$ORACLE_HOME/dbs” directory.

In Oracle9i, Oracle has also introduced the ‘SPFILE’, which is a binary file stored on the database Server. Changes which are applied to the instance parameters may be persistent across all startup/shutdown procedures.

Starting Oracle9i, you can startup the instance using either a spfile or an init.ora file.

An Oracle instance may be started by:

• default spfile –> spfile[SID].ora
• default init.ora file –> init.ora
• specified init.ora file
• specified spfile

The default initialization files are located as follows:

• on Unix —> $ORACLE_HOME/dbs
• on WindowsNT/2000 —> %ORACLE_HOME%\database

The examples below show the different ways an Oracle database can be started:

1. Specifying neither file

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

Oracle first searches if the spfile[SID].ora exists. If it does not, Oracle searches for the spfile.ora parameter file. If neither spfile[SID].ora nor spfile.ora exist, Oracle will use the init[SID].ora parameter file.

If none of these files exist, you receive the following messages:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORA901\DATABASE\INITORA901.ORA'

2. Specifying init.ora file:

This behavior is the same as in previous database versions.

SQL> startup pfile=d:\ora901\database\initORA901.ora
ORACLE instance started.

Total System Global Area  118255568 bytes
Fixed Size                   282576 bytes
Variable Size              83886080 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

This option is not available if you are using an spfile. If you try to startup specifying an spfile, Oracle displays the following error message:

SQL> startup spfile=d:\ora901\database\spfileORA901.ora
SP2-0714: invalid combination of STARTUP options

If you startup the database by specifying a init.ora file, the spile parameter is displayed as empty:

NAME                              TYPE        VALUE
--------------------------------- ----------- ------------------------------
spfile                            string

3. Specifying spfile:

In this case, you must startup with an init.ora file in which you only specify the spfile parameter full name:

spfile=d:\ora901\database\spfiletest.ora

SQL> startup pfile=d:\ora901\database\inittest.ora
ORACLE instance started.

Total System Global Area  122449892 bytes
Fixed Size                   282596 bytes
Variable Size              88080384 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

Now, you can check the usage of an spfile using the following command in SQL*Plus:

SQL> show parameter spfile

NAME                           TYPE        VALUE
------------------------------ ----------- ---------------------------------
spfile                         string      d:\ora901\database\spfiletest.ora

You can modify the content of the specified spfile as follows:

SQL>alter system set processes = 200 scope=spfile;

System altered.

SQL> select name, value from v$spparameter
     where name = 'processes';

NAME            VALUE
--------------- -----
processes       200