{"id":155,"date":"2023-11-15T14:23:22","date_gmt":"2023-11-15T12:23:22","guid":{"rendered":"http:\/\/130.61.57.200\/?p=155"},"modified":"2023-11-15T14:23:22","modified_gmt":"2023-11-15T12:23:22","slug":"check-archive-logs-production","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2023\/11\/15\/check-archive-logs-production\/","title":{"rendered":"Check Archive Logs production"},"content":{"rendered":"\n

A database in ARCHIVELOG mode copies redo log entries in the achive log files. Archive log number and size is a metric of a database transactions load in time. Use the following query to monitor the Archive log production per hour for the last 30 days:<\/p>\n\n\n\n

SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) "Total",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23"<br>FROM v$log_history<br>GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')<br>ORDER BY 1;<\/code><\/p>\n\n\n\n

OR the following to get size approach:<\/p>\n\n\n\n

SELECT TO_CHAR(FIRST_TIME,'MM-DD') DAY,<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',MB,0))) AS "00",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',MB,0))) AS "01",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',MB,0))) AS "02",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',MB,0))) AS "03",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',MB,0))) AS "04",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',MB,0))) AS "05",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',MB,0))) AS "06",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',MB,0))) AS "07",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',MB,0))) AS "08",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',MB,0))) AS "09",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',MB,0))) AS "10",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',MB,0))) AS "11",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',MB,0))) AS "12",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',MB,0))) AS "13",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',MB,0))) AS "14",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',MB,0))) AS "15",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',MB,0))) AS "16",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',MB,0))) AS "17",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',MB,0))) AS "18",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',MB,0))) AS "19",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',MB,0))) AS "20",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',MB,0))) AS "21",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',MB,0))) AS "22",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',MB,0))) AS "23",<br>ROUND(SUM(MB)) as "Tot"<br>FROM (SELECT DISTINCT SEQUENCE#, (BLOCKS*BLOCK_SIZE)\/1048576 mb, FIRST_TIME FROM V$ARCHIVED_LOG)<br>GROUP BY TO_CHAR(FIRST_TIME,'MM-DD')<br>ORDER BY 1;<\/code><\/p>\n\n\n\n

In case you need to check this production for a specific time window, this can be rewritten as:<\/p>\n\n\n\n

SELECT SUM (BLOCKS*BLOCK_SIZE)\/1048576 MB, TRUNC(FIRST_TIME) FROM V$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN TO_DATE('20\/12\/2021 16:00','DD\/MM\/YYYY HH24:MI') and to_date('20\/12\/2021 19:00','DD\/MM\/YYYY HH24:MI')<br>GROUP BY TRUNC(FIRST_TIME);<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

A database in ARCHIVELOG mode copies redo log entries in the achive log files. Archive log number and size is a metric of a database transactions load in time. Use the following query to monitor the Archive log production per hour for the last 30 days: SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) […]<\/p>\n","protected":false},"author":1,"featured_media":106,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[52],"class_list":["post-155","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-archive-logs"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=155"}],"version-history":[{"count":1,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/155\/revisions"}],"predecessor-version":[{"id":156,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/155\/revisions\/156"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/106"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=155"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}