{"id":4156,"date":"2025-06-08T02:30:00","date_gmt":"2025-06-08T02:30:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4156"},"modified":"2025-06-09T14:55:04","modified_gmt":"2025-06-09T14:55:04","slug":"archive-log-generation-in-oracle","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/archive-log-generation-in-oracle\/","title":{"rendered":"Archive Log Generation in Oracle"},"content":{"rendered":"\n

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<\/strong>, week<\/strong>, and month<\/strong>.<\/p>\n\n\n\n

Hourly Archive Log Generation<\/h3>\n\n\n\n
SET LINESIZE 299\nSELECT\n    TO_CHAR(TRUNC(FIRST_TIME), 'Mon DD') AS \"Date\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '9999') AS \"12AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '9999') AS \"01AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '9999') AS \"02AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '9999') AS \"03AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '9999') AS \"04AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '9999') AS \"05AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '9999') AS \"06AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '9999') AS \"07AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '9999') AS \"08AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '9999') AS \"09AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '9999') AS \"10AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '9999') AS \"11AM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '9999') AS \"12PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '9999') AS \"01PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '9999') AS \"02PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '9999') AS \"03PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '9999') AS \"04PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '9999') AS \"05PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '9999') AS \"06PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '9999') AS \"07PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '9999') AS \"08PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '9999') AS \"09PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '9999') AS \"10PM\",\n    TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '9999') AS \"11PM\",\n    TO_CHAR(SUM(1), '9999') AS \"Total Logs\"\nFROM\n    V$LOG_HISTORY\nGROUP BY\n    TRUNC(FIRST_TIME)\nORDER BY\n    TRUNC(FIRST_TIME) DESC;<\/code><\/pre>\n\n\n\n
Weekly Archive Log Generation<\/h3>\n\n\n\n
SELECT\n    TO_CHAR(TRUNC(FIRST_TIME, 'IW'), 'YYYY-IW') AS \"Week\",\n    COUNT(*) AS \"Total Logs\"\nFROM\n    V$LOG_HISTORY\nGROUP BY\n    TRUNC(FIRST_TIME, 'IW')\nORDER BY\n    TRUNC(FIRST_TIME, 'IW') DESC;<\/code><\/pre>\n\n\n\n
Monthly Archive Log Generation<\/h3>\n\n\n\n
SELECT\n    TO_CHAR(TRUNC(FIRST_TIME, 'MM'), 'YYYY-MM') AS \"Month\",\n    COUNT(*) AS \"Total Logs\"\nFROM\n    V$LOG_HISTORY\nGROUP BY\n    TRUNC(FIRST_TIME, 'MM')\nORDER BY\n    TRUNC(FIRST_TIME, 'MM') DESC;<\/code><\/pre>\n\n\n\n
\u26a0\ufe0f Notes & Best Practices<\/h3>\n\n\n\n
    \n
  • Analyze patterns to identify peak archive log generation periods.<\/li>\n\n\n\n
  • Correlate log spikes with application or batch job activity.<\/li>\n\n\n\n
  • Use findings to adjust redo log sizing and backup frequency.<\/li>\n\n\n\n
  • Ensure sufficient disk space in the archive destination.<\/li>\n\n\n\n
  • Automate alerts for unusual growth in archive logs.<\/li>\n<\/ul>\n\n\n\n
    \ud83d\udcc4 Summary<\/h3>\n\n\n\n
    Monitoring archive log generation using these queries empowers Oracle DBAs to:<\/p>\n\n\n\n
      \n
    • Proactively manage disk space<\/li>\n\n\n\n
    • Optimize backup and retention<\/li>\n\n\n\n
    • Maintain consistent performance<\/li>\n<\/ul>\n\n\n\n
      Stay ahead of issues\u2014track logs like a pro!<\/p>\n","protected":false},"excerpt":{"rendered":"
      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 Weekly Archive Log Generation Monthly Archive Log Generation \u26a0\ufe0f Notes & Best Practices \ud83d\udcc4 Summary Monitoring archive […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4156","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}