Question :
My main concern is knowing if the Oracle Archive Logs haven’t been backed up in a while because something is broken that I may not know about.
When I query v$log it appears to be good with First_Time being very recent. I would think v$log wouuld have lots of rows if the logs had not been backing up.
SELECT * FROM v$log;
Shows only a few, two say current (four returned).
select * from v$log_history order by FIRST_TIME desc;
Shows lots.
Answer :
You can do this from RMAN.
list archivelog all backed up 0 times to disk;
or
list archivelog all backed up 0 times to sbt_tape;
Depending on the backup method you use.
You can query v$archived_log
SELECT *
FROM V$ARCHIVED_LOG
WHERE ARCHIVED != 'YES';
I came up with the following query:
select THREAD#, count(SEQUENCE#), min(SEQUENCE#), max(SEQUENCE#)
from v$loghist
where FIRST_CHANGE# >=
(select MAX_NEXT_CHANGE#
from V$BACKUP_ARCHIVELOG_SUMMARY)
group by THREAD#;
My issue with the accepted answer is that I also get the archive log already backed up once. The v$archived_log
, on the other hand, only states that the redo-log is archived, but not that it’s backed up.