Source : http://lefterhs.blogspot.co.id/2012/07/archived-log-generation-volume-and.html
V$ARCHIVED_LOG displays information about the archived logs generated by your database and haven’t yet aged out from your control file.
We are going to use this view to generate a report, displaying some useful information about it.
BLOCKS column stores archived log’s size in blocks, BLOCK_SIZE stores block size in bytes, so if we multiply them (BLOCKS*BLOCK_SIZE) we get its size in bytes.
ARCHIVED indicates whether the log is archived and DELETED whether RMAN has deleted it.
The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven’t yet been deleted by RMAN.
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
| DAY | GENERATED_MB | DELETED_MB | REMAINING_MB |
| 29/06/2012 | 315.77 | 315.77 | 0 |
| 30/06/2012 | 158.57 | 158.57 | 0 |
| 01/07/2012 | 153.09 | 153.09 | 0 |
| … | … | … | … |
| 07/07/2012 | 1,040.22 | 1,040.22 | 0 |
| 08/07/2012 | 637.59 | 637.59 | 0 |
| 09/07/2012 | 328.72 | 174.28 | 154.44 |
The following report will display the number of archived logs generated per hour per day:
SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
"10-11",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
| DAY | 00-01 | 01-02 | 02-03 | 03-04 | … | 20-21 | 21-22 | 22-23 | 23-00 | TOTAL |
| 29/06/2012 | … | 1 | 2 | 9 | ||||||
| 30/06/2012 | 1 | … | 1 | 6 | ||||||
| 01/07/2012 | 1 | … | 1 | 6 | ||||||
| … | … | … | … | … | … | … | … | … | … | … |
| 07/07/2012 | 1 | 1 | 1 | … | 2 | 1 | 2 | 2 | 29 | |
| 08/07/2012 | 1 | 1 | 1 | … | 1 | 1 | 1 | 19 | ||
| 09/07/2012 | 2 | 1 | … | 9 |
So, for instance, at 30/06/12, between 03:00 and 04:00, no log was archived.
At 07/07/12, between 20:00 and 21:00, 2 logs were archived and, at that day, 29 logs were archived totally.
The combination of these scripts is:
SELECT LOG_HISTORY.*,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
"10-11",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) LOG_HISTORY,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE LOG_HISTORY.DAY = SUM_ARCH.DAY AND SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (LOG_HISTORY.DAY, 'DD/MM/YYYY');
asiandba 