Oracle redo log size increase in Oracle 12C

Oracle redo log is the most crucial structure for recovery operations, redo logs consists of 2 or additional preallocated files that store all changes made in database as they occur. Each instance of database change has an associated redo log to guard the info just in case database encounters a failure.Redo log file contains redo records. A redo record or redo entry consists of change vectors, each of which is a description of a change made to a single block in the database. So keeping oracle redo log size correct matters a lot in performance of database under high load situations. In this article we will learn how to manage the oracle redo log size in Oracle 12C database.

Steps to increase oracle redo log size

1) Run the below query as “SYS AS SYSDBA” and get the Logfile name with location, redo log size and group number.

sqlplus /nolog;
conn sys as sysdba;
SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

2) Run the below query and check status of the log file group. Status should be “INACTIVE” to update the log file size.

Currently in below screenshot the group 1 file is in current status, so we need to switch the logfile from CURRENT to INACTIVE to drop and create new one.

SELECT group#, status from v$log;

3) Run the below query to switch the status between ACTIVE and CURRENT.

After running below query you can see the CURRENT status moved to Group 2.

ALTER system switch logfile;

4) Run the below query to drop the logfile group. Make sure the status should not be in “CURRENT”

This also might not allow you to drop the logfile as still the Group 1 status is ACTIVE so we need to run the query provided in next step to make it INACTIVE to drop.

ALTER DATABASE DROP LOGFILE GROUP 1;

Error Log:

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('F:\ORACLE12C\ORADATA\DB12C\REDO02.LOG') SIZE 1G REUSE;
ALTER DATABASE ADD LOGFILE GROUP 2 ('F:\ORACLE12C\ORADATA\DB12C\REDO02.LOG') SIZE 1G REUSE
*
ERROR at line 1:
ORA-01184: logfile group 2 already exists

5) If it fails to drop the logfile group, please run the below query and then run the above query again to Drop.

ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 1;

6) Once the log group has been dropped, run the below query to create the log group with required size. This way you can manage the oracle redo log size according to your requirement.

ALTER DATABASE ADD LOGFILE GROUP 1 ('F:\ORACLE12C\ORADATA\DB12C\REDO01.LOG') SIZE 1G REUSE;

7) Repeat the same for all the groups to manage the oracle redo log size.

Example 1: For dropping and recreating Group 2 redo log file.

 

Example 2: For dropping and recreating Group 3 redo log file.

8) Now the status of newly added redo logfile is changed to UNUSED, so you can use below commands alternatively to make all INACTIVE,ACTIVE status.

By following all above steps you will be able to size the redo logs for oracle 12C.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.