000036796 - Why and how to increase Oracle redo log size using RSA Identity Governance &  Lifecycle

Document created by RSA Customer Support Employee on Oct 9, 2018Last modified by RSA Customer Support Employee on Oct 16, 2018
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000036796
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Appliance
 
Issue
If you are experiencing Overall application performance issue with a not very evident cause like a specific query running long and exhausting a specific resource, like CPU(s). and, by reviewing Oracle database alert_AVDB.log file, you find that it contains multiple ocurrances of Checkpoint cannot complete messages in short period of time (ethat is, two or more per hour), you may consider increasing the redo log size to alleviate some this performance issue.


 


The background



Checkpoint cannot complete messages are generated in the Oracle alert log when Oracle redo logs are switching so fast but a checkpoint associated with the log switch isn't completed fast enough for logs to be available when the next switch is needed. 

As per Oracle documentation, redo logs are



"The most crucial structure for recovery operations, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure."   


In times of very high load, Oracle's LGWR (LoG WRiter) process fill 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


Note that within under four minutes, Oracle had to switch logs three times; and twice, it was not able to perform the log switch without a database wait. The overall Oracle database performance may be dramatically be improved if the redo log could accommodate more activity and not causing the database to be stopped/halted. 
Tasks
Determine if your current configuration should be modified to avoid this waits.


  1. Review your current redo log file configuration by executing the following SQL while connected to Oracle as sysdba:


SELECT GROUP#, BLOCKSIZE, STATUS, MEMBERS, BYTES / (1024 * 1024) AS Size_MB FROM v$log;


  • By default, an Oracle database is configured with three redo logs of 50 MB each.
  • RSA Identity Governance & Lifecycle requires six redo logs of at least 800 MB each.
  • So, as a minimum requirement, the following would be the results of the query above:

GROUPSBLOCKSIZESTATUSMEMBERSSIZE_MB
1512INACTIVE1800
2512INACTIVE1800
3512INACTIVE1800
4512CURRENT1800
5512INACTIVE1800
6512INACTIVE1800


  1. Check the frequency of the log switches and determine if they are occurring too often.  

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.



Using the query below you can list the number of log switches per hour (NUM_LOGS).    



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_DATESTART_TIMENUM_LOGSMBYTESDBNAME
12/30/20158:00243200AVDB
12/30/20159:00121600AVDB
12/30/201510:001800AVDB
12/30/201511:001800AVDB



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.

Resolution

How to increase Oracle redo log size



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 8GB 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.


  • When using datafiles stored in ASM partition (RSA hardware appliances):


// 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;


  • When using datafiles stored in OS File System partition


// 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;

 

Executing the script



  1. Login as the oracle user 
  2. Save the content of the SQL above into a file called resize_redo_log.sql.
  3. Connect to the database as user sys, using the command below or by using SQL Developer.
  4. Execute the script with the command below


sqlplus "/as sysdba" @<filepath>/resize_redo_log.sql

Attachments

    Outcomes