Thread 1 cannot allocate new log, sequence 99999 Private strand flush not complete

===========

Issue

===========

Frequent messages "Thread 1 cannot allocate new log, sequence 15123. Private strand flush not complete" in alert.log, and timeouts in application.

This is a time frame, where batch job is running, performing DELETE task from major tables.

===========

Theory

===========

The issue - There are non commited transactions, that is still holding in use the redo log, and it cannot be allocated to be available to store new transactions.

===========

Solution:

===========

Option A - Add more Log File Groups - see this Technote Add Redo Log Group by Example. Fixing "log file switch" wait event

Option B - make Redo Logs bigger in size.

COL STATUS FOR A20

SELECT group#, sequence#, bytes, members, status 

  FROM V$LOG;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

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

         1      15148  524288000          1 INACTIVE

         2      15149  524288000          1 ACTIVE

         3      15147  524288000          1 INACTIVE

         4      15150  524288000          1 CURRENT

         5      15145  524288000          1 INACTIVE

         6      15146  524288000          1 INACTIVE

 

524,288,000

Lets increase from 512M to 1024M

When Log File Group is in INACTIVE status - drop it and recreate in bigger size.

To switch between log files:

ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 4;

ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 5;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 6;

ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06_a.rdo') SIZE 1024M REUSE;

COL STATUS FOR A20

SELECT group#, sequence#, bytes, status 

  FROM V$LOG;

  

    GROUP#  SEQUENCE#      BYTES STATUS

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

         1      15152 1073741824 CURRENT

         2          0 1073741824 UNUSED

         3          0 1073741824 UNUSED

         4      15150  524288000 ACTIVE

         5      15151  524288000 ACTIVE

         6          0 1073741824 UNUSED

 

Need to wait, for ACTIVE files, to become INACTIVE, and them recreate all the files with new size.

    GROUP#  SEQUENCE#      BYTES STATUS

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

         1      15152 1073741824 ACTIVE

         2      15153 1073741824 CURRENT

         3          0 1073741824 UNUSED

         4          0 1073741824 UNUSED

         5          0 1073741824 UNUSED

         6          0 1073741824 UNUSED