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”
- Shutdown the DB
- Startup RMAN dummy instance
- Restore the spfile to pfile
- Shutdown the Instance
- Adjust the pfile
- 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