Tuesday, October 14, 2014

Adding disk to ASM disk group in oracle 11g

Following steps will help you to add disks to oracle ASM disk group.

Below query will identify disk groups information to add disks

SQL> SET LINESIZE  145
SQL> SET PAGESIZE  9999
SQL> SET VERIFY    off
SQL> COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
SQL> COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
SQL> COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
SQL> COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
SQL> COLUMN state                  FORMAT a11           HEAD 'State'
SQL> COLUMN type                   FORMAT a6            HEAD 'Type'
SQL> COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
SQL> COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
SQL> COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
SELECT
SQL>   2      name                                     group_name
  , sector_size                              sector_size
  3    4    , block_size                               block_size
  5    , allocation_unit_size                     allocation_unit_size
  6    , state                                    state
  7    , type                                     type
  8    , total_mb                                 total_mb
  9    , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
 10   11  FROM
 12      v$asm_diskgroup
 13  ORDER BY
 14      name
 15  /

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
MKRSTDATA                512   4,096    4,194,304 MOUNTED     EXTERN         819,168        726,848     88.73
MKRSTFRA                 512   4,096    4,194,304 MOUNTED     EXTERN         819,168            192       .02
MKRSTREDO                512   4,096    4,194,304 MOUNTED     EXTERN          20,464          4,344     21.23
OCRVOTEDG                512   4,096    4,194,304 MOUNTED     NORMAL          15,348          1,040      6.78

Find ASM disk group number with using below query

SQL> select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from  v$asm_diskgroup where name='MKRSTDATA';

GROUP_NUMBER NAME                           State       Total Size (MB)    FREE_MB
------------ ------------------------------ ----------- --------------- ----------
           2 MKRSTDATA                      MOUNTED             819,168      92320

Following query will help to find existing disk names and disk sizes for MKRSTDATA disk group

SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk where GROUP_NUMBER=2;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1

8 rows selected.

Find available disks information

ASMCMD [+] > lsdsk
Path
/dev/mapper/t1_data01p1
/dev/mapper/t1_data02p1
/dev/mapper/t1_data03p1
/dev/mapper/t1_data04p1
/dev/mapper/t1_data05p1
/dev/mapper/t1_data06p1
/dev/mapper/t1_data07p1
/dev/mapper/t1_redo01p1
/dev/mapper/t1_redo02p1
/dev/mapper/t1_redo03p1
/dev/mapper/t1_redo04p1
/dev/mapper/t1_vote1p1
/dev/mapper/t1_vote2p1
/dev/mapper/t1_vote3p1
/dev/mapper/t2_fra01p1
/dev/mapper/t2_fra02p1
/dev/mapper/t2_fra03p1
/dev/mapper/t2_fra04p1
/dev/mapper/t2_fra05p1
/dev/mapper/t2_fra06p1
/dev/mapper/t2_fra07p1
ASMCMD [+] >

So here will add disks on MKRSTDATA disk group and group number 2. The size of the each disks are 100GB. So here I am adding additional 500GB to MKRSTDATA disk group. Request unix team to provide 5*100GB disks. After getting disks from Unix team verify CANDIDATE disks information.

Following query will help us to find CANDIDATE disks,

SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data12p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data11p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data10p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data09p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t1_data08p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1

27 rows selected.

SQL> select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from  v$asm_diskgroup where name='MKRSTDATA';

GROUP_NUMBER NAME                           State       Total Size (MB)    FREE_MB
------------ ------------------------------ ----------- --------------- ----------
           2 MKRSTDATA                      MOUNTED             819,168      92320

Here I am creating dummy disk group with provided disks because want to find out is they added 100GB of each disk or not.

Following query to create new ASM disk group

SQL> create diskgroup dummy external redundancy disk 

'/dev/mapper/t1_data08p1','/dev/mapper/t1_data09p1','/dev/mapper/t1_data10p1','/dev/mapper/t1_data11p1','/dev/mapper/t1_data12p1';

Diskgroup created.

SQL>
SQL> alter diskgroup dummy rebalance power 6;

Diskgroup altered.

SQL>

Note: The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER  clause is omitted from a rebalance operation.

Below query shows newly added disk group with disks information, Confirmed that Unix team added 100GB of disks each.

SQL> SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1
           5 MEMBER       NORMAL              102,398     102387 /dev/mapper/t1_data12p1
           5 MEMBER       NORMAL              102,398     102386 /dev/mapper/t1_data11p1
           5 MEMBER       NORMAL              102,398     102386 /dev/mapper/t1_data10p1
           5 MEMBER       NORMAL              102,398     102386 /dev/mapper/t1_data09p1
           5 MEMBER       NORMAL              102,398     102387 /dev/mapper/t1_data08p1

27 rows selected.


Dropping dummy ASM disk group

SQL> drop diskgroup dummy;

Diskgroup dropped.

SQL> 
SQL>set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data12p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data11p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data10p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data09p1
           0 FORMER       NORMAL                    0          0 /dev/mapper/t1_data08p1
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11556 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11544 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11548 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11532 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11536 /dev/mapper/t1_data04p1

27 rows selected.

SQL>

Now adding disks to existing MKRSTDATA disk group

SQL> alter diskgroup MKRSTDATA add disk '/dev/mapper/t1_data08p1','/dev/mapper/t1_data09p1','/dev/mapper/t1_data10p1','/dev/mapper/t1_data11p1','/dev/mapper/t1_data12p1'  rebalance power 11;

Diskgroup altered.

SQL> set lines 200 pages 200
col path for a30
select GROUP_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,FREE_MB,PATH from v$asm_disk;SQL> SQL>

GROUP_NUMBER HEADER_STATU State       Total Size (MB)    FREE_MB PATH
------------ ------------ ----------- --------------- ---------- ------------------------------
           0 CANDIDATE    NORMAL                    0          0 /dev/mapper/t2_fra08p1
           3 MEMBER       NORMAL              102,396     102372 /dev/mapper/t2_fra06p1
           2 MEMBER       NORMAL              102,396      11864 /dev/mapper/t2_fra07p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra04p1
           3 MEMBER       NORMAL              102,396     102376 /dev/mapper/t2_fra05p1
           3 MEMBER       NORMAL              102,396     102368 /dev/mapper/t2_fra03p1
           1 MEMBER       NORMAL                5,116       4772 /dev/mapper/t1_vote3p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote1p1
           1 MEMBER       NORMAL                5,116       4768 /dev/mapper/t1_vote2p1
           4 MEMBER       NORMAL                5,116       4016 /dev/mapper/t1_redo01p1
           4 MEMBER       NORMAL                5,116       4032 /dev/mapper/t1_redo02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo04p1
           3 MEMBER       NORMAL              204,792     204744 /dev/mapper/t2_fra02p1
           4 MEMBER       NORMAL                5,116       4036 /dev/mapper/t1_redo03p1
           3 MEMBER       NORMAL              204,792     204748 /dev/mapper/t2_fra01p1
           2 MEMBER       NORMAL              102,396      11868 /dev/mapper/t1_data05p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data06p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data01p1
           2 MEMBER       NORMAL              102,396      11872 /dev/mapper/t1_data07p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data03p1
           2 MEMBER       NORMAL              102,396      11856 /dev/mapper/t1_data02p1
           2 MEMBER       NORMAL              102,396      11860 /dev/mapper/t1_data04p1
           2 MEMBER       NORMAL              102,396     101872 /dev/mapper/t1_data12p1
           2 MEMBER       NORMAL              102,396     101872 /dev/mapper/t1_data11p1
           2 MEMBER       NORMAL              102,396     101876 /dev/mapper/t1_data10p1
           2 MEMBER       NORMAL              102,396     101876 /dev/mapper/t1_data09p1
           2 MEMBER       NORMAL              102,396     101876 /dev/mapper/t1_data08p1

27 rows selected.

SQL>
SQL> SET LINESIZE  145
SQL> SET PAGESIZE  9999
SQL> SET VERIFY    off
SQL> COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
SQL> COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
SQL> COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
SQL> COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
SQL> COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
SQL> SQL> COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
SQL> COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
SQL> COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
SQL> SELECT
  2      name                                     group_name
  3    , sector_size                              sector_size
  4    , block_size                               block_size
  5    , allocation_unit_size                     allocation_unit_size
  , state                                    state
  6    7    , type                                     type
  8    , total_mb                                 total_mb
  9    , (total_mb - free_mb)                     used_mb
 10    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
 11  FROM
 12      v$asm_diskgroup
 13  ORDER BY
 14      name
/ 15

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
MKRSTDATA                512   4,096    4,194,304 MOUNTED     EXTERN       1,331,148        726,888     54.61
MKRSTFRA                 512   4,096    4,194,304 MOUNTED     EXTERN         819,168            192       .02
MKRSTREDO                512   4,096    4,194,304 MOUNTED     EXTERN          20,464          4,344     21.23
OCRVOTEDG                512   4,096    4,194,304 MOUNTED     NORMAL          15,348          1,040      6.78

SQL>

Done.. Hope this will help you. Feel free to comment here for any help required... :)

Best Regards,
Chowdai