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

***Checked for relevance on 30-Apr-2012*** 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 --&gt spfile.ora - default init.ora file --&gt init.ora - specified init.ora file - specified spfile The default initialization files are located as follows: - on Unix ---&gt $ORACLE_HOME/dbs - on WindowsNT/2000 ---&gt %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