How to setup correct redo log size

Oracle recommendation is to have log file switch event once every 20 minutes

To see current status

SELECT sequence#, TO_CHAR(first_time,'YYYYMMDD hh24:mi:ss') first_time

  FROM V$LOG_HISTORY 

 WHERE sequence# > (SELECT MAX(sequence#)-21 FROM V$LOG_HISTORY )

  ORDER BY 1 DESC; 

 

sequence first_time

-------- ----------------

1056665  20230522 12:48:31

1056664  20230522 12:47:19

1056663  20230522 12:46:46

1056662  20230522 12:45:52

1056661  20230522 12:45:18

1056660  20230522 12:44:43

1056659  20230522 12:43:07

1056658  20230522 12:42:04

1056657  20230522 12:41:04

1056656  20230522 12:40:04

1056655  20230522 12:39:19

1056654  20230522 12:38:40

1056653  20230522 12:37:52

1056652  20230522 12:37:13

1056651  20230522 12:36:21

1056650  20230522 12:35:36

1056649  20230522 12:34:39

1056648  20230522 12:33:45

1056647  20230522 12:33:31

1056646  20230522 12:32:56

1056645  20230522 12:32:08

1056644  20230522 12:31:55

1056643  20230522 12:31:22

1056642  20230522 12:30:25

1056641  20230522 12:29:50

1056640  20230522 12:28:08

1056639  20230522 12:26:29

1056638  20230522 12:24:50

1056637  20230522 12:23:11

1056636  20230522 12:21:29

1056635  20230522 12:19:50

1056634  20230522 12:18:14

1056633  20230522 12:16:50

1056632  20230522 12:15:52

1056631  20230522 12:15:06

1056630  20230522 12:13:47

1056629  20230522 12:12:08

1056628  20230522 12:10:29

1056627  20230522 12:08:52

1056626  20230522 12:07:46

1056625  20230522 12:06:31

1056624  20230522 12:05:25

1056623  20230522 12:04:31

1056622  20230522 12:03:13

1056621  20230522 12:02:10

1056620  20230522 12:01:13

1056619  20230522 12:00:16

1056618  20230522 11:59:35

1056617  20230522 11:57:56

1056616  20230522 11:56:17

Per these evidences, it is way too frequent!

Need to increase size from current 1024Mb tp 2048Mb

current status:

SELECT group#, blocksize, members, status, bytes/1024/1024 As size_mb 

FROM V$LOG

group#  blocksize members status  size_mb 

------- --------- ------- ------- ------

1 512   1   CURRENT 1024

2 512   1   ACTIVE  1024

3 512   1   ACTIVE  1024

Increase steps
ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04.log') SIZE 2048M;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06.log') SIZE 2048M; 

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01.log') SIZE 2048M REUSE;

ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02.log') SIZE 2048M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03.log') SIZE 2048M REUSE;

Monitor

SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;

SELECT * FROM V$LOGFILE;