When the SPFILE is Missing

  • by
When you met the following situation, you are about to restore the spfile for your database.
SQL> startup open;

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


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:
SQL> create spfile='+data/compdb/spfileprimdb.ora' from pfile='/tmp/initprimdb-20120228.ora';
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.
  2. 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.
  3. Force the database to nomount without using any spfile.
  4. [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

  5. Try to restore the spfile using FROM AUTOBACKUP clause.
  6. 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.
  7. Indicate the acutual file of autobackup to RMAN
  8. 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

  9. Bounce the database to mount state for recovering database.
  10. 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

  11. Recover database.
  12. 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

  13. Open database with resetlogs.
  14. 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:

Leave a Reply

Your email address will not be published. Required fields are marked *