general queries for DBA

To get a picture of all the full or incremental backups taken along with the time it took, use the below script


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
from   SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
       SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
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#

This shows the backup of all datafiles and archive log files

 

 

To see if archived logs got backed up in last 24 hours and how many are still sitting on the disks, use the script below

SELECT backedup||' out of  '||archived||' archive logs backed up'  "Archlog files backed up",
       ondisk "Archlog files on disk"
  FROM (select count(*) archived
          from v$archived_log where completion_time > sysdate - 1),
       (select count(*) backedup from v$archived_log
         where backup_count > 0
           and completion_time > sysdate - 1),
       (select count(*) ondisk from v$archived_log
         where archived = 'YES' and deleted  = 'NO')

To find out how many datafiles out of the total got backed up in last 24 hours, use the script below


SQL> SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up",
                          cfiles "Control Files backed up", spfiles "SPFiles backed up"
          FROM    (select count(*) numfiles from v$datafile),
                         (select count(*) dbfiles  from v$backup_datafile a, v$datafile b
                          where a.file# = b.file#   and a.completion_time > sysdate - 1),
                         (select count(*) cfiles from v$backup_datafile
                          where file# = 0 and completion_time > sysdate - 1),
                         (select count(*) spfiles from v$backup_spfile
                         where completion_time > sysdate - 1);