Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File

Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File [ID 162491.1]

--------------------------------------------------------------------------------

Modified 10-DEC-2008 Type BULLETIN Status PUBLISHED



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


SCOPE & APPLICATION
Intended audience is DBAs.


Spfile and Init.ora Parameter File Startup of an Oracle9i Instance:
===================================================================

Before Oracle9i, Oracle instances were always started using a text file called
an init.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 accross all startup/shutdown procedures.

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

An Oracle9i instance may be started by:

- default spfile --> spfile.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 Oracle9i database can be started:


1. Specifying neither file:
========================

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

Oracle first searches if the spfile.ora exists. If it does not, Oracle
searchs for the spfile.ora parameter file. If neither spfile.ora nor
spfile.ora exist, Oracle will use the init.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 am 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



References:
===========

Note:152726.1 Oracle9i: Changes Made to Init.ora are not Applied at Instance
Startup
Note:146577.1 How to change static parameters through SPFILE parameter
Note:139646.1 Same Parameters Appear in Init.ora and in Embedded spfile.ora
Note:138129.1 What Happens and What to Do when SPFILE has been Manually
Modified