Systems Engineering and RDBMS

SPFile in Oracle

Posted by decipherinfosys on May 31, 2008

SPFile stands for Server Parameter File.  This was introduced in Oracle 9i and it is different than the normal init<sid>.ora file.  It is a binary file and not a text based file and is stored on the server rather than on the client machine.  The default location of this file is under $ORACLE_HOME/dbs directory.  It can be used to maintain the changes to the parameter values over the shutdown or startup of an instance and that is what makes it useful.  Basically any changes that we make to the configuration parameter using the ALTER SYSTEM statement get saved to the permanent configuration file.  If you want, you can also make dynamic changes to the parameters without making them a part of the SPFILE – you can use the SCOPE clause in order to make that happen – here is how the syntax looks like:

ALTER SYSTEM SET your_parameter_name_here = your_parameter_value_here

SCOPE = {MEMORY | SPFILE | BOTH};

You can also migrate the parameters from an existing init<sid>.ora file to SPFILE by using this command:

CREATE SPFILE FROM PFILE = ‘your_path_goes_here/init<sid>.ora’;

And do note that you should not directly update the SPFILE contents.  Use the ALTER SYSTEM command to do so.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: