REPORT RMAN

REPORT  RMAN querying  catalog  RMAN not each  db:

Report  day to  day:
[/appl/oracle/BIN]# cat report_rman.sql
set  lines  200
set  pages  250
prompt '-------------------------------------------------------------------------'
prompt 'backup bilan'
prompt '-------------------------------------------------------------------------'
select  status, count(status)  from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS where trunc(START_TIME) >trunc(sysdate -2) group by  status;

prompt '-------------------------------------------------------------------------'
prompt 'backup  ok'
prompt '-------------------------------------------------------------------------'
 select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
 input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
 from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a  where trunc(START_TIME) >trunc(sysdate -2)
and  status='COMPLETED'
   and  b.db_key=a.db_key  order  by 4;

prompt '-------------------------------------------------------------------------'
prompt  'backup  a  controler'
prompt '-------------------------------------------------------------------------'
 select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
 input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
 from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a  where trunc(START_TIME) >trunc(sysdate -2)
and  status!='COMPLETED'  and  status !='FAILED'
   and  b.db_key=a.db_key  order  by STATUS;
prompt '-------------------------------------------------------------------------'
prompt  'backup  FAILED'
prompt '-------------------------------------------------------------------------'
 select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
 input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
 from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a  where trunc(START_TIME) >trunc(sysdate -2)
and  status='FAILED'
   and  b.db_key=a.db_key  order  by STATUS;

Duree  pour  faire   un  diagramme de  gant :

 select  input_type||'_'||b.DB_NAME||'_'||a.dbid database,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,

 --to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,

 --input_type,STATUS,

 ELAPSED_SECONDS,

 --OUTPUT_DEVICE_TYPE,

 TIME_TAKEN_DISPLAY --,SESSION_KEY,SESSION_RECID,SESSION_STAMP

 from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a  where trunc(START_TIME) >trunc(sysdate -3)

 and  ELAPSED_SECONDS >300 

   and  b.db_key=a.db_key  order  by start_time;

============
Keep
============
select  c.DB_NAME,b.bs_key,KEEP,KEEP_UNTIL,KEEP_OPTIONS ,b.db_key,b.backup_type,b.START_TIME
,MEDIA
from RC_BACKUP_SET_details b ,RC_BACKUP_PIECE_DETAILS c
where
 c.db_key=b.db_key
and trunc(b.START_TIME) <trunc(sysdate -30)
and  KEEP_UNTIL is not  null
and  b.db_name=c.db_name
and b.BS_KEY=c.bs_key
and  c.db_name ='PRD'
and   trunc (c.START_TIME) =trunc(b.START_TIME)
--order by b.start_time, KEEP_UNTIL;
 and  KEEP_UNTIL is not  null
 order by b.start_time;