Verifica Log de Backup RMAN - Archives

set pages 200 lines 200
set feedback off
set timing on
select    decode(status, 'COMPLETED', 0,1)
from v$RMAN_BACKUP_JOB_DETAILS
where session_key =(select max(session_key)    from v$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('ARCHIVELOG'));

Verifica Log de Backup RMAN

set feedback off
set timing on
select decode(status, 'COMPLETED', 0,1) as STATUS
from v$RMAN_BACKUP_JOB_DETAILS
where session_key = (select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR'));

Verifica Archives Gerados por Dia

set lines 200 pages 200
set timing on;
select    
    trunc(first_time) day
    ,to_char(sum(blocks * block_size)/1024/1024,'999G999D99') MB
from gv\$archived_log
where first_time > trunc(sysdate) -30
and first_time < sysdate +1
group by trunc(first_time)
order by 1 desc;

Verifica Archivos Gerados no Ultimo Backup Full RMAN

set lines 200 pagesize 80
col TAG for a20
col DEVICE for a8
col CAMINHO for a65
col INICIO for a15
col FINAL for a17
col TAMANHO_TOTAL for a15
col TAMANHO for a10
set feedback off
set timing on
alter session set nls_date_format='dd/mm/yy hh24:mi';
select     /*+ rule */ tag
    ,device_type as DEVICE
    ,handle as CAMINHO
    ,START_TIME as INICIO
    ,COMPLETION_TIME as FINAL
    ,SIZE_BYTES_DISPLAY as TAMANHO
from v$backup_piece_details
where session_recid = (select max(session_recid) from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR') and status = 'COMPLETED')
and START_TIME > sysdate -30
order by START_TIME;
select     /*+ rule */  TO_CHAR(sum(bytes)/1024/1024,'9G999G999D9')  as TAMANHO_TOTAL
from v$backup_piece_details
where session_recid = (select max(session_recid) from V\$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR') and    status = 'COMPLETED')
and    START_TIME > sysdate -30;

Verifica Ultimos Backups de Archives Gerados

set pages 200 lines 200
set feedback off
col handle format a75
col tag format a25
col DEVICE_TYPE format a10
col TAMANHO for a10
col INICIO for a20
col FINAL for a20
set timing on
alter session set nls_date_format='dd/mm/yy hh24:mi';
select /*+ rule */ tag
    ,device_type
    ,handle
    ,START_TIME as INICIO
    ,COMPLETION_TIME as FINAL
    ,SIZE_BYTES_DISPLAY as TAMANHO
from v$backup_piece_details
where session_recid > (select max(session_recid) -5    from V$RMAN_BACKUP_JOB_DETAILS    where START_TIME > sysdate-10 and input_type in('ARCHIVELOG') and status = 'COMPLETED')   
and START_TIME > sysdate -30 order by START_TIME;

Verifica Quantidade de Archives Gerados por Hora

set feedback off
set timing on
alter session set nls_date_format='dd/mm/yy';
set pagesize 80 lines 200
col 00 for a4
col 01 for a4
col 02 for a4
col 03 for a4
col 04 for a4
col 05 for a4
col 06 for a4
col 07 for a4
col 08 for a4
col 09 for a4
col 10 for a4
col 11 for a4
col 12 for a4
col 13 for a4
col 14 for a4
col 15 for a4
col 16 for a4
col 17 for a4
col 18 for a4
col 19 for a4
col 20 for a4
col 21 for a4
col 22 for a4
col 23 for a4
select     trunc(first_time) day
    ,to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)), '999') "00"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)), '999') "01"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)), '999') "02"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)), '999') "03"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)), '999') "04"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)), '999') "05"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)), '999') "06"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)), '999') "07"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)), '999') "08"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)), '999') "09"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)), '999') "10"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)), '999') "11"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)), '999') "12"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)), '999') "13"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)), '999') "14"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)), '999') "15"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)), '999') "16"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)), '999') "17"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)), '999') "18"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)), '999') "19"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)), '999') "20"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)), '999') "21"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)), '999') "22"
    ,to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)), '999') "23"
from gv$log_history
where first_time > trunc(sysdate -5)
and first_time < sysdate + 1
group by trunc(first_time)
order by 1;

Verifica Estatisticas de Backup RMAN

set lines 200 pagesize 200
set timing on
col STATUS format a10
col tempo format a10
col INPUT_TYPE for a15
col OPTIMIZED for a10
col tam_backup for a10
col inicio for a20
col final for a20
select *
from (select INPUT_TYPE
        ,STATUS
        ,to_char(START_TIME,'DD/MM/yy hh24:mi') as INICIO
        ,to_char(END_TIME,'DD/MM/yy hh24:mi') as FINAL
        ,TIME_TAKEN_DISPLAY tempo
        ,OPTIMIZED
        ,d.OUTPUT_BYTES_DISPLAY tam_backup
    from V$RMAN_BACKUP_JOB_DETAILS d
    where START_TIME > sysdate-30
    order by session_key desc)
where rownum <=30;