There is the query connecting first to sysman Schema
set lines 200
set pages 200
col "who" for a35
select
sYS_CONTEXT('USERENV','CURRENT_USER')||'_'||
sYS_CONTEXT('USERENV','INSTANCE_NAME')||'_'||
to_char(sysdate,'DDMMYYYYHH24MISS') "who"
from dual;
col diskgroup for a40
col target_name for a20
col value for a55
col REDUNDANCY for a11
col PERCENT_USED for 999999999
col NO_OF_DISK for a11
col REBAL_PENDING for a5
col IMBALANCE for a5
col FREE_MB for a8
SELECT substr(target_name,2) target_name,
diskgroup,
-- disk_path,
MAX (DECODE (seq, 7, VALUE)) REDUNDANCY,
to_number( MAX (DECODE (seq, 4, VALUE))) PERCENT_USED,
--MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB,
MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB,
--MAX (DECODE (seq, 3, ceil(VALUE/1024),0)) FREE_GB,
MAX (DECODE (seq, 3, VALUE)) FREE_MB,
MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB,
MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK,
ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, VALUE)))) LUN_SIZE,
MAX (DECODE (seq, 5, decode(VALUE,'No','',value))) REBAL_PENDING,
MAX (DECODE (seq, 1, VALUE)) IMBALANCE
FROM (SELECT target_name,
key_value diskgroup,
VALUE,
metric_column,
ROW_NUMBER ()
OVER (PARTITION BY target_name, key_value
ORDER BY metric_column)
AS seq
FROM MGMT$METRIC_CURRENT
WHERE target_type in ('osm_instance','osm_cluster')
AND metric_column IN
('rebalInProgress',
'free_mb',
'usable_file_mb',
'type',
'computedImbalance',
'usable_total_mb',
'percent_used','diskCnt')
---'disk_path')
OR ( metric_column = 'total_mb'
AND metric_name = 'DiskGroup_Usage'))
GROUP BY target_name, diskgroup
order by 4,1,2;
The output :
TARGET_NAME DISKGROUP REDUNDANCY PERCENT_USED USABLE_TOTAL_GB FREE_MB USABLE_FREE_GB NO_OF_DISK LUN_SIZE REBAL IMBAL
--------------------
---------------------------------------- ----------- ------------
--------------- -------- -------------- ----------- ---------- ----- -----
ASM_pprlkcrs FRA
EXTERN 0 256 261845 256 1 256 0
ASM_prdlkcrs FRA
EXTERN 0 257 262997 257 1 257 0
ASM_dev-mtlunt119 TMPMBRDXVAL EXTERN 0
64 65407 64 1 64 0
ASM_qas-mtlunt89 ADOPTFRA EXTERN 0 64 65422 64 1 64
0
ASM_dev-mtlunt85 FRA
EXTERN 0 64 65468 64 1 64 0
ASM_dev-mtlunt105 TEMPRAD EXTERN 0 64 65360 64 1 64 0
ASM_dev-mtlunt115 TEMPRAD EXTERN 0 64 65376 64 1 64 0
ASM_dev-mtlunt119 TEMPRAD EXTERN 0 64 65364 64 1 64 0
ASM_dev-mtlunt105 RDXOCR EXTERN 0 32 32657 32 1 32 0
ASM_dev-mtlunt85 TBXOCR EXTERN 0 32 32688 32 1 32 0
ASM_val45 PIREL_ARCH EXTERN 0 32 32656 32 1 32 0
ASM_dev-mtlunt119 OCRD119 EXTERN 0 32 32632 32 1 32 0
ASM_mtlunt45 PIREL_ARCH EXTERN 0 32 32672 32 1 32 0
ASM_ADOPTCRS ADOPT_ARCH EXTERN 0 33 32670 32 1 33 0
ASM_dev-mtlunt115 OCR
EXTERN 0 16 16237 16 1 16 0
ASM_dev-mtlunt115 FRA
EXTERN 0 528 538510 526 2 264 .711
ASM_dev-mtlunt105 FRA EXTERN 1 256 259551 254 1 256 0
ASM_drprdrdx DROCR EXTERN 1 33 32375 32 1 33 0
ASM_pprrdx OCR
EXTERN 1 33 32375 32 1 33 0
ASM_PRDRDX FRA
EXTERN 1 384 387404 379 2 192 3.773
ASM_drprdrdx FRA
EXTERN 2 256 257208 252 2 128 .082
ASM_tbxcls TBXFRA EXTERN 3 64 63007 62 1 64 0
ASM_dev-mtlunt85 REDO
EXTERN 4 32 31454 31 1 32 0
ASM_val45 PIREL_DATA EXTERN 5 96 93439 92 3 32 .173
ASM_ADOPTCRS OCRDG EXTERN 9 33 29928 30 1 33 0
ASM_drptbxcls TBXFRA EXTERN 11 64 57730 57 1 64 0
ASM_dev-mtlunt119 REDORA EXTERN 13 64 56920 56 1 64 0
ASM_prdlkcrs OCR
EXTERN 14 33 29096 29 1 33 0
ASM_pprlkcrs OCR
EXTERN 14 33 28998 29 1 33 0
ASM_pprrdx FRA
EXTERN 17 256 216205 212 2 128 .039
ASM_mtlunt45 PIREL_DATA EXTERN 23 64 50329 50 1 64 0
ASM_tbxcls TBXREDO EXTERN 24 32 24388 24 1 32 0
ASM_dev-mtlunt119 FRAMBRDX EXTERN 28 64 47071 46 1 64 0
ASM_pprrdx REDO_RAD EXTERN 29 64 46744 46 1 64 0
ASM_prdlkcrs TEMPRAD EXTERN 30 65 46585 46 1 65 0
ASM_pprlkcrs TEMPRAD EXTERN 31 65 45903 45 1 65 0
ASM_PRDRDX REDO_RAD EXTERN 34 64 43260 43 1 64 0
ASM_dev-mtlunt85 OTANOTDATA EXTERN 36 64 41624 41 1 64 0
ASM_dev-mtlunt105 DATARDXVAL_OCR EXTERN 38 64 40760 40 1 64 0
ASM_val45 PIREL_REDO EXTERN 38 1 617 1 1 1 0
ASM_drptbxcls TBXDATA EXTERN 39 512 318164 311 2 256 .004
ASM_PRDRDX OCR
EXTERN 40 1 594 1 1 1 0
ASM_tbxcls TBXOCR EXTERN 40 1 594 1 1 1 0
ASM_drptbxcls TBXOCR EXTERN 40 1 589 1 1 1 0
ASM_tbxcls TBXDATA EXTERN 42 512 305256 299 2 256 .015
ASM_drprdrdx REDO_RAD EXTERN 42 64 38152 38 1 64 0
ASM_mtlunt45 PIREL_REDO EXTERN 47 1 523 1 1 1 0
ASM_drptbxcls TBXREDO EXTERN 48 32 16729 17 1 32 0
ASM_prdlkcrs RDXREDO EXTERN 49 65 33585 33 1 65 0
ASM_pprlkcrs RDXREDO EXTERN 50 65 33559 33 1 65 0
ASM_prdlkcrs DATARAD_OCR EXTERN 50 130 67048 66 2 65 .072
ASM_dev-mtlunt85 OTADEVDATA EXTERN 55 64 29652 29 1 64 0
ASM_ADOPTCRS ELEVA_DG EXTERN 59 64 27116 27 1 64 0
ASM_dev-mtlunt115 DATARDXQA EXTERN 60 792 322788 316 3 264 .022
ASM_dev-mtlunt119 DATAMBRDXVAL EXTERN 62 512 198216 194 2 256 .01
ASM_PRDRDX DATARAD_OCR EXTERN 63 64 24432 24 1 64 0
ASM_drprdrdx DATARAD_OCR EXTERN 63 64 24432 24 1 64 0
ASM_PRDRDX TEMPRAD EXTERN 63 64 24192 24 1 64 0
Aucun commentaire:
Enregistrer un commentaire