A while ago I was reviewing the metric extensions of our Enterprise Manager Cloud Control 13.2.0.0, we use to administer a couple of hundred databases. All acceptance and production databases are installed on Linux RAC-clusters with underlying ASM storage. One of the custom reports summarized the size and number of LUN’s, using a query I had found on the internet:
SELECT target_name,
diskgroup,
MAX (DECODE (seq, 7, VALUE)) as REDUNDANCY,
MAX (DECODE (seq, 4, ceil(VALUE))) as PERCENT_USED,
(100 – MAX (DECODE (seq, 4, ceil(VALUE)))) as PERCENT_FREE,
MAX (DECODE (seq, 6, ceil(VALUE/1024))) as TOTAL_GB,
MAX (DECODE (seq, 9, ceil(VALUE/1024))) as USABLE_TOTAL_GB,
— MAX (DECODE (seq, 3, ceil(VALUE/1024))) as FREE_GB,
MAX (DECODE (seq, 8, ceil(VALUE/1024))) as USABLE_FREE_GB,
MAX (DECODE (seq, 2, to_number(VALUE))) as NO_OF_DISK,
ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, VALUE)))) as LUN_SIZE,
MAX (DECODE (seq, 5, decode(VALUE,’No’,”,value))) as REBAL_PENDING,
MAX (DECODE (seq, 1,
CASE
WHEN (VALUE > 1 AND diskgroup NOT LIKE ‘%VOTE%’) THEN VALUE
ELSE ”
END)) as 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 sysman.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’)
OR ( metric_column = ‘total_mb’
AND metric_name = ‘DiskGroup_Usage’)))
GROUP BY target_name, diskgroup
order by 1,2;
But in the daily administration the answer to a more burning question was missing, namely: How many candidate LUN’s /disks are still available on this system, which could be added to one of the disk groups, if necessary? Or should we rapidly order extra storage? And are there other systems which need attention?
On its own we used on a single instance a query like:
SQL> SELECT count(disk_number), round(sum(os_mb)/1024,1)
FROM gv$asm_disk
WHERE HEADER_STATUS in (‘CANDIDATE’, ‘FORMER’)
AND os_mb/1024 > 10;
(BTW: the last term is to exclude candidates of voting disks)
This query answers part of the above questions for a given database, but we liked to see this information about all cluster machines in one go.
The only central place for this kind of information is the EM repository. But where to find it in the EM repository? Or how to use it in a report?
After frustrating myself with wild experiments with UNION ALL –queries and such like, I finally came up with the following:
First, I created a new Metric Extention “‘ME$Check_vrije_disks” (in English: “ME$CheckFreeDisks”) which uses the above query in its SQL Adapter. Later it defines two data columns: one for the number of disks and one for the total amount of disk space on those unused disks (in case we have different size of LUNS’s). Both are of data type Number. For the number of disks (here: ‘Aantal_disks’) we defined a warning threshold of 2 because we always want to hold 2 disks in reserve in case we run out of space in the weekends and can not be sure that we get extra storage quick enough.
After successful testing, this ME was deployed to all targets of the type “Cluster ASM” (type: osm_cluster and osm_instance). And this takes care of the disk counting per individual (cluster) ASM.
Now to the bit where the overview is created over all ASM installations, which is a scheduled Information Publisher report called “Show Available ASM Disks”. It was placed in the category “Custom reports >> subcategory Space”. This report uses a specific target: the OEM repository database.
There is no period attached and the standard monitoring logins are used.
Under tab “Elements” a “Table from SQL” uses the query below:
SELECT target_name “ASM Cluster”
,VALUE “Free Disks”
FROM sysman.MGMT$METRIC_CURRENT
WHERE target_type in (‘osm_instance’,’osm_cluster’)
AND ( metric_column = ‘Aantal_disks’
AND metric_name = ‘ME$Check_vrije_disks’
)
order by VALUE
Where ‘Aantal_disks’ is the (Dutch) name given to the data column in the ME to indicate the number of free candidate disks on a specific target, and ‘ME$Check_vrije_disks’ is the name of the metric extention. Overall, this query fetches all individual counts and displays them in ordered form.
Finally, we set the schedule to early Friday afternoon, so we still have enough time to order new disks if necessary and set the access to all superadministrators.
The report is mailed to a common mailbox and all the admins have to do is a) check whether the weekly mail is there on time and if so, b) look for cluster names with less than 2 free disks (and if not, order new disks).
Hope this helps to sprout new ideas.
Have fun!
Nice!