@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display Exadata Errors
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  20-Feb-13  Vishal Gupta  Created
*
*/




PROMPT 
PROMPT **************************************************
PROMPT *  E X A D A T A   C E L L   S T A T U S
PROMPT **************************************************


COLUMN cell_path                   HEADING "CellPath"                 FORMAT a15
COLUMN cellname                    HEADING "CellName"                 FORMAT a18
COLUMN upTime                      HEADING "UpTime"                   FORMAT a15
COLUMN status                      HEADING "Status"                   FORMAT a7
COLUMN fanStatus                   HEADING "Fan|Status"               FORMAT a6
COLUMN powerStatus                 HEADING "Power|Status"             FORMAT a6
COLUMN temperatureReading          HEADING "Temp|(C)"                 FORMAT a6
COLUMN temperatureStatus           HEADING "Temp|Status"              FORMAT a6
COLUMN locatorLEDStatus            HEADING "Locator|LED"              FORMAT a7
COLUMN offloadEfficiency           HEADING "OffLoad|Efficiency"       FORMAT 99999.99
COLUMN iormBoost                   HEADING "IORM|Boost"               FORMAT 99999.99

SELECT * 
FROM
(
SELECT MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/name') ,NULL))                cellname
     --, cc.cellname cell_path
     , LPAD(MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/upTime') ,NULL)),15)     upTime
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/status') ,NULL))              status
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/fanStatus') ,NULL))           fanStatus
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/powerStatus') ,NULL))         powerStatus
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/temperatureStatus') ,NULL))   temperatureStatus
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/temperatureReading') ,NULL))  temperatureReading
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/locatorLEDStatus') ,NULL))    locatorLEDStatus
FROM v$cell_config cc
WHERE cc.conftype = 'CELL'
GROUP BY cc.cellname
)
WHERE (   status <> 'online'
        OR fanStatus <> 'normal'
        OR powerStatus <> 'normal'
        OR temperatureStatus <> 'normal'
        OR locatorLEDStatus <> 'off'
        OR TO_NUMBER(temperatureReading) > 29
       )
ORDER BY cellname
;


PROMPT 
PROMPT ****************************************************
PROMPT *  E X A D A T A    P H Y S I C A L    D I S K S
PROMPT ****************************************************

COLUMN cellname                HEADING "CellName"                         FORMAT a10 
COLUMN diskType                HEADING "Disk|Type"                        FORMAT a10
COLUMN name                    HEADING "PhyDisk|Name"                     FORMAT a10
COLUMN status                  HEADING "Status"                           FORMAT a8
COLUMN physicalSize            HEADING "Size|(GB)"                        FORMAT 999,999
COLUMN errMediaCount           HEADING "Media|Error|Count"                FORMAT 9999999
COLUMN errOtherCount           HEADING "Other|Error|Count"                FORMAT 9999999
COLUMN foreignState            HEADING "Foreign|State"                    FORMAT a5
COLUMN physicalFirmware        HEADING "Firm|Ware"                        FORMAT a6
COLUMN physicalInsertTime      HEADING "InsertTime"                       FORMAT a10 TRUNC
COLUMN physicalInterface       HEADING "Interface"                        FORMAT a10
COLUMN predfailStatus          HEADING "Pred|Fail|Status"                 FORMAT a5
COLUMN poorPerfStatus          HEADING "Poor|Perf|Status"                 FORMAT a5
COLUMN wtCachingStatus         HEADING "Write|Through|Caching|Status"     FORMAT a5
COLUMN peerFailStatus          HEADING "Peer|Fail|Status"                 FORMAT a5
COLUMN criticalStatus          HEADING "Critical|Status"                  FORMAT a5
COLUMN physicalDatedSerial     HEADING "Physical|Dated|Serial"            FORMAT a12
 
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF physicalSize FORMAT 99,999,999,999  ON REPORT 
 
SELECT clioutput.cellname
     , pd.diskType
     , pd.name
     , pd.status
     , pd.errMediaCount
     , pd.errOtherCount
     , pd.foreignState
     , pd.predfailStatus
     , pd.poorPerfStatus
     , pd.wtCachingStatus
     , pd.peerFailStatus
     , pd.criticalStatus
     , pd.physicalDatedSerial
 FROM  v$cell_config cc
     , XMLTable('/cli-output' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        cellname       VARCHAR2(100) PATH 'context/@cell'
                      , physicaldisk   XMLTYPE       PATH 'physicaldisk'
                 ) clioutput
     , XMLTable('/physicaldisk' PASSING clioutput.physicaldisk
                   COLUMNS 
                        NAME                VARCHAR2(100) PATH 'name'
                      , deviceId            VARCHAR2(100) PATH 'deviceId'
                      , diskType            VARCHAR2(100) PATH 'diskType'
                      , devicePartition     VARCHAR2(100) PATH 'devicePartition'
                      , enclosureDeviceId   VARCHAR2(100) PATH 'enclosureDeviceId'
                      , errMediaCount       INTEGER       PATH 'errMediaCount'
                      , errOtherCount       INTEGER       PATH 'errOtherCount'
                      , foreignState        VARCHAR2(100) PATH 'foreignState'
                      , luns                VARCHAR2(100) PATH 'luns'
                      , makeModel           VARCHAR2(100) PATH 'makeModel'
                      , physicalFirmware    VARCHAR2(100) PATH 'physicalFirmware'
                      , physicalInsertTime  VARCHAR2(100) PATH 'physicalInsertTime'
                      , physicalInterface   VARCHAR2(100) PATH 'physicalInterface'
                      , physicalSerial      VARCHAR2(100) PATH 'physicalSerial'
                      , physicalSize        INTEGER       PATH 'physicalSize'
                      , slotNumber          VARCHAR2(100) PATH 'slotNumber'
                      , status              VARCHAR2(100) PATH 'status'
                      , id                  VARCHAR2(100) PATH 'id'
                      , key_500             VARCHAR2(100) PATH 'key_500'
                      , predfailStatus      VARCHAR2(100) PATH 'predfailStatus'
                      , poorPerfStatus      VARCHAR2(100) PATH 'poorPerfStatus'
                      , wtCachingStatus     VARCHAR2(100) PATH 'wtCachingStatus'
                      , peerFailStatus      VARCHAR2(100) PATH 'peerFailStatus'
                      , criticalStatus      VARCHAR2(100) PATH 'criticalStatus'
                      , physicalDatedSerial VARCHAR2(100) PATH 'physicalDatedSerial'
                 ) pd
WHERE cc.conftype = 'PHYSICALDISKS'
  AND (   pd.status <> 'normal'
       OR pd.errMediaCount > 0
       OR pd.errOtherCount > 0
       OR pd.foreignState <> 'false'
       OR pd.predfailStatus <> 'FALSE'
       OR pd.poorPerfStatus <> 'FALSE'
       OR pd.wtCachingStatus <> 'FALSE'
       OR pd.peerFailStatus <> 'FALSE'
       OR pd.criticalStatus <> 'FALSE'
       )
ORDER BY clioutput.cellname
       , pd.diskType
       , pd.name
;


PROMPT 
PROMPT **************************************************
PROMPT *  E X A D A T A   L U N S
PROMPT **************************************************


COLUMN cellname                HEADING "CellName"                  FORMAT a10 
COLUMN diskType                HEADING "Disk|Type"                 FORMAT a10
COLUMN name                    HEADING "LUN|Name"                  FORMAT a5
COLUMN status                  HEADING "Status"                    FORMAT a8
COLUMN lunSize                 HEADING "Size|(GB)"                 FORMAT 999,999
COLUMN cellDisk                HEADING "CellDisk|Name"             FORMAT a15
COLUMN deviceName              HEADING "deviceName"                FORMAT a10
COLUMN id                      HEADING "id"                        FORMAT a10
COLUMN isSystemLun             HEADING "isSystemLun"               FORMAT a5
COLUMN lunAutoCreate           HEADING "lunAutoCreate"             FORMAT a6
COLUMN lunUID                  HEADING "lunUID"                    FORMAT a6
COLUMN physicalDrives          HEADING "physicalDrives"            FORMAT a10
COLUMN raidLevel               HEADING "Raid|Level"                FORMAT a6
COLUMN lunWriteCacheMode       HEADING "lunWriteCacheMode"         FORMAT a61
 
 
SELECT clioutput.cellname
     , lun.diskType
     , lun.name
     , lun.status
     , ROUND(lun.lunSize/power(1000,3)) lunSize
     , lun.cellDisk
     , lun.deviceName
     , lun.id
     , lun.isSystemLun
     , lun.lunAutoCreate
     , lun.lunUID
     , lun.physicalDrives
     , lun.raidLevel
     , lun.lunWriteCacheMode
 FROM  v$cell_config cc
     , XMLTable('/cli-output' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        cellname       VARCHAR2(100) PATH 'context/@cell'
                      , lun            XMLTYPE       PATH 'lun'
                 ) clioutput
     , XMLTable('/lun' PASSING clioutput.lun
                   COLUMNS 
                        name               VARCHAR2(100) PATH 'name'
                      , cellDisk           VARCHAR2(100) PATH 'cellDisk'
                      , deviceName         VARCHAR2(100) PATH 'deviceName'
                      , diskType           VARCHAR2(100) PATH 'diskType'
                      , id                 VARCHAR2(100) PATH 'id'
                      , isSystemLun        VARCHAR2(100) PATH 'isSystemLun'
                      , lunAutoCreate      VARCHAR2(100) PATH 'lunAutoCreate'
                      , lunSize            NUMBER        PATH 'lunSize'
                      , lunUID             VARCHAR2(100) PATH 'lunUID'
                      , physicalDrives     VARCHAR2(100) PATH 'physicalDrives'
                      , raidLevel          VARCHAR2(100) PATH 'raidLevel'
                      , lunWriteCacheMode  VARCHAR2(100) PATH 'lunWriteCacheMode'
                      , status             VARCHAR2(100) PATH 'status'
                 ) lun
WHERE cc.conftype = 'LUNS'
  AND (   lun.status <> 'normal'
       OR  (lun.diskType = 'HardDisk' AND lun.lunWriteCacheMode NOT LIKE '%WriteBack%')
      )
ORDER BY clioutput.cellname
       , lun.diskType
       , lun.name
;

PROMPT 
PROMPT ********************************************
PROMPT *  E X A D A T A   C E L L    D I S K S
PROMPT ********************************************

COLUMN cellname                HEADING "CellName"               FORMAT a10 
COLUMN cellDiskName            HEADING "CellDiskName"           FORMAT a16
COLUMN status                  HEADING "Status"                 FORMAT a11
COLUMN creationTime            HEADING "CreationTime"           FORMAT a25
COLUMN deviceName              HEADING "Device|Name"            FORMAT a10
COLUMN devicePartition         HEADING "Device|Partition"       FORMAT a10
COLUMN diskType                HEADING "DiskType"               FORMAT a10
COLUMN celdisksize             HEADING "Size|(GB)"              FORMAT 999,999
COLUMN freeSpace               HEADING "Free|Space|(MB)"        FORMAT 9,999,999
COLUMN id                      HEADING "id"                     FORMAT a40
COLUMN errorCount              HEADING "Error|(#)"              FORMAT 99999
COLUMN interleaving            HEADING "Inter|Leaving"          FORMAT a8
COLUMN lun                     HEADING "Lun"                    FORMAT a5
COLUMN physicalDisk            HEADING "Physical|Disk"          FORMAT a15
COLUMN raidLevel               HEADING "Raid|Level"             FORMAT a5


SELECT clioutput.cellname
     , celldisk.diskType
     , celldisk.name   cellDiskName
     , celldisk.status
     , celldisk.creationTime
     , celldisk.celdisksize/power(1024,3) celdisksize
     , celldisk.freeSpace/power(1024,2) freeSpace
     , celldisk.deviceName
     , celldisk.devicePartition
     , celldisk.errorCount errorCount
     , celldisk.interleaving
     , celldisk.lun
     , celldisk.physicalDisk
     , celldisk.raidLevel
 FROM  v$cell_config cc
     , XMLTable('/cli-output' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        cellname       VARCHAR2(100) PATH 'context/@cell'
                      , celldisk       XMLTYPE       PATH 'celldisk'
                 ) clioutput
     , XMLTable('/celldisk' PASSING clioutput.celldisk
                   COLUMNS 
                        NAME            VARCHAR2(100) PATH 'name'
                      , creationTime    VARCHAR2(100) PATH 'creationTime'
                      , deviceName      VARCHAR2(100) PATH 'deviceName'
                      , devicePartition VARCHAR2(100) PATH 'devicePartition'
                      , diskType        VARCHAR2(100) PATH 'diskType'
                      , errorCount      NUMBER        PATH 'errorCount'
                      , freeSpace       NUMBER        PATH 'freeSpace'
                      , interleaving    VARCHAR2(100) PATH 'interleaving'
                      , lun             VARCHAR2(100) PATH 'lun'
                      , physicalDisk    VARCHAR2(100) PATH 'physicalDisk'
                      , celdisksize     NUMBER        PATH 'size'
                      , raidLevel       VARCHAR2(100) PATH 'raidLevel'
                      , status          VARCHAR2(100) PATH 'status'
                 ) celldisk
WHERE cc.conftype = 'CELLDISKS'
  AND (   status <>  'normal'
       OR errorCount > 0
       OR freeSpace > 0
      )
ORDER BY cellname
       , DiskType
       , cellDiskName
;



PROMPT 
PROMPT **************************************************
PROMPT *  E X A D A T A   G R I D    D I S K S
PROMPT **************************************************


COLUMN cellname                HEADING "CellName"               FORMAT a10 
COLUMN name                    HEADING "GridDiskName"           FORMAT a25
COLUMN status                  HEADING "Status"                 FORMAT a11
COLUMN creationTime            HEADING "Creation|Time"           FORMAT a10 TRUNC
COLUMN diskType                HEADING "DiskType"               FORMAT a10
COLUMN asmDiskgroupName        HEADING "ASM|Diskgroup|Name"     FORMAT a10
COLUMN asmDiskName             HEADING "asmDiskName"            FORMAT a25
COLUMN asmDiskgroupName        HEADING "asmDiskgroupName"       FORMAT a10
COLUMN asmFailGroupName        HEADING "asmFailGroupName"       FORMAT a10
COLUMN availableTo             HEADING "Available|To"           FORMAT a10
COLUMN cachingPolicy           HEADING "Caching|Policy"         FORMAT a10
COLUMN offset                  HEADING "offset|(GB)"            FORMAT 999,999
COLUMN griddisksize            HEADING "Size|(GB)"              FORMAT 999,999
COLUMN errorCount              HEADING "Error|(#)"              FORMAT 99999

 
SELECT clioutput.cellname
      , griddisk.diskType
      , griddisk.name
      , griddisk.status
      , griddisk.asmDiskgroupName
      , griddisk.asmDiskName
      , griddisk.asmFailGroupName
      , griddisk.availableTo
      , griddisk.cachingPolicy
      , griddisk.creationTime
      , griddisk.errorCount
      , griddisk.offset/power(1024,3) offset
      , griddisk.griddisksize/power(1024,3) griddisksize
 FROM  v$cell_config cc
     , XMLTable('/cli-output' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        cellname       VARCHAR2(100) PATH 'context/@cell'
                      , griddisk       XMLTYPE       PATH 'griddisk'
                 ) clioutput
     , XMLTable('/griddisk' PASSING clioutput.griddisk
                   COLUMNS 
                        name                VARCHAR2(100) PATH 'name'
                      , asmDiskgroupName    VARCHAR2(100) PATH 'asmDiskgroupName'
                      , asmDiskName         VARCHAR2(100) PATH 'asmDiskName'
                      , asmFailGroupName    VARCHAR2(100) PATH 'asmFailGroupName'
                      , availableTo         VARCHAR2(100) PATH 'availableTo'
                      , cachingPolicy       VARCHAR2(100) PATH 'cachingPolicy'
                      , creationTime        VARCHAR2(100) PATH 'creationTime'
                      , diskType            VARCHAR2(100) PATH 'diskType'
                      , errorCount          NUMBER        PATH 'errorCount'
                      , offset              NUMBER        PATH 'offset'
                      , griddisksize        NUMBER        PATH 'size'
                      , status              VARCHAR2(100) PATH 'status'
                 ) griddisk
WHERE cc.conftype = 'GRIDDISKS'
  AND ( griddisk.status <> 'active'
      )
ORDER BY clioutput.cellname
       , griddisk.diskType
       , offset
       , griddisk.name
;


@@footer