The Application DBA  

Database Parameter File

Single Instance Database Without ASM

Either an init.ora file or an spfile can be used for the Oracle database.

If an init.ora parameter file is used then it should be named init<$ORACLE_SID>.ora and located in the $ORACLE_BASE/admin/<$ORACLE_SID>/pfile directory and symbolically linked back to the $ORACLE_HOME/dbs directory.

If an spfile is used then it should be created from the init.ora file and then moved to the $ORACLE_BASE/admin/<$ORACLE_SID>/pfile directory. A one line init.ora file should then be created and placed in the $ORACLE_HOME/dbs directory. Note that this file cannot be linked. Oracle expects it to reside in the $ORACLE_HOME/dbs directory and will not follow a symbolic link. The file should contain a single line of the following format:

spfile=$ORACLE_BASE/admin/DBSID/pfile/spfileDBSID.ora

The following is an example:

initDBSID.ora:

spfile=/oracle/admin/DBSID/pfile/spfileDBSID.ora
# Override parameters go below

NOTE: When using an SPFILE it is possible to get into the situation where the database won't start due to an invalid parameter, and since the database won't start it is not possible to fix the SPFILE. In this case the offending parameter can be overridden in the single line init.ora file above by adding the parameter with the correct value after the "spfile" designation. Oracle will always use the last specified value for any parameter that it finds. Now the database can be started and the SPFILE can be fixed.

ASM and RAC

If using ASM then the SPFILE can be stored in ASM rather than on a file system. If using RAC then the SPFILE must be stored in a shared location so ASM is a logical choice. When storing the SPFILE in ASM, or any location other than $ORACLE_HOME/dbs then you must use the spfile parameter in an init.ora file as described in the previous section.

Controlfile Autobackup

It is strongly recommended to use RMAN's controlfile autobackup feature. The autobackup feature will automatically backup the controlfile and SPFILE when enabled. This makes it much simpler to restore whether restoring the database or instantiating a standby.

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.