ADVERTISEMENT

Archive Log Generation in Oracle

Monitoring archive log generation is crucial for maintaining database health, planning disk space usage, and ensuring backup efficiency. This post provides practical SQL queries to monitor archive log activity by hour, week, and month.

Hourly Archive Log Generation

SET LINESIZE 299
SELECT
    TO_CHAR(TRUNC(FIRST_TIME), 'Mon DD') AS "Date",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '9999') AS "12AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '9999') AS "01AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '9999') AS "02AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '9999') AS "03AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '9999') AS "04AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '9999') AS "05AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '9999') AS "06AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '9999') AS "07AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '9999') AS "08AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '9999') AS "09AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '9999') AS "10AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '9999') AS "11AM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '9999') AS "12PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '9999') AS "01PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '9999') AS "02PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '9999') AS "03PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '9999') AS "04PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '9999') AS "05PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '9999') AS "06PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '9999') AS "07PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '9999') AS "08PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '9999') AS "09PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '9999') AS "10PM",
    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '9999') AS "11PM",
    TO_CHAR(SUM(1), '9999') AS "Total Logs"
FROM
    V$LOG_HISTORY
GROUP BY
    TRUNC(FIRST_TIME)
ORDER BY
    TRUNC(FIRST_TIME) DESC;

Weekly Archive Log Generation

SELECT
    TO_CHAR(TRUNC(FIRST_TIME, 'IW'), 'YYYY-IW') AS "Week",
    COUNT(*) AS "Total Logs"
FROM
    V$LOG_HISTORY
GROUP BY
    TRUNC(FIRST_TIME, 'IW')
ORDER BY
    TRUNC(FIRST_TIME, 'IW') DESC;

Monthly Archive Log Generation

SELECT
    TO_CHAR(TRUNC(FIRST_TIME, 'MM'), 'YYYY-MM') AS "Month",
    COUNT(*) AS "Total Logs"
FROM
    V$LOG_HISTORY
GROUP BY
    TRUNC(FIRST_TIME, 'MM')
ORDER BY
    TRUNC(FIRST_TIME, 'MM') DESC;

⚠️ Notes & Best Practices

  • Analyze patterns to identify peak archive log generation periods.
  • Correlate log spikes with application or batch job activity.
  • Use findings to adjust redo log sizing and backup frequency.
  • Ensure sufficient disk space in the archive destination.
  • Automate alerts for unusual growth in archive logs.

📄 Summary

Monitoring archive log generation using these queries empowers Oracle DBAs to:

  • Proactively manage disk space
  • Optimize backup and retention
  • Maintain consistent performance

Stay ahead of issues—track logs like a pro!

ADVERTISEMENT