在ASM下建立spfile 出現DB_UNKOWN

文章來源於metalink。

出現DB_UNKNOWN的真實原因就在於在diskgroup上建立spfile的時候，沒有開啟rdbms(在此區別asm)資料庫。其實，spfile檔案出現在db_unkown裡還是出現在/裡，都不會影響資料庫的啟動和執行。因此不會引起問題，所以不調整spfile讓他呆在db_unknown裡也沒關係。

如下描述：

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7
This problem can occur on any platform.

Symptoms

On 10.2.0.1 in Production:

-- Suppose that the database instance name is "osmdr"

-- In the database instance

When trying to create a new spfile from a pfile under sqlplus

SQL> create SPFILE='+DATA/osmdr/spfileosmdr.ora' from pfile='/home/oracle/osmdr.init';
File created.


SQL>create SPFILE='+DATA/osmdr/spfileosmdr.ora' from PFILE='/home/oracle/osmdr.init'; File created


Under ASM , the spileosmdr.ora is link to the wrong location under ASM


ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfileosmdr.ora =>
+DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.613162051
ASMCMD>

Q. Why it will create +DATA/DB_UNKNOW instead of +DATA/osmdr ?

The same question can be like that

Q. Why does the spfile get created in the directory DB_UNKNOWN?


IT'S EXPECTED BEHAVIOR

Cause

-- Considering the TESTCASE hereunder

The string "DB_UNKNOWN/PARAMETERFILE/SPFILE" is a generic system tag for the proxied spfile creation. This will be used in case the database instance has no open client session to the ASM instance at the time of the 'create spfile' command. That is, the ASM instance is not aware of the db name and therefore uses "DB_UNKNOWN".

Please see the TESTCASE section above that demonstrates two scenarios; [

【A】where the DB_UNKNOWN
directory structure gets created

【B】where the PARAMETERFILE
directory structure gets created


Solution

TESTCASE
[A] No open database connection to the ASM instance

-- In the ASM instance


SQL> select * from v$asm_client;

no rows selected

-- In the database instance


SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string V1020

SQL> select * from v$asm_client;

no rows selected

SQL> create spfile='+DG1' from pfile;

File created.

-- In asmcmd
-- The DB_UNKNOWN directory structure gets created

ASMCMD> pwd
+dg1

ASMCMD> ls
DB_UNKNOWN/
V1020/

ASMCMD> ls DB_UNKNOWN/
PARAMETERFILE/

ASMCMD> ls DB_UNKNOWN/PARAMETERFILE/
SPFILE.259.613339345

或者 在直接建立spfile.ora，這個檔案會自動指向/DB_UNKNOWN/
PARAMETERFILE /spfile****檔案。如下

SQL> create spfile='+DG1/V1020/spfileV1020.ora' from pfile;

ASMCMD> pwd
V1020

ASMCMD> ls -lta
Type           Redund  Striped  Time             Sys  Name
                                                 Y    TEMPFILE/
                                                 Y    ONLINELOG/
                                                 Y    DATAFILE/
                                                 Y    CONTROLFILE/
                                                 N    spfileV1020.ora => +DG1/DB_UNKNOWN/PARAMETERFILE/spfile.259.613339813

-- Remove the parameter file from the ASM diskgroup

ASMCMD> rm DB_UNKNOWN/PARAMETERFILE/SPFILE.259.613339345

-- Now the DB_UNKNOWN directory structure gets automatically removed (刪除檔案的同時，目錄也被刪除了)


[B]open database connection to the ASM instance

-- In the database instance
-- Open a connection to the ASM instance by accessing a datafile in an ASM diskgroup
-- In this example, a tablespace with a datafile in an ASM diskgroup is onlined
(其實不用特意去訪問資料檔案來達到連線的效果，好像rdbms應該可以建立到ASM的session連線，可以從V$asm_client檢視可以看到)

SQL> alter tablespace ts1 online;

Tablespace altered.

SQL> select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAM DB_NAME STATUS SOFTWARE
------------ ------------ -------- ------------ -
1 +ASM V1020 CONNECTED 10.2.0.3.0 10.2.0.0.0

-- In the ASM instance

SQL> select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAM DB_NAME STATUS SOFTW
------------ ------------ -------- -----------
1 V1020 V1020 CONNECTED 10.2.0.3.0 10.2.0.1.0

-- In the database instance

SQL> create spfile='+DG1' from pfile;

File created.

-- In asmcmd

ASMCMD> pwd
+dg1
ASMCMD> ls
V1020/
ASMCMD> ls V1020/
DATAFILE/
PARAMETERFILE/
ASMCMD> ls V1020/PARAMETERFILE/
spfile.259.613339813

-- Result:
- The spfile is located in the directory //PARAMETERFILE
- No DB_UNKNOWN directory structure got created

或者 在直接建立spfile.ora，這個檔案會自動指向//PARAMETERFILE /spfile****檔案。

SQL> create spfile='+DG1/V1020/spfileV1020.ora' from pfile;

 

ASMCMD> pwd
+dg1

ASMCMD> ls
V1020/

ASMCMD> ls V1020/
DATAFILE/
PARAMETERFILE/

spfileV1020.ora'

ASMCMD> ls -lta
Type           Redund  Striped  Time             Sys  Name
                                                 Y    TEMPFILE/
                                                 Y    PARAMETERFILE/
                                                 Y    ONLINELOG/
                                                 Y    DATAFILE/
                                                 Y    CONTROLFILE/
                                                 N    spfileV1020.ora => +DG1/LV1020/PARAMETERFILE/spfile.259.613339813
ASMCMD> ls V1020/PARAMETERFILE/
spfile.259.613339813

ASMCMD> ls V1020/PARAMETERFILE/
spfile.259.613339813

最後別忘了在$ORACLE_HOME/dbs/iniit.ora檔案裡寫入'spfile=''+DG1/....',讓rdbms能找到spfile檔案就行了【上述的TESTCASE A 和TASTCASE B 都適用】，這樣以後rdbms就可以啟動了。