Managing PFILE and SPFILE

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.

Remember that a 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.

To back up an SPFILE, you will first want to convert it to a PFILE. You can do this with the following syntax.

SQL> create pfile from spfile;

This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory.

Note that the SID in initSID.ora will be replaced with the SID of your database as defined during creation.

In addition, you can back up the file directly to the preferred location with the command:

SQL> create pfile=/path/to/backup.ora from spfile;

If the time comes that you must put the SPFILE back into place, you can do so with this command:

SQL> create spfile from pfile=/path/to/backup.ora

If your database is currently running using the SPFILE, be sure to shut down first so Oracle can replace the file. As your SPFILE is in use the entire time your database is running, you should never overwrite it during normal operations