How to find archivelog gaps in the backups

oraclerman

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:

Take full backup with archivelogs
Back up morning archivelogs and delete input
Back up evening archivelogs and delete input
Someone permanently deletes the backupsets containing the morning archivelogs
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?

Best Answer

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;

Related Solutions

Incremental level 1 backup with no Incremental level 0

Yes, if you want to prevent a level 1 from being created without a corresponding level 0 being available, then you need to do a crosscheck in the script so RMAN knows that the level 0 is not available.

Prior to 10g (or with compatibility < 10.0) Oracle would do a level 0 when a level 1 was done without a level 0. Since you are on 11g, I would expect your compatibility to be >=10.0, so RMAN "copies all blocks that have been changed since the file was created". This behavior seems to have changed between versions and there is some confusion as to what the current behavior is, but your findings are consistent with the documentation.

Row level restore with Oracle Recovery Manager (RMAN)

The answer to your question is no, however....

It sounds like a flashback query is what you need. Query the data as of a time when it existed and when it returns the correct data, insert it into the current table. This solution does require space in the UNDO tablespace sufficient to meet your UNDO_RETENTION requirements. It also doesn't use RMAN, but is significantly simpler than importing the entire schema.

INSERT INTO T1 (
   SELECT * FROM T1 AS OF TIMESTAMP sysdate-1 
   WHERE MyDateColumn = to_date('05/25/2011','MM/DD/YYYY')
);

Best Answer

Related Solutions

Incremental level 1 backup with no Incremental level 0

Row level restore with Oracle Recovery Manager (RMAN)

Related Question