SPFILE

I have Book of Thomas Kyte Effective Oracle by Design. He had write good things on SPFILE which I want to share with you.

The Problem with PFILEs
PFILE resided on the database server. To start and shutdown the server everyone need to access the database server physically to ensure the correct init.ora file being used.

Also, if you used system management tools OEM, OMS to start and stop the database, the PFILEs needed to be physically wherever the system management tools was, and this generally, was not the machine where your database ran. In short you great confusion and “missing parameters” from time to time, If you simply started the database from a different machine, an entirely different set of parameters could be used, since a different PFILE would be used.

Another problem was that changes where made to the instance via the ALTER SYSTEM command, you would need to remember to update each and every copy of the PFILE out there in order to have the new setting take effect after next restart of the database. If you forgot, the system would run file until someone restarted the database, and then you would discover that the new settings you made were no longer there.

How SPFILEs Work
As SPFILE is stored in binary format on the server itself. You no longer need to have a local copy to start the database remotely. The binary file format serves two purposes:
• Change made via the ALTER SYSTEM command may now persist across server restarts. If is no longer necessary to update the init.ora text file with every little change. You can use SQL to maintain your parameter settings.
• It prevents us from being tempted to edit the file using any sort of editor. If provides SQL to manipulate it and prohibits us from editing it directly. However, if you do want to edit the file, there are techniques you can employ to accomplish that.

How to force database to use SPFILE.
If you are using oracle 9i or later by default server used SPFILE but if you migrated you can do it with.

Create spfile from pfile=’location of your pfile’;

Next time you start your database it will use SPFILE.

You can create textual version of this binary SPFILE using below command.

Create pfile=’path where you want to be created’ from spfile=’path of spfile’;

Now if you want to force database to use PFILE instead of SPFILE.
Start your database with below command instead of just startup.

Startup pfile=’location of your pfile’;

PFILE is not still obsolete?

Using a PFILE meant that if you started a database on Unix from your windows pc, the parameter file for the database need to be accessible on the PC. For some this was not a big deal. They would simply telnet into the database server itself and start it up or shut it down. The drawback to this was that everyone needed to physically access the server in order to ensure the correct init.ora file was being used to start the database (so everyone needed the ability to physically access the server)