ORA-15027

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.

OR

 I had similar situation today, the freshly installed grid infra had the DATA disk group which held the spfile due to which it did not allow me to drop the disk group. I could drop the disk group after I deleted the spfile from there.

SQL> drop diskgroup data;
drop diskgroup data
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup “DATA” contains existing files

SQL> show parameter pfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/asm/asmparameterfile/reg
istry.253.826304061

SQL> create pfile from spfile;
File created.

SQL> shutdown immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown

SQL> startup pfile=’/orabin/oracle/11.2.0.3/grid/dbs/init+ASM.ora’
ASM instance started
Total System Global Area 284008448 bytes
Fixed Size 2158616 bytes
Variable Size 256684008 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> alter diskgroup data mount;
Diskgroup altered.

SQL> drop diskgroup data;
Diskgroup dropped.