database spfile created in wrong place under ASM

SYMPTOMS

DBA is trying to create a new spfile from a pfile as following :

SQL> create SPFILE='+DATAC1/TESTDB/' from pfile='/home/oracle/testdb.pfile';
File created.

Under ASM , the spile is link to the wrong location under ASM:

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

OR

ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfile =>
+DATAC1/ASM/PARAMETERFILE/spfile.4186.1126528877
ASMCMD>

CAUSE

This is because 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” or ‘ASM’.

SOLUTION

When database in OPEN or MOUNT status, make sure  there is at least one connection to the ASM instance. 

SQL>select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAME
------------ ----------------------------------------------------------------
DB_NAME  CLUSTER_NAME                    STATUS
-------- ------------------------------- ------------
SOFTWARE_VERSION
------------------------------------------------------------
COMPATIBLE_VERSION                                               CON_ID
------------------------------------------------------------ ----------
           0 +ASM1
TESTDB_ usa1234clu07cb8                 CONNECTED
19.0.0.0.0
19.0.0.0.0                                                            0

— Create spfile from a pfile

SQL>  create spfile='+datac1' from pfile='/home/oracle/testdb.pfile';

File created.

— check spfile is created in the right directory now

ASMCMD> pwd
+datac1
ASMCMD> cd TESTDB/
ASMCMD> ls PARAMETERFILE/
spfile.4186.1126530135

— configure TESTDB database with new spfile

$ srvctl modify  database -d TESTDB -spfile  '+DATAC1/TESTDB/PARAMETERFILE/spfile.4186.1126530135'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: