Frequently used SQL Queries for ASM

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.

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
• Details of running concurrent requests in Oracle EBS - May 26, 2020
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020