1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month.
[oracle@oracledb script]$ ps -ef|grep smon
oracle 2524 1 0 12:53 ? 00:00:00 ora_smon_GGSOURCE
oracle 3122 1 0 12:54 ? 00:00:00 ora_smon_ORA12C
oradev 4029 1 0 12:56 ? 00:00:00 ora_smon_ORACDB
oracle 13118 1 0 Feb22 ? 00:00:56 ora_smon_ORCL
oracle 26246 18177 0 17:33 pts/0 00:00:00 grep --color=auto smon
[oracle@oracledb script]$
2. Set the environment to a database where you are generating the archivelog generation for last 30 days
[oracle@oracledb script]$ . oraenv
ORACLE_SID = [ORCL] ? ORCL
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracledb script]$
3. Hours archivelog generation monitoring script
[oracle@oracledb script]$ ll *hour*
-rw-r--r--. 1 oracle oinstall 1916 Oct 26 2022 archivelog_generation_hourly.sql
[oracle@oracledb script]$ more archivelog_generation_hourly.sql
set verify off
set feed off
set timing off
PROMPT
PROMPT number of hourly redo switches for the last 31 days
set pages 1000 lines 1000
SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
COUNT (1) "Total",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate-31
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;
[oracle@oracledb script]$
[oracle@oracledb script]$
[oracle@oracledb script]$
4. Run the hour archivelog monitoring script against ORCL database
[oracle@oracledb script]$ sqlplus / as sysdba @archivelog_generation_hourly.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 17:34:09 2024
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
number of hourly redo switches for the last 31 days
Date INST_ID Day Total h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13
--------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
01-MAR-24 1 Fri 25 0 0 0 0 0 0 0 0 0 0 0 0 0 0
02-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
03-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
04-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
05-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
06-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
07-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
08-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
09-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
10-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
11-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
12-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
13-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
14-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
15-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
16-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
17-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
18-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
19-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
20-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
21-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
22-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
23-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
24-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
25-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
26-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
27-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
28-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
29-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
30-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
31-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4
01-APR-24 1 Mon 93 4 4 4 4 4 4 4 4 4 4 4 4 4 23
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
[oracle@oracledb script]$
Regards,
Mallikarjun / Vismo Technologies
WhatsApp: +91 9880616848 / +91 9036478079
Cell: +91 9880616848 / +91 9036478079
Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com / info@vismotechnologies.com
