Creating a Report of available ASM candidate disks from the OEM Repository

0

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!

About Author

Karin is an experienced, broadly orientated Oracle consultant (senior DBA consultant (OCP)) and is specializing in Oracle Database Security (Oracle Implementation Specialist Security 11g). Karin uses to work in divers enterprise environments like pension fonds, banks or pharmaceuticals, where ITIL and Service Management software products (e.g. Assyst, Remedy, Jira, GLPI) play a big role. She is regularly working for custumers in development projects and migrations, so her experience lead her to pay special attention in the areas of maintenance and (database)infrastructure. Her knowledge embraces multiple operating systems like OpenVMS, Linux, HP-UX and of course Windows, and she is comfortable with Oracle Database versions starting with Oracle 7 up to 12c and virtualization environments like vmWare and Oracle Virtualbox. Add to this her ability of scripting (for Windows commandline and Powershell, *nix shells) and her knowlegde of Oracle applicatieservers (incl. WebLogic) to her portfolio and it makes her a technical allround Oracle specialist. Karin is very customer and service orientated and always strives to reach her goals. She has a very sharp analytical mind and a keen eye for the details in the big picture. Karin is a senior DBA at AMIS b.v. for more than 15 years and she still likes the changing environments she is able to work in and meeting new and interesting people all the time.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.