Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Friday, 24 August 2012

Script to List Completed and Pending RMAN backup of last 24-hours

select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
              'Unknown type='||BACKUP_TYPE) TYPE,
       to_char(a.start_time, 'DDMON HH24:MI') start_time,
       to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
       substr(handle, -35) handle,
       nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
where  a.start_time between sysdate-1 and sysdate
  and  a.SET_STAMP = b.SET_STAMP
  and  a.SET_STAMP = d.SET_STAMP(+)
  and  a.SET_STAMP = l.SET_STAMP(+)
order  by start_time, file#;
Datafiles Backed up during past 24 Hours
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';


Post a Comment