How to drop diskgroup giving ORA-15027

Problem

While removing all the contents of a database using ASM you may encounter error from acmca or sqlplus:

ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount

Cause

The reason behind is the parameter file residing in the ASM disk:

Solution

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME                           TYPE     TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ------ ---------- ---------- ----------------------- --------------
DATA                           EXTERN    7168000    7167858                       0        7167858
FRA                            EXTERN     512000     511222                       0         511222

SQL>


SQL> column HEADER_STATUS format a20
SQL> set pages 100
SQL> col PATH format a25


SQL> r
1* SELECT name, header_status, path FROM V$ASM_DISK

NAME                           HEADER_STATUS        PATH
------------------------------ -------------------- -------------------------
DATA_0004                      MEMBER               /dev/rdisk/disk38
DATA_0005                      MEMBER               /dev/rdisk/disk39
DATA_0006                      MEMBER               /dev/rdisk/disk40
DATA_0007                      MEMBER               /dev/rdisk/disk41
DATA_0008                      MEMBER               /dev/rdisk/disk42
DATA_0009                      MEMBER               /dev/rdisk/disk43
DATA_0010                      MEMBER               /dev/rdisk/disk44
DATA_0011                      MEMBER               /dev/rdisk/disk45
DATA_0012                      MEMBER               /dev/rdisk/disk46
DATA_0000                      MEMBER               /dev/rdisk/disk60
DATA_0001                      MEMBER               /dev/rdisk/disk61
DATA_0002                      MEMBER               /dev/rdisk/disk62
DATA_0003                      MEMBER               /dev/rdisk/disk63
DATA_0013                      MEMBER               /dev/rdisk/disk64
FRA_0000                       MEMBER               /dev/rdisk/disk65

15 rows selected.






SQL>
SQL>
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount

SQL>
SQL> DROP DISKGROUP data FORCE INCLUDING CONTENTS;
DROP DISKGROUP data FORCE INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15230: diskgroup 'DATA' does not require the FORCE option


SQL> create pfile='/tmp/init.ora' from spfile;

File created.

SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup pfile='/tmp/init.ora';
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size                  2169104 bytes
Variable Size             256595696 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted


SQL>  DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATA" does not exist or is not mounted


SQL> DROP DISKGROUP data FORCE INCLUDING CONTENTS;

Diskgroup dropped.

SQL>