Pages

Saturday, March 26, 2016

How To Use Spfile For Database Startup

How To Use Spfile For Database Startup

The difference between pfile and spfile. Pfile is the text file -database initialization parameter file which is read at the time of database startup. The dynamic parameters can be changed using alter system command but they are not persistent and to make them permanent we have to make changes in init.ora file and then we  need to bounce the database for the effect to take place.

However spfile is the server parameter file which is a binary file, resides on the server and whatever changes we do using 'alter system' are persistent (depending on scope we define) and hence we don't have to manually edit the file and no need to bounce the db. Please note that it is a binary file and we can't edit it manually.

When the database is started it will read the initialization parameter file in the following order
from $ORACLE_HOME/dbs -unix or $ORACLE_HOME/database -windows

Note:No parameters needed to start database. Even if you don not specify the pfile or spfile database starts itself. It searches spfile or pfile in ORACLE_HOME/dbs on Unix and ORACLE_HOME/database folder in windows.

Order in which it check for the particular parameter file sequentially is as given bellow.
spfileSID.ora
spfile.ora
InitSID.ora
init.ora.

If parameter file is on non default location then database can be started with specifying the location.
e.g
startup pfile=d:\initTSH1.ora
startup (if you don't say pfile= then the database will start with the spfile)
If spfile is not present then create it with pfile

How to create spfile->
connect as sysdba
create spfile from pfile;

how to create pfile from spfile->
create pfile from spfile;

Note :You have correct permissions of this file in unix or else database won't start using spfile and it will give the following error, if pfile is not there.

LRM-00109: could not open parameter file '/u01/oradb/9.2.0/dbs/initSID.ora'
ORA-01078: failure in processing system parameters

No comments:

Post a Comment