Archive log backup analysis

 -- ======================================================================

-- Actual and Total Space Per Hour

-- ======================================================================

select TRUNC(FIRST_TIME, 'HH') DAY,

       ROUND((SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) GB,

       ROUND((SUM(BACKUP_COUNT * BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) TOTAL_BACKUP_SIZE

  FROM V$ARCHIVED_LOG

  WHERE BACKUP_COUNT > 0

  GROUP BY TRUNC(FIRST_TIME, 'HH')

  ORDER BY day DESC;

-- ======================================================================

-- Check Additional Space by File

-- ======================================================================

WITH A AS (select VAL.SEQUENCE#,

                  VAL.BLOCKS,

                  VAL.FIRST_TIME,

                  VAL.NEXT_TIME,

                  VAL.BACKUP_COUNT,

                  VAL.COMPLETION_TIME,

                  VAL.DELETED,

                  (BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_MB,

                  (VAL.BLOCKS * VAL.BLOCK_SIZE * VAL.BACKUP_COUNT) / 1024 / 1024 TOTAL_SIZE_MB

      FROM V$ARCHIVED_LOG VAL

      WHERE VAL.BACKUP_COUNT > 0

  --AND VAL.FIRST_TIME BETWEEN TO_TIMESTAMP('2023/08/11 09:00', 'yyyy/mm/dd hh24:mi')

  --AND TO_TIMESTAMP('2023/08/11 09:59', 'yyyy/mm/dd hh24:mi')

  )

select A.SEQUENCE#,

       A.FIRST_TIME,

       A.COMPLETION_TIME,

       A.BACKUP_COUNT,

       A.DELETED,

       A.SIZE_MB,

       A.TOTAL_SIZE_MB,

       ROUND((A.TOTAL_SIZE_MB - A.SIZE_MB), 2) EXTRA_MB_BACKED

  FROM A

  ORDER BY FIRST_TIME DESC;

-- ======================================================================

-- Additional Space Used Per Day

-- ======================================================================

WITH arch AS (select TRUNC(FIRST_TIME, 'DD') DAY,

                     ROUND((SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) GB,

                     ROUND((SUM(BACKUP_COUNT * BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) TOTAL_BACKUP_SIZE

      FROM V$ARCHIVED_LOG

      WHERE BACKUP_COUNT > 0

      GROUP BY TRUNC(FIRST_TIME, 'DD')

      ORDER BY 1 DESC)

select arch.day,

       arch.GB,

       arch.TOTAL_BACKUP_SIZE,

       (arch.TOTAL_BACKUP_SIZE - arch.GB) ADDITONAL_GB_BACKED

  FROM arch;

-- ======================================================================

-- Additional Space Used Per Month

-- ======================================================================

WITH arch AS (select TRUNC(COMPLETION_TIME, 'MM') DAY,

                     ROUND((SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) GB,

                     ROUND((SUM(BACKUP_COUNT * BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024), 2) TOTAL_BACKUP_SIZE

      FROM V$ARCHIVED_LOG

      WHERE BACKUP_COUNT > 0

      GROUP BY TRUNC(COMPLETION_TIME, 'MM')

      ORDER BY 1 DESC)

select arch.day,

       arch.GB,

       arch.TOTAL_BACKUP_SIZE,

       (arch.TOTAL_BACKUP_SIZE - arch.GB) ADDITONAL_GB_BACKED

  FROM arch;