Few frequently used SQL queries related to 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.
