Vishal desai’s Oracle Blog

September 18, 2013

Learning Bloom Filters

Filed under: Exadata, Performance Tuning — vishaldesai @ 9:35 pm

Thanks to Jonathan Lewis for quiz on bloom filter. I learned more about bloom filters from quiz and trying to understand answers for below questions:

1) How many bloom filters are created?

2) How many bloom filters are used and where exactly they are used?

Scripts to create test case are as follows:

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;

create table t1 tablespace ism_ts
as
select
    rownum        id,
    to_char(rownum)    small_vc,
    rpad('x',100)    padding
from
    all_objects
where
    rownum <= 100
;

alter table t1 
    add constraint t1_pk primary key(id)
;

create table t2 tablespace ism_ts
as
select
    rownum        id,
    to_char(rownum)    small_vc,
    rpad('x',100)    padding
from
    all_objects
where
    rownum <= 100
;

alter table t2
    add constraint t2_pk primary key(id)
;

create table t3 tablespace ism_ts
as
select
    rownum        id,
    to_char(rownum)    small_vc,
    rpad('x',100)    padding
from
    all_objects
where
    rownum <= 100
;

alter table t3
    add constraint t3_pk primary key(id)
;

create table t4 tablespace ism_ts
nologging
as
select
    t1.id            id1,
    t2.id            id2,
    t3.id            id3,
    rpad(rownum,10)        small_vc,
    rpad('x',100)        padding
from
    t1, t2, t3
;


begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        user,
        't2',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        user,
        't3',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        user,
        't4',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

Case 1: Broadcast method

SQL, Output and plan are as follows:

select /*+
             monitor
             parallel(t1,4)
             parallel(t2,4)
             parallel(t3,4)
             parallel(t4,4)
             full(t1)
             full(t2)
             full(t3)
             full(t4)
     */
      count(t1.small_vc),
      count(t2.small_vc),
      count(t3.small_vc),
      count(t4.small_vc)
  from
      t3,
      t2,
      t1,
      t4
  where
      t1.id = t4.id1
  and t2.id = t4.id2
  and t3.id = t4.id3
  and t1.small_vc in (1,2,3)
  and t2.small_vc in (1,2,3)
  and t3.small_vc in (1,2,3)
;

COUNT(T1.SMALL_VC) COUNT(T2.SMALL_VC) COUNT(T3.SMALL_VC) COUNT(T4.SMALL_VC)
------------------ ------------------ ------------------ ------------------
                27                 27                 27                 27
                
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |   833 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE             |          |     1 |    38 |            |          |        |      |            |
|   2 |   PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)      | :TQ10003 |     1 |    38 |            |          |  Q1,03 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE          |          |     1 |    38 |            |          |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN              |          |    26 |   988 |   833   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|   6 |       PX RECEIVE            |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|   7 |        PX SEND BROADCAST    | :TQ10000 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   8 |         PX BLOCK ITERATOR   |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS FULL  | T1       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |       HASH JOIN             |          |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  11 |        PX RECEIVE           |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  12 |         PX SEND BROADCAST   | :TQ10001 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |
|  13 |          PX BLOCK ITERATOR  |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|* 14 |           TABLE ACCESS FULL | T2       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 15 |        HASH JOIN            |          | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  16 |         PX RECEIVE          |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  17 |          PX SEND BROADCAST  | :TQ10002 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | P->P | BROADCAST  |
|  18 |           PX BLOCK ITERATOR |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|* 19 |            TABLE ACCESS FULL| T3       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  20 |         PX BLOCK ITERATOR   |          |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,03 | PCWC |            |
|* 21 |          TABLE ACCESS FULL  | T4       |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,03 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."ID"="T4"."ID1")
   9 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T1"."SMALL_VC")=:SYS_B_0 OR TO_NUMBER("T1"."SMALL_VC")=:SYS_B_1 OR
              TO_NUMBER("T1"."SMALL_VC")=:SYS_B_2))
  10 - access("T2"."ID"="T4"."ID2")
  14 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T2"."SMALL_VC")=:SYS_B_3 OR TO_NUMBER("T2"."SMALL_VC")=:SYS_B_4 OR
              TO_NUMBER("T2"."SMALL_VC")=:SYS_B_5))
  15 - access("T3"."ID"="T4"."ID3")
  19 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T3"."SMALL_VC")=:SYS_B_6 OR TO_NUMBER("T3"."SMALL_VC")=:SYS_B_7 OR
              TO_NUMBER("T3"."SMALL_VC")=:SYS_B_8))
  21 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3"),SYS_OP_BLOOM_FILTER(:BF0000,"T
              4"."ID2"),SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID1")))

 

image

 

image

1) How many bloom filters are created?

Bloom filter/filters were applied at line 21. Based on comment from Sayan on Jonathan’s blog it looks like only one filter was created after accessing table T1 and same bloom filter was appended after accessing table T2 and T3 (:BF0000) which got applied on line 21. So probably the answer to this question is 1.

2) How many bloom filters are used and where exactly they are used?

If answer to above question is 1, then obviously oracle used only one bloom filter at line 21. Did it really converted all joins into one bloom filter?

SQL> select distinct id from t1,t4 where t1.id = t4.id1 and t1.small_vc in (1,2,3);

        ID
----------
         1
         2
         3<= output 1

SQL> select distinct id from t2,t4 where t2.id = t4.id2 and t2.small_vc in (1,2,3);

        ID
----------
         1
         2
         3<= output 2

SQL> select distinct id from t3,t4 where t3.id = t4.id3 and t3.small_vc in (1,2,3);

        ID
----------
         1
         2
         3<= output 3
         
SQL> select count(1) from t4 where id1 in (1,2,3) AND id2 in (1,2,3) AND id3 in (1,2,3);

  COUNT(1)
----------
        27 <= output 4

Bloom filters basically converts join into filters and that’s why its a big deal on Exadata as they can be offloaded to Cell server. As you can see from above bloom filter created got applied at line 21 and as per real time SQL monitoring actual number of rows returned are 27 which matches with output 4.

So  one bloom filter (appended with multiple filters) is created and its used on single plan line.

Case 2: HASH method

select /*+
          monitor
             parallel(t1,4)
             parallel(t2,4)
             parallel(t3,4)
             parallel(t4,4)
             full(t1)
             full(t2)
             full(t3)
             full(t4)
             PQ_DISTRIBUTE(@"SEL$1" "T4"@"SEL$1" HASH HASH)
             PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" HASH HASH)
             PQ_DISTRIBUTE(@"SEL$1" "T1"@"SEL$1" HASH HASH)
     */
      count(t1.small_vc),
      count(t2.small_vc),
      count(t3.small_vc),
      count(t4.small_vc)
  from
      t3,
      t2,
      t1,
      t4
  where
      t1.id = t4.id1
  and t2.id = t4.id2
  and t3.id = t4.id3
  and t1.small_vc in (1,2,3)
  and t2.small_vc in (1,2,3)
  and t3.small_vc in (1,2,3)
;

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |       |       |   833 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                    |          |     1 |    38 |            |          |        |      |            |
|   2 |   PX COORDINATOR                   |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)             | :TQ10006 |     1 |    38 |            |          |  Q1,06 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                 |          |     1 |    38 |            |          |  Q1,06 | PCWP |            |
|*  5 |      HASH JOIN                     |          |    26 |   988 |   833   (1)| 00:00:01 |  Q1,06 | PCWP |            |
|   6 |       JOIN FILTER CREATE           | :BF0000  |     3 |    18 |     2   (0)| 00:00:01 |  Q1,06 | PCWP |            |
|   7 |        PX RECEIVE                  |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,06 | PCWP |            |
|   8 |         PX SEND HASH               | :TQ10004 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,04 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR         |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,04 | PCWC |            |
|* 10 |           TABLE ACCESS FULL        | T1       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  11 |       PX RECEIVE                   |          |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,06 | PCWP |            |
|  12 |        PX SEND HASH                | :TQ10005 |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,05 | P->P | HASH       |
|  13 |         JOIN FILTER USE            | :BF0000  |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,05 | PCWP |            |
|* 14 |          HASH JOIN BUFFERED        |          |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,05 | PCWP |            |
|  15 |           JOIN FILTER CREATE       | :BF0001  |     3 |    18 |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  16 |            PX RECEIVE              |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  17 |             PX SEND HASH           | :TQ10002 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|  18 |              PX BLOCK ITERATOR     |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|* 19 |               TABLE ACCESS FULL    | T2       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  20 |           PX RECEIVE               |          | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,05 | PCWP |            |
|  21 |            PX SEND HASH            | :TQ10003 | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | P->P | HASH       |
|  22 |             JOIN FILTER USE        | :BF0001  | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|* 23 |              HASH JOIN BUFFERED    |          | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  24 |               JOIN FILTER CREATE   | :BF0002  |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  25 |                PX RECEIVE          |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  26 |                 PX SEND HASH       | :TQ10000 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  27 |                  PX BLOCK ITERATOR |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 28 |                   TABLE ACCESS FULL| T3       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  29 |               PX RECEIVE           |          |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  30 |                PX SEND HASH        | :TQ10001 |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  31 |                 JOIN FILTER USE    | :BF0002  |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|  32 |                  PX BLOCK ITERATOR |          |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|* 33 |                   TABLE ACCESS FULL| T4       |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."ID"="T4"."ID1")
  10 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T1"."SMALL_VC")=:SYS_B_0 OR TO_NUMBER("T1"."SMALL_VC")=:SYS_B_1 OR
              TO_NUMBER("T1"."SMALL_VC")=:SYS_B_2))
  14 - access("T2"."ID"="T4"."ID2")
  19 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T2"."SMALL_VC")=:SYS_B_3 OR TO_NUMBER("T2"."SMALL_VC")=:SYS_B_4 OR
              TO_NUMBER("T2"."SMALL_VC")=:SYS_B_5))
  23 - access("T3"."ID"="T4"."ID3")
  28 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T3"."SMALL_VC")=:SYS_B_6 OR TO_NUMBER("T3"."SMALL_VC")=:SYS_B_7 OR
              TO_NUMBER("T3"."SMALL_VC")=:SYS_B_8))
  33 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3"))

image

image

image

image

1) How many bloom filters are created?

If we look at SQL predicate information it shows only one bloom filter is used (:BF0000) at line 33. According to PLAN, there are three bloom filters created (:BF0000, :BF0001, :BF0002). So answer is 3 bloom filters are created.

2) How many bloom filters are used and where exactly they are used?

SQL> select count(1) from t3,t4 where t3.id = t4.id3 and t3.small_vc in (1,2,3);

  COUNT(1)
----------
     30000 <= output 5

Based on real time SQL monitoring, actual rows returned from line 33 is 30,000 which matches with output 5. :BF0000 was created at line 6 (For T1, t1.id = t4.id1) and as per filter predicate it used :BF0000 for T4.ID3 and line 31 says it used :BF0002 which is confusing.

Going back to 3 bloom filters where are :BF0000 and  :BF0001 used?

I think they are used exactly where they are shown in plan. Notice number of rows returned drops from 30k to 906 and 900 to 33.

image

With "_bloom_filter_enabled" set to FALSE plan looks like following:

image

Memory consumption for hash join buffered also went up from 1 MB to 3 MB.

So three bloom filters (each having one filter) are created and all three are used on different plan lines. One filter was used while accessing T4 and other two were used when accessing data from hash joins result set.

Advertisements

April 1, 2013

Exadata Cell Server Monitoring

Filed under: Exadata — vishaldesai @ 9:21 pm

If you are new to Exadata then you can learn more about Exadata monitoring by reading Chapter 12 of Expert Oracle Exadata and Oracle Exadata Command Reference. Once you know little bit about monitoring next challenge will be how to display those metrics in proper format. If you have Oracle Enterprise Manager 11g or 12c installed and configured in your infrastructure you can create dashboards to start monitoring Exadata.

I tried to write couple of Perl scripts that will display metrics on unix shell prompt for all storage cells and will refresh at regular interval of time (like Unix top command).

cell_metrics.pl will display CPU%, Memory% and runq for all storage cells. It takes two parameters samples and intervals. You could see some latency in interval as script submits dcli command and gets all data and formats it.

image

cell_ionet_metrics.pl will display Cell Disk IO, Grid Disk IO and few Network Statistics.

image

If there is a :, it represents min on left side and max on right side. Network Throughput are absolute numbers. Network errors columns should always be 0:0.

I haven’t paid much attention to tuning the dcli, awk and sed itself and did not test this on full rack. But basically this two wrapper script will give you idea if you want to monitor other cell metrics from command line.

Download code

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).

December 7, 2012

Monitoring Long Running Query using gv$sql_monitor

Filed under: Exadata, Oracle Enterprise Manager, Performance Tuning — vishaldesai @ 4:13 pm

To proactively monitor long running queries, metric extensions can be created in Oracle Enterprise Manager 12c. I have created metric extension on Exadata platform but it can also be used on non Exadata platform.

Below are steps to create metric extension in Oracle Enterprise Manager 12c.

1. Navigate to Enterprise -> Monitoring -> Metric Extensions

2. Below Metric Extension click on Create

3. General properties tab: Select Target Type as Cluster Database, Specify name and display name, select Adapter as SQL and choose appropriate frequency based on your requirement.

4. Adapter: SQL Query select key,sql_id,username,sql_id,elapsed_time  from gV$SQL_MONITOR where status=’EXECUTING’ and username is not null

Columns: Add following as Name, Display Name, Column Name, Value Type, Comparison Operator, Warning & Critical
key – Key – Key Column – Number
sql_id – sql_id – Data Column – String
username – username – Data Column – String
sql_id – sql_id – Data Column – String
elapsed_time – elapsed_time – Data Column – Number – > 900000000 – 3600000000

Elapsed_time is in micro seconds so choose appropriate values according to your environment. You can choose lower values for aggressive monitoring.

5. Credentials: Database Credentials Use Default Monitoring Credentials.

6. Test Targets: Here you can add couple of targets and test whether Metric Extension is working or not.

7. Review: You can review selections you made on previous screens.

8. Finish: Click on Finish to create Metric Extension.

9. Once the Metric Extension is created, click on action tab and convert template to deployable draft. Again choose Metric Extension and choose Deploy to target.

10. Provided you have setup email/pager notification in your Oracle Enterprise Manager 12c, you will get notified on long running queries.

Example: I got notification for one sql_id that my adhoc user was running on Exadata platform. As you can see from below there was space missing after full hint and query was running in serial mode with smart scan feature turned off. I notified user and he fixed query hint and query ran in 4 minutes instead of 90 plus minutes.

SELECT /*+ parallel(r,16),full(r)*/  MIN(from_dos),MAX(from_dos) 
FROM CSUSER.REPOS_CLAIM_NEW r 
WHERE  r.proc_type ='C'   
AND r.CHARge > 0.99 
AND substr(r.error_flag, 17, 1) IN ('H', 'L', ' ')     
AND nvl(trim(mdr_flag),'~') IN('~','D','C') 
AND substr(error_flag,9,1) IN (' ','O') 

PLAN hash value: 3370285159 

--------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                           |       |       |   983K(100)|          |       |       | 
|   1 |  SORT AGGREGATE                     |                           |     1 |    83 |            |          |       |       | 
|   2 |   PARTITION RANGE ALL               |                           |  3713M|   287G|   983K  (1)| 03:16:45 |     1 |   119 | 
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| REPOS_CLAIM_NEW           |  3713M|   287G|   983K  (1)| 03:16:45 |     1 |   119 | 
|   4 |     BITMAP CONVERSION TO ROWIDS     |                           |       |       |            |          |       |       | 
|   5 |      BITMAP AND                     |                           |       |       |            |          |       |       | 
|   6 |       BITMAP OR                     |                           |       |       |            |          |       |       | 
|*  7 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|*  8 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|*  9 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|* 10 |       BITMAP INDEX SINGLE VALUE     | REPOS_CLAIM_PROC_TYPE_IDX |       |       |            |          |     1 |   119 | 
--------------------------------------------------------------------------------------------------------------------------------- 

October 15, 2012

Exadata Compute node System Backup using LVM Snapshots

Filed under: Backup & Recovery, Exadata — vishaldesai @ 3:48 pm

Below is the sample shell script to backup compute nodes (system only) on Exadata. It’s not very dynamic so change tar location, snap volume names etc as per your environment.

#!/bin/ksh

SHOW_USAGE() {
   echo "Usage: lvmsnap_backup.sh <computenode> "
   exit 1
}


computenode=$1
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

#Create snapshot
lvcreate -L2G -s -n root_snap /dev/VGExaDb/LVDbSys1
lvcreate -L10G -s -n u01_snap /dev/VGExaDb/LVDbOra1

#Mount Snap volumes
mkdir /mnt/snap
mkdir /mnt/snap/root
mkdir /mnt/snap/u01
mount /dev/VGExaDb/root_snap /mnt/snap/root
mount /dev/VGExaDb/u01_snap /mnt/snap/u01

#Backup Snapshots
cd /zfs/fhrestore01/restore01
tar -pjcvf /zfs/fhrestore01/restore01/${computenode}_system_backup_$(date +%m%d%y).tar.bz2 /mnt/snap/* /boot --exclude /zfs/fhrestore01/restore01/${computenode}_system_backup_$(date +%m%d%y).tar.bz2 >/zfs/fhrestore01/restore01/${computenode}_system_backup_$(date +%m%d%y).stdout 2> /zfs/fhrestore01/restore01/${computenode}_system_backup_$(date +%m%d%y).stderr



#Unmount and drop temporary partition
umount /mnt/snap/u01
rmdir  /mnt/snap/u01
umount /mnt/snap/root
rmdir  /mnt/snap/root
lvremove -f /dev/VGExaDb/root_snap
lvremove -f /dev/VGExaDb/u01_snap
rmdir /mnt/snap

October 5, 2012

How to Resize ASM disk groups on Exadata

Filed under: ASM, Exadata — vishaldesai @ 6:18 pm

Oracle Advanced customer support configured Exadata disk groups by allocating 40% of space to DATA disk group and 60% of space to RECO disk group.  Flashback and force logging was not required for our DW environment so we decided to resize ASM disk groups and give maximum available space to DATA disk group for future data growth and to accommodate more databases.

Steps are provided by metalink note 1245494.1 but I have made some changes to commands and additions to monitor ASM rebalance operation. ASM rebalance operation can be monitored using shell script provided by metalink note 1274322.1.

First step is to check for space and make sure we have full database backup.

1. "free_mb" should be greater than "required_mirror_free_mb".

SQL> select name, total_mb, free_mb, required_mirror_free_mb from v$asm_diskgroup; 

NAME                             TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB
------------------------------ ---------- ---------- -----------------------
DATA_FHDB                        18235392   15863580                 2605056
RECO_FHDB                        27240192   25419252                 3891456
DBFS_DG                           2087680    1037092                  298240
 
2. Backup your databases.

3. Checks disks are balanced and have free space.

SQL> select name,total_mb,free_mb from v$asm_disk where mount_status='CACHED' and (name like 'DATA%' or name like 'RECO%') order by 1; 

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
DATA_FHDB_CD_00_FHDBCEL01          217088     188852
DATA_FHDB_CD_00_FHDBCEL02          217088     188864
DATA_FHDB_CD_00_FHDBCEL03          217088     188868
DATA_FHDB_CD_00_FHDBCEL04          217088     188828
DATA_FHDB_CD_00_FHDBCEL05          217088     188864
DATA_FHDB_CD_00_FHDBCEL06          217088     188880
DATA_FHDB_CD_00_FHDBCEL07          217088     188696
...
...
RECO_FHDB_CD_11_FHDBCEL01          324288     302756
RECO_FHDB_CD_11_FHDBCEL02          324288     302656
RECO_FHDB_CD_11_FHDBCEL03          324288     302560
RECO_FHDB_CD_11_FHDBCEL04          324288     302436
RECO_FHDB_CD_11_FHDBCEL05          324288     302672
RECO_FHDB_CD_11_FHDBCEL06          324288     302608
RECO_FHDB_CD_11_FHDBCEL07          324288     302548

Drop the disks of Failgroup Storage cell 1 from ASM instance

-- For DATA diskgroup, get the list of disk name for DATA dikgroup and failgroup storage FHDBCEL01

column failgroup format a20
set pages 200
set linesize 200
select name,header_status,mount_status,failgroup from v$asm_disk where group_number=1 and failgroup='FHDBCEL01' order by 1;

NAME                           HEADER_STATU MOUNT_S FAILGROUP
------------------------------ ------------ ------- --------------------
DATA_FHDB_CD_00_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_01_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_02_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_03_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_04_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_05_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_06_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_07_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_08_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_09_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_10_FHDBCEL01      MEMBER       CACHED  FHDBCEL01
DATA_FHDB_CD_11_FHDBCEL01      MEMBER       CACHED  FHDBCEL01

12 rows selected.

 
-- Now use the below command to drop all disks of failgroup "FHDBCEL01"
 
alter diskgroup DATA_FHDB drop disks in failgroup FHDBCEL01 rebalance power 11 NOWAIT;
 
-- Check the rebalance operation has started or not. Use rebalance_progress.sh as shown below to monitor progress of ASM rebalance operation as v$asm_operation will not show you right estimate.
 
select * from v$asm_operations;
GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN          11         11     112691     142408       9782           3


Once the rebalance complets ,check the header_status column in v$asm_disk by running below sql..It should show as FORMER for dropped disk
 
set linesize 300
column path format a40
select name,path,header_status,mount_status from v$asm_disk where group_number=0;

NAME                           PATH                                     HEADER_STATU MOUNT_S
------------------------------ ---------------------------------------- ------------ -------
                               o/192.168.10.5/DATA_FHDB_CD_00_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_06_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_02_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_10_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_01_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_03_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_09_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_11_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_08_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_05_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_04_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/DATA_FHDB_CD_07_fhdbcel01 FORMER       CLOSED

12 rows selected.
 
-- Now peform the steps for RECO diskgroup. For RECO diskgroup, get the list of disk name for RECO dikgroup and failgroup FHDBCEL01

column failgroup format a20
set pages 200
set linesize 200 
select name,header_status,mount_status,group_number,failgroup from v$asm_disk where group_number=2 and failgroup='FHDBCEL01';

NAME                           HEADER_STATU MOUNT_S GROUP_NUMBER FAILGROUP
------------------------------ ------------ ------- ------------ --------------------
RECO_FHDB_CD_02_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_11_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_00_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_06_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_01_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_05_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_04_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_07_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_10_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_09_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_08_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01
RECO_FHDB_CD_03_FHDBCEL01      MEMBER       CACHED             2 FHDBCEL01

12 rows selected.

 
-- Now use the below command to drop all disks of failgroup  "FHDBCEL01'
 
alter diskgroup RECO_FHDB drop disks in failgroup FHDBCEL01 rebalance power 11 NOWAIT;

Diskgroup altered.

-- Check the rebalance operation has started or not. Use rebalance_progress.sh as shown below to monitor progress of ASM rebalance operation as v$asm_operation will not show you right estimate.
 
select * from v$asm_operations;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           2 REBAL RUN          11         11       9909      18242       3159           2


 
-- Once the rebalance completes ,check the header_status column in v$asm_disk. It should show as FORMER for dropped disk
 
set linesize 300
column path format a40
select name,path,header_status,mount_status from v$asm_disk where group_number=0 order by 2;

NAME                           PATH                                     HEADER_STATU MOUNT_S
------------------------------ ---------------------------------------- ------------ -------
                               o/192.168.10.5/RECO_FHDB_CD_00_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_06_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_02_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_10_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_01_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_03_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_09_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_11_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_08_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_05_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_04_fhdbcel01 FORMER       CLOSED
                               o/192.168.10.5/RECO_FHDB_CD_07_fhdbcel01 FORMER       CLOSED

Drop and re-create the Grid disks at Storage cell node FHDBCEL01 with desired size.

cellcli> list griddisk attributes name,cellDisk,size,status;

         DATA_FHDB_CD_00_fhdbcel01       CD_00_fhdbcel01         212G            active
         DATA_FHDB_CD_01_fhdbcel01       CD_01_fhdbcel01         212G            active
         DATA_FHDB_CD_02_fhdbcel01       CD_02_fhdbcel01         212G            active
         DATA_FHDB_CD_03_fhdbcel01       CD_03_fhdbcel01         212G            active
         DATA_FHDB_CD_04_fhdbcel01       CD_04_fhdbcel01         212G            active
         DATA_FHDB_CD_05_fhdbcel01       CD_05_fhdbcel01         212G            active
         DATA_FHDB_CD_06_fhdbcel01       CD_06_fhdbcel01         212G            active
         DATA_FHDB_CD_07_fhdbcel01       CD_07_fhdbcel01         212G            active
         DATA_FHDB_CD_08_fhdbcel01       CD_08_fhdbcel01         212G            active
         DATA_FHDB_CD_09_fhdbcel01       CD_09_fhdbcel01         212G            active
         DATA_FHDB_CD_10_fhdbcel01       CD_10_fhdbcel01         212G            active
         DATA_FHDB_CD_11_fhdbcel01       CD_11_fhdbcel01         212G            active
         DBFS_DG_CD_02_fhdbcel01         CD_02_fhdbcel01         29.125G         active
         DBFS_DG_CD_03_fhdbcel01         CD_03_fhdbcel01         29.125G         active
         DBFS_DG_CD_04_fhdbcel01         CD_04_fhdbcel01         29.125G         active
         DBFS_DG_CD_05_fhdbcel01         CD_05_fhdbcel01         29.125G         active
         DBFS_DG_CD_06_fhdbcel01         CD_06_fhdbcel01         29.125G         active
         DBFS_DG_CD_07_fhdbcel01         CD_07_fhdbcel01         29.125G         active
         DBFS_DG_CD_08_fhdbcel01         CD_08_fhdbcel01         29.125G         active
         DBFS_DG_CD_09_fhdbcel01         CD_09_fhdbcel01         29.125G         active
         DBFS_DG_CD_10_fhdbcel01         CD_10_fhdbcel01         29.125G         active
         DBFS_DG_CD_11_fhdbcel01         CD_11_fhdbcel01         29.125G         active
         RECO_FHDB_CD_00_fhdbcel01       CD_00_fhdbcel01         316G             active
         RECO_FHDB_CD_01_fhdbcel01       CD_01_fhdbcel01         316G             active
         RECO_FHDB_CD_02_fhdbcel01       CD_02_fhdbcel01         316G             active
         RECO_FHDB_CD_03_fhdbcel01       CD_03_fhdbcel01         316G             active
         RECO_FHDB_CD_04_fhdbcel01       CD_04_fhdbcel01         316G             active
         RECO_FHDB_CD_05_fhdbcel01       CD_05_fhdbcel01         316G             active
         RECO_FHDB_CD_06_fhdbcel01       CD_06_fhdbcel01         316G             active
         RECO_FHDB_CD_07_fhdbcel01       CD_07_fhdbcel01         316G             active
         RECO_FHDB_CD_08_fhdbcel01       CD_08_fhdbcel01         316G             active
         RECO_FHDB_CD_09_fhdbcel01       CD_09_fhdbcel01         316G             active
         RECO_FHDB_CD_10_fhdbcel01       CD_10_fhdbcel01         316G             active
         RECO_FHDB_CD_11_fhdbcel01       CD_11_fhdbcel01         316G             active

#Login into FHDBCEL01 cell server and start cellcli.
 
cellcli> ALTER GRIDDISK  DATA_FHDB_CD_00_FHDBCEL01,DATA_FHDB_CD_01_FHDBCEL01,DATA_FHDB_CD_02_FHDBCEL01,DATA_FHDB_CD_03_FHDBCEL01,DATA_FHDB_CD_04_FHDBCEL01,DATA_FHDB_CD_05_FHDBCEL01,DATA_FHDB_CD_06_FHDBCEL01,DATA_FHDB_CD_07_FHDBCEL01,DATA_FHDB_CD_08_FHDBCEL01,DATA_FHDB_CD_09_FHDBCEL01,DATA_FHDB_CD_10_FHDBCEL01,DATA_FHDB_CD_11_FHDBCEL01 INACTIVE

GridDisk DATA_FHDB_CD_00_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_01_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_02_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_03_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_04_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_05_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_06_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_07_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_08_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_09_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_10_fhdbcel01 successfully altered
GridDisk DATA_FHDB_CD_11_fhdbcel01 successfully altered

cellcli> DROP GRIDDISK ALL PREFIX=DATA_FHDB

GridDisk DATA_FHDB_CD_00_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_01_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_02_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_03_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_04_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_05_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_06_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_07_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_08_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_09_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_10_fhdbcel01 successfully dropped
GridDisk DATA_FHDB_CD_11_fhdbcel01 successfully dropped


cellcli> ALTER GRIDDISK  RECO_FHDB_CD_00_FHDBCEL01,RECO_FHDB_CD_01_FHDBCEL01,RECO_FHDB_CD_02_FHDBCEL01,RECO_FHDB_CD_03_FHDBCEL01,RECO_FHDB_CD_04_FHDBCEL01,RECO_FHDB_CD_05_FHDBCEL01,RECO_FHDB_CD_06_FHDBCEL01,RECO_FHDB_CD_07_FHDBCEL01,RECO_FHDB_CD_08_FHDBCEL01,RECO_FHDB_CD_09_FHDBCEL01,RECO_FHDB_CD_10_FHDBCEL01,RECO_FHDB_CD_11_FHDBCEL01 INACTIVE

GridDisk RECO_FHDB_CD_00_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_01_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_02_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_03_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_04_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_05_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_06_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_07_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_08_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_09_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_10_fhdbcel01 successfully altered
GridDisk RECO_FHDB_CD_11_fhdbcel01 successfully altered


cellcli> DROP GRIDDISK ALL PREFIX=RECO_FHDB

GridDisk RECO_FHDB_CD_00_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_01_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_02_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_03_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_04_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_05_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_06_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_07_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_08_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_09_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_10_fhdbcel01 successfully dropped
GridDisk RECO_FHDB_CD_11_fhdbcel01 successfully dropped


-- Create the grid disk with desired size
 
cellcli> CREATE GRIDDISK ALL PREFIX=DATA_FHDB, size=516G;

Cell disks were skipped because they had no freespace for grid disks: FD_00_fhdbcel01, FD_01_fhdbcel01, FD_02_fhdbcel01, FD_03_fhdbcel01, FD_04_fhdbcel01, FD_05_fhdbcel01, FD_06_fhdbcel01, FD_07_fhdbcel01, FD_08_fhdbcel01, FD_09_fhdbcel01, FD_10_fhdbcel01, FD_11_fhdbcel01, FD_12_fhdbcel01, FD_13_fhdbcel01, FD_14_fhdbcel01, FD_15_fhdbcel01.
GridDisk DATA_FHDB_CD_00_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_01_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_02_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_03_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_04_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_05_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_06_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_07_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_08_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_09_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_10_fhdbcel01 successfully created
GridDisk DATA_FHDB_CD_11_fhdbcel01 successfully created

cellcli> CREATE GRIDDISK ALL PREFIX=RECO_FHDB, size=12G;

Cell disks were skipped because they had no freespace for grid disks: FD_00_fhdbcel01, FD_01_fhdbcel01, FD_02_fhdbcel01, FD_03_fhdbcel01, FD_04_fhdbcel01, FD_05_fhdbcel01, FD_06_fhdbcel01, FD_07_fhdbcel01, FD_08_fhdbcel01, FD_09_fhdbcel01, FD_10_fhdbcel01, FD_11_fhdbcel01, FD_12_fhdbcel01, FD_13_fhdbcel01, FD_14_fhdbcel01, FD_15_fhdbcel01.
GridDisk RECO_FHDB_CD_00_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_01_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_02_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_03_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_04_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_05_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_06_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_07_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_08_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_09_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_10_fhdbcel01 successfully created
GridDisk RECO_FHDB_CD_11_fhdbcel01 successfully created


-- Check Griddisk information

cellcli> list griddisk attributes name,cellDisk,size,status;

         DATA_FHDB_CD_00_fhdbcel01       CD_00_fhdbcel01         516G            active
         DATA_FHDB_CD_01_fhdbcel01       CD_01_fhdbcel01         516G            active
         DATA_FHDB_CD_02_fhdbcel01       CD_02_fhdbcel01         516G            active
         DATA_FHDB_CD_03_fhdbcel01       CD_03_fhdbcel01         516G            active
         DATA_FHDB_CD_04_fhdbcel01       CD_04_fhdbcel01         516G            active
         DATA_FHDB_CD_05_fhdbcel01       CD_05_fhdbcel01         516G            active
         DATA_FHDB_CD_06_fhdbcel01       CD_06_fhdbcel01         516G            active
         DATA_FHDB_CD_07_fhdbcel01       CD_07_fhdbcel01         516G            active
         DATA_FHDB_CD_08_fhdbcel01       CD_08_fhdbcel01         516G            active
         DATA_FHDB_CD_09_fhdbcel01       CD_09_fhdbcel01         516G            active
         DATA_FHDB_CD_10_fhdbcel01       CD_10_fhdbcel01         516G            active
         DATA_FHDB_CD_11_fhdbcel01       CD_11_fhdbcel01         516G            active
         DBFS_DG_CD_02_fhdbcel01         CD_02_fhdbcel01         29.125G         active
         DBFS_DG_CD_03_fhdbcel01         CD_03_fhdbcel01         29.125G         active
         DBFS_DG_CD_04_fhdbcel01         CD_04_fhdbcel01         29.125G         active
         DBFS_DG_CD_05_fhdbcel01         CD_05_fhdbcel01         29.125G         active
         DBFS_DG_CD_06_fhdbcel01         CD_06_fhdbcel01         29.125G         active
         DBFS_DG_CD_07_fhdbcel01         CD_07_fhdbcel01         29.125G         active
         DBFS_DG_CD_08_fhdbcel01         CD_08_fhdbcel01         29.125G         active
         DBFS_DG_CD_09_fhdbcel01         CD_09_fhdbcel01         29.125G         active
         DBFS_DG_CD_10_fhdbcel01         CD_10_fhdbcel01         29.125G         active
         DBFS_DG_CD_11_fhdbcel01         CD_11_fhdbcel01         29.125G         active
         RECO_FHDB_CD_00_fhdbcel01       CD_00_fhdbcel01         12G             active
         RECO_FHDB_CD_01_fhdbcel01       CD_01_fhdbcel01         12G             active
         RECO_FHDB_CD_02_fhdbcel01       CD_02_fhdbcel01         12G             active
         RECO_FHDB_CD_03_fhdbcel01       CD_03_fhdbcel01         12G             active
         RECO_FHDB_CD_04_fhdbcel01       CD_04_fhdbcel01         12G             active
         RECO_FHDB_CD_05_fhdbcel01       CD_05_fhdbcel01         12G             active
         RECO_FHDB_CD_06_fhdbcel01       CD_06_fhdbcel01         12G             active
         RECO_FHDB_CD_07_fhdbcel01       CD_07_fhdbcel01         12G             active
         RECO_FHDB_CD_08_fhdbcel01       CD_08_fhdbcel01         12G             active
         RECO_FHDB_CD_09_fhdbcel01       CD_09_fhdbcel01         12G             active
         RECO_FHDB_CD_10_fhdbcel01       CD_10_fhdbcel01         12G             active
         RECO_FHDB_CD_11_fhdbcel01       CD_11_fhdbcel01         12G             active

Last step is to add disks back to ASM disk groups and rebalance disks.

ALTER DISKGROUP DATA_FHDB ADD DISK 'o/192.168.10.5/DATA_FHDB_CD*' rebalance power 11 NOWAIT;

Diskgroup altered.

select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN          11         11       7761     257561       9649          25

[oracle@fhdbdb01 scripts]$ ./rebalance_progress.sh 120
######################################################################
This script will monitor Phase 1 (rebalance) file by file and Phase 2
(compaction) disk by disk. Both phases should increment, showing progress.
This script will *not* estimate how long the rebalance will take.
######################################################################

Diskgroup being rebalanced is DATA_FHDB
ASM file numbers for databases start at 256.
Default check interval is 600 seconds. This run using 120 seconds...

Mon Oct  1 16:06:53 EDT 2012: PHASE 1 (of 2): Processing file 444 out of 641
Mon Oct  1 16:08:53 EDT 2012: PHASE 1 (of 2): Processing file 542 out of 641
Mon Oct  1 16:10:53 EDT 2012: PHASE 1 (of 2): Processing file 593 out of 641
Mon Oct  1 16:12:53 EDT 2012: PHASE 1 (of 2): Processing file 634 out of 641
Mon Oct  1 16:14:53 EDT 2012: PHASE 1 (of 2): Processing file 639 out of 641
*******************************************************
Mon Oct  1 16:17:23 EDT 2012: PHASE 1 (of 2) complete.
*******************************************************
Mon Oct  1 16:17:23 EDT 2012: PHASE 2 (of 2): 11 disks processed out of 84


ALTER DISKGROUP RECO_FHDB ADD DISK 'o/192.168.10.5/RECO_FHDB_CD*' rebalance power 11 NOWAIT; 

Diskgroup altered.

select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           2 REBAL RUN          11         11        918       4067       1469           2

 
-- Check the disks are added properly.
 
select name,header_status,mount_status from v$asm_disk where group_number=1 and failgroup='FHDBCEL01';

NAME                           HEADER_STATU MOUNT_S
------------------------------ ------------ -------
DATA_FHDB_CD_04_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_08_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_05_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_09_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_11_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_01_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_03_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_10_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_06_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_02_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_00_FHDBCEL01      MEMBER       CACHED
DATA_FHDB_CD_07_FHDBCEL01      MEMBER       CACHED

12 rows selected.

select name,header_status,mount_status from v$asm_disk where group_number=2 and failgroup='FHDBCEL01';

NAME                           HEADER_STATU MOUNT_S
------------------------------ ------------ -------
RECO_FHDB_CD_02_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_11_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_00_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_06_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_01_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_05_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_04_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_07_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_10_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_09_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_08_FHDBCEL01      MEMBER       CACHED
RECO_FHDB_CD_03_FHDBCEL01      MEMBER       CACHED

12 rows selected.

Repeat same steps on other cell servers.

August 20, 2012

v$cell_config

Filed under: Exadata — vishaldesai @ 7:56 pm

Using v$cell_config you can query details about Exadata cell, cell disks, grid disks, luns and physical disks.

Download Scripts

Sample output:

VDesai@db_exa > @exadata\show_cell.sql

NAME            CELLVERSION                           CPUCOUNT IPADDRESS1   KERNELVERSION             POWERSTATUS STATUS      TEMPERATUR UPTIME                                                         
--------------- ----------------------------------- ---------- ------------ ------------------------- ----------- ----------- ---------- --------------------                                           
fhdbcel01       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.5 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:16                                                  
fhdbcel02       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.6 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:17                                                  
fhdbcel03       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.7 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:17                                                  
fhdbcel04       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.8 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:17                                                  
fhdbcel05       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.9 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:17                                                  
fhdbcel06       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.1 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:17                                                  
fhdbcel07       OSS_11.2.3.1.0_LINUX.X64_120304             24 192.168.10.1 2.6.18-274.18.1.0.1.el5   normal      online      normal     36 days, 8:17                                                  

7 rows selected.

VDesai@db_exa > @exadata\show_celldisks.sql

CELLNAME             NAME            DEVICENAM DISKTYPE   FREESPACE  DISK_SIZE STATUS      INTERLEAVI ERRORCOUNT                                                                                        
-------------------- --------------- --------- --------- ---------- ---------- ----------- ---------- ----------                                                                                        
192.168.10.10        CD_00_fhdbcel06 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.10        CD_01_fhdbcel06 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.10        CD_02_fhdbcel06 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_03_fhdbcel06 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_04_fhdbcel06 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_05_fhdbcel06 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_06_fhdbcel06 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_07_fhdbcel06 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_08_fhdbcel06 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_09_fhdbcel06 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_10_fhdbcel06 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        CD_11_fhdbcel06 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.10        FD_00_fhdbcel06 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_01_fhdbcel06 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_02_fhdbcel06 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_03_fhdbcel06 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_04_fhdbcel06 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_05_fhdbcel06 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_06_fhdbcel06 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_07_fhdbcel06 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_08_fhdbcel06 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_09_fhdbcel06 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_10_fhdbcel06 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_11_fhdbcel06 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_12_fhdbcel06 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_13_fhdbcel06 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_14_fhdbcel06 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.10        FD_15_fhdbcel06 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        CD_00_fhdbcel07 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.11        CD_01_fhdbcel07 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.11        CD_02_fhdbcel07 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_03_fhdbcel07 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_04_fhdbcel07 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_05_fhdbcel07 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_06_fhdbcel07 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_07_fhdbcel07 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_08_fhdbcel07 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_09_fhdbcel07 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_10_fhdbcel07 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        CD_11_fhdbcel07 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.11        FD_00_fhdbcel07 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_01_fhdbcel07 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_02_fhdbcel07 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_03_fhdbcel07 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_04_fhdbcel07 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_05_fhdbcel07 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_06_fhdbcel07 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_07_fhdbcel07 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_08_fhdbcel07 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_09_fhdbcel07 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_10_fhdbcel07 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_11_fhdbcel07 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_12_fhdbcel07 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_13_fhdbcel07 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_14_fhdbcel07 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.11        FD_15_fhdbcel07 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         CD_00_fhdbcel01 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.5         CD_01_fhdbcel01 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.5         CD_02_fhdbcel01 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_03_fhdbcel01 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_04_fhdbcel01 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_05_fhdbcel01 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_06_fhdbcel01 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_07_fhdbcel01 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_08_fhdbcel01 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_09_fhdbcel01 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_10_fhdbcel01 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         CD_11_fhdbcel01 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.5         FD_00_fhdbcel01 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_01_fhdbcel01 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_02_fhdbcel01 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_03_fhdbcel01 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_04_fhdbcel01 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_05_fhdbcel01 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_06_fhdbcel01 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_07_fhdbcel01 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_08_fhdbcel01 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_09_fhdbcel01 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_10_fhdbcel01 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_11_fhdbcel01 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_12_fhdbcel01 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_13_fhdbcel01 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_14_fhdbcel01 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.5         FD_15_fhdbcel01 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         CD_00_fhdbcel02 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.6         CD_01_fhdbcel02 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.6         CD_02_fhdbcel02 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_03_fhdbcel02 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_04_fhdbcel02 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_05_fhdbcel02 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_06_fhdbcel02 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_07_fhdbcel02 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_08_fhdbcel02 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_09_fhdbcel02 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_10_fhdbcel02 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         CD_11_fhdbcel02 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.6         FD_00_fhdbcel02 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_01_fhdbcel02 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_02_fhdbcel02 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_03_fhdbcel02 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_04_fhdbcel02 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_05_fhdbcel02 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_06_fhdbcel02 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_07_fhdbcel02 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_08_fhdbcel02 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_09_fhdbcel02 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_10_fhdbcel02 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_11_fhdbcel02 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_12_fhdbcel02 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_13_fhdbcel02 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_14_fhdbcel02 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.6         FD_15_fhdbcel02 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         CD_00_fhdbcel03 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.7         CD_01_fhdbcel03 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.7         CD_02_fhdbcel03 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_03_fhdbcel03 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_04_fhdbcel03 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_05_fhdbcel03 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_06_fhdbcel03 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_07_fhdbcel03 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_08_fhdbcel03 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_09_fhdbcel03 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_10_fhdbcel03 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         CD_11_fhdbcel03 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.7         FD_00_fhdbcel03 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_01_fhdbcel03 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_02_fhdbcel03 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_03_fhdbcel03 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_04_fhdbcel03 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_05_fhdbcel03 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_06_fhdbcel03 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_07_fhdbcel03 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_08_fhdbcel03 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_09_fhdbcel03 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_10_fhdbcel03 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_11_fhdbcel03 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_12_fhdbcel03 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_13_fhdbcel03 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_14_fhdbcel03 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.7         FD_15_fhdbcel03 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         CD_00_fhdbcel04 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.8         CD_01_fhdbcel04 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.8         CD_02_fhdbcel04 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_03_fhdbcel04 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_04_fhdbcel04 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_05_fhdbcel04 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_06_fhdbcel04 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_07_fhdbcel04 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_08_fhdbcel04 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_09_fhdbcel04 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_10_fhdbcel04 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         CD_11_fhdbcel04 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.8         FD_00_fhdbcel04 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_01_fhdbcel04 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_02_fhdbcel04 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_03_fhdbcel04 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_04_fhdbcel04 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_05_fhdbcel04 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_06_fhdbcel04 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_07_fhdbcel04 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_08_fhdbcel04 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_09_fhdbcel04 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_10_fhdbcel04 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_11_fhdbcel04 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_12_fhdbcel04 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_13_fhdbcel04 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_14_fhdbcel04 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.8         FD_15_fhdbcel04 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         CD_00_fhdbcel05 /dev/sda  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.9         CD_01_fhdbcel05 /dev/sdb  HardDisk           0     528.73 normal      none                0                                                                                        
192.168.10.9         CD_02_fhdbcel05 /dev/sdc  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_03_fhdbcel05 /dev/sdd  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_04_fhdbcel05 /dev/sde  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_05_fhdbcel05 /dev/sdf  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_06_fhdbcel05 /dev/sdg  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_07_fhdbcel05 /dev/sdh  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_08_fhdbcel05 /dev/sdi  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_09_fhdbcel05 /dev/sdj  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_10_fhdbcel05 /dev/sdk  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         CD_11_fhdbcel05 /dev/sdl  HardDisk           0     557.86 normal      none                0                                                                                        
192.168.10.9         FD_00_fhdbcel05 /dev/sdr  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_01_fhdbcel05 /dev/sds  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_02_fhdbcel05 /dev/sdt  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_03_fhdbcel05 /dev/sdu  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_04_fhdbcel05 /dev/sdz  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_05_fhdbcel05 /dev/sdaa FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_06_fhdbcel05 /dev/sdab FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_07_fhdbcel05 /dev/sdac FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_08_fhdbcel05 /dev/sdn  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_09_fhdbcel05 /dev/sdo  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_10_fhdbcel05 /dev/sdp  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_11_fhdbcel05 /dev/sdq  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_12_fhdbcel05 /dev/sdv  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_13_fhdbcel05 /dev/sdw  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_14_fhdbcel05 /dev/sdx  FlashDisk          0      22.88 normal      none                0                                                                                        
192.168.10.9         FD_15_fhdbcel05 /dev/sdy  FlashDisk          0      22.88 normal      none                0                                                                                        

196 rows selected.

VDesai@db_exa > @exadata\show_griddisks.sql

CELLNAME        NAME                           ASMDISKGRO AVAILABLET  DISK_SIZE CELLDISK             DISKTYPE             ERRORCOUNT                                                                    
--------------- ------------------------------ ---------- ---------- ---------- -------------------- -------------------- ----------                                                                    
192.168.10.5    DATA_FHDB_CD_00_fhdbcel01      DATA_FHDB                    212 CD_00_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_01_fhdbcel01      DATA_FHDB                    212 CD_01_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_02_fhdbcel01      DATA_FHDB                    212 CD_02_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_03_fhdbcel01      DATA_FHDB                    212 CD_03_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_04_fhdbcel01      DATA_FHDB                    212 CD_04_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_05_fhdbcel01      DATA_FHDB                    212 CD_05_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_06_fhdbcel01      DATA_FHDB                    212 CD_06_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_07_fhdbcel01      DATA_FHDB                    212 CD_07_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_08_fhdbcel01      DATA_FHDB                    212 CD_08_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_09_fhdbcel01      DATA_FHDB                    212 CD_09_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_10_fhdbcel01      DATA_FHDB                    212 CD_10_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DATA_FHDB_CD_11_fhdbcel01      DATA_FHDB                    212 CD_11_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_02_fhdbcel01        DBFS_DG                    29.13 CD_02_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_03_fhdbcel01        DBFS_DG                    29.13 CD_03_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_04_fhdbcel01        DBFS_DG                    29.13 CD_04_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_05_fhdbcel01        DBFS_DG                    29.13 CD_05_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_06_fhdbcel01        DBFS_DG                    29.13 CD_06_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_07_fhdbcel01        DBFS_DG                    29.13 CD_07_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_08_fhdbcel01        DBFS_DG                    29.13 CD_08_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_09_fhdbcel01        DBFS_DG                    29.13 CD_09_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_10_fhdbcel01        DBFS_DG                    29.13 CD_10_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    DBFS_DG_CD_11_fhdbcel01        DBFS_DG                    29.13 CD_11_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_00_fhdbcel01      RECO_FHDB                 316.69 CD_00_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_01_fhdbcel01      RECO_FHDB                 316.69 CD_01_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_02_fhdbcel01      RECO_FHDB                 316.69 CD_02_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_03_fhdbcel01      RECO_FHDB                 316.69 CD_03_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_04_fhdbcel01      RECO_FHDB                 316.69 CD_04_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_05_fhdbcel01      RECO_FHDB                 316.69 CD_05_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_06_fhdbcel01      RECO_FHDB                 316.69 CD_06_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_07_fhdbcel01      RECO_FHDB                 316.69 CD_07_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_08_fhdbcel01      RECO_FHDB                 316.69 CD_08_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_09_fhdbcel01      RECO_FHDB                 316.69 CD_09_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_10_fhdbcel01      RECO_FHDB                 316.69 CD_10_fhdbcel01      HardDisk                      0                                                                    
192.168.10.5    RECO_FHDB_CD_11_fhdbcel01      RECO_FHDB                 316.69 CD_11_fhdbcel01      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_00_fhdbcel02      DATA_FHDB                    212 CD_00_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_01_fhdbcel02      DATA_FHDB                    212 CD_01_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_02_fhdbcel02      DATA_FHDB                    212 CD_02_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_03_fhdbcel02      DATA_FHDB                    212 CD_03_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_04_fhdbcel02      DATA_FHDB                    212 CD_04_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_05_fhdbcel02      DATA_FHDB                    212 CD_05_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_06_fhdbcel02      DATA_FHDB                    212 CD_06_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_07_fhdbcel02      DATA_FHDB                    212 CD_07_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_08_fhdbcel02      DATA_FHDB                    212 CD_08_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_09_fhdbcel02      DATA_FHDB                    212 CD_09_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_10_fhdbcel02      DATA_FHDB                    212 CD_10_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DATA_FHDB_CD_11_fhdbcel02      DATA_FHDB                    212 CD_11_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_02_fhdbcel02        DBFS_DG                    29.13 CD_02_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_03_fhdbcel02        DBFS_DG                    29.13 CD_03_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_04_fhdbcel02        DBFS_DG                    29.13 CD_04_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_05_fhdbcel02        DBFS_DG                    29.13 CD_05_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_06_fhdbcel02        DBFS_DG                    29.13 CD_06_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_07_fhdbcel02        DBFS_DG                    29.13 CD_07_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_08_fhdbcel02        DBFS_DG                    29.13 CD_08_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_09_fhdbcel02        DBFS_DG                    29.13 CD_09_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_10_fhdbcel02        DBFS_DG                    29.13 CD_10_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    DBFS_DG_CD_11_fhdbcel02        DBFS_DG                    29.13 CD_11_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_00_fhdbcel02      RECO_FHDB                 316.69 CD_00_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_01_fhdbcel02      RECO_FHDB                 316.69 CD_01_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_02_fhdbcel02      RECO_FHDB                 316.69 CD_02_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_03_fhdbcel02      RECO_FHDB                 316.69 CD_03_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_04_fhdbcel02      RECO_FHDB                 316.69 CD_04_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_05_fhdbcel02      RECO_FHDB                 316.69 CD_05_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_06_fhdbcel02      RECO_FHDB                 316.69 CD_06_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_07_fhdbcel02      RECO_FHDB                 316.69 CD_07_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_08_fhdbcel02      RECO_FHDB                 316.69 CD_08_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_09_fhdbcel02      RECO_FHDB                 316.69 CD_09_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_10_fhdbcel02      RECO_FHDB                 316.69 CD_10_fhdbcel02      HardDisk                      0                                                                    
192.168.10.6    RECO_FHDB_CD_11_fhdbcel02      RECO_FHDB                 316.69 CD_11_fhdbcel02      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_00_fhdbcel03      DATA_FHDB                    212 CD_00_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_01_fhdbcel03      DATA_FHDB                    212 CD_01_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_02_fhdbcel03      DATA_FHDB                    212 CD_02_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_03_fhdbcel03      DATA_FHDB                    212 CD_03_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_04_fhdbcel03      DATA_FHDB                    212 CD_04_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_05_fhdbcel03      DATA_FHDB                    212 CD_05_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_06_fhdbcel03      DATA_FHDB                    212 CD_06_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_07_fhdbcel03      DATA_FHDB                    212 CD_07_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_08_fhdbcel03      DATA_FHDB                    212 CD_08_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_09_fhdbcel03      DATA_FHDB                    212 CD_09_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_10_fhdbcel03      DATA_FHDB                    212 CD_10_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DATA_FHDB_CD_11_fhdbcel03      DATA_FHDB                    212 CD_11_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_02_fhdbcel03        DBFS_DG                    29.13 CD_02_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_03_fhdbcel03        DBFS_DG                    29.13 CD_03_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_04_fhdbcel03        DBFS_DG                    29.13 CD_04_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_05_fhdbcel03        DBFS_DG                    29.13 CD_05_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_06_fhdbcel03        DBFS_DG                    29.13 CD_06_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_07_fhdbcel03        DBFS_DG                    29.13 CD_07_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_08_fhdbcel03        DBFS_DG                    29.13 CD_08_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_09_fhdbcel03        DBFS_DG                    29.13 CD_09_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_10_fhdbcel03        DBFS_DG                    29.13 CD_10_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    DBFS_DG_CD_11_fhdbcel03        DBFS_DG                    29.13 CD_11_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_00_fhdbcel03      RECO_FHDB                 316.69 CD_00_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_01_fhdbcel03      RECO_FHDB                 316.69 CD_01_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_02_fhdbcel03      RECO_FHDB                 316.69 CD_02_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_03_fhdbcel03      RECO_FHDB                 316.69 CD_03_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_04_fhdbcel03      RECO_FHDB                 316.69 CD_04_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_05_fhdbcel03      RECO_FHDB                 316.69 CD_05_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_06_fhdbcel03      RECO_FHDB                 316.69 CD_06_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_07_fhdbcel03      RECO_FHDB                 316.69 CD_07_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_08_fhdbcel03      RECO_FHDB                 316.69 CD_08_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_09_fhdbcel03      RECO_FHDB                 316.69 CD_09_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_10_fhdbcel03      RECO_FHDB                 316.69 CD_10_fhdbcel03      HardDisk                      0                                                                    
192.168.10.7    RECO_FHDB_CD_11_fhdbcel03      RECO_FHDB                 316.69 CD_11_fhdbcel03      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_00_fhdbcel04      DATA_FHDB                    212 CD_00_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_01_fhdbcel04      DATA_FHDB                    212 CD_01_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_02_fhdbcel04      DATA_FHDB                    212 CD_02_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_03_fhdbcel04      DATA_FHDB                    212 CD_03_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_04_fhdbcel04      DATA_FHDB                    212 CD_04_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_05_fhdbcel04      DATA_FHDB                    212 CD_05_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_06_fhdbcel04      DATA_FHDB                    212 CD_06_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_07_fhdbcel04      DATA_FHDB                    212 CD_07_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_08_fhdbcel04      DATA_FHDB                    212 CD_08_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_09_fhdbcel04      DATA_FHDB                    212 CD_09_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_10_fhdbcel04      DATA_FHDB                    212 CD_10_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DATA_FHDB_CD_11_fhdbcel04      DATA_FHDB                    212 CD_11_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_02_fhdbcel04        DBFS_DG                    29.13 CD_02_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_03_fhdbcel04        DBFS_DG                    29.13 CD_03_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_04_fhdbcel04        DBFS_DG                    29.13 CD_04_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_05_fhdbcel04        DBFS_DG                    29.13 CD_05_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_06_fhdbcel04        DBFS_DG                    29.13 CD_06_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_07_fhdbcel04        DBFS_DG                    29.13 CD_07_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_08_fhdbcel04        DBFS_DG                    29.13 CD_08_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_09_fhdbcel04        DBFS_DG                    29.13 CD_09_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_10_fhdbcel04        DBFS_DG                    29.13 CD_10_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    DBFS_DG_CD_11_fhdbcel04        DBFS_DG                    29.13 CD_11_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_00_fhdbcel04      RECO_FHDB                 316.69 CD_00_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_01_fhdbcel04      RECO_FHDB                 316.69 CD_01_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_02_fhdbcel04      RECO_FHDB                 316.69 CD_02_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_03_fhdbcel04      RECO_FHDB                 316.69 CD_03_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_04_fhdbcel04      RECO_FHDB                 316.69 CD_04_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_05_fhdbcel04      RECO_FHDB                 316.69 CD_05_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_06_fhdbcel04      RECO_FHDB                 316.69 CD_06_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_07_fhdbcel04      RECO_FHDB                 316.69 CD_07_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_08_fhdbcel04      RECO_FHDB                 316.69 CD_08_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_09_fhdbcel04      RECO_FHDB                 316.69 CD_09_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_10_fhdbcel04      RECO_FHDB                 316.69 CD_10_fhdbcel04      HardDisk                      0                                                                    
192.168.10.8    RECO_FHDB_CD_11_fhdbcel04      RECO_FHDB                 316.69 CD_11_fhdbcel04      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_00_fhdbcel05      DATA_FHDB                    212 CD_00_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_01_fhdbcel05      DATA_FHDB                    212 CD_01_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_02_fhdbcel05      DATA_FHDB                    212 CD_02_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_03_fhdbcel05      DATA_FHDB                    212 CD_03_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_04_fhdbcel05      DATA_FHDB                    212 CD_04_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_05_fhdbcel05      DATA_FHDB                    212 CD_05_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_06_fhdbcel05      DATA_FHDB                    212 CD_06_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_07_fhdbcel05      DATA_FHDB                    212 CD_07_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_08_fhdbcel05      DATA_FHDB                    212 CD_08_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_09_fhdbcel05      DATA_FHDB                    212 CD_09_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_10_fhdbcel05      DATA_FHDB                    212 CD_10_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DATA_FHDB_CD_11_fhdbcel05      DATA_FHDB                    212 CD_11_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_02_fhdbcel05        DBFS_DG                    29.13 CD_02_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_03_fhdbcel05        DBFS_DG                    29.13 CD_03_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_04_fhdbcel05        DBFS_DG                    29.13 CD_04_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_05_fhdbcel05        DBFS_DG                    29.13 CD_05_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_06_fhdbcel05        DBFS_DG                    29.13 CD_06_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_07_fhdbcel05        DBFS_DG                    29.13 CD_07_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_08_fhdbcel05        DBFS_DG                    29.13 CD_08_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_09_fhdbcel05        DBFS_DG                    29.13 CD_09_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_10_fhdbcel05        DBFS_DG                    29.13 CD_10_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    DBFS_DG_CD_11_fhdbcel05        DBFS_DG                    29.13 CD_11_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_00_fhdbcel05      RECO_FHDB                 316.69 CD_00_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_01_fhdbcel05      RECO_FHDB                 316.69 CD_01_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_02_fhdbcel05      RECO_FHDB                 316.69 CD_02_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_03_fhdbcel05      RECO_FHDB                 316.69 CD_03_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_04_fhdbcel05      RECO_FHDB                 316.69 CD_04_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_05_fhdbcel05      RECO_FHDB                 316.69 CD_05_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_06_fhdbcel05      RECO_FHDB                 316.69 CD_06_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_07_fhdbcel05      RECO_FHDB                 316.69 CD_07_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_08_fhdbcel05      RECO_FHDB                 316.69 CD_08_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_09_fhdbcel05      RECO_FHDB                 316.69 CD_09_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_10_fhdbcel05      RECO_FHDB                 316.69 CD_10_fhdbcel05      HardDisk                      0                                                                    
192.168.10.9    RECO_FHDB_CD_11_fhdbcel05      RECO_FHDB                 316.69 CD_11_fhdbcel05      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_00_fhdbcel06      DATA_FHDB                    212 CD_00_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_01_fhdbcel06      DATA_FHDB                    212 CD_01_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_02_fhdbcel06      DATA_FHDB                    212 CD_02_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_03_fhdbcel06      DATA_FHDB                    212 CD_03_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_04_fhdbcel06      DATA_FHDB                    212 CD_04_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_05_fhdbcel06      DATA_FHDB                    212 CD_05_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_06_fhdbcel06      DATA_FHDB                    212 CD_06_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_07_fhdbcel06      DATA_FHDB                    212 CD_07_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_08_fhdbcel06      DATA_FHDB                    212 CD_08_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_09_fhdbcel06      DATA_FHDB                    212 CD_09_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_10_fhdbcel06      DATA_FHDB                    212 CD_10_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DATA_FHDB_CD_11_fhdbcel06      DATA_FHDB                    212 CD_11_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_02_fhdbcel06        DBFS_DG                    29.13 CD_02_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_03_fhdbcel06        DBFS_DG                    29.13 CD_03_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_04_fhdbcel06        DBFS_DG                    29.13 CD_04_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_05_fhdbcel06        DBFS_DG                    29.13 CD_05_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_06_fhdbcel06        DBFS_DG                    29.13 CD_06_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_07_fhdbcel06        DBFS_DG                    29.13 CD_07_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_08_fhdbcel06        DBFS_DG                    29.13 CD_08_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_09_fhdbcel06        DBFS_DG                    29.13 CD_09_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_10_fhdbcel06        DBFS_DG                    29.13 CD_10_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   DBFS_DG_CD_11_fhdbcel06        DBFS_DG                    29.13 CD_11_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_00_fhdbcel06      RECO_FHDB                 316.69 CD_00_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_01_fhdbcel06      RECO_FHDB                 316.69 CD_01_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_02_fhdbcel06      RECO_FHDB                 316.69 CD_02_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_03_fhdbcel06      RECO_FHDB                 316.69 CD_03_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_04_fhdbcel06      RECO_FHDB                 316.69 CD_04_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_05_fhdbcel06      RECO_FHDB                 316.69 CD_05_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_06_fhdbcel06      RECO_FHDB                 316.69 CD_06_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_07_fhdbcel06      RECO_FHDB                 316.69 CD_07_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_08_fhdbcel06      RECO_FHDB                 316.69 CD_08_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_09_fhdbcel06      RECO_FHDB                 316.69 CD_09_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_10_fhdbcel06      RECO_FHDB                 316.69 CD_10_fhdbcel06      HardDisk                      0                                                                    
192.168.10.10   RECO_FHDB_CD_11_fhdbcel06      RECO_FHDB                 316.69 CD_11_fhdbcel06      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_00_fhdbcel07      DATA_FHDB                    212 CD_00_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_01_fhdbcel07      DATA_FHDB                    212 CD_01_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_02_fhdbcel07      DATA_FHDB                    212 CD_02_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_03_fhdbcel07      DATA_FHDB                    212 CD_03_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_04_fhdbcel07      DATA_FHDB                    212 CD_04_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_05_fhdbcel07      DATA_FHDB                    212 CD_05_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_06_fhdbcel07      DATA_FHDB                    212 CD_06_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_07_fhdbcel07      DATA_FHDB                    212 CD_07_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_08_fhdbcel07      DATA_FHDB                    212 CD_08_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_09_fhdbcel07      DATA_FHDB                    212 CD_09_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_10_fhdbcel07      DATA_FHDB                    212 CD_10_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DATA_FHDB_CD_11_fhdbcel07      DATA_FHDB                    212 CD_11_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_02_fhdbcel07        DBFS_DG                    29.13 CD_02_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_03_fhdbcel07        DBFS_DG                    29.13 CD_03_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_04_fhdbcel07        DBFS_DG                    29.13 CD_04_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_05_fhdbcel07        DBFS_DG                    29.13 CD_05_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_06_fhdbcel07        DBFS_DG                    29.13 CD_06_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_07_fhdbcel07        DBFS_DG                    29.13 CD_07_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_08_fhdbcel07        DBFS_DG                    29.13 CD_08_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_09_fhdbcel07        DBFS_DG                    29.13 CD_09_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_10_fhdbcel07        DBFS_DG                    29.13 CD_10_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   DBFS_DG_CD_11_fhdbcel07        DBFS_DG                    29.13 CD_11_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_00_fhdbcel07      RECO_FHDB                 316.69 CD_00_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_01_fhdbcel07      RECO_FHDB                 316.69 CD_01_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_02_fhdbcel07      RECO_FHDB                 316.69 CD_02_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_03_fhdbcel07      RECO_FHDB                 316.69 CD_03_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_04_fhdbcel07      RECO_FHDB                 316.69 CD_04_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_05_fhdbcel07      RECO_FHDB                 316.69 CD_05_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_06_fhdbcel07      RECO_FHDB                 316.69 CD_06_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_07_fhdbcel07      RECO_FHDB                 316.69 CD_07_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_08_fhdbcel07      RECO_FHDB                 316.69 CD_08_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_09_fhdbcel07      RECO_FHDB                 316.69 CD_09_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_10_fhdbcel07      RECO_FHDB                 316.69 CD_10_fhdbcel07      HardDisk                      0                                                                    
192.168.10.11   RECO_FHDB_CD_11_fhdbcel07      RECO_FHDB                 316.69 CD_11_fhdbcel07      HardDisk                      0                                                                    

238 rows selected.

VDesai@db_exa > @exadata\show_lun.sql

CELLNAME        NAME                 CELLDISK             DEVICENAME           ISSYSTEMLU    LUNSIZE DISKTYPE     STATUS      LUNWRITECACHEMODE                                                         
--------------- -------------------- -------------------- -------------------- ---------- ---------- ------------ ----------- ----------------------------------------------------------------------    
192.168.10.10   0_0                  CD_00_fhdbcel06      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_1                  CD_01_fhdbcel06      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_10                 CD_10_fhdbcel06      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_11                 CD_11_fhdbcel06      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_2                  CD_02_fhdbcel06      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_3                  CD_03_fhdbcel06      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_4                  CD_04_fhdbcel06      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_5                  CD_05_fhdbcel06      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_6                  CD_06_fhdbcel06      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_7                  CD_07_fhdbcel06      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_8                  CD_08_fhdbcel06      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   0_9                  CD_09_fhdbcel06      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.10   1_0                  FD_00_fhdbcel06      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   1_1                  FD_01_fhdbcel06      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   1_2                  FD_02_fhdbcel06      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   1_3                  FD_03_fhdbcel06      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   2_0                  FD_04_fhdbcel06      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   2_1                  FD_05_fhdbcel06      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.10   2_2                  FD_06_fhdbcel06      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.10   2_3                  FD_07_fhdbcel06      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.10   4_0                  FD_08_fhdbcel06      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   4_1                  FD_09_fhdbcel06      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   4_2                  FD_10_fhdbcel06      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   4_3                  FD_11_fhdbcel06      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   5_0                  FD_12_fhdbcel06      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   5_1                  FD_13_fhdbcel06      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   5_2                  FD_14_fhdbcel06      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.10   5_3                  FD_15_fhdbcel06      /dev/sdy             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   0_0                  CD_00_fhdbcel07      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_1                  CD_01_fhdbcel07      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_10                 CD_10_fhdbcel07      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_11                 CD_11_fhdbcel07      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_2                  CD_02_fhdbcel07      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_3                  CD_03_fhdbcel07      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_4                  CD_04_fhdbcel07      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_5                  CD_05_fhdbcel07      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_6                  CD_06_fhdbcel07      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_7                  CD_07_fhdbcel07      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_8                  CD_08_fhdbcel07      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   0_9                  CD_09_fhdbcel07      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.11   1_0                  FD_00_fhdbcel07      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   1_1                  FD_01_fhdbcel07      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   1_2                  FD_02_fhdbcel07      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   1_3                  FD_03_fhdbcel07      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   2_0                  FD_04_fhdbcel07      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   2_1                  FD_05_fhdbcel07      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.11   2_2                  FD_06_fhdbcel07      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.11   2_3                  FD_07_fhdbcel07      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.11   4_0                  FD_08_fhdbcel07      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   4_1                  FD_09_fhdbcel07      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   4_2                  FD_10_fhdbcel07      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   4_3                  FD_11_fhdbcel07      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   5_0                  FD_12_fhdbcel07      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   5_1                  FD_13_fhdbcel07      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   5_2                  FD_14_fhdbcel07      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.11   5_3                  FD_15_fhdbcel07      /dev/sdy             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    0_0                  CD_00_fhdbcel01      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_1                  CD_01_fhdbcel01      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_10                 CD_10_fhdbcel01      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_11                 CD_11_fhdbcel01      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_2                  CD_02_fhdbcel01      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_3                  CD_03_fhdbcel01      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_4                  CD_04_fhdbcel01      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_5                  CD_05_fhdbcel01      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_6                  CD_06_fhdbcel01      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_7                  CD_07_fhdbcel01      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_8                  CD_08_fhdbcel01      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    0_9                  CD_09_fhdbcel01      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.5    1_0                  FD_00_fhdbcel01      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    1_1                  FD_01_fhdbcel01      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    1_2                  FD_02_fhdbcel01      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    1_3                  FD_03_fhdbcel01      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    2_0                  FD_04_fhdbcel01      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    2_1                  FD_05_fhdbcel01      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.5    2_2                  FD_06_fhdbcel01      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.5    2_3                  FD_07_fhdbcel01      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.5    4_0                  FD_08_fhdbcel01      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    4_1                  FD_09_fhdbcel01      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    4_2                  FD_10_fhdbcel01      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    4_3                  FD_11_fhdbcel01      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    5_0                  FD_12_fhdbcel01      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    5_1                  FD_13_fhdbcel01      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    5_2                  FD_14_fhdbcel01      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.5    5_3                  FD_15_fhdbcel01      /dev/sdy             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    0_0                  CD_00_fhdbcel02      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_1                  CD_01_fhdbcel02      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_10                 CD_10_fhdbcel02      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_11                 CD_11_fhdbcel02      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_2                  CD_02_fhdbcel02      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_3                  CD_03_fhdbcel02      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_4                  CD_04_fhdbcel02      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_5                  CD_05_fhdbcel02      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_6                  CD_06_fhdbcel02      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_7                  CD_07_fhdbcel02      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_8                  CD_08_fhdbcel02      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    0_9                  CD_09_fhdbcel02      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.6    1_0                  FD_00_fhdbcel02      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    1_1                  FD_01_fhdbcel02      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    1_2                  FD_02_fhdbcel02      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    1_3                  FD_03_fhdbcel02      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    2_0                  FD_04_fhdbcel02      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    2_1                  FD_05_fhdbcel02      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.6    2_2                  FD_06_fhdbcel02      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.6    2_3                  FD_07_fhdbcel02      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.6    4_0                  FD_08_fhdbcel02      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    4_1                  FD_09_fhdbcel02      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    4_2                  FD_10_fhdbcel02      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    4_3                  FD_11_fhdbcel02      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    5_0                  FD_12_fhdbcel02      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    5_1                  FD_13_fhdbcel02      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    5_2                  FD_14_fhdbcel02      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.6    5_3                  FD_15_fhdbcel02      /dev/sdy             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    0_0                  CD_00_fhdbcel03      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_1                  CD_01_fhdbcel03      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_10                 CD_10_fhdbcel03      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_11                 CD_11_fhdbcel03      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_2                  CD_02_fhdbcel03      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_3                  CD_03_fhdbcel03      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_4                  CD_04_fhdbcel03      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_5                  CD_05_fhdbcel03      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_6                  CD_06_fhdbcel03      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_7                  CD_07_fhdbcel03      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_8                  CD_08_fhdbcel03      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    0_9                  CD_09_fhdbcel03      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.7    1_0                  FD_00_fhdbcel03      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    1_1                  FD_01_fhdbcel03      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    1_2                  FD_02_fhdbcel03      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    1_3                  FD_03_fhdbcel03      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    2_0                  FD_04_fhdbcel03      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    2_1                  FD_05_fhdbcel03      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.7    2_2                  FD_06_fhdbcel03      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.7    2_3                  FD_07_fhdbcel03      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.7    4_0                  FD_08_fhdbcel03      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    4_1                  FD_09_fhdbcel03      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    4_2                  FD_10_fhdbcel03      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    4_3                  FD_11_fhdbcel03      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    5_0                  FD_12_fhdbcel03      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    5_1                  FD_13_fhdbcel03      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    5_2                  FD_14_fhdbcel03      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.7    5_3                  FD_15_fhdbcel03      /dev/sdy             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    0_0                  CD_00_fhdbcel04      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_1                  CD_01_fhdbcel04      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_10                 CD_10_fhdbcel04      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_11                 CD_11_fhdbcel04      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_2                  CD_02_fhdbcel04      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_3                  CD_03_fhdbcel04      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_4                  CD_04_fhdbcel04      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_5                  CD_05_fhdbcel04      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_6                  CD_06_fhdbcel04      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_7                  CD_07_fhdbcel04      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_8                  CD_08_fhdbcel04      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    0_9                  CD_09_fhdbcel04      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.8    1_0                  FD_00_fhdbcel04      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    1_1                  FD_01_fhdbcel04      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    1_2                  FD_02_fhdbcel04      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    1_3                  FD_03_fhdbcel04      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    2_0                  FD_04_fhdbcel04      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    2_1                  FD_05_fhdbcel04      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.8    2_2                  FD_06_fhdbcel04      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.8    2_3                  FD_07_fhdbcel04      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.8    4_0                  FD_08_fhdbcel04      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    4_1                  FD_09_fhdbcel04      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    4_2                  FD_10_fhdbcel04      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    4_3                  FD_11_fhdbcel04      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    5_0                  FD_12_fhdbcel04      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    5_1                  FD_13_fhdbcel04      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    5_2                  FD_14_fhdbcel04      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.8    5_3                  FD_15_fhdbcel04      /dev/sdy             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    0_0                  CD_00_fhdbcel05      /dev/sda             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_1                  CD_01_fhdbcel05      /dev/sdb             TRUE                  HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_10                 CD_10_fhdbcel05      /dev/sdk             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_11                 CD_11_fhdbcel05      /dev/sdl             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_2                  CD_02_fhdbcel05      /dev/sdc             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_3                  CD_03_fhdbcel05      /dev/sdd             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_4                  CD_04_fhdbcel05      /dev/sde             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_5                  CD_05_fhdbcel05      /dev/sdf             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_6                  CD_06_fhdbcel05      /dev/sdg             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_7                  CD_07_fhdbcel05      /dev/sdh             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_8                  CD_08_fhdbcel05      /dev/sdi             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    0_9                  CD_09_fhdbcel05      /dev/sdj             FALSE                 HardDisk     normal      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"             
192.168.10.9    1_0                  FD_00_fhdbcel05      /dev/sdr             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    1_1                  FD_01_fhdbcel05      /dev/sds             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    1_2                  FD_02_fhdbcel05      /dev/sdt             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    1_3                  FD_03_fhdbcel05      /dev/sdu             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    2_0                  FD_04_fhdbcel05      /dev/sdz             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    2_1                  FD_05_fhdbcel05      /dev/sdaa            FALSE                 FlashDisk    normal                                                                                
192.168.10.9    2_2                  FD_06_fhdbcel05      /dev/sdab            FALSE                 FlashDisk    normal                                                                                
192.168.10.9    2_3                  FD_07_fhdbcel05      /dev/sdac            FALSE                 FlashDisk    normal                                                                                
192.168.10.9    4_0                  FD_08_fhdbcel05      /dev/sdn             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    4_1                  FD_09_fhdbcel05      /dev/sdo             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    4_2                  FD_10_fhdbcel05      /dev/sdp             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    4_3                  FD_11_fhdbcel05      /dev/sdq             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    5_0                  FD_12_fhdbcel05      /dev/sdv             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    5_1                  FD_13_fhdbcel05      /dev/sdw             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    5_2                  FD_14_fhdbcel05      /dev/sdx             FALSE                 FlashDisk    normal                                                                                
192.168.10.9    5_3                  FD_15_fhdbcel05      /dev/sdy             FALSE                 FlashDisk    normal                                                                                

196 rows selected.

VDesai@db_exa > @exadata\show_physicaldisk.sql

CELLNAME             NAME                 LUNS  STATUS      DISKTYPE     PHYSICALSIZE_GB PHYSI ERRMEDIACOUNT ERROTHERCOUNT                                                                              
-------------------- -------------------- ----- ----------- ------------ --------------- ----- ------------- -------------                                                                              
192.168.10.10        20:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        20:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.10        FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.10        FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                
192.168.10.11        20:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        20:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.11        FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.11        FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                
192.168.10.5         20:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         20:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.5         FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.5         FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                
192.168.10.6         20:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         20:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.6         FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.6         FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                
192.168.10.7         28:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         28:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.7         FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.7         FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                
192.168.10.8         20:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         20:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.8         FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.8         FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                
192.168.10.9         20:0                 0_0   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:1                 0_1   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:10                0_10  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:11                0_11  normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:2                 0_2   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:3                 0_3   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:4                 0_4   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:5                 0_5   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:6                 0_6   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:7                 0_7   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:8                 0_8   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         20:9                 0_9   normal      HardDisk                 559 sas               0             0                                                                              
192.168.10.9         FLASH_1_0            1_0   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_1_1            1_1   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_1_2            1_2   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_1_3            1_3   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_2_0            2_0   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_2_1            2_1   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_2_2            2_2   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_2_3            2_3   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_4_0            4_0   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_4_1            4_1   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_4_2            4_2   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_4_3            4_3   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_5_0            5_0   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_5_1            5_1   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_5_2            5_2   normal      FlashDisk                 23                                                                                                                
192.168.10.9         FLASH_5_3            5_3   normal      FlashDisk                 23                                                                                                                

196 rows selected.



August 7, 2012

Exadata – Storage Index column limitation and optimization

Filed under: Exadata — vishaldesai @ 9:11 pm

I have been working on health care databases for past couple of years and claims table  usually has large number of fields (indicators) such as ICD1, ICD2….ICDn. BA team runs queries against this indicators. Oracle Expert Exadata states that “A further limitation is that storage indexes may contain only eight columns. They are created and maintained for eight-columns per table”. With such limitation I was curious how queries with ICD indicators will work on Exadata especially for predicates ICD9, ICD10 and so on.

Test Case Scripts:

drop table t1 purge;
drop sequence s1;

create sequence s1 start with 1 cache 100;

create table t1 tablespace data 
as
select /*+ parallel (a,16) */
        s1.nextval as id
      , case when mod(rownum,100) = 1   then null else rownum end as col1
      , dbms_random.string('A',50) as filler1
      , case when mod(rownum,100) = 2   then null else rownum end as col2
      , dbms_random.string('A',50) as filler2
      , case when mod(rownum,100) = 3   then null else rownum end as col3
      , dbms_random.string('A',50) as filler3
      , case when mod(rownum,100) = 4   then null else rownum end as col4
      , dbms_random.string('A',50) as filler4
      , case when mod(rownum,100) = 5   then null else rownum end as col5
      , dbms_random.string('A',50) as filler5
      , case when mod(rownum,100) = 6   then null else rownum end as col6
      , dbms_random.string('A',50) as filler6
      , case when mod(rownum,100) = 7   then null else rownum end as col7
      , dbms_random.string('A',50) as filler7
      , case when mod(rownum,100) = 8   then null else rownum end as col8
      , dbms_random.string('A',50) as filler8
      , case when mod(rownum,100) = 9   then null else rownum end as col9
      , dbms_random.string('A',50) as filler9
      , case when mod(rownum,100) = 10  then null else rownum end as col10
      , dbms_random.string('A',50) as filler10
      , case when mod(rownum,100) = 11  then null else rownum end as col11
      , dbms_random.string('A',50) as filler11
      , case when mod(rownum,100) = 12  then null else rownum end as col12
      , dbms_random.string('A',50) as filler12
      , case when mod(rownum,100) = 13  then null else rownum end as col13
      , dbms_random.string('A',50) as filler13
      , case when mod(rownum,100) = 14  then null else rownum end as col14
      , dbms_random.string('A',50) as filler14
      , case when mod(rownum,100) = 15  then null else rownum end as col15
      , dbms_random.string('A',50) as filler15
      , case when mod(rownum,100) = 16  then null else rownum end as col16
      , dbms_random.string('A',50) as filler16
from
        dual a
connect by
        level <= 1000000;

alter table t1 storage (cell_flash_cache none);

alter session enable parallel dml;
begin
for i in 1..63 loop
insert /*+ append parallel (s,4) */ into t1 s select /* parallel (t,4) */ s1.nextval,
col1,filler1,col2,filler2,col3,filler3,col4,filler4,col5,filler5,
col6,filler6,col7,filler7,col8,filler8,col9,filler9,col10,filler10,
col11,filler11,col12,filler12,col13,filler13,col14,filler14,col15,filler15,
col16,filler16  from t1 t;
commit;
end loop;
end;
/

exec dbms_stats.gather_table_stats(null, 't1',estimate_percent=>100,degree=>16);

col1,col2…col16 are indicators and filler1,filler2…filler16 are for another test case. cell_flash_cache is set to none to make sure data is retrieved from physical disks and not flash cache.

Table Stats are as shown below  and Storage cells were restarted to make sure nothing is stored in flash cache.

vdesai@exadata > @statistics T1

Database: csprod2; Instance: csprod22; ORACLE Release: 11.2.0.3.0; Platform: x86_64/Linux 2.4.xx; Host name: fhdbdb02.fairhe

Table Owner                              Number          Blocks          Blocks        Empty Average Chain Average Global
and Name                                of Rows    (Statistics)       (Segment)       Blocks   Space Count Row Len Stats
------------------------------ ---------------- --------------- --------------- ------------ ------- ----- ------- ------
VDESAI . T1                          64,000,000       9,066,568       9,066,568            0       0     0     902 YES    

                               Block-            Moni-      Modifi- Buffer                 IOT
Tablespace                     size    Size (MB) toring cations (%) Pool    Degree Cluster Type    IOT Name
------------------------------ ------- --------- ------ ----------- ------- ------ ------- ------- -------------------------
DATA                           8 KB    70832.6   YES                DEFAULT 1

                               Column                              Distinct           Number             Number Global User     
Column Name                    Details                               Values  Density Buckets              Nulls Stats  Stats
------------------------------ ------------------------ ------------------- -------- ------- ------------------ ------ -----
ID                             NUMBER(38)                        64,000,000    .0000       1                  0 YES    NO   
COL1                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER1                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL2                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER2                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL3                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER3                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL4                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER4                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL5                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER5                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL6                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER6                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL7                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER7                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL8                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER8                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL9                           NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER9                        VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL10                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER10                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL11                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER11                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL12                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER12                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL13                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER13                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL14                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER14                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL15                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER15                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
COL16                          NUMBER                               990,000    .0000       1            640,000 YES    NO   
FILLER16                       VARCHAR2(4000)                     1,000,000    .0000       1                  0 YES    NO   
vdesai@exadata >

vdesai@exadata > select object_id from user_objects where object_name='T1';

 OBJECT_ID
----------
     81512

vdesai@exadata > select bytes/1024/1024  from user_segments where segment_name='T1';

BYTES/1024/1024
---------------
          70832

[root@fhdbcel01 ~]# dcli -g all_cells -l root "cellcli -e list flashcachecontent attributes dbid,objectnumber,cachedsize where objectnumber=81512"
fhdbcel03: 2215964125    81512   8192
fhdbcel05: 2215964125    81512   966656

[root@fhdbcel01 ~]# dcli -c fhdbcel01 -l root "cellcli -e alter cell restart services all"                                             
fhdbcel01:
fhdbcel01: Stopping the RS, CELLSRV, and MS services...
fhdbcel01: The SHUTDOWN of services was successful.
fhdbcel01: Starting the RS, CELLSRV, and MS services...
fhdbcel01: Getting the state of RS services...
fhdbcel01: running
fhdbcel01: Starting CELLSRV services...
fhdbcel01: The STARTUP of CELLSRV services was successful.
fhdbcel01: Starting MS services...
fhdbcel01: The STARTUP of MS services was successful.
[root@fhdbcel01 ~]# dcli -c fhdbcel02 -l root "cellcli -e alter cell restart services all"
fhdbcel02:
fhdbcel02: Stopping the RS, CELLSRV, and MS services...
fhdbcel02: The SHUTDOWN of services was successful.
fhdbcel02: Starting the RS, CELLSRV, and MS services...
fhdbcel02: Getting the state of RS services...
fhdbcel02: running
fhdbcel02: Starting CELLSRV services...
fhdbcel02: The STARTUP of CELLSRV services was successful.
fhdbcel02: Starting MS services...
fhdbcel02: The STARTUP of MS services was successful.
[root@fhdbcel01 ~]# dcli -c fhdbcel03 -l root "cellcli -e alter cell restart services all"
fhdbcel03:
fhdbcel03: Stopping the RS, CELLSRV, and MS services...
fhdbcel03: The SHUTDOWN of services was successful.
fhdbcel03: Starting the RS, CELLSRV, and MS services...
fhdbcel03: Getting the state of RS services...
fhdbcel03: running
fhdbcel03: Starting CELLSRV services...
fhdbcel03: The STARTUP of CELLSRV services was successful.
fhdbcel03: Starting MS services...
fhdbcel03: The STARTUP of MS services was successful.
[root@fhdbcel01 ~]# dcli -c fhdbcel04 -l root "cellcli -e alter cell restart services all"
fhdbcel04:
fhdbcel04: Stopping the RS, CELLSRV, and MS services...
fhdbcel04: The SHUTDOWN of services was successful.
fhdbcel04: Starting the RS, CELLSRV, and MS services...
fhdbcel04: Getting the state of RS services...
fhdbcel04: running
fhdbcel04: Starting CELLSRV services...
fhdbcel04: The STARTUP of CELLSRV services was successful.
fhdbcel04: Starting MS services...
fhdbcel04: The STARTUP of MS services was successful.
[root@fhdbcel01 ~]# dcli -c fhdbcel05 -l root "cellcli -e alter cell restart services all"
fhdbcel05:
fhdbcel05: Stopping the RS, CELLSRV, and MS services...
fhdbcel05: The SHUTDOWN of services was successful.
fhdbcel05: Starting the RS, CELLSRV, and MS services...
fhdbcel05: Getting the state of RS services...
fhdbcel05: running
fhdbcel05: Starting CELLSRV services...
fhdbcel05: The STARTUP of CELLSRV services was successful.
fhdbcel05: Starting MS services...
fhdbcel05: The STARTUP of MS services was successful.
[root@fhdbcel01 ~]# dcli -c fhdbcel06 -l root "cellcli -e alter cell restart services all"
fhdbcel06:
fhdbcel06: Stopping the RS, CELLSRV, and MS services...
fhdbcel06: The SHUTDOWN of services was successful.
fhdbcel06: Starting the RS, CELLSRV, and MS services...
fhdbcel06: Getting the state of RS services...
fhdbcel06: running
fhdbcel06: Starting CELLSRV services...
fhdbcel06: The STARTUP of CELLSRV services was successful.
fhdbcel06: Starting MS services...
fhdbcel06: The STARTUP of MS services was successful.
[root@fhdbcel01 ~]# dcli -c fhdbcel07 -l root "cellcli -e alter cell restart services all"
fhdbcel07:
fhdbcel07: Stopping the RS, CELLSRV, and MS services...
fhdbcel07: The SHUTDOWN of services was successful.
fhdbcel07: Starting the RS, CELLSRV, and MS services...
fhdbcel07: Getting the state of RS services...
fhdbcel07: running
fhdbcel07: Starting CELLSRV services...
fhdbcel07: The STARTUP of CELLSRV services was successful.
fhdbcel07: Starting MS services...
fhdbcel07: The STARTUP of MS services was successful.

[root@fhdbcel01 ~]# dcli -g all_cells -l root "cellcli -e list flashcachecontent attributes dbid,objectnumber,cachedsize where objectnumber=81512"
[root@fhdbcel01 ~]#

Case 1: In this case we will consecutively loop though each column twice i.e. col1 to col16

Case 1 Scripts part 1:

drop table storage_ind_stat purge;

create table storage_ind_stat (exec varchar2(15),
                               col1 number,
                               col2 number,
                               col3 number,
                               col4 number,
                               col5 number,
                               col6 number,
                               col7 number,
                               col8 number,
                               col9 number,
                               col10 number,
                               col11 number,
                               col12 number,
                               col13 number,
                               col14 number,
                               col15 number,
                               col16 number);
insert into storage_ind_stat(exec) values ('Execute1');
insert into storage_ind_stat(exec) values ('Execute2');
commit;

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

set serveroutput on
declare
stmt varchar2(200);
is_null number;
val1 number;
val2 number;
val3 number;
cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
begin
for v1 in c1 loop
select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;

select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;

select value into val3 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';


stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
execute immediate stmt;
stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val3-val2) || ' where exec= ' || '''' || 'Execute2' || '''';
execute immediate stmt;
commit;

val1:=0;
val2:=0;
val3:=0; 

end loop;
end;
/

Case 1 output part 1:

vdesai@exadata > select * from STORAGE_IND_STAT;

EXEC                    COL1         COL2         COL3         COL4         COL5         COL6         COL7         COL8         COL9        COL10        COL11        COL12        COL13        COL14        COL15        COL16
--------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
Execute1                   0            0            0            0            0            0            0            0            0            0            0            0            0            0            0            0
Execute2         31727861760  31097012224  31102484480  31726813184  31723601920  31077318656  31098060800  31728910336  31731990528  31094161408  31090786304  31733170176  31728779264  31087640576  31091965952  31732842496

“cell physical IO bytes saved by storage index” statistics are updated for all 16 columns so the storage indexes are created for more than 16 columns.

Case 1 Scripts part 2:

insert into storage_ind_stat(exec) values ('Execute3');
commit;

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

set serveroutput on
declare
stmt varchar2(200);
is_null number;
val1 number;
val2 number;
val3 number;
cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
begin
for v1 in c1 loop
select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute3' || '''';
execute immediate stmt;

commit;

val1:=0;
val2:=0;
val3:=0; 

end loop;
end;
/

Case 1 output part2:

vdesai@exadata > select * from STORAGE_IND_STAT order by 1; 

EXEC                    COL1         COL2         COL3         COL4         COL5         COL6         COL7         COL8         COL9        COL10        COL11        COL12        COL13        COL14        COL15        COL16
--------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
Execute1                   0            0            0            0            0            0            0            0            0            0            0            0            0            0            0            0
Execute2         31727861760  31097012224  31102484480  31726813184  31723601920  31077318656  31098060800  31728910336  31731990528  31094161408  31090786304  31733170176  31728779264  31087640576  31091965952  31732842496
Execute3         31727861760  31097012224  31102484480  31726813184  31723601920  31077318656  31098060800  31728910336  31731990528  31094161408  31090786304  31733170176  31728779264  31087640576  31091965952  31732842496 

As you can see from above that stroage indexes are not only created for more than 8 columns but are also maintained for more than 8 columns.

Case 2: In this case first I will run single sql statement using filter for all 16 columns and then run queries one by one with single column filter.

Case 2 Scripts:

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

select count(1) from t1 where col1 is null
                          and col2 is null
                          and col3 is null
                          and col4 is null
                          and col5 is null
                          and col6 is null
                          and col7 is null
                          and col8 is null
                          and col9 is null
                          and col10 is null
                          and col11 is null
                          and col12 is null
                          and col13 is null
                          and col14 is null
                          and col15 is null
                          and col16 is null;
                          
select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
                          
                          
drop table storage_ind_stat purge;

create table storage_ind_stat (exec varchar2(15),
                               col1 number,
                               col2 number,
                               col3 number,
                               col4 number,
                               col5 number,
                               col6 number,
                               col7 number,
                               col8 number,
                               col9 number,
                               col10 number,
                               col11 number,
                               col12 number,
                               col13 number,
                               col14 number,
                               col15 number,
                               col16 number);
insert into storage_ind_stat(exec) values ('Execute1');
commit;

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

set serveroutput on
declare
stmt varchar2(200);
is_null number;
val1 number;
val2 number;
val3 number;
cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
begin
for v1 in c1 loop
select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
execute immediate stmt;

commit;

val1:=0;
val2:=0;
val3:=0; 

end loop;
end;
/

Case 2 output:

vdesai@exadata > alter session set "_serial_direct_read"=true;

Session altered.

vdesai@exadata > alter system flush shared_pool;

System altered.

vdesai@exadata > alter system flush buffer_cache;

System altered.

vdesai@exadata >
vdesai@exadata > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

vdesai@exadata > select count(1) from t1 where col1 is null
  2                                               and col2 is null
  3                                               and col3 is null
  4                                               and col4 is null
  5                                               and col5 is null
  6                                               and col6 is null
  7                                               and col7 is null
  8                                               and col8 is null
  9                                               and col9 is null
 10                                               and col10 is null
 11                                               and col11 is null
 12                                               and col12 is null
 13                                               and col13 is null
 14                                               and col14 is null
 15                                               and col15 is null
 16                                               and col16 is null;

  COUNT(1)
----------
         0



vdesai@exadata > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

vdesai@exadata >
vdesai@exadata > alter session set "_serial_direct_read"=true;

Session altered.

vdesai@exadata > alter system flush shared_pool;

System altered.

vdesai@exadata > alter system flush buffer_cache;

System altered.

vdesai@exadata >
vdesai@exadata > set serveroutput on
vdesai@exadata > declare
  2  stmt varchar2(200);
  3  is_null number;
  4  val1 number;
  5  val2 number;
  6  val3 number;
  7  cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
  8  begin
  9  for v1 in c1 loop
 10  select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 11  execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
 12  select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 13
 14  stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
 15  execute immediate stmt;
 16
 17  commit;
 18
 19  val1:=0;
 20  val2:=0;
 21  val3:=0;
 22
 23  end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

vdesai@exadata >

vdesai@exadata > select * from STORAGE_IND_STAT order by 1;

EXEC                    COL1         COL2         COL3         COL4         COL5         COL6         COL7         COL8         COL9        COL10        COL11        COL12        COL13        COL14        COL15        COL16
--------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
Execute1         31727861760  31097012224  31102484480  31726813184  31723601920  31077318656  31098060800  31728910336            0            0            0            0            0            0            0            0

As you can see from above that Storage indexes were created and used for columns COL1 to COL8 (8 columns).

Case 3: Same as Case 2 but replaced AND condition in query with OR.

Case 3 Scripts:

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

select count(1) from t1 where col1 is null
                          or col2 is null
                          or col3 is null
                          or col4 is null
                          or col5 is null
                          or col6 is null
                          or col7 is null
                          or col8 is null
                          or col9 is null
                          or col10 is null
                          or col11 is null
                          or col12 is null
                          or col13 is null
                          or col14 is null
                          or col15 is null
                          or col16 is null;
                          
select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
                          
                          

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

set serveroutput on
declare
stmt varchar2(200);
is_null number;
val1 number;
val2 number;
val3 number;
cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
begin
for v1 in c1 loop
select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
execute immediate stmt;

commit;

val1:=0;
val2:=0;
val3:=0; 

end loop;
end;
/

Case 3 output:

vdesai@exadata > alter session set "_serial_direct_read"=true;

Session altered.

vdesai@exadata > alter system flush shared_pool;

System altered.

vdesai@exadata > alter system flush buffer_cache;

System altered.

vdesai@exadata >
vdesai@exadata > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

vdesai@exadata >
vdesai@exadata > select count(1) from t1 where col1 is null
  2                                               or col2 is null
  3                                               or col3 is null
  4                                               or col4 is null
  5                                               or col5 is null
  6                                               or col6 is null
  7                                               or col7 is null
  8                                               or col8 is null
  9                                               or col9 is null
 10                                               or col10 is null
 11                                               or col11 is null
 12                                               or col12 is null
 13                                               or col13 is null
 14                                               or col14 is null
 15                                               or col15 is null
 16                                               or col16 is null;

  COUNT(1)
----------
  10240000

vdesai@exadata >
vdesai@exadata > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

vdesai@exadata > alter session set "_serial_direct_read"=true;

Session altered.

vdesai@exadata > alter system flush shared_pool;

System altered.

vdesai@exadata > alter system flush buffer_cache;

System altered.

vdesai@exadata >
vdesai@exadata > set serveroutput on
vdesai@exadata > declare
  2  stmt varchar2(200);
  3  is_null number;
  4  val1 number;
  5  val2 number;
  6  val3 number;
  7  cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
  8  begin
  9  for v1 in c1 loop
 10  select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 11  execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
 12  select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 13
 14  stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
 15  execute immediate stmt;
 16
 17  commit;
 18
 19  val1:=0;
 20  val2:=0;
 21  val3:=0;
 22
 23  end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

vdesai@exadata >

vdesai@exadata > select * from STORAGE_IND_STAT order by 1;

EXEC                    COL1         COL2         COL3         COL4         COL5         COL6         COL7         COL8         COL9        COL10        COL11        COL12        COL13        COL14        COL15        COL16
--------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
Execute1                   0            0            0            0            0            0            0            0            0            0            0            0            0            0            0            0

I tried above test case with "_cell_storidx_mode"=ALL but got same results. As the main query fetched 10,240,000 rows (scattered) out of 64,000,000 may be Oracle Storage cell optimizer decided not to create storage indexes. 🙂

Case 4: Same as Case 3 but will use only two column filters

Case 4 Scripts:

alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

select count(1) from t1 where col1 is null
                          or col2 is null;
                          
select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
                          
                          
alter session set "_serial_direct_read"=true;
alter system flush shared_pool;
alter system flush buffer_cache;

set serveroutput on
declare
stmt varchar2(200);
is_null number;
val1 number;
val2 number;
val3 number;
cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
begin
for v1 in c1 loop
select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
execute immediate stmt;

commit;

val1:=0;
val2:=0;
val3:=0; 

end loop;
end;
/

Case 4 output:

vdesai@exadata > alter session set "_serial_direct_read"=true;

Session altered.

vdesai@exadata > alter system flush shared_pool;

System altered.

vdesai@exadata > alter system flush buffer_cache;

System altered.

vdesai@exadata >
vdesai@exadata > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

vdesai@exadata >
vdesai@exadata > select count(1) from t1 where col1 is null
                                          or col2 is null;

  COUNT(1)
----------
   1280000

vdesai@exadata >
vdesai@exadata > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

vdesai@exadata > alter session set "_serial_direct_read"=true;

Session altered.

vdesai@exadata > alter system flush shared_pool;

System altered.

vdesai@exadata > alter system flush buffer_cache;

System altered.

vdesai@exadata >
vdesai@exadata > set serveroutput on
vdesai@exadata > declare
  2  stmt varchar2(200);
  3  is_null number;
  4  val1 number;
  5  val2 number;
  6  val3 number;
  7  cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
  8  begin
  9  for v1 in c1 loop
 10  select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 11  execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
 12  select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 13
 14  stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
 15  execute immediate stmt;
 16
 17  commit;
 18
 19  val1:=0;
 20  val2:=0;
 21  val3:=0;
 22
 23  end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

vdesai@exadata >

vdesai@exadata > select * from STORAGE_IND_STAT order by 1;

EXEC                    COL1         COL2         COL3         COL4         COL5         COL6         COL7         COL8         COL9        COL10        COL11        COL12        COL13        COL14        COL15        COL16
--------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
Execute1                   0            0            0            0            0            0            0            0            0            0            0            0            0            0            0            0

This time storage indexes should have kicked in for 1,028,000 records but it did not. To prove that Oracle Storage optimizer exists next I’m going to minimize number of nulls in col1 and col2.

Case 4 output with reduced number of nulls:

VDESAI@csprod2_exa > alter session enable parallel dml;

Session altered.

VDESAI@csprod2_exa > update /*+ parallel(t1,4) */ t1 set col1=100 where col1 is null;

640000 rows updated.

VDESAI@csprod2_exa >  update /*+ parallel(t1,4) */ t1 set col2=100 where col2 is null;

640000 rows updated.

VDESAI@csprod2_exa >

VDESAI@csprod2_exa > commit;

Commit complete.

VDESAI@csprod2_exa > update /*+ parallel(t1,4) */ t1 set col1=null,col2=null where rownum=1;

1 row updated.

VDESAI@csprod2_exa > commit;

Commit complete.

VDESAI@csprod2_exa > alter session set "_serial_direct_read"=true;

Session altered.

VDESAI@csprod2_exa > alter system flush shared_pool;

System altered.

VDESAI@csprod2_exa > alter system flush buffer_cache;

System altered.

VDESAI@csprod2_exa >
VDESAI@csprod2_exa > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

VDESAI@csprod2_exa >
VDESAI@csprod2_exa > select count(1) from t1 where col1 is null
  2                                               or col2 is null;

  COUNT(1)
----------
         1

VDESAI@csprod2_exa >
VDESAI@csprod2_exa > select value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

     VALUE
----------
         0

VDESAI@csprod2_exa > alter session set "_serial_direct_read"=true;

Session altered.

VDESAI@csprod2_exa > alter system flush shared_pool;

System altered.

VDESAI@csprod2_exa > alter system flush buffer_cache;

System altered.

VDESAI@csprod2_exa >
VDESAI@csprod2_exa > set serveroutput on
VDESAI@csprod2_exa > declare
  2  stmt varchar2(200);
  3  is_null number;
  4  val1 number;
  5  val2 number;
  6  val3 number;
  7  cursor c1 is select column_name from user_tab_columns where table_name='T1' and column_name like 'COL%';
  8  begin
  9  for v1 in c1 loop
 10  select value into val1 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 11  execute immediate 'select count(1) from t1 where '  || v1.column_name || '  is null' into is_null;
 12  select value into val2 from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';
 13
 14  stmt := ' update storage_ind_stat set ' ||  v1.column_name || ' = ' || to_number(val2-val1) || ' where exec= ' || '''' || 'Execute1' || '''';
 15  execute immediate stmt;
 16
 17  commit;
 18
 19  val1:=0;
 20  val2:=0;
 21  val3:=0;
 22
 23  end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

VDESAI@csprod2_exa > select * from storage_ind_stat;

EXEC                        COL1             COL2       COL3       COL4       COL5       COL6       COL7       COL8       COL9      COL10      COL11      COL12      COL13      COL14      COL15      COL16
--------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Execute1             74113728512      74113728512          0          0          0          0          0          0          0          0          0          0          0          0          0          0

Storage indexes were created and used this time and Storage indexes almost eliminated scan of entire table.

PS: I have used Oracle Storage optimizer term loosely but probably Oracle Marketing folks should rename Storage Indexes to Smart Storage Indexes.

July 30, 2012

Exadata – Change Diskgroup Redundancy from High to Normal

Filed under: Exadata — vishaldesai @ 4:34 pm

My first task on half Rack Exadata was to reconfigure ASM diskgroups with Normal redundancy. Oracle ACS did default install and created two diskgroups called DATA_FHDB and RECO_FHDB with high redundancy.

I was not hands on with onecommand, so I went with command line option. Dropping RECO_FHDB was quite straight forward. Here are the high level steps that I followed to recreate RECO_FHDB diskgroup with normal redundancy.

Step 1: Drop Diskgroup

SUCCESS:  drop diskgroup RECO_FHDB including contents
Tue Jul 17 22:55:09 2012
NOTE: diskgroup resource ora.RECO_FHDB.dg is dropped

Step 2: Extract DDL (create diskgroup) command for RECO_FHDB from ASM alert log and replace redundancy clause and run create diskgroup command on ASM instance.

SQL> CREATE DISKGROUP RECO_FHDB NORMAL REDUNDANCY  DISK
'o/192.168.10.10/RECO_FHDB_CD_00_fhdbcel06',
….

….

'o/192.168.10.9/RECO_FHDB_CD_11_fhdbcel05' ATTRIBUTE
'compatible.asm'='11.2.0.2','compatible.rdbms'='11.2.0.2','au_size'='4M','cell.smart_scan_capable'='TRUE' /* ASMCA */

SUCCESS: diskgroup RECO_FHDB was mounted


ASM spfile, OCR and voting disks were located on DATA_FHDB diskgroup and I had to relocate above files from DATA_FHDB to RECO_FHDB to recreate DATA_FHDB diskgroup with normal redundancy.

Step 1: Drop diskgroup will throw following error when ASM SPFILE is located on same diskgroup.

SQL> drop diskgroup DATA_FHDB including contents
NOTE: Active use of SPFILE in group
Wed Jul 18 14:49:29 2012
GMON querying group 1 at 18 for pid 19, osid 9914
Wed Jul 18 14:49:29 2012
NOTE: Instance updated compatible.asm to 11.2.0.2.0 for grp 1
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA_FHDB" precludes its dismount



Step 2: Move OCR and voting disk to RECO_FHDB

[oracle@fhdbdb01 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3344
         Available space (kbytes) :     258776
         ID                       : 1272363019
         Device/File Name         : +DATA_FHDB
                                    Device/File integrity check succeeded 

                                    Device/File not configured 

                                    Device/File not configured 

                                    Device/File not configured 

                                    Device/File not configured 

         Cluster registry integrity check succeeded 

         Logical corruption check bypassed due to non-privileged user

[root@fhdbdb01 cssd]# ocrconfig -add +RECO_FHDB
[root@fhdbdb01 cssd]# 

[root@fhdbdb01 cssd]# ocrconfig -delete +DATA_FHDB
[root@fhdbdb01 cssd]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3364
         Available space (kbytes) :     258756
         ID                       : 1272363019
         Device/File Name         : +RECO_FHDB
                                    Device/File integrity check succeeded 

                                    Device/File not configured 

                                    Device/File not configured 

                                    Device/File not configured 

                                    Device/File not configured 

         Cluster registry integrity check succeeded 

         Logical corruption check succeeded

[root@fhdbdb01 ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   75c79c52f88b4fcebf2f84ccad0be646 (o/192.168.10.10/DATA_FHDB_CD_00_fhdbcel06) [DATA_FHDB]
 2. ONLINE   14f6d0e1c8b94f3bbf222b821f7f48ab (o/192.168.10.11/DATA_FHDB_CD_00_fhdbcel07) [DATA_FHDB]
 3. ONLINE   7aed830fb6ee4f70bf9160b2f39ea64b (o/192.168.10.5/DATA_FHDB_CD_00_fhdbcel01) [DATA_FHDB]
 4. ONLINE   9cc87608cabd4fb0bfea7e1f7d403134 (o/192.168.10.6/DATA_FHDB_CD_00_fhdbcel02) [DATA_FHDB]
 5. ONLINE   2c6008a2c0864fbfbf4ae1c9cbc60d5c (o/192.168.10.7/DATA_FHDB_CD_00_fhdbcel03) [DATA_FHDB] 

[root@fhdbdb01 cssd]# crsctl replace votedisk +RECO_FHDB
Successful addition of voting disk 161fa97cc71e4fffbfe10408e1e32aa0.
Successful addition of voting disk 128fb088bd7c4fe7bf6dff63d946dbc6.
Successful addition of voting disk 804b6348a5974f53bfccb328b92f9350.
Successful deletion of voting disk 75c79c52f88b4fcebf2f84ccad0be646.
Successful deletion of voting disk 14f6d0e1c8b94f3bbf222b821f7f48ab.
Successful deletion of voting disk 7aed830fb6ee4f70bf9160b2f39ea64b.
Successful deletion of voting disk 9cc87608cabd4fb0bfea7e1f7d403134.
Successful deletion of voting disk 2c6008a2c0864fbfbf4ae1c9cbc60d5c.
Successfully replaced voting disk group with +RECO_FHDB.
CRS-4266: Voting file(s) successfully replaced 

[root@fhdbdb01 cssd]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   161fa97cc71e4fffbfe10408e1e32aa0 (o/192.168.10.10/RECO_FHDB_CD_00_fhdbcel06) [RECO_FHDB]
 2. ONLINE   128fb088bd7c4fe7bf6dff63d946dbc6 (o/192.168.10.11/RECO_FHDB_CD_00_fhdbcel07) [RECO_FHDB]
 3. ONLINE   804b6348a5974f53bfccb328b92f9350 (o/192.168.10.5/RECO_FHDB_CD_00_fhdbcel01) [RECO_FHDB]
Located 3 voting disk(s).



Step 3: Move ASM spfile.

SQL> create pfile='/nfs/zfs/init+ASM.ora' from spfile; 

File created. 

SQL> create spfile='+RECO_FHDB/fhdb-cluster/asmparameterfile/spfileASM.ora' from pfile='/nfs/zfs/init+ASM.ora'; 

File created. 

echo "SPFILE='+RECO_FHDB/fhdb-cluster/asmparameterfile/spfileASM.ora'" > init+ASM.ora



Step 4: Drop DATA_FHDB diskgroup

SQL> drop diskgroup DATA_FHDB including contents;
drop diskgroup DATA_FHDB including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA_FHDB" precludes its dismount
ASMCMD> cd DATA_FHDB/
ASMCMD> ls
fhdb-cluster/
ASMCMD> cd fhdb-cluster
ASMCMD> ls
ASMPARAMETERFILE/
OCRFILE/
ASMCMD> cd ASMPARAMETERFILE/
ASMCMD> ls
REGISTRY.253.788355279
ASMCMD> rm REGISTRY.253.788355279
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA_FHDB/fhdb-cluster/ASMPARAMETERFILE/REGISTRY.253.788355279' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)



SQL> alter diskgroup DATA_FHDB dismount force;
Diskgroup altered.
SQL> drop diskgroup DATA_FHDB force including contents;
Diskgroup dropped.



Step 5: Create DATA_FHDB diskgroup

Create a free website or blog at WordPress.com.