Home > Workload Solutions > Oracle > Guides > Implementation Guide—Oracle Database 19c Best Practices on PowerStore > Oracle 19c Database: Increase the Redo Log Size
In this best practice, we increased the size of the Oracle redo logs to minimize log switching frequency to improve database performance. Proper sizing of the database redo logs can reduce wait events in the database providing an optimal database system. However, the redo logs sizing also depends on Recovery Time Objective (RTO) and Recovery Point Objective (RPO) based on the business SLA.
Overview
The most crucial structure for database recovery operations is the online redo log, which consists of two or more pre-allocated 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.
A log file sync wait event is triggered when a user session issues a commit or a rollback. It is the time it takes for the log writer (LGWR) to write to the redo log file and post the user session. The log file sync wait event for the Oracle database was used to validate the best practices.
In this case the original size of the log files was 200MB and they were replaced with log files of 8 GB in size.
Note: Not every workload will show significant performance increase when increasing the size of the redo logs. Customers should evaluate the AWR to determine if the redo logs have an associated high number and duration of wait times. If the redo logs show a possible bottleneck, increasing the size of the redo logs could improve performance.
Implementation steps
We recreated five new redo log groups, each 8 GB in size, and dropped the three original smallest size of redo log groups by performing the following SQL commands with system or sys privilege:
SQL> ALTER DATABASE ADD LOGFILE GROUP 4('+REDO') SIZE 8G;
SQL> ALTER DATABASE ADD LOGFILE GROUP 5('+REDO') SIZE 8G;
SQL> ALTER DATABASE ADD LOGFILE GROUP 6('+REDO') SIZE 8G;
SQL> ALTER DATABASE ADD LOGFILE GROUP 7('+REDO') SIZE 8G;
SQL> ALTER DATABASE ADD LOGFILE GROUP 8('+REDO') SIZE 8G;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1,
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE GROUP 3 ;