How can I use RMAN to show if there are archive logs that need to backed up?

Posted on

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;


list archivelog all backed up 0 times to sbt_tape;

Depending on the backup method you use.

You can query v$archived_log


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#  
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.

Leave a Reply

Your email address will not be published. Required fields are marked *