In times of very high load, Oracle's LGWR (LoG WRiter) process fills up all the redo log groups very fast and is now waiting for the next redo log groups to become available after the checkpoint is successfully executed. As part of the database recovery process, the Oracle server needs to stop processing and halts all requests for database access until the checkpoint is completed successfully. This stop causes the application to experience a temporary performance degradation and may impact RSA Identity Governance & Lifecycle application response.
The following is an example of "checkpoint not complete" messages that can be found in the alert_AVDB.log:
Wed Dec 30 08:46:14 2015
Thread 1 cannot allocate new log, sequence 72
Checkpoint not complete
Current log# 5 seq# 71 mem# 0: +DG01/AVDB/ONLINELOG/group_5.262.890736453
Wed Dec 30 08:48:39 2015
Thread 1 advanced to log sequence 72 (LGWR switch)
Current log# 6 seq# 72 mem# 0: +DG01/AVDB/ONLINELOG/group_6.263.890736453
Wed Dec 30 08:49:08 2015
Thread 1 cannot allocate new log, sequence 73
Checkpoint not complete
Current log# 6 seq# 72 mem# 0: +DG01/AVDB/ONLINELOG/group_6.263.890736453
SELECT GROUP#, BLOCKSIZE, STATUS, MEMBERS, BYTES / (1024 * 1024) AS Size_MB FROM v$log;
GROUPS | BLOCKSIZE | STATUS | MEMBERS | SIZE_MB |
1 | 512 | INACTIVE | 1 | 800 |
2 | 512 | INACTIVE | 1 | 800 |
3 | 512 | INACTIVE | 1 | 800 |
4 | 512 | CURRENT | 1 | 800 |
5 | 512 | INACTIVE | 1 | 800 |
6 | 512 | INACTIVE | 1 | 800 |
Oracle's recommendation is that the interval for log switches be 15 to 30 minutes apart, with a maximum of four log switches per hour.
SELECT Start_Date, Start_Time, Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes, Vdb.NAME AS Dbname FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh GROUP BY To_Char(Vlh.First_Time, 'YYYY-MM-DD'), To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist, V$log Vl, V$database Vdb WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,log_Hist.Start_Time;
The following is a sample result from the query above.
START_DATE | START_TIME | NUM_LOGS | MBYTES | DBNAME |
12/30/2015 | 8:00 | 24 | 3200 | AVDB |
12/30/2015 | 9:00 | 12 | 1600 | AVDB |
12/30/2015 | 10:00 | 1 | 800 | AVDB |
12/30/2015 | 11:00 | 1 | 800 | AVDB |
Once you determine that Oracle can benefit by being reconfigured to optimize the database resources and minimize application performance impact, consider increasing the size of the redo log groups.
Important Note: Oracle Data Guard is not part of the RSA Identity Governance & Lifecycle out-of-the-box installation package or product.
To determine if you are using Oracle Data Guard, you can;
Before proceeding, take a backup of the database.
The following are scripts that can be executed to increase the redo log sizes from any size to 8 GB.
RSA Identity Governance & Lifecycle recommends 8 GB size for redo group logs in production implementations with very high loads.
Because Oracle requires at least two redo log groups, the specific sequence must be followed to be completed successfully by dropping three redo logs and adding six redo logs of 8GB each.
// Extend the existing redo logs.
alter Database Clear Logfile '+DG01/avdb/onlinelog/group_1.263.763560403';
alter Database Drop Logfile Group 1;
alter Database Add Logfile Thread 1 Group 1 '+DG01/avdb/onlinelog/group_1' Size 8g;
alter Database Clear Logfile '+DG01/avdb/onlinelog/group_2.262.763560421';
alter Database Drop Logfile Group 2;
alter Database Add Logfile Thread 1 Group 2 '+DG01/avdb/onlinelog/group_2' Size 8g;
alter Database Clear Logfile '+DG01/avdb/onlinelog/group_3.261.763560439';
alter Database Drop Logfile Group 3;
alter Database Add Logfile Thread 1 Group 3 '+DG01/avdb/onlinelog/group_3' Size 8g;
// Add three additional redo logs.
alter Database Add Logfile Thread 1 Group 4 '+DG01/avdb/onlinelog/group_4' Size 8g;
alter Database Add Logfile Thread 1 Group 5 '+DG01/avdb/onlinelog/group_5' Size 8g;
alter Database Add Logfile Thread 1 Group 6 '+DG01/avdb/onlinelog/group_6' Size 8g;
// Extend the existing redo logs.
alter Database Clear Logfile '{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/GROUP_ 1';
alter Database Drop Logfile Group 1;
alter Database Add Logfile Thread 1 Group 1 '{ORACLE_BASE}/oradata/{DB_ UNIQUE_NAME}/group_1' Size 8g;
alter Database Clear Logfile {ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/GROUP_ 2';
alter Database Drop Logfile Group 2;
alter Database Add Logfile Thread 1 Group 2 '{ORACLE_BASE}/oradata/{DB_ UNIQUE_NAME}/group_2' Size 8g;
alter Database Clear Logfile '{ORACLE_BASE}/oradata/{DB_UNIQUE_ NAME}/REDO03.LOG';
alter Database Drop Logfile Group 3;
alter Database Add Logfile Thread 1 Group 3 '{ORACLE_BASE}/oradata/{DB_ UNIQUE_NAME}/group_3' Size 8g;
// Add three additional redo logs.
alter Database Add Logfile Thread 1 Group 4 '{ORACLE_BASE}/oradata/{DB_ UNIQUE_NAME}/group_4' Size 8g;
alter Database Add Logfile Thread 1 Group 5 '{ORACLE_BASE}/oradata/{DB_ UNIQUE_NAME}/group_5' Size 8g;
alter Database Add Logfile Thread 1 Group 6 '{ORACLE_BASE}/oradata/{DB_ UNIQUE_NAME}/group_6' Size 8g;
sqlplus "/as sysdba" @<filepath>/resize_redo_log.sql