By William Sescu

Have you ever noticed a DB_UNKNOWN directory in your ASM structure? It usually happens in combination with spfile creation in ASM, or with RMAN spfile restores to ASM.

The correct location is +DATA/<SID>/PARAMETERFILE/SPFILE.<#>.<#>, and an ASM alias from +DATA/<SID>/ pointing to it.

But sometimes, the spfile ends up in +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.<#>.<#>

Technically no issue. The spfile in the DB_UNKNOWN directory is perfectly ok and can be used. However, you might need to adjust your init<SID>.ora in case you have a config like the following

oracle@oel001:/u00/app/oracle/product/12.1.0.2/dbs/ [OCM121] cat initOCM121.ora
SPFILE='+data/DB_UNKNOWN/PARAMETERFILE/SPFILE.293.927371209'

Maybe you have a 4 node RAC, then you need to adjust it on every node. Maybe you have a cluster resource with a spfile entry. Then you need to adjust that one as well. And besides that, to what database does the DB_UNKNOWN belong to? Imagine you have 20 DB’s running and you need to find out, which database has something in the DB_UNKNOWN directory, in case there are more entries.

No … it is not a good situation. It has to be corrected. But how?

First of all, let’s create a situation that ends up with a DB_UNKNOWN directory.

It is quite easy to do. Typically, with spfile restores or with a “create spfile from pfile”

  1. Shutdown the DB
  2. Startup RMAN dummy instance
  3. Restore the spfile to pfile
  4. Shutdown the Instance
  5. Adjust the pfile
  6. Create the spfile from pfile while the DB is shutdown

Here is an example with 12cR1 (12.1.0.2). I am jumping directly to the RMAN restore, because RMAN dummy instance was already explained in http://dbi-services.com/blog/oracle-12c-when-the-rman-dummy-instance-does-not-start-up/

Ok. Let’s check the current location of the spfile of the cluster resource.

oracle@oel001:/home/oracle/ [OCM121] srvctl config database -d OCM121 | grep -i spfile
Spfile: +DATA/OCM121/spfileOCM121.ora

Now we can run the RMAN restore of the spfile to pfile. Restoring it to a pfile first has the advantage, that we can take a look at all settings and maybe adjust them, before we put it back into production.

run {
restore spfile to pfile '/tmp/initOCM121.ora' for db_unique_name='OCM121' from
'+fra/OCM121/AUTOBACKUP/2016_10_29/s_926511850.517.926511853';
}

Starting restore at 08-NOV-2016 11:01:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=364 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=12 device type=DISK
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra/OCM121/AUTOBACKUP/2016_10_29/s_926511850.517.926511853
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-NOV-2016 11:01:14

The pfile was successfully created. Now we can correct some settings in the pfile if we want and then create a spfile again.

oracle@oel001:/home/oracle/ [OCM121] ls -l /tmp/initOCM121.ora
-rw-r--r-- 1 oracle asmadmin 1777 Nov  8 11:01 /tmp/initOCM121.ora

Ok. Let’s create the new spfile while the DB is shutdown.

oracle@oel001:/home/oracle/ [OCM121] sqh

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 8 11:03:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile='+DATA' from pfile='/tmp/initOCM121.ora';

File created.

Oppssss … and now it happened. The directory DB_UNKNOWN is created. While the database is shutdown, Oracle does not know the DB_NAME and so, it has to create a placeholder directory to save the spfile.

ASMCMD> pwd
+data
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    CDB121/
                                        Y    DB_UNKNOWN/
                                        Y    OCM121/

ASMCMD> pwd
+data/DB_UNKNOWN/PARAMETERFILE
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 08 11:00:00  Y    SPFILE.293.927371209

However, this is not the configuration that we want. To correct it, cleanup the DB_UNKNOWN entries, and start your DB into the nomount state and execute then the spfile from pfile command again.

SQL> startup nomount pfile=/tmp/initOCM121.ora
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             436211328 bytes
Database Buffers         1157627904 bytes
Redo Buffers               13848576 bytes

SQL> create spfile='+DATA' from pfile='/tmp/initOCM121.ora';

File created.

And here we go. The spfile is the correct location.

ASMCMD> pwd
+data/OCM121/PARAMETERFILE
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 08 11:00:00  Y    spfile.291.927372029

The only thing missing is the ASM alias. That one has to be created manually afterwards.

ASMCMD> cd +data/OCM121
ASMCMD> mkalias +data/OCM121/PARAMETERFILE/spfile.291.927372029 spfileOCM121.ora
ASMCMD>

Conclusion

It makes a big difference if you create your spfile in the nomount state or while the database is shutdown. You might end up with a totally different directory structure in ASM. With 12.1.0.2 and 11.2.0.4 the nomount state is enough to end up in the correct location. In earlier versions you might need to startup mount to have the same effect.

Cheers,
William