OEM Notification on Generated Trace Files

The predefined metric “Dump Area Used (%)” can monitor space consumption for dump destination of oracle database. However, it just triggers on percentage occupied, and if there are several databases on the host using the same file system for trace files destination, the metric notifies DBA for all databases at once.
I decided to create a user-defined metric to gather information about generated trace files in order to clarify the notification and have that information in the OEM repository database for further reporting on accumulated data. I only took 11g databases since information from ADR can be read through internal tables in a database and for versions below, it would be required to build additional procedures to access trace files.
I checked Oracle 11g documentation but there were no even v$diag… views described; however, search in 11.2.0.3 database showed 88 of them. Going through them I found that I was looking for – V$DIAG_DIR_EXT (about V$DIAG_INFO I knew already). Finally I built SQL to calculate a number of newly created trace files for the last hour and put it as a user-defined metric to OEM:

select key_value, value from (
with sql_trc as (
/*+ all trace files for last 2 hours*/
select /*+ MATERIALIZE */PHYSICAL_PATH, CREATION_TIMESTAMP, PHYSICAL_FILENAME FROM V$DIAG_DIR_EXT
where PHYSICAL_FILENAME like '%trc'
and CREATION_TIMESTAMP > trunc(sysdate, 'HH') - interval '2' hour
),
/*+ only trace files related to rdbms */
sql_all as (
select distinct CREATION_TIMESTAMP, PHYSICAL_FILENAME from sql_trc
where PHYSICAL_PATH = (
select value from v$diag_info where name = 'Diag Trace')
),
/*+ trace files created within 1 hour ago */
sql_new1 as (
select count(*) cnt1 from sql_all
where CREATION_TIMESTAMP between trunc(sysdate, 'HH') - 1/24 and trunc(sysdate, 'HH') - 1/86400
),
/*+ trace files created within 2 hours ago */
sql_new2 as (
select count(*) cnt2 from sql_all
where CREATION_TIMESTAMP between trunc(sysdate, 'HH') - 2/24 and trunc(sysdate, 'HH') - 1 - 1/86400
)
/*+ count of files */
select 'CNT' key_value, cnt1 value from sql_new1
union all
/*+ percentage of growth */
select 'PCT', round(100*(cnt1/decode(cnt2, 0, 1, cnt2)), 2) from sql_new1, sql_new2
)

While I was creating the SQL I got stuck with one problem – if the value for physical path was taken from v$diag_info in subquery, it returned no rows.

SQL> select distinct  PHYSICAL_PATH  from V$DIAG_DIR_EXT
where PHYSICAL_FILENAME like 'TEST_ora_25089.trc'
and PHYSICAL_PATH = '/u01/oracle/diag/rdbms/test/TEST/trace';
PHYSICAL_PATH
-----------------------------------------------------------
/u01/oracle/diag/rdbms/test/TEST/trace
SQL> select value from v$diag_info where name = 'Diag Trace';
VALUE
-----------------------------------------------------------
/u01/oracle/diag/rdbms/test/TEST/trace
SQL> select distinct PHYSICAL_PATH from V$DIAG_DIR_EXT
where PHYSICAL_FILENAME like 'TEST_ora_25089.trc' and PHYSICAL_PATH =
(select value from v$diag_info where name = 'Diag Trace')
/
no rows selected

To avoid subquery, I used WITH statement and MATERIALIZE hints to fill internal temporary tables for further resulting output.

When the query to get a number of generated trace files was ready, I had to apply a template to only 11g databases, building the query to generate emcli verbs for specific databases. The template had the metric which was taken from one of the databases:

select './emcli apply_template -name="UDMs" -targets="'||target_name||':oracle_database" -input_file="FILE1:/home/oracle/udms_creds.txt"',
target_name, db_ver, host_name, dg_stat from (
select target_name, max(db_ver) db_ver, max(host_name) host_name, max(dg_stat) dg_stat from (
select target_name,
(case when property_name = 'DBVersion' then property_value end) db_ver,
(case when property_name = 'MachineName' then property_value end) host_name,
(case when property_name = 'DataGuardStatus' then decode(property_value, ' ', 'Primary', property_value) end) dg_stat
from MGMT$TARGET_PROPERTIES
where target_type = 'oracle_database'
and property_name in ('DBVersion', 'MachineName', 'DataGuardStatus')
)
group by target_name)
where db_ver like '%11%' and dg_stat = 'Primary'
order by target_name, host_name

and file udms_creds.txt had DBSNMP credentials for the metric. After having metrics running for some time, I got information about trace files growth from OEM repository:

select target_name, tm, cnt, pct from (
select target_name, tm, max(cnt) cnt, max(pct) pct from (
select target_name, to_char(rollup_timestamp, 'DD-MON-YY HH24:MI') tm,
decode(key_value2, 'CNT', to_number(average)) cnt,
decode(key_value2, 'PCT', to_number(average)) pct
from mgmt$metric_hourly
where target_type = 'oracle_database'
and metric_name = 'SQLUDMNUM'
and column_label = 'UDM_trc_files'
and key_value = 'UDM_trc_files'
and rollup_timestamp > sysdate - 1
) group by target_name, tm
) order by target_name, tm

Have a good day and enjoy adding new metrics to OEM repository!