Environment
Oracle Enterprise Manager (OEM) Grid Control 11.1
Report
This report shows backup jobs scheduled in the given time
interval. It also shows HOST name and IP address of the database target which
is handy for preparing for network, server or co-location outages.
Modify parameters in the end of the SQL.
Attention! The report looks
for NEXT execution time (not all future ones). If a job is supposed to run
before the given interval as well as in the interval, it will not appear in the
report.
Run it in OEM repository (OMR) as any user who can select
SYSMAN tables.
SELECT
j.job_name,
t.target_name, --t.target_type,
t.target_guid
--j.job_owner, j.job_type,
--j.job_id,
--e.execution_id, e.start_time, e.end_time,
DECODE(status,
1, 'Scheduled',
2, 'Running',
3, 'Error',
4, 'Failed',
5, 'Succeeded',
6, 'Suspended By User',
7, 'Suspended: Agent Unreacheable',
8, 'Stopped',
9, 'Suspended on Lock',
10, 'Suspended on Event',
11, 'Stop Pending',
13, 'Suspend Pending',
14, 'Inactive',
15, 'Queued',
16, 'Failed Retried',
17, 'Suspended',
18, 'Skipped', status) status,
e.scheduled_time,
DECODE(s.timezone_info, 3, to_char(s.timezone_offset), s.timezone_region) timezone_region,
DECODE (s.timezone_info,
1, 'Repository',
2, 'Agent',
3, 'Specified Offset/Region',
4, 'Specified Offset/Region',
s.timezone_info) timezone_type,
DECODE (s.frequency_code, 1, 'One Time',
2, 'Interval',
3, 'Daily',
4, 'Weekly',
5, 'Monthy',
6, 'Yearly') schedule_type,
s.interval,
TH.TARGET_NAME host_name, TP.PROPERTY_VALUE
IP_address
FROM
sysman.MGMT_JOB j,
sysman.MGMT_JOB_EXEC_SUMMARY e,
sysman.MGMT_JOB_TARGET jt,
sysman.MGMT_TARGETS t,
sysman.MGMT_TARGET_ASSOCS ta,
sysman.MGMT_TARGETS th,
sysman.MGMT_TARGET_PROPERTIES tp,
sysman.MGMT_JOB_SCHEDULE s
WHERE
j.schedule_id = s.schedule_id AND
J.IS_LIBRARY = 0 and
j.system_job = 0 AND
j.nested=0 AND
j.job_id=e.job_id AND
j.is_corrective_action=0 AND
e.job_id=jt.job_id (+) AND
e.execution_id=jt.execution_id (+) AND
jt.target_guid=t.target_guid (+)
and jt.target_guid=TA.SOURCE_TARGET_GUID
and TA.ASSOC_TARGET_GUID=Th.TARGET_GUID and Th.TARGET_TYPE='host'
and TA.ASSOC_TARGET_GUID=TP.TARGET_GUID and TP.PROPERTY_NAME='IP_address'
--
-- you can modify the
following parameters
--
and Job_name like 'BACKUP%'
--and status = 1 --
Scheduled
and scheduled_time between to_date('2012-10-27
16:00','yyyy-mm-dd
hh24:mi') and to_date('2012-10-28
10:00','yyyy-mm-dd hh24:mi');
