denrepo

OracleBackup & recovery

Datafiles without a recent backup

Datafiles with no backup of any kind in the last two days, or none at all. A new datafile added after the last full backup shows up here first.

Not yet verified. How scripts are tested

ora-backup-gaps.sql 
1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN file#       HEADING "FILE#"       FORMAT 999994COLUMN name        HEADING "DATAFILE"    FORMAT A755COLUMN last_backup HEADING "LAST BACKUP" FORMAT A166 7SELECT f.file#, f.name,8       NVL(TO_CHAR(MAX(b.completion_time), 'YYYY-MM-DD HH24:MI'), 'never') AS last_backup9FROM v$datafile f10LEFT JOIN v$backup_datafile b ON b.file# = f.file#11GROUP BY f.file#, f.name12HAVING MAX(b.completion_time) IS NULL13    OR MAX(b.completion_time) < SYSDATE - 214ORDER BY f.file#;

Save it as ora-backup-gaps.sql and run it with SQL> @ora-backup-gaps.

Open in denrepoRelated tool: RMAN command builder

Helps with

Part of these runbooks

More Oracle scripts: Backup & recovery