How to Fix ORA-19687: SPFILE Not Found in Backup Set

If you’re working with Oracle RMAN and trying to restore the SPFILE, running into the ORA-19687 error can be frustrating. This error message indicates that the backup set you’re trying to use doesn’t contain the SPFILE, which is critical for database startup and recovery operations.

Let’s understand what causes this issue and how to fix it step-by-step.

What is ORA-19687?

The error message looks like this when executing a restore command in RMAN:

RMAN> restore spfile from '/backup/rman/xyz123ab_1_1';

...

ORA-19687: SPFILE not found in backup set

This clearly means that the backup piece you’re referencing does not include the SPFILE. RMAN cannot restore the SPFILE unless the specific backup set contains it.

Root Cause

This issue typically happens when:

• The backup set you selected was created without including the SPFILE.
• You’re pointing to the wrong backup piece.
• The SPFILE backup might be stored in a different backup set than the datafiles or control files.

Step-by-Step Solution

1. Start the Database with PFILE

Since SPFILE is missing or unavailable, start the instance using a text-based PFILE:

RMAN> shutdown immediate;
RMAN> startup mount pfile='/u01/app/oracle/dbs/initMYDB.ora';

Replace the path and filename with the location of your existing PFILE (if available).

2. Identify Backup Set Containing SPFILE

Now check which backup sets include the SPFILE using the following RMAN command:

RMAN> list backup of spfile;

This will display all backup pieces that have a copy of the SPFILE. Make note of the correct backup piece name.

3. Restore the SPFILE

Use the correct backup piece to restore the SPFILE:

RMAN> restore spfile from '/u01/backup/abc45dfg_1_1';

Ensure you’re using the backup piece identified in the previous step. If you’re unsure about its contents, you can run:

RMAN> list backup of spfile summary;

Or use tools like RMAN> report schema; to help guide the recovery process.

Bonus Tips

• Automate SPFILE backup: Always include SPFILE in regular backups using CONFIGURE BACKUP OPTIMIZATION ON and BACKUP DATABASE PLUS ARCHIVELOG INCLUDE SPFILE;.
• Maintain both SPFILE and PFILE: Keep a copy of the PFILE as a backup just in case SPFILE gets corrupted or deleted.
• Use Recovery Catalog: Managing your backups via a recovery catalog can simplify backup management and make identifying SPFILE-containing backups easier.

Conclusion

The ORA-19687: SPFILE not found in backup set is a common issue when working with RMAN restores. The key is to correctly identify the backup set that actually includes the SPFILE and then restore from it. Always verify your backup configurations and maintain proper backups of both PFILE and SPFILE for smooth recovery operations.

Stay prepared, stay backed up!