[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 managedBut 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 managedBut 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 rhel6m2Dismount 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 MOUNTEDAs 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 rhel6m2This 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 managedA 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 ---------- 10Remove 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 managedDropping 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 managedAs 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 managedAs there are no dependencies the delete command gets executed without any errors
[grid@rhel6m1 ~]$ crsctl delete resource ora.TEST.dgOnce 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