How to find if there are datafiles that have objects that are not recoverable since last backup?

Using the following query one can find the datafiles that had nologging turned on, direct load or unrecoverable option was used on a datafile since the last successful backup.

SELECT, to_char(df.unrecoverable_time, ‘DD-MON-YYYY HH24:MI:SS’) unrecover_time
FROM v$datafile df,
(SELECT bd.file#, max(completion_time) completion_time
FROM v$backup_datafile bd GROUP BY bd.file#) bd
WHERE bd.file# (+) = df.file#
AND df.unrecoverable_time > bd.completion_time;

If it returns no rows it means no unrecoverable/nologging options were used since the last backup.

—————————————————————- ———————-
/u01/oradata/TESTDB/INDX_01.dbf 27-APR-2009 13:31:52
/u03/oradata/TESTDB/DATA_01.dbf 27-APR-2009 03:52:59

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.