Unable to drop ASM diskgroup getting ORA-15027

Hello,

Recently I came across such situations that I was unable to drop the ASM diskgroup. I was getting  ORA-15027 . Later I realized that the spfile fot the ASM instance is available in the diskgroup.

To solve this, first we need to move the spfile to different location. Once that is done, we can delete the desired diskgroup.

State	Type	Rebal	AU	Total_MB	Free_MB	Name
MOUNTED	EXTERN	N	1048576	1488042	1487967	DATA

 Here is the desired, diskgroup to be dropped.

ASMCMD> dropdg -r DATA
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.840344755
ASMCMD> spmove '+DATA/asm/asmparameterfile/registry.253.840344755' '/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora';
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/asm/asmparameterfile/registry.253.840344755' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora;
ASMCMD> exit
-bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 08:31:24 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> shut immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora/app/oracle/product/11.2.0
                                                 .3/grid/dbs/spfile+ASM.ora;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
-bash-3.2$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   1488042  1487967                0         1487967              0             N  DATA/
ASMCMD> spget
/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora;
ASMCMD> dropdg -r DATA
ASMCMD> lsdg
ASMCMD> exit


 Good luck !!!!!!!!!!!!!!!!!!!!

Move asm spfile from file system to asm filesystem

Hello ,

Here is the below step to copy the asm spfile from non asm filesystem to asm filesystem.

1.) Make sure you have pfile in grid home

bash-3.2$ ls -ltr
total 24
-rw-r--r-- 1 oracle oinst 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinst 1536 Mar 29 05:06 orapw+ASM
-rw-rw---- 1 oracle oinst 1200 Mar 29 08:31 ab_+ASM.dat
-rw-rw---- 1 oracle oinst 1544 Mar 29 09:02 hc_+ASM.dat
-rw-r--r-- 1 oracle oinst  359 Mar 29 09:06 init+ASM.ora

bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 09:11:12 2014

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

Connected to an idle instance.

SQL> startup pfile='init+ASM.ora'
ASM instance started

Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

2.) Create spfile from pfile

SQL> create spfile='+DATA' from pfile='init+ASM.ora';

File created.


SQL> shut immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 09:17:00 2014

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

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/registry.253.849870843


That's all.


Error you may encounter is :



ORA-15124: ASM file name contains an invalid alias name

Solution is ==> Don't try to give absolute name for spfile, just mention the diskgroup name as above example.