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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s