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 df.name, 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.

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