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.
- List all backups we have now.
- Force the database to nomount without using any spfile.
- Try to restore the spfile using FROM AUTOBACKUP clause.
- Indicate the acutual file of autobackup to RMAN
- Bounce the database to mount state for recovering database.
- Recover database.
- Open database with resetlogs.
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.
[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
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.
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
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
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
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