SPFILE in DB_UNKNOWN
When I tried to restore SPFILE to the default location at nomount state, I found that SPFILE was restored to a DB_UNKNOWN in ASM. Is it a bug dedicated to Oracle 10g RAC (Bug 5370663)? No, I saw it too in 12c R1 RAC restoration.
Let's reproduce the problem. First, check what we have in the diskgroup ORA_DATA by ASMCMD.
[oracle@primary01 ~]$ srvctl disable database -d PRIMDB
[oracle@primary01 ~]$ export ORACLE_SID=+ASM1
[oracle@primary01 ~]$ asmcmd
ASMCMD> cd ORA_DATA/
ASMCMD> mkdir PRIMDB
ASMCMD> ls
PRIMDB/
ASMCMD> exit
There's nothing in diskgroup ORA_DATA.
Then we startup a dummy instance.
[oracle@primary01 ~]$ export ORACLE_SID=PRIMDB1
[oracle@primary01 ~]$ rman target /
...
connected to target database (not started)
RMAN> startup nomount force;
startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+ORA_DATA/PRIMDB/spfilePRIMDB.ora'
ORA-17503: ksfdopn:2 Failed to open file +ORA_DATA/PRIMDB/spfilePRIMDB.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +ORA_DATA/primdb/spfileprimdb.ora
ORA-17503: ksfdopn:2 Failed to open file +ORA_DATA/primdb/spfileprimdb.ora
ORA-15173: entry 'spfileprimdb.ora' does not exist in directory 'primdb'
ORA-06512: at line 4
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 58722596 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
We restored SPFILE from a backup piece. Normally, it will go to where it come from.
RMAN> restore spfile from '/backup/PRIMDB_685307556_0gu45h8j_1_1';
Starting restore at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: autobackup found: /backup/PRIMDB_685307556_0gu45h8j_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 15-JUN-19
Let's verify the result.
[oracle@primary01 ~]$ export ORACLE_SID=+ASM1
[oracle@primary01 ~]$ asmcmd
ASMCMD> cd ORA_DATA/PRIMDB
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N spfileprimdb.ora => +ORA_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.256.1011021817
ASMCMD> cd ..
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DB_UNKNOWN/
N PRIMDB/
We have a SPFILE which is an alias in the right place, but it points to a wrong directory DB_UNKNOWN. Although the wrong pointer is no harm to database running, it doesn't mean this is right, according to Oracle ASM directory structure guideline: Alias Oracle ASM File Name Forms.
Solution to DB_UNKNOWN
To tackle the problem, we need to let RMAN know the actual database unique name by mounting the database. In most cases, database unique name equals to database name. The following course of action to solve DB_UNKNOWN problem might be a little complicated, you have to be patient.
Remove DB_UNKNOWN Directory
ASMCMD> rm -rf DB_UNKNOWN/
ASMCMD> exit
Restore SPFILE to a Local Directory
[oracle@primary01 ~]$ export ORACLE_SID=PRIMDB1
[oracle@primary01 ~]$ rman target /
...
RMAN> restore spfile to '/tmp/spfilePRIMDB.ora' from '/backup/PRIMDB_685307556_0gu45h8j_1_1';
Starting restore at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /backup/PRIMDB_685307556_0gu45h8j_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 15-JUN-19
RMAN> exit
Recovery Manager complete.
Create a PFILE for Later Startup
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> create pfile='/tmp/initPRIMDB.ora' from spfile='/tmp/spfilePRIMDB.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup Nomount by PFILE
SQL> startup nomount pfile='/tmp/initPRIMDB.ora';
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 1222648 bytes
Variable Size 260048904 bytes
Database Buffers 658505728 bytes
Redo Buffers 2969600 bytes
Restore Controlfile for Mounting Database
RMAN> restore controlfile from '/backup/PRIMDB_685307556_0gu45h8j_1_1';
Starting restore at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=535 instance=PRIMDB1 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+ORA_DATA/primdb/control01.ctl
output filename=+ORA_DATA/primdb/control02.ctl
output filename=+ORA_DATA/primdb/control03.ctl
Finished restore at 15-JUN-19
RMAN> shutdown immediate;
Oracle instance shut down
It's worth noting that if the controlfile is restored as an oracle-managed file (OMF), the filename of current controlfile is different from the original one, you have to modify your PFILE before using it to mount the database. For a more complete example, please check the following post: How to Restore 12c RAC Database from a Backup Set.
Mount Database by PFILE
RMAN> startup mount pfile='/tmp/initPRIMDB.ora';
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 922746880 bytes
Fixed Size 1222648 bytes
Variable Size 260048904 bytes
Database Buffers 658505728 bytes
Redo Buffers 2969600 bytes
Restore SPFILE to ASM
This is the second time we restore SPFILE.
RMAN> restore spfile from '/backup/PRIMDB_685307556_0gu45h8j_1_1';
Starting restore at 15-JUN-19
Starting implicit crosscheck backup at 15-JUN-19
allocated channel: ORA_DISK_1
Crosschecked 9 objects
Finished implicit crosscheck backup at 15-JUN-19
Starting implicit crosscheck copy at 15-JUN-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-JUN-19
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /backup/PRIMDB_685307556_0gu45h8j_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 15-JUN-19
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
Once again, if your files are oracle-managed files, you have to create SPFILE from the previously created PFILE in order to align with current controlfile name before using it.
Check Restored SPFILE in ASM
[oracle@primary01 ~]$ export ORACLE_SID=+ASM1
[oracle@primary01 ~]$ asmcmd
ASMCMD> cd ORA_DATA/PRIMDB/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y PARAMETERFILE/
N control01.ctl => +ORA_DATA/PRIMDB/CONTROLFILE/Current.256.1011023197
N control02.ctl => +ORA_DATA/PRIMDB/CONTROLFILE/current.257.1011023197
N control03.ctl => +ORA_DATA/PRIMDB/CONTROLFILE/current.258.1011023199
N spfileprimdb.ora => +ORA_DATA/PRIMDB/PARAMETERFILE/spfile.259.1011023265
We're good. Just remember that the whole key to the problem is to startup mount the database by PFILE, this make RMAN know DB_UNIQUE_NAME, then SPFILE will be stored to the right place.
After you finish all restoration, just don't forget to enable database.
[oracle@primary01 ~]$ srvctl enable database -d PRIMDB
For full restoration of 10g RAC database, you may check this post: How to Restore 10g RAC Database from a Backup Set.