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

[#1]

Article Number

000036796

Applies To

RSA 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 occurrences of Checkpoint not complete messages in a short period of time (that is, two or more per hour), you may consider increasing the redo log size to alleviate some this performance issue.

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

Note that within under four minutes, Oracle had to switch redo 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 improved if the redo log could accommodate more activity and consequently not cause the database to be waiting for the log switch to complete. 

Oracle AWR Performance Observations

An AWR can also be generated to confirm the impact on the Oracle database performance.  See article 000027961 - How to generate Oracle Automatic Workload Repository (AWR) report for RSA Identity Governance and Lifecycle.

The following AWR section may show the following symptoms, indicating that the redo log size is too small.

• Load Profile shows large Redo size (bytes) Per Second & Per Transaction.
• Top 10 Foreground Events by Total Wait Time shows large log file switch (checkpoint incomplete) Wait Avg(ms).
• Foreground Wait Events shows large log file switch (checkpoint incomplete) Avg wait (ms).
• Top Sessions shows Events for log file sync and log file parallel write.
• Top Blocking Sessions shows the Event Caused for log file sync and log file switch (checkpoint incomplete) for the Log Writer (LGWR).
• Activity Over Time shows the top Event as log file sync.

If these symptoms are present, then it is likely that the redo logs are too small.

Task

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:

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

2. 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_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.

Resolution

Special consideration if using Oracle Data Guard

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;

• Check the alert_AVDB.log to see if the log_archive_config parameter has been set.
• Connect to the database as SYSDBA, and issue the following command: show parameter log_archive_config

If the parameter is set i.e. log_archive_config='DG_CONFIG=(AVDB,AVDBSTBY)', then Oracle Data Guard is enabled.

If Oracle Data Guard is enabled, then increasing the Oracle redo log size needs to be performed by your local DBA.  This is because the logs also need to be changed on the StandBy database; plus, the increased size needs to be taken into consideration, with regards to the shipping and storage.  In particular, the storage needs to be taken into account, to avoid an ORA-00257: Archiver error, for the message Archived log Destination is full.

How to increase the Oracle redo log size

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.
 

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