When the SPFILE is Missing

When you met the following situation, you are about to restore the spfile for your database.

If you have a copy of pfile which is a text-based parameter file, it can be used to create spfile when the database is not open:

Pfile can make the situation easier to solve, but if you don't have a text-based pfile or the pfile deviates from the current setting, you can use RMAN to solve. Here are the steps to solve the problem by RMAN.

1. List all backups we have now.
RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

...
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    1.14G      DISK        00:02:35     12-DEC-12
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T085811
        Piece Name: +DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2937050    12-DEC-12 +DATA/primdb/datafile/system.900.801828739
  2       Full 2937050    12-DEC-12 +DATA/primdb/datafile/sysaux.898.797943569
  3       Full 2937050    12-DEC-12 +DATA/primdb/datafile/undotbs1.899.797943687
  4       Full 2937050    12-DEC-12 +DATA/primdb/datafile/users.897.801828959
  5       Full 2937050    12-DEC-12 +DATA/primdb/datafile/example.902.801828959
  6       Full 2937050    12-DEC-12 +DATA/primdb/datafile/undotbs2.901.797943737

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    18.64M     DISK        00:00:09     12-DEC-12
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T085811
        Piece Name: +DATA/primdb/backupset/2012_12_12/ncsnf0_tag20121212t085811_0.1080.801824455
  SPFILE Included: Modification time: 12-DEC-12
  SPFILE db_unique_name: PRIMDB
  Control File Included: Ckp SCN: 2937050      Ckp time: 12-DEC-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    18.64M     DISK        00:00:04     12-DEC-12
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T135622
        Piece Name: +DATA/primdb/autobackup/2012_12_12/s_801830930.1089.801842187
  SPFILE Included: Modification time: 12-DEC-12
  SPFILE db_unique_name: PRIMDB
  Control File Included: Ckp SCN: 2948291      Ckp time: 12-DEC-12

You can see that we have at least one copy of autobackup containing the spfile.

2. Force the database to nomount without using any spfile.
[oracle@primary01 ~]$ rman target /
...
connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/compdb/spfileprimdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/compdb/spfileprimdb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/compdb/spfileprimdb.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/compdb/spfileprimdb.ora
ORA-15173: entry 'spfileprimdb.ora' does not exist in directory 'compdb'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 96469384 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5455872 bytes
3. Try to restore the spfile using FROM AUTOBACKUP clause.
RMAN> run {
2> set dbid 841830157;
3> set controlfile autobackup format for device type disk to '%F';
4> restore spfile from autobackup;
5> }

executing command: SET DBID

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 14-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121214
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121213
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121212
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121211
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121210
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121209
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121208
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/14/2012 18:26:54
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Yes, sometimes RMAN cannot find out any autobackups, even you set dbid and configure the correct format of autobackup.

4. Indicate the acutual file of autobackup to RMAN

In this step, we restore the SPFILE from a backup piece. Since RMAN is able to recognize backup pieces, we will indicate the autobackup file explicitly to notify RMAN where and which file can be used.

RMAN> restore spfile from '+data/primdb/autobackup/2012_12_12/s_801830930.1089.801842187';

Starting restore at 14-DEC-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +data/primdb/autobackup/2012_12_12/s_801830930.1089.801842187
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-DEC-12
5. Bounce the database to mount state for recovering database.
RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     839282688 bytes

Fixed Size                     2217992 bytes
Variable Size                792725496 bytes
Database Buffers              41943040 bytes
Redo Buffers                   2396160 bytes
6. Recover database.
RMAN> recover database;

Starting recover at 14-DEC-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 90 is already on disk as file +DATA/primdb/archivelog/2012_12_14/thread_1_seq_90.1113.802006459
archived log for thread 2 with sequence 75 is already on disk as file +DATA/primdb/onlinelog/group_3.905.797943785
archived log file name=+DATA/primdb/archivelog/2012_12_14/thread_1_seq_90.1113.802006459 thread=1 sequence=90
archived log file name=+DATA/primdb/onlinelog/group_3.905.797943785 thread=2 sequence=75
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-DEC-12
7. Open database with resetlogs.
RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog
database opened

More related posts that you may be interested in:

• When the SPFILE is Missing
• When the Controlfile is Missing
• When Some of the Archived Logs are Missing
• When Several Datafiles Are Missing - Restore From Local Backups
• When Several Datafiles Are Missing - Restore From the Standby Database
• When Almost Everything of a Database is Missing
• How Will the Database React to Missing Tempfiles