Frequently used SQL Queries for ASM

1) Create Disk Group:
Create Disk groups using the CREATE DISKGROUP statement and specify the level of redundancy.
Disk group redundancy types:-
NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY –  Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware RAID or mirroring.

Example 1 : External Redundancy

SQL> create diskgroup DATA external redundancy disk ‘/dev/oracleasm/disks/DISK1′ name DATA_1;

Example 2 : Normal Redundancy

SQL> CREATE DISKGROUP data NORMAL REDUNDANCY
 FAILGROUP failure_group_1 DISK ‘/dev/oracleasm/disks/DISK2′ NAME DATA_2,’/dev/oracleasm/disks/DISK3′ NAME DATA_3,
 FAILGROUP failure_group_2 DISK ‘/dev/oracleasm/disks/DISK4′ NAME DATA_4,’/dev/oracleasm/disks/DISK5′ NAME DATA_5;

---

2) Drop Disk Group:
Using DROP DISKGROUP statement.

SQL> DROP DISKGROUP data INCLUDING CONTENTS;

---

3) Alter Disk Group:
Add or remove disks from disk groups Using ALTER DISKGROUP statement. You can also use wildcard “*” to reference disks.
   3.1) Add a disk.

 SQL> ALTER DISKGROUP data ADD DISK ”/dev/oracleasm/disks/DISK6′ ;

 3.2) Drop/remove a disk.

 SQL> ALTER DISKGROUP data DROP DISK DATA_5;

  3.3) Undrop disk
The UNDROP command used to undo only pending drop of disks. After you drop the disks you cannot revert.

SQL> ALTER DISKGROUP data UNDROP DISKS;

  3.4) Diskgroup Rebalance:
Disk groups can be rebalanced manually Using REBALANCE clause and you can modify the POWER clause default value.

SQL> ALTER DISKGROUP DATA REBALANCE POWER 8;

  3.5) MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are mounted at ASM instance startup and dismounted at shutdown.
Using MOUNT and DISMOUNT options you can make one or more Disk Groups available or unavailable.

 SQL> ALTER DISKGROUP data MOUNT;
 SQL> ALTER DISKGROUP data DISMOUNT;
 SQL> ALTER DISKGROUP ALL MOUNT;
 SQL> ALTER DISKGROUP ALL DISMOUNT;

 3.6) DiskGroup Check:
Use CHECK ALL to verify the internal consistency of disk group metadata and repair in case of any error.

SQL> ALTER DISKGROUP data CHECK ALL;

 3.7) DiskGroup resize:
Resize the one or all disks in the Diskgroup.
Resize all disks in a failure group.

SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 1024G;

Resize a specific disk.

SQL> ALTER DISKGROUP data RESIZE DISK DATA_0006 SIZE 100G;

Resize all disks in a disk group.

SQL> ALTER DISKGROUP data RESIZE ALL SIZE 100G;

---

4) To find ASM Diskgroup and Disks status

set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,VOTING_FILES from v$asm_diskgroup;
GROUP_NUMBER NAME      STATE   TYPE  TOTAL_MB FREE_MB V
------------ ------------------------------ ----------- ------ ---------- ---------- -
1           OCR_VOTE MOUNTED EXTERN 152999    152603   Y
2           DB_DATA  MOUNTED EXTERN 812000   810198  N

select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,VOTING_FILE,name,path from v$asm_disk;
 GROUP_NUMBER  DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE V     NAME              PATH
------------ ----------- ------- ------------ ------- -------- - -------------- ---------------------------------
1             0         CACHED MEMBER         ONLINE NORMAL Y OCR_VOTE_0 /dev/oracleasm/disks/OCR_VOTE01
2             3         CACHED MEMBER         ONLINE NORMAL N DB_DATA_3 /dev/oracleasm/disks/DB_DATA04
2             2         CACHED MEMBER         ONLINE NORMAL N DB_DATA_2 /dev/oracleasm/disks/DB_DATA03
2             1         CACHED MEMBER         ONLINE NORMAL N DB_DATA_1 /dev/oracleasm/disks/DB_DATA02
2             0         CACHED MEMBER         ONLINE NORMAL N DB_DATA_0 /dev/oracleasm/disks/DB_DATA01
6 rows selected.

Share: