Monday, July 9, 2012

Archived log generation volume and count report

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

DAYGENERATED_MBDELETED_MBREMAINING_MB
29/06/2012315.77315.770
30/06/2012158.57158.570
01/07/2012153.09153.090
............
07/07/20121,040.221,040.220
08/07/2012637.59637.590
09/07/2012328.72174.28154.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');
   
DAY00-0101-0202-0303-04...20-2121-2222-2323-00TOTAL
29/06/2012...129
30/06/20121...16
01/07/20121...16
.................................
07/07/2012111...212229
08/07/2012111...11119
09/07/201221...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');

Posted by
Labels:

15 comments:

  1. AnonymousMay 3, 2013 at 4:32 PM

    Thanks for your hard work!

    ReplyDelete
  2. RajeshMay 14, 2013 at 12:58 PM

    Great work..!!

    ReplyDelete
  3. AnonymousJune 4, 2013 at 5:01 PM

    Wonderful job !

    ReplyDelete
  4. AnonymousAugust 8, 2013 at 9:57 AM

    Can you provide similary for RAC please?

    ReplyDelete
    Replies
    1. Lefteris TsekourasAugust 10, 2013 at 8:42 PM

      V$ARCHIVED_LOG contains information from all RAC's instances.
      So, the queries published in this post if ran in a RAC database, you will get aggregated results for it.

      Delete
  5. AnonymousOctober 14, 2013 at 11:06 AM

    Job is wonderful.

    I would like suggest some improvements. First and third selects shows only days where archivelogs are deleted.
    Small improvements:

    SELECT SUM_ARCH.DAY,
    SUM_ARCH.GENERATED_GB,
    NVL(SUM_ARCH_DEL.DELETED_GB,0) "DELETED_GB",
    SUM_ARCH.GENERATED_GB - NVL(SUM_ARCH_DEL.DELETED_GB,0) "REMAINING_GB"
    ...
    WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
    ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

    and

    SELECT LOG_HISTORY.*,
    SUM_ARCH.GENERATED_GB,
    NVL(SUM_ARCH_DEL.DELETED_GB,0)"DELETED_GB",
    SUM_ARCH.GENERATED_GB - NVL(SUM_ARCH_DEL.DELETED_GB,0) "REMAINING_GB"
    FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
    ...
    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');


    Those select show data for all archivelogs dates.

    ReplyDelete
    Replies
    1. Lefteris TsekourasOctober 14, 2013 at 11:24 AM

      Yes, you are right, I added the modifications.
      Thanks for the contribution.

      Delete
    2. AnonymousMay 14, 2016 at 2:23 AM

      If the logic is correct then that seems shorter
      select to_char (completion_time, 'yyyy-mm-dd') day
      , round(sum (case when archived='YES' then blocks * block_size /1048576 end), 2) generated_mb
      , round(sum (case when archived='YES' and deleted='YES' then blocks * block_size /1048576 end), 2) deleted_mb
      , round(sum (case when archived='YES' and deleted='NO' then blocks * block_size /1048576 end), 2) remaining_mb
      from v$archived_log
      group by to_char (completion_time, 'yyyy-mm-dd')
      order by day
      /

      Delete
  6. AnonymousOctober 14, 2013 at 12:34 PM

    I also used NVL function in selects especially in REMAINING column. I you think use of NVL is not important please omit this comment.

    ReplyDelete
  7. Andreas BergOctober 16, 2013 at 10:48 PM

    hey, great work!

    i had to improve it a bit because i want to use it in a RAC+Dataguard enviroment
    so i had to extent ther WHERE clausel for each query with
    >>> and standby_dest='NO' <<<
    i think this would work now generic for each type of database

    regards
    Andreas

    ReplyDelete
  8. RucknrunMarch 13, 2014 at 3:48 PM

    Thank you. These queries are really great.

    ReplyDelete
  9. Joao CesarFebruary 2, 2015 at 5:04 PM

    This is awesome ... Many thanks for the hard work !!!

    ReplyDelete
  10. newbie01.oracleFebruary 20, 2015 at 1:14 PM

    Hi,

    Can you advise how to change the SQL to have hour and dates output instead with the date as the heading?

    Example as below:

    HOUR 01-JAN-2015 02-JAN-2015 03-JAN-2015 04-JAN-2015
    00
    01
    02
    03

    ReplyDelete
    Replies
    1. Lefteris TsekourasFebruary 24, 2015 at 10:16 AM

      Hello.
      No, I can't think of a way to do this.
      In this query we pivot V$ARCHIVED_LOG, which means we aggregate rows and we convert them to columns (The HOUR portion of COMPLETION_TIME).
      With this method [SUM(DECODE(...))] or even the 11g PIVOT operator, you must have a finite set of values to decode the rows to them. A day always has 24 hours [00-01,...,23-00], but the days displayed in V$ARCHIVED_LOG are not always the same.

      Delete
  11. AnonymousNovember 8, 2016 at 4:55 PM

    SAME OUTPUT, but better looking output.

    Add this at the beggining:

    set linesize 300
    set pagesize 300
    col 00-01 format 999
    col 01-02 format 999
    col 02-03 format 999
    col 03-04 format 999
    col 04-05 format 999
    col 05-06 format 999
    col 06-07 format 999
    col 07-08 format 999
    col 08-09 format 999
    col 10-11 format 999
    col 11-12 format 999
    col 12-13 format 999
    col 13-14 format 999
    col 14-15 format 999
    col 15-16 format 999
    col 16-17 format 999
    col 17-18 format 999
    col 18-19 format 999
    col 20-21 format 999
    col 21-22 format 999
    col 22-23 format 999
    col 23-00 format 999

    ReplyDelete

Subscribe to: Post Comments (Atom)