:

SPFILE, INIT File & PFILE

All of the above are common names which all refer to a configuration file used by the Oracle database to set values for parameters used to start and manage the database instance. Let’s look at them in a little more detail and compare them.

Server Parameter File – SPFILE

The Oracle Server Parameter File (SPFILE) is stored locally to the database, on the same server. It is a binary file, which means that it cannot be directly edited with a text editor. You have to modify it using SQL commands from within the database, such as ALTER SYSTEM SET ….

Default Location for Parameter File

The default location for pfiles and spfiles is the database directory underneath the Oracle Home, E.g. D:\Oracle\product\11.2.0\dbhome_1\database. This is also the default location for the password file. (Oracle’s preference in terms of looking for a pfile or spfile is the spfile first and the pfile second, so if you have both in the same location the SPFILE will be read first).

Have you Clustered…?

Now, if you have installed your Oracle software onto a local drive on your server, say the D drive for example, and you have placed all the datafiles on clustered LUNS, you may have a set-up which allows you to failover your database to another server. In the event that you do want to switch your instance to be running from a different server, you would manually have to copy the password file and parameter file over to the other server. Or, you could make sure that they are kept up-to-date which is a hassle and prone to error. Also, if your database was to failover to the other server in the middle of the night and the parameter files were out of sync you could have a database which is running with old settings.

The Solution to your PFile Problems…

To get around this problem, you can specify in the Windows registry a non default location for both the parameter file and the password file. Do the following:

    • START -> Run -> regedit = new window opens
    • Navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME

Where HOME0 may be HOME1 or KeyHome10g_1 or have 11g in the name. You will see a list of parameters on the right hand side of the screen, most of which are in the format ORA_<SID>_<Parameter>
< SID> is the database name < Parameter> is the parameter, for example AUTOSTART

You can add two new registry entries here, one for the pfile and one for the password file. You may wish to take a backup of the registry first just in case you break something!

  • ORA_<SID>_PFILE – specifies the parameter file location
  • ORA_<SID>_PWFILE – specifies the password file location

After you restart the Oracle service these settings will be picked up from the registry and it means that you can have your files in non default Oracle locations.

If you are using an spfile and not a pfile, you will still need to have a file named INIT<SID>.ora in the default location which points to the non-default location. So the file contains a line something like spfile=’F:\Oracle\Admin\DB\pfile\spfile<SID>.ora’

Like it, share it...

Category: Windows


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *