本脚本用于巡检24小时之内的备份情况
with backupset
as
(
select object_name,backup_time,to_char(sysdate-1,'yyyy-mm-dd') || '->1days' backup_check
from v$backupset
where backup_time between sysdate-1 and sysdate
union all
select object_name,backup_time,to_char(sysdate-7,'yyyy-mm-dd') || '->7days'
from v$backupset
where backup_time between sysdate-7 and sysdate-1
union all
select null,null,to_char(sysdate-1,'yyyy-mm-dd') || '->1days'
from dual
where not exists (select *
from v$backupset where backup_time between sysdate-1 and sysdate )
union all
select null,null,to_char(sysdate-7,'yyyy-mm-dd') || '->7days'
from dual
where not exists (select *
from v$backupset where backup_time between sysdate-7 and sysdate-1 )
)
select name,backup_check,max(decode(object_name,'ARCHIVE',to_char(backup_time,'yyyy-mm-dd-hh24:mi:ss'),'NULL')) ARCH_BAK_TIME,
max(decode(object_name,'ARCHIVE','NULL',to_char(backup_time,'yyyy-mm-dd-hh24:mi:ss'))) DB_BAK_TIME
from backupset,v$database
group by name,backup_check
输出结果如下所示:
第一行显示最近24小时的数据库备份和归档日志备份情况,第二行显示昨天之前7日的备份情况。
ARCH_BAK_TIME表示最新归档备份的发起时间,DB_BAK_TIME表示最新数据库备份的发起时间。NULL表示未发现成功完成的备份集。