Archivelog calendar

Summary 

You need to know, especially in TByte systems, how much archives produced every day, how they are produced during the day hours and of course how many of them are deleted by RMAN backup. 

With this script you have a per day and per hour calendar of archiving! It shows how many produced as a total every day, how much have already deleted from Unix and what the remaining is in GBytes.

SELECT * FROM (
SELECT A.*, B.SIZE_GB, DECODE(C.SIZE_GB, NULL, 0, C.SIZE_GB) DELETED_GB, B.SIZE_GB - DECODE(C.SIZE_GB, NULL, 0, C.SIZE_GB) REMAIN_GB 
FROM (SELECT * FROM (SELECT TO_DATE(b.date_time, 'DD/MM/YYYY') dt, TO_CHAR(TO_DATE(b.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", TOTAL 
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(first_time, 'DD/MM/YYYY') DATE_TIME,  
SUBSTR(TO_CHAR(first_time, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$LOG_HISTORY)
--where date_time = '06/12/2004' 
GROUP BY date_time) b)) A, 
(SELECT TO_CHAR(FIRST_TIME, 'DD/MM/YYYY') DATE_TIME, 
SUM(ROUND((blocks*block_size)/(1024*1024*1024),3)) SIZE_GB 
FROM v$archived_log GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM/YYYY')) B, 
(SELECT TO_CHAR(FIRST_TIME, 'DD/MM/YYYY') DATE_TIME, 
SUM(ROUND((blocks*block_size)/(1024*1024*1024),3)) SIZE_GB 
FROM v$archived_log WHERE DELETED = 'YES' GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM/YYYY')) C 
WHERE TO_CHAR(A.DT, 'DD/MM/YYYY') = B.DATE_TIME 
AND TO_CHAR(A.DT, 'DD/MM/YYYY') = c.DATE_TIME(+)
) ORDER BY dt DESC;

Output

DT
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
TOTAL
SIZE_GB
DELETED_GB
REMAIN_GB
18/2/2008
MONDAY   
6
4
2
 
 
2
 
 
3
1
3
5
3
2
18
 
 
 
 
 
 
 
 
 
49
19,423
12,954
6,469
17/2/2008
SUNDAY   
6
3
4
 
 
2
 
 
2
 
1
4
1
2
2
 
 
3
 
 
3
 
6
3
42
14,577
14,577
0
16/2/2008
SATURDAY 
58
55
36
18
7
5
20
14
3
52
60
40
13
11
43
16
2
2
 
1
2
 
1
7
466
177,047
177,047
0
15/2/2008
FRIDAY   
8
7
45
24
22
5
2
4
8
5
54
6
5
4
20
4
3
2
4
2
4
25
36
60
359
146,44
146,44
0
14/2/2008
THURSDAY 
8
6
2
2
3
2
3
2
5
3
7
7
5
7
8
4
3
3
3
16
10
3
3
8
123
51,207
51,207
0
13/2/2008
WEDNESDAY
7
5
7
60
64
55
38
30
28
29
35
61
79
89
92
93
99
91
70
15
45
14
6
3
1115
416,01
416,01
0
12/2/2008
TUESDAY  
56
54
38
6
7
4
2
15
16
11
10
12
9
4
21
5
5
3
2
2
16
12
7
4
321
132,257
132,257
0
11/2/2008
MONDAY   
45
4
2
 
1
2
 
3
5
3
15
60
5
3
7
4
11
28
14
4
10
30
52
56
364
147,742
147,742
0
10/2/2008
SUNDAY   
5
4
3
2
 
2
 
 
3
1
2
4
3
9
3
6
 
3
 
 
3
 
13
40
106
40,397
40,397
0
9/2/2008
SATURDAY 
48
9
19
33
11
12
 
1
7
5
5
3
1
2
19
 
5
2
84
87
47
15
6
2
423
159,33
159,33
0
8/2/2008
FRIDAY   
9
8
72
77
72
25
22
32
21
12
82
9
5
5
5
4
10
36
19
12
49
64
66
67
783
299,919
299,919
0
7/2/2008
THURSDAY 
48
57
64
9
22
31
 
1
6
5
5
4
4
5
4
2
4
4
8
14
7
3
3
9
319
127,344
127,344
0
6/2/2008
WEDNESDAY
6
6
16
7
6
3
2
3
5
3
83
6
4
2
20
4
3
4
3
5
29
60
62
57
399
161,974
161,974
0
5/2/2008
TUESDAY  
8
7
14
4
2
2
 
3
4
5
6
8
5
4
5
2
5
4
2
3
8
12
12
15
140
57,463
57,463
0
4/2/2008
MONDAY   
37
16
2
 
1
2
 
1
5
3
7
8
5
5
3
3
4
4
6
13
6
3
4
8
146
61,009
61,009
0
3/2/2008
SUNDAY   
 
 
 
2
 
2
 
1
2
1
1
5
2
11
18
1
2
2
 
3
2
 
6
2
63
28,999
28,999
0