mark How to find the average active sessions between 2 dates. SELECT rollup_timestamp, average, minimum, maximum FROM mgmt$metric_daily WHERE target_name = ‘TRCOPDOV’ AND metric_label = ‘Throughput’ AND column_label = ‘Average Active Sessions’ AND rollup_timestamp BETWEEN TO_DATE (’01/11/2015′, ‘dd/mm/yyyy’) AND TO_DATE (’01/12/2015′, ‘dd/mm/yyyy’) ORDER BY 1;
How to resolve close waits from the OS Lets look at the close waits and see how many and the offending port. The following will show us the port and IPof the close wait netstat -an |grep -i close tcp 1 0 icudbaup02-priv.xxx.:53494 icudbaup01-priv.xxx.:61020 CLOSE_WAIT tcp 1 0 icudbaup02-priv.xxx.:53447 icudbaup01-priv.xxx.:61020 CLOSE_WAIT tcp 1 0 icudbaup02-priv.xxx.:53451 … Read more
SELECT t.target_name, t.target_type, el.severity, DECODE (el.open_status, 1, ‘Open’, ‘Closed’), el.msg, el.creation_date, el.incident_num, i.ticket_id FROM mgmt$events_latest el, mgmt$target t, mgmt$incidents i WHERE el.target_guid = t.target_guid AND I.INCIDENT_ID = el.incident_id AND t.target_type IN (‘oracle_listener’, ‘oracle_database’, ‘rac_database’, ‘host’) — AND el.open_status = 1 AND TRUNC (el.creation_date) >= SYSDATE – 7 AND i.ticket_id IS NOT NULL ORDER BY 4 … Read more
The MGMT$BLACKOUT_HISTORY file contains an overview of all finished blackouts for each target. To view the number of targets blacked out in the last 30 days, enter the following query: SELECT target_type, COUNT (*) cnt FROM mgmt$blackout_history WHERE start_time > SYSDATE – 30 GROUP BY target_type ;
The MGMT$BLACKOUTS file contains definitions of all blackouts known in the system. To view a list of future scheduled blackouts, enter the following query: SELECT blackout_name, reason, created_by, schedule_type, scheduled_time FROM mgmt$blackouts WHERE status = ‘Scheduled’;
To return all targets under blackout, enter the following query: SELECT target_name, target_type, start_time, end_time FROM mgmt$blackout_history WHERE sysdate BETWEEN start_time AND NVL(end_time,sysdate+1/60*60*24);
select target_name, max(rollup_timestamp) from mgmt$metric_daily where target_TYPE=’rac_database’ and METRIC_NAME=’DATABASE_SIZE’ and column_label like ‘Used%’ group by target_name;
with my_data AS ( select key_value2, ROUND ((( (MAX (AVERAGE) – MIN (AVERAGE)))/1024/1024),2) as AVERAGE from mgmt$metric_daily where target_type = ‘osm_cluster’ and key_value like ‘DATA%’ and column_label = ‘Total Bytes’ and rollup_timestamp >= sysdate-182 group by key_value2 ) select * from my_data pivot ( AVG(AVERAGE) for key_value2 IN (‘ASM’, ‘EILPRD1’, ‘EILPRDDOV’, ‘EILPRDSUN’, ‘NEWDSS02DOV’, ‘NEWDSS03PRD’, ‘NEWDSSOAP01’, … Read more
with my_data AS ( select key_value2, rollup_timestamp, average from mgmt$metric_daily where target_type = ‘osm_cluster’ and key_value like ‘DATA%’ and column_label = ‘Total Bytes’ and rollup_timestamp >= sysdate-14 ) select * from my_data pivot ( sum(average/1024/1024/1024) for key_value2 IN (‘ASM’, ‘EILPRD1’, ‘EILPRDDOV’, ‘EILPRDSUN’, ‘NEWDSS02DOV’, ‘NEWDSS03PRD’, ‘NEWDSSOAP01’, ‘NEWDSSOAP04’, ‘NEWSOA02DOV’, ‘NEWSOAP01’, ‘NEWSOAP04’, ‘NEWTOLTTP01’, ‘ORCDBAUZ01’, ‘SBYDSS02DOV’, ‘SBYDSS02PRD’, ‘SBYDSS03PRD’, ‘SBYDSSOAP01’, … Read more
SELECT target_name, ROUND ( (MAX (AVERAGE) – MIN (AVERAGE)) / 14 / 1024 / 1024 / 1024/1024,2) AS GB FROM mgmt$metric_daily WHERE target_name in (select target_name from mgmt_targets where target_type=’osm_cluster’) AND key_value like ‘DATA%’ AND column_label = ‘Total Bytes’ AND TRUNC (rollup_timestamp) >= TRUNC (SYSDATE) – 14 GROUP BY target_name;