4

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?

1

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;
| improve this answer | |
0

"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.

| improve this answer | |
  • crosscheck will show me that a log is missing, but not that it is needed. If yesterday's archivelog backups go missing, but this morning's full backup and associated logs from today are not missing, I can still restore and recover. I'm trying to see if there is a way to detect when I can't restore to now. – Stephen Kendall Jul 7 '17 at 22:38
  • 1
    Whether or not it is needed for any particular recovery can only be known when the specs of that particular recovery are known. Rman has to consider whether it may be needed for any recovery within your specified retention policy. Depending on what backups are available, rman may decide to use an incremental backup for a particular part of a given recovery, or it may choose to use archivelogs for the same SCN range for a differently specified recovery. I'd concentrate of figuring out how you lost a backup in the first place .. and stop it from happening again . . . – EdStevens Jul 8 '17 at 0:01
  • . . . and stop it from happening again. And institute a secondary backup - a backup of the backups. – EdStevens Jul 8 '17 at 0:02
  • I understand that avoiding this problem is better than responding to it. Regardless, there should be a way to detect this. I understand that for RMAN to calculate anything about a recovery, the endpoint has to be known (e.g., SET UNTIL). Ok, assume that I specify the recovery to be as of exactly one hour before I run the check. Without spending hours running an actual recovery, is there a way to find out with certainty that I would not be able to because of missing archivelogs or missing archivelog backup pieces? – Stephen Kendall Jul 9 '17 at 17:10
  • You might try taking a look at the RC_* (Recovery Catalog) views. docs.oracle.com/cd/E11882_01/backup.112/e10643/toc.htm and see if you can tease out what you need there. Especially the RC_ARCHIVED_LOG and RC_BACKUP_ARCHIVELOG views. – EdStevens Jul 9 '17 at 20:11
0

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';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.