Monitoring Hourly, Weekly, and Monthly Archive Log Generation in Oracle

Monitoring archive log generation is a crucial task for maintaining database performance and ensuring effective management in Oracle. This post provides SQL scripts to monitor archive log generation at hourly, weekly, and monthly intervals. These insights help database administrators (DBAs) track log activity, optimize resources, and plan storage and backup strategies.

1. Hourly Archive Log Generation

To analyze archive log generation on an hourly basis, use the following SQL query. This query groups logs by hour and displays the count for each hour of the day:

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;

2. Weekly Archive Log Generation

For a weekly overview of archive log generation, use the following SQL script. This query groups logs by ISO week (standard week numbering) and provides the total count per week:

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;

3. Monthly Archive Log Generation

To monitor archive log generation on a monthly basis, execute the SQL query below. It groups logs by month and calculates the total logs generated in each month:

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;

4. Conclusion

By using these SQL scripts, Oracle DBAs can effectively monitor archive log generation on hourly, weekly, and monthly intervals. Regular analysis of this data helps in:

• Identifying periods of high log activity.
• Optimizing disk space and archive log retention policies.
• Enhancing database performance through informed resource planning.

Proactive monitoring is key to maintaining an efficient and stable Oracle database environment.