SQL QUERY TO CHECK STATUS OF BACKUP AND DATABASES REGISTERED TO RMAN CATALOG
If you use rman catalog and need to check the status of backups in catalog for different database or need a daily report , use this script
col OUTPUT_DEVICE_TYPE format a6
col OUTPUT_DEVICE_TYPE format a10
col status format a10
col TIME_TAKEN_DISPLAY format a10
col DAYOFENDTIME format a10
col OUTPUT_BYTES_DISPLAY format a10
select db_name,input_type,
output_device_type,status,
output_bytes_display,time_taken_display,
to_char(start_time,'DD/MM/YYYY HH24:MI') start_time,
to_char(end_time,'DD/MM/YYYY HH24:MI') end_time,
to_char(end_time,'DAY') dayofendtime
from RC_RMAN_BACKUP_JOB_DETAILS
order by db_name,input_type;Output:
SQL> col OUTPUT_DEVICE_TYPE format a6
col OUTPUT_DEVICE_TYPE format a10
col status format a10
col TIME_TAKEN_DISPLAY format a10
col DAYOFENDTIME format a10
col OUTPUT_BYTES_DISPLAY format a10
select db_name,input_type,
output_device_type,status,
output_bytes_display,time_taken_display,
to_char(start_time,'DD/MM/YYYY HH24:MI') start_time,
to_char(end_time,'DD/MM/YYYY HH24:MI') end_time,
to_char(end_time,'DAY') dayofendtime
from RC_RMAN_BACKUP_JOB_DETAILS
order by db_name,input_type;SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8
DB_NAME INPUT_TYPE OUTPUT_DEV STATUS OUTPUT_BYT TIME_TAKEN START_TIME END_TIME DAYOFENDTI
-------- ------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
ORCL11X CONTROLFILE DISK COMPLETED 9.33M 00:00:06 28/07/2021 21:06 28/07/2021 21:06 WEDNESDAY
ORCL11X DB FULL FAILED 0.00K 28/07/2021 19:22
ORCL11X DB FULL DISK COMPLETED 285.10M 00:01:07 28/07/2021 20:44 28/07/2021 20:45 WEDNESDAY