How to Resolve SPFILE in DB_UNKNOWN

  • by

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.

1. Remove DB_UNKNOWN Directory

ASMCMD> rm -rf DB_UNKNOWN/
ASMCMD> exit

2. 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.

3. 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.

4. 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

5. 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 by a Backup Set.

6. 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

7. 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.

8. 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 by a Backup Set.

Leave a Reply

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