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
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,
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!