We can query all the information about the cellnodes on Exadata from any database.
It is possible to query information from v$cell_config. With this view, all information about the following can be queried.
- IORM (I/O Source Management)
- CELLDISKS (Cell Disk Informations)
- GRIDDISKS (Grid Disk Informations)
- CELL (Information about Cell)
- LUNS (Lun Informations)
- PHYSICALDISKS (Physical Disk Informations)
In this view, it is possible to access the requested information by parsing the xml data in the clob area named CONFVAL. Cell, ASM information can be queried globally from all db nodes. So you don’t need to query with gv $. v$ will also query globally.
A sample query is specified below.
You can use the following query to access cellnode physical disk information:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SELECT * FROM ( SELECT c.cellname cv_cellname , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/name/text()') AS VARCHAR2(20)) diskname , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/diskType/text()') AS VARCHAR2(20)) diskType , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/luns/text()') AS VARCHAR2(20)) luns , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/makeModel/text()') AS VARCHAR2(40)) makeModel , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalFirmware/text()') AS VARCHAR2(20)) physicalFirmware , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalInsertTime/text()') AS VARCHAR2(30)) physicalInsertTime , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalSerial/text()') AS VARCHAR2(20)) physicalSerial , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalSize/text()') AS VARCHAR2(20)) physicalSize , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/sectorRemapCount/text()') AS VARCHAR2(20)) sectorRemapCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/slotNumber/text()') AS VARCHAR2(30)) slotNumber , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/status/text()') AS VARCHAR2(20)) status , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/id/text()') AS VARCHAR2(20)) id , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/key_500/text()') AS VARCHAR2(20)) key_500 , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/predfailStatus/text()') AS VARCHAR2(20)) predfailStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/poorPerfStatus/text()') AS VARCHAR2(20)) poorPerfStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/wtCachingStatus/text()') AS VARCHAR2(20)) wtCachingStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/peerFailStatus/text()') AS VARCHAR2(20)) peerFailStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/criticalStatus/text()') AS VARCHAR2(20)) criticalStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errCmdTimeoutCount/text()') AS VARCHAR2(20)) errCmdTimeoutCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errHardReadCount/text()') AS VARCHAR2(20)) errHardReadCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errHardWriteCount/text()') AS VARCHAR2(20)) errHardWriteCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errMediaCount/text()') AS VARCHAR2(20)) errMediaCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errOtherCount/text()') AS VARCHAR2(20)) errOtherCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errSeekCount/text()') AS VARCHAR2(20)) errSeekCount FROM v$cell_config c , TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(c.confval), '/cli-output/physicaldisk'))) v WHERE c.conftype = 'PHYSICALDISKS' ) ORDER BY cv_cellname , diskname; |
