Exadata disk topolojisinin raporlanması
Exadata içerisindeki cell node’lar üzerindeki fiziksel disk , celldisk , griddisk ve flash disklerin birbirleriyle bağlantısını sorgulamak için aşağıdaki script’i herhangi bir veritabanı sunucusundan çalıştırmanız yeterli olacaktır. Script’te kullanılan v$ yerine gv$ kullanılmasına gerek yoktur. v$ ile sorguladığınız zaman da tüm cell’lerden bilgi alabilirsiniz.
Ayrı ayrı cellnode’lardan cellcli ile bilgiler toplayıp, bunları asm disk gruplarının bilgileri ile eşleştirmek çoğu zaman eziyet haline dönüşür. Aşağıdaki script ile bu bilgilere kolayca ulaşabilirsiniz.
Aşağıdaki komutları exadatadisktopology.sql gibi bir dosyaya kayıt edip çalıştırmak daha kolay olacaktır.
exadatadisktopology.sql :
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 /