159 lines
7.4 KiB
MySQL
159 lines
7.4 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata PhysicalDisks
|
|
* Parameters : 1 - Cell Name (Use % for wildcard)
|
|
* 2 - DiskType (FlashDisk or HardDisk,Use % for wildcard )
|
|
* 3 - PhysicalDisk Name (Use % for wildcard)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 20-Feb-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE cell_name
|
|
UNDEFINE diskType
|
|
UNDEFINE name
|
|
|
|
DEFINE cell_name="&&1"
|
|
DEFINE diskType="&&2"
|
|
DEFINE name="&&3"
|
|
|
|
set term off
|
|
COLUMN _cell_name NEW_VALUE cell_name NOPRINT
|
|
COLUMN _diskType NEW_VALUE diskType NOPRINT
|
|
COLUMN _name NEW_VALUE name NOPRINT
|
|
|
|
SELECT DECODE('&&cell_name','','%','&&cell_name') "_cell_name"
|
|
, DECODE('&&diskType','','%','&&diskType') "_diskType"
|
|
, DECODE('&&name','','%','&&name') "_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT
|
|
PROMPT ****************************************************
|
|
PROMPT * E X A D A T A P H Y S I C A L D I S K S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * CellName = '&&cell_name'
|
|
PROMPT * DiskType = '&&diskType'
|
|
PROMPT * PhyDiskName = '&&name'
|
|
PROMPT ****************************************************
|
|
|
|
COLUMN cellname HEADING "CellName" FORMAT a18
|
|
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 foreignState HEADING "Foreign|State" FORMAT a7
|
|
COLUMN physicalFirmware HEADING "Firm|Ware" FORMAT a8
|
|
COLUMN physicalInsertTime HEADING "InsertTime" FORMAT a19 TRUNC
|
|
COLUMN physicalInterface HEADING "Inter|face" FORMAT a5
|
|
COLUMN physicalSerial HEADING "Serial No" FORMAT a18
|
|
COLUMN slotNumber HEADING "slotNumber" FORMAT a10
|
|
COLUMN id HEADING "id" FORMAT a10
|
|
COLUMN key_500 HEADING "key_500" FORMAT a10
|
|
COLUMN predfailStatus HEADING "Pred|Fail|Status" FORMAT a6
|
|
COLUMN poorPerfStatus HEADING "Poor|Perf|Status" FORMAT a6
|
|
COLUMN wtCachingStatus HEADING "Write|Through|Caching|Status" FORMAT a7
|
|
COLUMN peerFailStatus HEADING "Peer|Fail|Status" FORMAT a6
|
|
COLUMN criticalStatus HEADING "Critical|Status" FORMAT a6
|
|
COLUMN physicalDatedSerial HEADING "Physical|Dated|Serial" FORMAT a12
|
|
COLUMN errMediaCount HEADING "Media|Error|Count" FORMAT 9999999
|
|
COLUMN errorCount HEADING "|Error|Count" FORMAT 9999999
|
|
COLUMN errHardReadCount HEADING "HardRead|Error|Count" FORMAT 9999999
|
|
COLUMN errHardWriteCount HEADING "HardWrite|Error|Count" FORMAT 9999999
|
|
COLUMN errorCount HEADING "Error|Count" FORMAT 9999999
|
|
COLUMN errOtherCount HEADING "Other|Error|Count" FORMAT 9999999
|
|
|
|
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
|
|
, ROUND(pd.physicalSize/power(1000,3)) physicalSize
|
|
--, pd.deviceId
|
|
--, pd.devicePartition
|
|
--, pd.enclosureDeviceId
|
|
, pd.foreignState
|
|
--, pd.luns
|
|
--, pd.makeModel
|
|
, pd.physicalFirmware
|
|
, pd.physicalInsertTime
|
|
, pd.physicalInterface
|
|
, pd.physicalSerial
|
|
--, pd.slotNumber
|
|
--, pd.id
|
|
--, pd.key_500
|
|
, pd.predfailStatus
|
|
, pd.poorPerfStatus
|
|
, pd.wtCachingStatus
|
|
, pd.peerFailStatus
|
|
, pd.criticalStatus
|
|
, pd.physicalDatedSerial
|
|
, pd.errCmdTimeoutCount
|
|
, pd.errMediaCount
|
|
, pd.errHardReadCount
|
|
, pd.errHardWriteCount
|
|
, pd.errorCount
|
|
, pd.errOtherCount
|
|
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'
|
|
, errCmdTimeoutCount INTEGER PATH 'errCmdTimeoutCount'
|
|
, errHardReadCount INTEGER PATH 'errHardReadCount'
|
|
, errHardWriteCount INTEGER PATH 'errHardWriteCount'
|
|
, errMediaCount INTEGER PATH 'errMediaCount'
|
|
, errorCount INTEGER PATH 'errorCount'
|
|
, 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 UPPER(clioutput.cellname) LIKE UPPER('&&cell_name')
|
|
AND UPPER(pd.diskType) LIKE UPPER('&&diskType')
|
|
AND UPPER(pd.name) LIKE UPPER('&&name')
|
|
ORDER BY clioutput.cellname
|
|
, pd.diskType
|
|
, pd.slotNumber
|
|
;
|
|
|
|
|
|
@@footer |