Backup calendar

If you have set online RMAN backup for the archives and database files, 

you can see how thing going on with tha backup with the script : - 

SELECT * FROM 

(SELECT dates.dt, TO_CHAR(dates.dt, 'DAY') DAY,

DECODE(archives."00", NULL, NULL, archives."00" || 'L') || ' ' || DECODE(data_files."00", NULL, NULL, data_files."00" || 'D') 

|| ' ' || DECODE(INCREMENTAL."00", NULL, NULL, INCREMENTAL."00" || 'I') "00", 

DECODE(archives."01", NULL, NULL, archives."01" || 'L') || ' ' || DECODE(data_files."01", NULL, NULL, data_files."01" || 'D') 

|| ' ' || DECODE(INCREMENTAL."01", NULL, NULL, INCREMENTAL."01" || 'I') "01",

DECODE(archives."02", NULL, NULL, archives."02" || 'L') || ' ' || DECODE(data_files."02", NULL, NULL, data_files."02" || 'D') 

|| ' ' || DECODE(INCREMENTAL."02", NULL, NULL, INCREMENTAL."02" || 'I') "02",

DECODE(archives."03", NULL, NULL, archives."03" || 'L') || ' ' || DECODE(data_files."03", NULL, NULL, data_files."03" || 'D') 

|| ' ' || DECODE(INCREMENTAL."03", NULL, NULL, INCREMENTAL."03" || 'I') "03",

DECODE(archives."04", NULL, NULL, archives."04" || 'L') || ' ' || DECODE(data_files."04", NULL, NULL, data_files."04" || 'D') 

|| ' ' || DECODE(INCREMENTAL."04", NULL, NULL, INCREMENTAL."04" || 'I') "04",

DECODE(archives."05", NULL, NULL, archives."05" || 'L') || ' ' || DECODE(data_files."05", NULL, NULL, data_files."05" || 'D') 

|| ' ' || DECODE(INCREMENTAL."05", NULL, NULL, INCREMENTAL."05" || 'I') "05",

DECODE(archives."06", NULL, NULL, archives."06" || 'L') || ' ' || DECODE(data_files."06", NULL, NULL, data_files."06" || 'D') 

|| ' ' || DECODE(INCREMENTAL."06", NULL, NULL, INCREMENTAL."06" || 'I') "06",

DECODE(archives."07", NULL, NULL, archives."07" || 'L') || ' ' || DECODE(data_files."07", NULL, NULL, data_files."07" || 'D') 

|| ' ' || DECODE(INCREMENTAL."07", NULL, NULL, INCREMENTAL."07" || 'I') "07",

DECODE(archives."08", NULL, NULL, archives."08" || 'L') || ' ' || DECODE(data_files."08", NULL, NULL, data_files."08" || 'D') 

|| ' ' || DECODE(INCREMENTAL."08", NULL, NULL, INCREMENTAL."08" || 'I') "08",

DECODE(archives."09", NULL, NULL, archives."09" || 'L') || ' ' || DECODE(data_files."09", NULL, NULL, data_files."09" || 'D') 

|| ' ' || DECODE(INCREMENTAL."09", NULL, NULL, INCREMENTAL."09" || 'I') "09",

DECODE(archives."10", NULL, NULL, archives."10" || 'L') || ' ' || DECODE(data_files."10", NULL, NULL, data_files."10" || 'D') 

|| ' ' || DECODE(INCREMENTAL."10", NULL, NULL, INCREMENTAL."10" || 'I') "10",

DECODE(archives."11", NULL, NULL, archives."11" || 'L') || ' ' || DECODE(data_files."11", NULL, NULL, data_files."11" || 'D') 

|| ' ' || DECODE(INCREMENTAL."11", NULL, NULL, INCREMENTAL."11" || 'I') "11",

DECODE(archives."12", NULL, NULL, archives."12" || 'L') || ' ' || DECODE(data_files."12", NULL, NULL, data_files."12" || 'D') 

|| ' ' || DECODE(INCREMENTAL."12", NULL, NULL, INCREMENTAL."12" || 'I') "12",

DECODE(archives."13", NULL, NULL, archives."13" || 'L') || ' ' || DECODE(data_files."13", NULL, NULL, data_files."13" || 'D') 

|| ' ' || DECODE(INCREMENTAL."13", NULL, NULL, INCREMENTAL."13" || 'I') "13",

DECODE(archives."14", NULL, NULL, archives."14" || 'L') || ' ' || DECODE(data_files."14", NULL, NULL, data_files."14" || 'D') 

|| ' ' || DECODE(INCREMENTAL."14", NULL, NULL, INCREMENTAL."14" || 'I') "14",

DECODE(archives."15", NULL, NULL, archives."15" || 'L') || ' ' || DECODE(data_files."15", NULL, NULL, data_files."15" || 'D') 

|| ' ' || DECODE(INCREMENTAL."15", NULL, NULL, INCREMENTAL."15" || 'I') "15",

DECODE(archives."16", NULL, NULL, archives."16" || 'L') || ' ' || DECODE(data_files."16", NULL, NULL, data_files."16" || 'D') 

|| ' ' || DECODE(INCREMENTAL."16", NULL, NULL, INCREMENTAL."16" || 'I') "16",

DECODE(archives."17", NULL, NULL, archives."17" || 'L') || ' ' || DECODE(data_files."17", NULL, NULL, data_files."17" || 'D') 

|| ' ' || DECODE(INCREMENTAL."17", NULL, NULL, INCREMENTAL."17" || 'I') "17",

DECODE(archives."18", NULL, NULL, archives."18" || 'L') || ' ' || DECODE(data_files."18", NULL, NULL, data_files."18" || 'D') 

|| ' ' || DECODE(INCREMENTAL."18", NULL, NULL, INCREMENTAL."18" || 'I') "18",

DECODE(archives."19", NULL, NULL, archives."19" || 'L') || ' ' || DECODE(data_files."19", NULL, NULL, data_files."19" || 'D') 

|| ' ' || DECODE(INCREMENTAL."19", NULL, NULL, INCREMENTAL."19" || 'I') "19",

DECODE(archives."20", NULL, NULL, archives."20" || 'L') || ' ' || DECODE(data_files."20", NULL, NULL, data_files."20" || 'D') 

|| ' ' || DECODE(INCREMENTAL."20", NULL, NULL, INCREMENTAL."20" || 'I') "20",

DECODE(archives."21", NULL, NULL, archives."21" || 'L') || ' ' || DECODE(data_files."21", NULL, NULL, data_files."21" || 'D') 

|| ' ' || DECODE(INCREMENTAL."21", NULL, NULL, INCREMENTAL."21" || 'I') "21",

DECODE(archives."22", NULL, NULL, archives."22" || 'L') || ' ' || DECODE(data_files."22", NULL, NULL, data_files."22" || 'D') 

|| ' ' || DECODE(INCREMENTAL."22", NULL, NULL, INCREMENTAL."22" || 'I') "22",

DECODE(archives."23", NULL, NULL, archives."23" || 'L') || ' ' || DECODE(data_files."23", NULL, NULL, data_files."23" || 'D') 

|| ' ' || DECODE(INCREMENTAL."23", NULL, NULL, INCREMENTAL."23" || 'I') "23" 

FROM 

(SELECT DISTINCT(TRUNC(COMPLETION_TIME)) dt FROM V$BACKUP_SET WHERE BACKUP_TYPE='L'

UNION

SELECT DISTINCT(TRUNC(COMPLETION_TIME)) dt FROM V$BACKUP_SET WHERE BACKUP_TYPE='D' 

UNION

SELECT DISTINCT(TRUNC(COMPLETION_TIME)) dt FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'

ORDER BY dt DESC) dates, 

(SELECT * FROM (SELECT TO_DATE(date_time, 'DD/MM/YYYY') DT, TO_CHAR(TO_DATE(date_time, 'DD/MM/YYYY'), 'DAY') DAY, 

"00", "01",  "02", "03", "04", "05", "06", "07", "08", "09", 

"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23" 

FROM 

(

SELECT DATE_TIME, 

SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02", 

SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05", 

SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08", 

SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",

SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",

SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",

SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",

SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL  

FROM (SELECT TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY') DATE_TIME,  

SUBSTR(TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$BACKUP_SET WHERE BACKUP_TYPE='D') 

GROUP BY date_time

) ORDER BY dt DESC)) data_files, 

(SELECT TO_DATE(date_time, 'DD/MM/YYYY') DT, TO_CHAR(TO_DATE(date_time, 'DD/MM/YYYY'), 'DAY') DAY, 

"00", "01",  "02", "03", "04", "05", "06", "07", "08", "09", 

"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23" 

FROM 

(

SELECT DATE_TIME, 

SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02", 

SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05", 

SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08", 

SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",

SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",

SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",

SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",

SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL  

FROM (SELECT TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY') DATE_TIME,  

SUBSTR(TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$BACKUP_SET WHERE BACKUP_TYPE='L') 

GROUP BY date_time

) ORDER BY dt DESC) archives, 

(SELECT TO_DATE(date_time, 'DD/MM/YYYY') DT, TO_CHAR(TO_DATE(date_time, 'DD/MM/YYYY'), 'DAY') DAY, 

"00", "01",  "02", "03", "04", "05", "06", "07", "08", "09", 

"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23" 

FROM 

(

SELECT DATE_TIME, 

SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02", 

SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05", 

SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08", 

SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",

SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",

SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",

SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",

SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL  

FROM (SELECT TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY') DATE_TIME,  

SUBSTR(TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$BACKUP_SET WHERE BACKUP_TYPE='I') 

GROUP BY date_time

) ORDER BY dt DESC) INCREMENTAL

WHERE dates.dt = data_files.dt(+) AND 

dates.dt = archives.dt (+) 

AND dates.dt = INCREMENTAL.dt(+)

)

ORDER BY dt DESC;

Apps DBA Free Lance

Sukhwinder Singh