Oracle DB : What is difference between spfile and init.ora file?

Question

I have tried to understand through oracle docs but could not get it properly Any body please explain. What is difference between spfile and init.ora file?

Thanks.

Answer 1

init.ora

• init.ora or the pfile(parameter file) is a simple text file which can be updated by a standard editor like vi which contains the various initialization parameters used while starting a database instance. Here is a sample init.ora file:

db_cache_size = 176000M

db_2k_cache_size = 2048M

db_16k_cache_size = 99000M

db_keep_cache_size = 600000M

db_recycle_cache_size = 64000M

shared_pool_size = 14000M

• We need to restart the database after editing the init.ora using a text editor so that the changes can come into effect.
• If we want to start a remote db we need a local pfile.
• RMAN(Recovery manager) which helps in data backup recovery process does not keep the backup of pfile or init.ora file.

spfile

• The Oracle spfile is a binary representation of the text based init.ora file or pfile( parameter file) which contains the various initialization parameters used while starting a database instance.Came into existence in and after Oracle 9i.

• We do not edit this file using an editor as it is a binary file and may get corrupted and which may lead to your database instance not starting rather it is altered by using the alter system query. An example of editing a spfile :
  ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

• The changes come into effect after running the Alter system query and we do not need to restart the database.

• We do not need a local copy in case of spfile if we need to start a remote database.

• As this file is maintained by the server the parameters are validated before they are accepted.
• RMAN keeps the backup of spfile.

• By default our database uses pfile to start we can change that and create a spfile from a pfile or init.ora file by using the following query and we don't even need to restart the db.

  sqlplus / as sydba; CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora;

Answer 2

The difference between init.ora and spfile.

• init.ora and spfile both contains Database parameters info. With spfile you can set Oracle database settings with the ALTER SYSTEM command that is used in sqlplus to add/Modify/delete settings. However for init.ora you edit it as a text file because init.ora saved in the format of ASCII.
• init.ora info is read by oracle engine at the time of database instance . in spfile modifications can applicable without restarting oracle database.
• When an Oracle database is started, the process will always use spfile.ora, if it exists. If the spfile.ora is not found, the init.ora would be used on startup.