Xrhrft

I manage Oracle databases that are backed up using RMAN. How can I find gaps in the backed-up archivelogs?

I.e., I want to detect when something like this happens:

1. Take full backup with archivelogs


2. Back up morning archivelogs and delete input


3. Back up evening archivelogs and delete input


4. Someone permanently deletes the backupsets containing the morning archivelogs


5. Crosscheck so RMAN knows some backupsets have gone missing

Short of doing or simulating a full restore and recovery, how can I detect that there is a gap? How can I see that backupsets are missing that would be needed for full recovery?

"How can I see that backupsets are missing that would be needed for full recovery?" That's exactly what crosscheck does. Examine the output of that command. Set up a scheduled job to run a crosscheck and write the output to a log file. Examine the log file. You could even get real sophisticated and have a script that parses the log file and send an email if anything is found.

Someone permanently deletes the backupsets containing the morning archivelogs

If you are using RMAN correctly and never mucking about with backupsets "by hand", then you should never get gaps in the archivelogs - such a condition renders your database unrecoveable and RMAN goes to great lengths to prevent you from doing so.

Ensure your RETENTION PERIOD and ARCHIVELOG DELETION POLICY are set correctly, for example:

RMAN> show all ; 

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 3 TIMES TO 'SBT_TAPE';

I think that REPORT is the appropriate command, but at the moment I cannot test it. Before you execute the report command you should execute a CROSSCHECK command so that rman does actually know which backups exists and which are missing.

If you want to find out if there are archive log backups missing then

REPORT NEED BACKUP DATABASE;

should do the job. The whole database must be backed up because of the missing archive logs the database cannot be restored to the current point in time. So the command will report all data files.

If backup pieces were destroyed that contain datafiles of the morning full backup then

REPORT NEED BACKUP DAYS 1 DATABASE;

So this command will report both problems.

The report-command is documented in the Backup and Recovery Reference and in the Backup and Recovery User's Guide.

So all in all do

CROSSCHEK BACKUP;

REPORT NEED BACKUP DAYS 1 DATABASE;