Differences between pfile and spfile

PROBLEM:

What is the differences between pfile and spfile?

SOLUTION:

Oracle provides two parameter files that you can use: PFILE or  SPFILE.

PFILE

1. The PFILE is a text-based file usually called “initSID.ora”, meaning the file will use the ORACLE_SID you defined when you created the database.  If your SID is called TESTDB, the resulting PFILE should be called initTESTDB.ora
2. Inside the PFILE there are database settings called parameters. These parameters help the Oracle programs know how to start.
3. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and where certain database files already exist.
4. As the PFILE is text based, one can edit it in an editor like notepad or vi.
5. Depending on which operating system you are running on, your PFILE is located by default in the ORACLE_HOME\database (usually the case on Windows) or ORACLE_HOME\dbs directory for most other platforms.

SPFILE

1. The SPFILE is different from the PFILE in that it can not be directly edited. This is because it has a header and footer that contains binary values.
2. Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the alter system command.
3. For using an SPFILE, you can reap great benefits. It can be backed up by RMAN (Oracle’s backup and recovery software) every time a change is made or when the database is backed up, which means it’s easier to recover.
4. SPFILES allow you to make dynamic changes to parameters that are persistent. For example
• Alter system set db_recovery_file_dest_size=10g;
5. If we were using SPFILES the parameter would keep the same value, even after a database restart. This means you only have to change the parameter value in one place, and that you can forget having to change it in the PFILE of the database.
6. One of the most important benefits of the SPFILE is that Oracle has introduced many automatic tuning features into the core of the database.  Without an SPFILE, Oracle can not autotune your database.
7. An SPFILE uses the same formatting for its file name as the PFILE, except the word spfile replaces init.  For instance, if your ORACLE_SID is TESTDB, the resulting spfile would be called spfileTESTDB.ora.

PFILE and SPFILE Backup

1. As a DBA the main thing you need to worry about with the SPFILE and PFILES are backing them up. You can use RMAN to backup an SPFILE, or back them up yourself.
2. PFILE is simply a text based file, which means you can copy it to another directory without affecting the Oracle instance.  This is the easiest way to backup a PFILE.
3. To back up an SPFILE, you will first want to convert it to a PFILE. This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory. You can do this with the following syntax
• create pfile from spfile;
4. In addition, you can back up the file directly to the preferred location with the command
• create pfile=/path/to/backup.ora from spfile;
5. If the time comes that you must put the SPFILE back into place, you can do so with this command
• create spfile from pfile=/path/to/backup.ora
6. You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters
• select name, value from v$parameter where name = 'control_files';

The Parameter File at Startup Time

Oracle prefers the use of an SPFILE to a PFILE.  When you startup your Oracle database, Oracle will scan the contents of your parameter directory ($ORACLE_HOME/database on Windows or the Linux directory name $ORACLE_HOME/dbs), searching in the following order:

1. spfileSID.ora
2. spfile.ora
3. initSID.ora
4. init.ora

If the directory contains none of the above, then the startup will fail.

Alternatively, you can tell Oracle where to find a PFILE if you store it in a different location.

startup pfile=/path/to/pfile/inittestdb.ora

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!