Search

Wednesday, June 22, 2016

Monitor RMAN status, while a backup is running

SELECT dbfiles||' from '||numfiles "Datafiles backed up",
       cfiles "Control Files backed up", spfiles "SPFiles backed up"
  FROM (select count(*) numfiles from sys.v_$datafile),
       (select count(*) dbfiles
          from sys.v_$backup_datafile a, sys.v_$datafile b
         where a.file# = b.file#
           and a.completion_time > sysdate - 1),
       (select count(*) cfiles from sys.v_$backup_datafile
         where file# = 0 and completion_time > sysdate - 1),
       (select count(*) spfiles from sys.v_$backup_spfile
         where completion_time > sysdate - 1)
/
Archlog Files Backed up during past 24 Hours:
SELECT backedup||' from '||archived "Archlog files backed up",
       ondisk "Archlog files still on disk"
  FROM (select count(*) archived
          from sys.v_$archived_log where completion_time > sysdate - 1),
       (select count(*) backedup from sys.v_$archived_log
         where backup_count > 0
           and completion_time > sysdate - 1),
       (select count(*) ondisk from sys.v_$archived_log
         where archived = 'YES' and deleted  = 'NO')
/
RMAN Backups Still Running:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
       sofar, totalwork,
       elapsed_seconds/60 "ELAPSE (Min)",
       round(sofar/totalwork*100,2) "Complete%"
FROM   sys.v_$session_longops
WHERE  compnam = 'dbms_backup_restore'
/