Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Tuesday, 27 April 2010

OEM Repository Scripts

This Blog will be a running compilation of useful Enterprise Manager Repository scripts (run as the SYSMAN user). Please note some of the metric names used here may be exclusive to my work place and not generic. You should be able to find something similar in your databases though!
 
Database Sizes (1)
select a.DTTM 
     , trim(substr(a.target_name, instr(a.target_name,'.')+1,20)) HOST_NAME
     , trim(substr(a.target_name, 1, instr(a.target_name,'.')-1)) DATABASE
     , ALLOC_GB
     , USED_GB
   from
(SELECT TARGET_NAME 
     , TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD') DTTM
     ,TO_CHAR(MAX(AVERAGE),'999999.99') USED_GB
  FROM MGMT$METRIC_DAILY 
 WHERE METRIC_NAME = 'DATABASE_SIZE'
   AND TO_CHAR(ROLLUP_TIMESTAMP,'DD') = '01'
   AND METRIC_COLUMN = 'USED_GB'
GROUP BY TARGET_NAME, TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD')) a ,
(SELECT TARGET_NAME 
     , TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD') DTTM
     ,TO_CHAR(MAX(AVERAGE),'999999.99') ALLOC_GB
  FROM MGMT$METRIC_DAILY 
 WHERE METRIC_NAME = 'DATABASE_SIZE'
   AND TO_CHAR(ROLLUP_TIMESTAMP,'DD') = '01'
   AND METRIC_COLUMN = 'ALLOCATED_GB'
GROUP BY TARGET_NAME, TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD')) b
WHERE A.TARGET_NAME = B.TARGET_NAME
  AND A.DTTM = B.DTTM
ORDER BY HOST_NAME, DATABASE, DTTM
Database Sizes (2)
SELECT target_name, ROUND (SUM (FILE_SIZE) / 1024 / 1024 / 1024) GB
FROM MGMT$db_datafiles_all
GROUP BY target_name
Datafile Sizes - A good way to find datafiles incorrectly set to autoextend to Unlimited, i.e. growing to 32768M
SELECT target_name
, file_name
, sum (file_size) / 1024 / 1024
FROM mgmt$db_datafiles_all
HAVING sum(file_size)/1024/1024 > 15000
GROUP BY target_name, file_name
ORDER BY 3 desc;
Tablespace Allocated and Used Space Query
select A.RT
, A.ALLOCATED
, B.USED
from (
SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
, AVG(AVERAGE) ALLOCATED
FROM MGMT$METRIC_DAILY
WHERE TARGET_NAME LIKE {database_name}
AND METRIC_NAME='tbspAllocation'
and key_value={tablespace_name}
and METRIC_COLUMN='spaceAllocated'
group by TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')
) a,
(
SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
, AVG(AVERAGE) USED
FROM MGMT$METRIC_DAILY
WHERE TARGET_NAME LIKE {database_name}
AND METRIC_NAME='tbspAllocation'
and key_value={tablespace_name}
and METRIC_COLUMN='spaceUsed'
group by TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')
) b
where a.rt = b.rt
order by a.rt DESC;
Clear Alerts
select 'execute em_severity.delete_current_severity(p_target_guid =>'''||sev.target_guid||''', p_metric_guid =>'''||sev.metric_guid||''', p_key_value =>'''||key_value||''');' as command
from MGMT_TARGETS tgt, MGMT_CURRENT_SEVERITY sev
where TGT.TARGET_NAME like '%{TGT_NAME}%';
For older severities - just a straight delete from MGMT_SEVERITY will do the trick.
User Defined Metrics
select *
from mgmt$metric_current
where metric_name like '%UDM%';

7 comments:

Steve Harville said...

Thanks for posting this Nazim!

Steve Harville said...

Thanks for posting this Nazim!

Abul said...

If possible can you post a oem repository script with below details.

database_name,
hostname,
database_type ,
version

Thanks in advance
By-
Abul

nazimcricket said...

Hi Abul,

From memory

select * from mgmt$target;

Let me know if this doesn't work and I'll double-check it :)

Naz

prabhakar talari said...

Hi Nazim,

Could you provide is there any script to get archive log generation per your for a target through repository database.

Thank in advance

Prabhakar

prabhakar talari said...

Hi Nazim,

Could you provide me is there any script to get archive log generation per day for a target through repository database.

Thank in advance.

Prabhakar

nazimcricket said...

Sorry Prabhakar, I haven't comes across any views in the repository that stores the archivelog information you're after.

I personally would write a shell script to obtain this information through the O/S for example in unix you could have your script do a du -sh * across the archivelog directory of each target and collate/administer the results as necessary.