Friday, August 1, 2014

ASM Disk Group Dependency Exists Even After Being Dropped

Database using ASM has storage has dependency on the ASM disk groups.
[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed
But it seems even if there's no real dependency exists between the database and ASM disk group, the ASM disk group is listed as a resource. By "no real dependency" it's meant that there's no database objects currently existing on disk group in concern. Following is the steps of the test case (tested on 11.2.0.3).
Create a new disk group and mount it on all nodes
SQL> create diskgroup test external redundancy disk '/dev/sdg1';

Diskgroup created.

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
CLUSTER_DG                     MOUNTED
DATA                           MOUNTED
FLASH                          MOUNTED
TEST                           MOUNTED
SQL> select name,state from v$asm_diskgroup;
As there are no database objects it's still not part of the DB configuration
[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed
But listed a resources
[oracle@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.TEST.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m2
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m2
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
Dismount from all but one node and drop the disk group from the node it's mounted
SQL> alter diskgroup test dismount;
SQL> drop diskgroup test;
SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
CLUSTER_DG                     MOUNTED
DATA                           MOUNTED
FLASH                          MOUNTED
As seen from the above output disk group no longer exists and also is not listed on the resource list
[grid@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m2
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m2
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
This would be the expected behavior. Drop the disk group and it should be removed from the cluster. Next is the oddity.



Create the disk group as before and create some database objects. In this a tablespace is created
SQL> create diskgroup test external redundancy disk '/dev/sdg1';
SQL> create tablespace testtbs datafile '+test(datafile)' SIZE 10M;
SQL> ALTER USER ASANGA QUOTA UNLIMITED ON TESTTBS;
Creating the tablespace makes the disk group part of the configuration.
[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH,TEST
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed
A table is created on the tablespace created earlier and few rows inserted to simulate some DB activity.
SQL> create table test (a number) tablespace testtbs;
SQL> insert into test values(10);
SQL> commit;
SQL> select * from test;

         A
----------
        10
Remove the database objects and drop the disk group

SQL> drop table test purge;
SQL> alter user asanga quota 0 on testtbs;
SQL> drop tablespace testtbs including contents and datafiles;
Even though there are no database objects on this disk group it is still part of the DB configuration
[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH,TEST
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed
Dropping the disk group doesn't make any difference either
SQL> drop diskgroup test;

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
CLUSTER_DG                     MOUNTED
DATA                           MOUNTED
FLASH                          MOUNTED

[grid@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.TEST.dg
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m2
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m2
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2


[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH,TEST
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed
As seen from above outputs even though there's no disk group exists it's listed as part of the database configuration and listed as a resource. Trying to drop the resource result in following error.
[grid@rhel6m1 ~]$ crsctl delete resource ora.TEST.dg
CRS-2730: Resource 'ora.std11g2.db' depends on resource 'ora.TEST.dg'
CRS-4000: Command Delete failed, or completed with errors.
Solution is to remove database dependency on the disk group.
[oracle@rhel6m1 ~]$ srvctl modify database -d std11g2 -a "DATA,FLASH"
After which the disk group is not part of the DB configuration
[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed
As there are no dependencies the delete command gets executed without any errors
[grid@rhel6m1 ~]$ crsctl delete resource ora.TEST.dg
Once deleted the disk group is no longer listed as a resource
[grid@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m1
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2