When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.
SPFILEs provide the following advantages over PFILEs:
- An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
- Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
- Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
- Easy to find - stored in a central location
What is the difference between a PFILE and SPFILE:
A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.
An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.
How will I know if my database is using a PFILE or SPFILE:
Execute the following query to see if your database was started with a PFILE or SPFILE:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the "value" column is NULL for all parameters, you are using a PFILE.
Starting a database with a PFILE or SPFILE:
continue with this URL http://www.orafaq.com/node/5
