Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Simply run the following script from any database server to query the physical disk, celldisk, griddisk, and flash disks on the cell nodes in Exadata. There is no need to use gv$ instead of v$ used in script. When you query with v$, you can get information from all cells.
Collecting information from individual cellnodes with cellcli and matching them with the information of asm disk groups often turns into grind. You can easily access this information with the following script.
It will be easier to save and run the following commands in a file such as exadatadisktopology.sql
exadatadisktopology.sql :
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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | COL cellname HEAD CELLNAME FOR A20 COL celldisk_name HEAD CELLDISK FOR A30 COL physdisk_name HEAD PHYSDISK FOR A30 COL griddisk_name HEAD GRIDDISK FOR A30 COL asmdisk_name HEAD ASMDISK FOR A30 BREAK ON asm_diskgroup SKIP 1 ON asm_disk PROMPT Showing Exadata disk topology from V$ASM_DISK and V$CELL_CONFIG.... WITH pd AS ( SELECT /*+ MATERIALIZE */ c.cellname , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/name/text()') AS VARCHAR2(100)) name , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/diskType/text()') AS VARCHAR2(100)) diskType , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/luns/text()') AS VARCHAR2(100)) luns , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/makeModel/text()') AS VARCHAR2(100)) makeModel , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalFirmware/text()') AS VARCHAR2(100)) physicalFirmware , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalInsertTime/text()') AS VARCHAR2(100)) physicalInsertTime , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalSerial/text()') AS VARCHAR2(100)) physicalSerial , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/physicalSize/text()') AS VARCHAR2(100)) physicalSize , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/slotNumber/text()') AS VARCHAR2(100)) slotNumber , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/status/text()') AS VARCHAR2(100)) status , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/id/text()') AS VARCHAR2(100)) id , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/key_500/text()') AS VARCHAR2(100)) key_500 , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/predfailStatus/text()') AS VARCHAR2(100)) predfailStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/poorPerfStatus/text()') AS VARCHAR2(100)) poorPerfStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/wtCachingStatus/text()') AS VARCHAR2(100)) wtCachingStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/peerFailStatus/text()') AS VARCHAR2(100)) peerFailStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/criticalStatus/text()') AS VARCHAR2(100)) criticalStatus , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errCmdTimeoutCount/text()') AS VARCHAR2(100)) errCmdTimeoutCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errHardReadCount/text()') AS VARCHAR2(100)) errHardReadCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errHardWriteCount/text()') AS VARCHAR2(100)) errHardWriteCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errMediaCount/text()') AS VARCHAR2(100)) errMediaCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errOtherCount/text()') AS VARCHAR2(100)) errOtherCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/errSeekCount/text()') AS VARCHAR2(100)) errSeekCount , CAST(EXTRACTVALUE(VALUE(v), '/physicaldisk/sectorRemapCount/text()') AS VARCHAR2(100)) sectorRemapCount FROM v$cell_config c , TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(c.confval), '/cli-output/physicaldisk'))) v WHERE c.conftype = 'PHYSICALDISKS' ), cd AS ( SELECT /*+ MATERIALIZE */ c.cellname , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/name/text()') AS VARCHAR2(100)) name , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/comment /text()') AS VARCHAR2(100)) disk_comment , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/creationTime /text()') AS VARCHAR2(100)) creationTime , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/deviceName /text()') AS VARCHAR2(100)) deviceName , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/devicePartition/text()') AS VARCHAR2(100)) devicePartition , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/diskType /text()') AS VARCHAR2(100)) diskType , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/errorCount /text()') AS VARCHAR2(100)) errorCount , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/freeSpace /text()') AS VARCHAR2(100)) freeSpace , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/id /text()') AS VARCHAR2(100)) id , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/interleaving /text()') AS VARCHAR2(100)) interleaving , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/lun /text()') AS VARCHAR2(100)) lun , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/physicalDisk /text()') AS VARCHAR2(100)) physicalDisk , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/size /text()') AS VARCHAR2(100)) disk_size , CAST(EXTRACTVALUE(VALUE(v), '/celldisk/status /text()') AS VARCHAR2(100)) status FROM v$cell_config c , TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(c.confval), '/cli-output/celldisk'))) v WHERE c.conftype = 'CELLDISKS' ), gd AS ( SELECT /*+ MATERIALIZE */ c.cellname , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/name/text()') AS VARCHAR2(100)) name , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/asmDiskgroupName/text()') AS VARCHAR2(100)) asmDiskgroupName , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/asmDiskName /text()') AS VARCHAR2(100)) asmDiskName , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/asmFailGroupName/text()') AS VARCHAR2(100)) asmFailGroupName , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/availableTo /text()') AS VARCHAR2(100)) availableTo , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/cachingPolicy /text()') AS VARCHAR2(100)) cachingPolicy , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/cellDisk /text()') AS VARCHAR2(100)) cellDisk , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/comment /text()') AS VARCHAR2(100)) disk_comment , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/creationTime /text()') AS VARCHAR2(100)) creationTime , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/diskType /text()') AS VARCHAR2(100)) diskType , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/errorCount /text()') AS VARCHAR2(100)) errorCount , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/id /text()') AS VARCHAR2(100)) id , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/offset /text()') AS VARCHAR2(100)) offset , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/size /text()') AS VARCHAR2(100)) disk_size , CAST(EXTRACTVALUE(VALUE(v), '/griddisk/status /text()') AS VARCHAR2(100)) status FROM v$cell_config c , TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(c.confval), '/cli-output/griddisk'))) v WHERE c.conftype = 'GRIDDISKS' ), lun AS ( SELECT /*+ MATERIALIZE */ c.cellname , CAST(EXTRACTVALUE(VALUE(v), '/lun/cellDisk /text()') AS VARCHAR2(100)) cellDisk , CAST(EXTRACTVALUE(VALUE(v), '/lun/deviceName /text()') AS VARCHAR2(100)) deviceName , CAST(EXTRACTVALUE(VALUE(v), '/lun/diskType /text()') AS VARCHAR2(100)) diskType , CAST(EXTRACTVALUE(VALUE(v), '/lun/id /text()') AS VARCHAR2(100)) id , CAST(EXTRACTVALUE(VALUE(v), '/lun/isSystemLun /text()') AS VARCHAR2(100)) isSystemLun , CAST(EXTRACTVALUE(VALUE(v), '/lun/lunAutoCreate /text()') AS VARCHAR2(100)) lunAutoCreate , CAST(EXTRACTVALUE(VALUE(v), '/lun/lunSize /text()') AS VARCHAR2(100)) lunSize , CAST(EXTRACTVALUE(VALUE(v), '/lun/physicalDrives /text()') AS VARCHAR2(100)) physicalDrives , CAST(EXTRACTVALUE(VALUE(v), '/lun/raidLevel /text()') AS VARCHAR2(100)) raidLevel , CAST(EXTRACTVALUE(VALUE(v), '/lun/lunWriteCacheMode/text()') AS VARCHAR2(100)) lunWriteCacheMode , CAST(EXTRACTVALUE(VALUE(v), '/lun/status /text()') AS VARCHAR2(100)) status FROM v$cell_config c , TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(c.confval), '/cli-output/lun'))) v WHERE c.conftype = 'LUNS' ) , ad AS (SELECT /*+ MATERIALIZE */ * FROM v$asm_disk) , adg AS (SELECT /*+ MATERIALIZE */ * FROM v$asm_diskgroup) SELECT adg.name asm_diskgroup , ad.name asm_disk , gd.name griddisk_name , cd.name celldisk_name , pd.cellname , SUBSTR(cd.devicepartition,1,20) cd_devicepart , pd.name physdisk_name , SUBSTR(pd.status,1,20) physdisk_status , lun.lunWriteCacheMode -- , SUBSTR(cd.devicename,1,20) cd_devicename -- , SUBSTR(lun.devicename,1,20) lun_devicename -- disktype FROM gd , cd , pd , lun , ad , adg WHERE ad.group_number = adg.group_number (+) AND gd.asmdiskname = ad.name (+) AND cd.name = gd.cellDisk (+) AND pd.id = cd.physicalDisk (+) AND cd.name = lun.celldisk (+) --GROUP BY -- cellname -- , disktype -- , status ORDER BY -- disktype asm_diskgroup , asm_disk , griddisk_name , celldisk_name , physdisk_name , cellname / |
