Irrecoverable full backup part II : reporting

After my post Can you restore from a full online backup ?, I needed to come up with a report.

Assuming that each backup goes in a different directory, I just wrote two reports.

1. Report gaps in v$backup_redolog (or rc_backup_redolog if you use the catalog)
   
   DIR FIRST_CHANGE# NEXT_CHANGE#
   ------- ------------- ------------
   /bck01/ 284891 285140
   /bck01/ 285140 285178

   /bck02/ 284891 285140
   === GAP ===
   /bck02/ 285178 285245

   /bck03/ 285178 285245
/bck03/ 285245 286931
/bck03/ 286931 287803
/bck03/ 287803 288148


   This could be done with analytics, by checking where the last next_change is not the current first_change, within a directory
   
SELECT dir,
LAG missing_from_change#,
first_change# missing_to_change#
FROM (
SELECT REGEXP_REPLACE (handle, '[^/\]+$') dir,
first_change#,
next_change#,
LAG(next_change#) OVER (
PARTITION BY REGEXP_REPLACE (handle, '[^/\]+$')
ORDER BY first_change#
) LAG
FROM v$backup_piece p
JOIN v$backup_redolog l
USING (set_stamp, set_count))
WHERE LAG != first_change#;

   DIR MISSING_FROM_CHANGE# MISSING_TO_CHANGE#
------- -------------------- ------------------
/bck02/ 285140 285178


2. Reports directories where archivelogs don’t include changes (backup redolog) from the earliest to the latest checkpoint (backup datafile)
   
   SELECT
   REGEXP_REPLACE (handle, '[^/\]+$') dir,
   MIN (checkpoint_change#),
   MAX (checkpoint_change#),
   MIN (first_change#),
   MAX (next_change#)
   FROM v$backup_piece p
   LEFT JOIN v$backup_datafile f
   USING (set_stamp, set_count)
   LEFT JOIN v$backup_redolog l
   USING (set_stamp, set_count)
   WHERE handle IS NOT NULL
   HAVING
   MIN (checkpoint_change#) < MIN (first_change#) OR MAX (checkpoint_change#) > MAX (next_change#)
   GROUP BY REGEXP_REPLACE (handle, '[^/\]+$');

   DIR MINCHECKPT MAXCHECKPT MINFIRST MAXNEXT
------- ---------- ---------- ---------- ----------
/bck04/ 954292 954299 959487 1145473

   the archives for the changes from 954292 to 959487 are missing.

If some archive backups are missing in one directory, it does not mean the database is irrecoverable, the archive backups could be in another directory. But it means that single directory would no longer permit you to restore or duplicate.

Another approach with RESTORE PREVIEW was provided by Franck in my previous post : List all RMAN backups that are needed to recover.

Usual disclaimer: there are plenty of other irrecoverabilty causes from hardware defect to backup “optimization” that are beyond the scope of this post.