Skip to content

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.

[Post Views: 3892]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

3 Comments

  1. J J

    Very nice and informative blog. I have never seen such a good collection of articles

    http://techgoeasy.com is also very site..Please do check

  2. Vinay Vinay

    What is the reason you have not enabled copy option for your pages? There is no point visiting your pages, if the SQL’s cannot be copied to clipboard. If you are expecting people to type it again and use it, then this is not a good option in today’s IT world.

    • Brijesh Gogia Brijesh Gogia

      Thanks for the feedback. I have excluded this post as well as any other post which has useful codes for easy copying.

Leave a Reply

Required fields are marked *