Vishal desai’s Oracle Blog

January 8, 2013

ASM Script to map disks with partner disks on Exadata

Filed under: ASM, Exadata, Uncategorized — vishaldesai @ 7:16 pm

Script and output:

SQL> column "Partnerdisk" heading "Partner|Disks" format a30
SQL> column "PartnerdiskCell" heading "Partnerdisk|Cell" format a30
SQL> set pages 200
SQL>
  1  SELECT disk,
  2    cell,
  3    LISTAGG(NUMBER_KFDPARTNER, ',') WITHIN GROUP (ORDER BY NUMBER_KFDPARTNER) AS "Partnerdisk",
  4    LISTAGG(pd, ',') WITHIN GROUP (ORDER BY pd) AS "PartnerdiskCell",
  5    CASE
  6      WHEN instr(LISTAGG(pd, ',') WITHIN GROUP (ORDER BY pd),cell ) =0    THEN 'OK'
  7      WHEN instr(LISTAGG(pd, ',') WITHIN GROUP (ORDER BY pd),cell ) >0    THEN 'ERROR'
  8    END AS status
  9  FROM
 10    (SELECT disk,
 11      CASE
 12        WHEN DISK>= 0 AND DISK <=11 THEN 1
 13        WHEN DISK>=12 AND DISK <=23 THEN 2
 14        WHEN DISK>=24 AND DISK <=35 THEN 3
 15        WHEN DISK>=36 AND DISK <=47 THEN 4
 16        WHEN DISK>=48 AND DISK <=59 THEN 5
 17        WHEN DISK>=60 AND DISK <=71 THEN 6
 18        WHEN DISK>=72 AND DISK <=83 THEN 7
 19      END AS cell,
 20      NUMBER_KFDPARTNER,
 21      CASE
 22        WHEN NUMBER_KFDPARTNER>= 0 AND NUMBER_KFDPARTNER <=11 THEN 1
 23        WHEN NUMBER_KFDPARTNER>=12 AND NUMBER_KFDPARTNER <=23 THEN 2
 24        WHEN NUMBER_KFDPARTNER>=24 AND NUMBER_KFDPARTNER <=35 THEN 3
 25        WHEN NUMBER_KFDPARTNER>=36 AND NUMBER_KFDPARTNER <=47 THEN 4
 26        WHEN NUMBER_KFDPARTNER>=48 AND NUMBER_KFDPARTNER <=59 THEN 5
 27        WHEN NUMBER_KFDPARTNER>=60 AND NUMBER_KFDPARTNER <=71 THEN 6
 28        WHEN NUMBER_KFDPARTNER>=72 AND NUMBER_KFDPARTNER <=83 THEN 7
 29      END AS pd
 30    FROM V$ASM_DISK A,
 31         X$KFDPARTNER B
 32    WHERE B.NUMBER_KFDPARTNER = A.DISK_NUMBER
 33    AND GRP                   =1
 34    AND name LIKE 'DATA%'
 35    ORDER BY 1 ASC
 36    )
 37* GROUP BY disk,cell

                      Partner                        Partnerdisk
      DISK       CELL Disks                          Cell                           STATU
---------- ---------- ------------------------------ ------------------------------ -----
         0          1 29,30,32,66,71,78,79,83        3,3,3,6,6,7,7,7                OK
         1          1 28,31,34,62,68,70,76,82        3,3,3,6,6,6,7,7                OK
         2          1 27,29,66,67,68,72,73,77        3,3,6,6,6,7,7,7                OK
         3          1 16,28,30,33,35,70,74,80        2,3,3,3,3,6,7,7                OK
         4          1 27,28,33,35,60,66,76,80        3,3,3,3,6,6,7,7                OK
         5          1 12,24,65,67,70,75,76,83        2,3,6,6,6,7,7,7                OK
         6          1 25,26,31,68,69,71,72,82        3,3,3,6,6,6,7,7                OK
         7          1 12,18,19,20,22,26,27,82        2,2,2,2,2,3,3,7                OK
         8          1 16,22,23,64,68,69,71,76        2,2,2,6,6,6,6,7                OK
         9          1 22,25,29,66,69,73,76,81        2,3,3,6,6,7,7,7                OK
        10          1 21,26,28,33,64,73,79,81        2,3,3,3,6,7,7,7                OK
        11          1 13,17,18,19,21,32,67,70        2,2,2,2,2,3,6,6                OK
        12          2 5,7,36,45,46,81,82,83          1,1,4,4,4,7,7,7                OK
        13          2 11,38,41,44,74,78,79,82        1,4,4,4,7,7,7,7                OK
        14          2 38,42,44,47,74,76,79,80        4,4,4,4,7,7,7,7                OK
        15          2 29,35,42,45,76,78,81,82        3,3,4,4,7,7,7,7                OK
        16          2 3,8,29,44,46,47,73,78          1,1,3,4,4,4,7,7                OK
        17          2 11,36,42,45,46,72,81,82        1,4,4,4,4,7,7,7                OK
        18          2 7,11,41,44,47,74,79,83         1,1,4,4,4,7,7,7                OK
        19          2 7,11,44,45,72,79,80,81         1,1,4,4,7,7,7,7                OK
        20          2 7,36,42,43,47,78,80,83         1,4,4,4,4,7,7,7                OK
        21          2 10,11,42,45,72,73,81,83        1,1,4,4,7,7,7,7                OK
        22          2 7,8,9,24,30,38,43,47           1,1,1,3,3,4,4,4                OK
        23          2 8,30,31,32,33,43,79,80         1,3,3,3,3,4,7,7                OK
        24          3 5,22,37,44,47,48,50,59         1,2,4,4,4,5,5,5                OK
        25          3 6,9,36,42,52,53,56,59          1,1,4,4,5,5,5,5                OK
        26          3 6,7,10,38,41,55,57,58          1,1,1,4,4,5,5,5                OK
        27          3 2,4,7,39,40,41,49,59           1,1,1,4,4,4,5,5                OK
        28          3 1,3,4,10,40,41,56,57           1,1,1,1,4,4,5,5                OK
        29          3 0,2,9,15,16,36,37,43           1,1,1,2,2,4,4,4                OK
        30          3 0,3,22,23,39,51,56,58          1,1,2,2,4,5,5,5                OK
        31          3 1,6,23,36,40,50,52,54          1,1,2,4,4,5,5,5                OK
        32          3 0,11,23,37,38,44,48,49         1,1,2,4,4,4,5,5                OK
        33          3 3,4,10,23,37,46,52,58          1,1,1,2,4,4,5,5                OK
        34          3 1,39,42,45,49,50,51,56         1,4,4,4,5,5,5,5                OK
        35          3 3,4,15,39,48,50,51,53          1,1,2,4,5,5,5,5                OK
        36          4 12,17,20,25,29,31,56,63        2,2,2,3,3,3,5,6                OK
        37          4 24,29,32,33,48,56,61,62        3,3,3,3,5,5,6,6                OK
        38          4 13,14,22,26,32,55,66,70        2,2,2,3,3,5,6,6                OK
        39          4 27,30,34,35,53,57,65,68        3,3,3,3,5,5,6,6                OK
        40          4 27,28,31,55,58,60,63,69        3,3,3,5,5,6,6,6                OK
        41          4 13,18,26,27,28,59,67,68        2,2,3,3,3,5,6,6                OK
        42          4 14,15,17,20,21,25,34,62        2,2,2,2,2,3,3,6                OK
        43          4 20,22,23,29,53,60,63,64        2,2,2,3,5,6,6,6                OK
        44          4 13,14,16,18,19,24,32,63        2,2,2,2,2,3,3,6                OK
        45          4 12,15,17,19,21,34,54,71        2,2,2,2,2,3,5,6                OK
        46          4 12,16,17,33,49,54,57,58        2,2,2,3,5,5,5,5                OK
        47          4 14,16,18,20,22,24,50,62        2,2,2,2,2,3,5,6                OK
        48          5 24,32,35,37,60,66,78,83        3,3,3,4,6,6,7,7                OK
        49          5 27,32,34,46,64,65,67,73        3,3,3,4,6,6,6,7                OK
        50          5 24,31,34,35,47,62,65,69        3,3,3,3,4,6,6,6                OK
        51          5 30,34,35,62,64,70,74,75        3,3,3,6,6,6,7,7                OK
        52          5 25,31,33,61,65,70,72,75        3,3,3,6,6,6,7,7                OK
        53          5 25,35,39,43,60,67,74,75        3,3,4,4,6,6,7,7                OK
        54          5 31,45,46,63,64,68,75,77        3,4,4,6,6,6,7,7                OK
        55          5 26,38,40,60,61,63,71,73        3,4,4,6,6,6,6,7                OK
        56          5 25,28,30,34,36,37,60,61        3,3,3,3,4,4,6,6                OK
        57          5 26,28,39,46,62,63,77,78        3,3,4,4,6,6,7,7                OK
        58          5 26,30,33,40,46,63,66,71        3,3,3,4,4,6,6,6                OK
        59          5 24,25,27,41,61,69,74,77        3,3,3,4,6,6,7,7                OK
        60          6 4,40,43,48,53,55,56,72         1,4,4,5,5,5,5,7                OK
        61          6 37,52,55,56,59,75,78,82        4,5,5,5,5,7,7,7                OK
        62          6 1,37,42,47,50,51,57,74         1,4,4,4,5,5,5,7                OK
        63          6 36,40,43,44,54,55,57,58        4,4,4,4,5,5,5,5                OK
        64          6 8,10,43,49,51,54,73,75         1,1,4,5,5,5,7,7                OK
        65          6 5,39,49,50,52,72,77,83         1,4,5,5,5,7,7,7                OK
        66          6 0,2,4,9,38,48,58,81            1,1,1,1,4,5,5,7                OK
        67          6 2,5,11,41,49,53,76,77          1,1,1,4,5,5,7,7                OK
        68          6 1,2,6,8,39,41,54,79            1,1,1,1,4,4,5,7                OK
        69          6 6,8,9,40,50,59,75,77           1,1,1,4,5,5,7,7                OK
        70          6 1,3,5,11,38,51,52,80           1,1,1,1,4,5,5,7                OK
        71          6 0,6,8,45,55,58,77,80           1,1,1,4,5,5,7,7                OK
        72          7 2,6,17,19,21,52,60,65          1,1,2,2,2,5,6,6                OK
        73          7 2,9,10,16,21,49,55,64          1,1,1,2,2,5,5,6                OK
        74          7 3,13,14,18,51,53,59,62         1,2,2,2,5,5,5,6                OK
        75          7 5,51,52,53,54,61,64,69         1,5,5,5,5,6,6,6                OK
        76          7 1,4,5,8,9,14,15,67             1,1,1,1,1,2,2,6                OK
        77          7 2,54,57,59,65,67,69,71         1,5,5,5,6,6,6,6                OK
        78          7 0,13,15,16,20,48,57,61         1,2,2,2,2,5,5,6                OK
        79          7 0,10,13,14,18,19,23,68         1,1,2,2,2,2,2,6                OK
        80          7 3,4,14,19,20,23,70,71          1,1,2,2,2,2,6,6                OK
        81          7 9,10,12,15,17,19,21,66         1,1,2,2,2,2,2,6                OK
        82          7 1,6,7,12,13,15,17,61           1,1,1,2,2,2,2,6                OK
        83          7 0,5,12,18,20,21,48,65          1,1,2,2,2,2,5,6                OK

84 rows selected.

Above output is from half rack Exadata with normal ASM disk group redundancy. Basically it shows that for a particular disk, mirrored extent will be created on one of eight partner disks listed and Partnerdisk Cell column shows on which cell partner disks are located. Status column will show whether Exadata will survive cell failure or not (OK if it will, ERROR if it may not).

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: