OEM Management view:
Whenever OEM Grid control is being practiced to monitor a majority of your databases and whenever RMAN is being used to take backup of the databases ‘either to Tape or to disk’, then a fast way to get backup status reports right away from the OEM repository database is to run a script like to the below.
In one of my customer environments, there’s no RMAN catalog being used and there is need to create this from the OEM repository to report on a routine basis:
1-Backup report of all databases FROM OEM Database:
You need to login in your OEM database.
. oraenv
oemdba1
sqlplus / as sysdba
sql>
set lines 200 pages 999
col database_name format a20
col host format a50
col status format a28
col start_time format a20
col end_time format a20
col OUTPUT_BYTES_DISPLAY format a20
col time_taken_displat format a10
col output_device_type format a10
select host,DATABASE_NAME, status, to_char(start_time,’dd-MON-yyyy hh24:mi’) start_time,
to_char(end_time,’dd-MON-yyyy hh24:mi’) end_time, input_type,OUTPUT_BYTES_DISPLAY,output_device_type
from mgmt$ha_backup
order by database_name,host;
2- Backup report of database which status is failed:
set lines 200 pages 999
col database_name format a30
col host format a45
col status format a28
col start_time format a20
col end_time format a20
col OUTPUT_BYTES_DISPLAY format a12
col time_taken_displat format a20
col output_device_type format a13
col status format a25
select host,DATABASE_NAME, status, to_char(start_time,’dd-MON-yyyy hh24:mi’) start_time,
to_char(end_time,’dd-MON-yyyy hh24:mi’) end_time, input_type,OUTPUT_BYTES_DISPLAY,output_device_type
from mgmt$ha_backup where STATUS= ‘FAILED’ or status= ‘COMPLETED WITH ERRORS’ order by database_name,host;
3-Backup report of database which COMPLETED WITH ERRORS:
set lines 200 pages 999
col database_name format a30
col host format a45
col status format a28
col start_time format a20
col end_time format a20
col OUTPUT_BYTES_DISPLAY format a12
col time_taken_displat format a20
col status format a25
select host,DATABASE_NAME, status, to_char(start_time,’dd-MON-yyyy hh24:mi’) start_time,
to_char(end_time,’dd-MON-yyyy hh24:mi’) end_time, input_type,OUTPUT_BYTES_DISPLAY
from mgmt$ha_backup where STATUS= ‘FAILED’ or status= ‘COMPLETED WITH ERRORS’ order by database_name,host;
4-Backup report of database as per database name:
set lines 200 pages 999
col database_name format a20
col host format a45
col status format a28
col start_time format a20
col end_time format a20
col OUTPUT_BYTES_DISPLAY format a12
col time_taken_displat format a20
col output_device_type format a13
col status format a25
select host,DATABASE_NAME, status, to_char(start_time,’dd-MON-yyyy hh24:mi’) start_time,
to_char(end_time,’dd-MON-yyyy hh24:mi’) end_time, input_type,OUTPUT_BYTES_DISPLAY,output_device_type
from mgmt$ha_backup where database_name in (‘prod01’, ‘prod02’, ‘prod03’, ‘prod04’, ‘qaxyz01’, ‘qaxyz02’, ‘pa1234’) order by database_name,host;
5-Database RMAN Backup report where STATUS= ‘FAILED’ or status= ‘COMPLETED WITH ERRORS’ :
set lines 200 pages 999
col database_name format a20
col host format a45
col status format a25
col start_time format a20
col end_time format a20
col time_taken_displat format a20
col status format a25
select host,DATABASE_NAME, status, to_char(start_time,’dd-MON-yyyy hh24:mi’) start_time,
to_char(end_time,’dd-MON-yyyy hh24:mi’) end_time,OUTPUT_BYTES_DISPLAY
from mgmt$ha_backup where STATUS= ‘FAILED’ or status= ‘COMPLETED WITH ERRORS’ order by database_name,host;