Use of a recovery catalog provides for additional protection against data loss. The catalog database holds information about databases, their structure, backupsets, and a host of other useful information. The best part about it is that all of this useful information for multiple databases is available in the tables and views of the user that owns the recovery catalog schema.

Typically, you would use a tool such as Oracle Enterprise Manager (OEM) to execute and monitor database backups. OEM can be configured so that you are sent alerts if there is a problem during execution of a backup job. This is a great asset to any DBA that is responsible for managing multiple Oracle databases.

As a DBA, there are many things that you can get wrong before your company makes you turn in your badge and gun. You can bounce the wrong instance, grant silly roles to database users, even allow a filesystem or ASM disk group to fill up and blame it on the storage administrators. These may elicit a slap on the wrist from management, but there is one thing that a DBA must absolutely not get wrong: backup and recovery.

DBAs are responsible for many databases these days, we can’t expect to know at any given point in time the current status of all backups, can we? Suppose management asks what is the earliest point in time that database DBTST23 can be recovered to. We likely setup backup jobs for DBTST23 many moons ago and have since forgotten that schedule. Was it nightly full backups? Full level zero on Sunday, then daily incrementals? What is the retention policy? Did we configure retention based on redundancy of datafiles or for a recovery window? Is the database running in archivelog mode? Do we even have a backup, and if so, is it recent? How large is the backup, does it use compression, and what was the original size of the database? Do we have an estimate for how long it would take to restore?

A recovery catalog database can help answer some of these questions, and in a hurry as well. The below single script gives you information that helps to answer many of the questions in the previous paragraph. Execute this against the recovery catalog database. The query assumes that the RMAN user owns the recovery catalog schema, adjust it for your recovery catalog schema owner.

column db_name       format a10 heading 'Database'
column oldest_backup format a20 heading 'Oldest Backup'
column newest_backup format a20 heading 'Newest Backup'
column input_size    format a20 heading 'Input Size'
column output_size   format a20 heading 'Output Size'
set linesize 200

select db_name,
       to_char(oldest_backup_time, 'yyyy-mm-dd hh24:mi:ss') oldest_backup,
       to_char(newest_backup_time, 'yyyy-mm-dd hh24:mi:ss') newest_backup,
       original_input_bytes_display input_size,
       output_bytes_display output_size
from rman.rc_backup_set_summary
order by db_name;

The script when run in SQL*Plus will give you a backup summary list for all databases that are registered in the recovery catalog. The output will look something like below:

Database   Oldest Backup        Newest Backup        Input Size    Output Size                                                                                                                   
---------- -------------------- -------------------- ------------- -------------------                                                                                                          
ORCL1      2023-12-01 17:45:08  2023-12-22 07:04:19     87.05G               17.75G                                                                                                                     
ORCL2      2023-12-01 18:41:28  2023-12-21 17:31:32     38.12G                5.93G                                                                                                                     
ORCLDG1    2023-12-01 18:36:19  2023-12-21 18:02:12     28.11G                5.99G                                                                                                                     
ORCLDG2    2023-12-01 18:31:28  2023-12-22 08:32:19     72.86G               17.50G                                                                                                                     

If you’re using a recovery catalog database, but you’ve not yet queried some of the tables and views within it, you could be missing out on valuable summary information like the above query provides. I encourage you to take another look at your recovery catalog, or if you do not use one, I encourage you to start. Even in the event that you never need to use the catalog during a recovery operation, it can be a valuable source of information. Plus, why not add another database to the list of those that we must maintain!