Archived log generation volume and count report


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');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s