Cannot drop the first disk group in ASM 11.2
I've been using ASM for long time in different configurations, with pfile, with spfile and in some configurations on shared raw device. Playing with ASM 11gR2 (Grid Infrastructure) it happens to me that I cannot neither drop neither dismount the first disk group.
The following error can be seen in asmca when try to dismount the first disk group:
Dismounting DiskGroup DATA failed with the following message:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount
If you go to sqlplus and try to drop the disk group, there is an error that the disk group is actually still active:
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
So, let's try to dismount it:
SQL> alter diskgroup all dismount;
alter diskgroup all dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount
Lets see why the active group is still active. Query of v$asm_file shows all files located at this disk group:
SQL> select group_number, file_number, bytes, type from v$asm_file; GROUP_NUMBER FILE_NUMBER BYTES TYPE
------------ ----------- ---------- --------------------
1 253 1536 ASMPARAMETERFILE
It seems that the ASM instance server parameter file is located in the first created disk group and thats why I cannot dismount it.
Quick check confirms that:
SQL> show parameter spfile
NAME TYPE VALUE
--------- ----------- ------------------------------
spfile string +DATA/asm/asmparameterfile/registry.253.731706127
srvctl command also can be used to confirm that:
[oracle@oradb /]$ srvctl config asm
...
Spfile: +DATA/asm/asmparameterfile/registry.253.731706127
...
I made an experiment and I just installed the grid infrastructure software. Then I went with asmca to create ASM instance. There is a button ASM Parameters and then Show Advanced Parameters. There is clearly written that the parameter file is located in $ORACLE_HOME/dbs/spfile+ASM.ora
It seems that after the installation the file is moved to the disk group. So if you really want to drop the disk group you should move the spfile from the disk group to the file system (or other disk group), then shutdown and startup the ASM instance and then drop the disk group. The traditional way would be to create pfile from spfile and then start the instance with pfile. There are couple of commands in ASM 11.2 available in asmcmd, one of them is spmove. It gives you the ability to move the spfile and automatically updates Grid Plug and Play (GPnP) profile. Another one is spget which gives you the current location of the ASM instance server parameter file.
Start the asmcmd utility and execute spget to get the current spfile, then spmove to move the spfile from the disk group to the file system:
[oracle@oradb ~]$ asmcmd
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.731706127
ASMCMD> spmove '+DATA/asm/asmparameterfile/registry.253.731706127' '/oracle/grid11g/dbs/spfile+ASM.ora';
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/asm/asmparameterfile/registry.253.731706127' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
/oracle/grid11g/dbs/spfile+ASM.ora;
I get the error that the file is still being used, but actually the file is copied to the file system and the profile is updated. Now shutdown and startup the ASM instance:
SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started
Total System Global Area 283930624 bytes
Fixed Size 2212656 bytes
Variable Size 256552144 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> show parameter spfile;
NAME TYPE VALUE
---------- ----------- ------------------------------
spfile string /oracle/grid11g/dbs/spfile+ASM.ora;
spget command also can be used to get the current spfile location from GPnP profile:
[oracle@oradb ~]$ asmcmd
ASMCMD> spget
/oracle/grid11g/dbs/spfile+ASM.ora;
So now we have the same behavior of ASM prior 11gR2, the server parameter file is in the file system and finally we can drop the disk group:
SQL> alter diskgroup data mount;
Diskgroup altered.
SQL> drop diskgroup data including contents;
Diskgroup dropped.
This behavior of placing the spfile of the ASM instance inside ASM disk group is now recommended by Oracle. This is accomplished by the ASM background process ASMB, which reads the spfile from the disk group and starts the instance itself. Whats more if you want this behavior or use the new spmove/spcopy/spset/spget/spbackup commands, the disk group attribute COMPATIBLE.ASM must be set to 11.2
At the end I found that there is also MOS note about this problem:
11gR2 (11.2.0.1) ORA-15027: active use of diskgroup precludes its dismount (With no database clients connected) [ID 1082876.1]