How to Resize Redo Logs on Primary and Standby Database in Oracle Data Guard – Complete DBA Guide

Managing redo log file size is one of the most important Oracle DBA tasks for maintaining database performance, archive log efficiency, and Data Guard synchronization.

In Oracle Data Guard environments, resizing redo logs requires careful coordination between both the primary database and standby database to maintain proper log shipping and recovery consistency.

Improper redo log sizing can lead to:

• Excessive log switches
• High archive generation
• Checkpoint performance issues
• Standby synchronization delays
• Database performance degradation
• Recovery inefficiencies

In this complete Oracle DBA guide, you will learn:

• Why redo log resizing is important
• How redo logs work in Oracle
• How to resize redo logs safely
• How to manage redo logs in Data Guard
• Step-by-step primary and standby procedures
• Important Oracle SQL commands
• Best practices for redo log management

What are Redo Logs in Oracle?

Redo logs are Oracle database files that record all database changes before they are written to datafiles.

Oracle uses redo logs for:

• Crash recovery
• Instance recovery
• Media recovery
• Data Guard log shipping
• Transaction consistency

Every committed transaction generates redo entries.

Why Resize Redo Logs?

Redo logs should be sized properly according to database workload.

Very small redo logs may cause:

• Frequent log switches
• Archive log overload
• Checkpoint activity spikes
• Performance bottlenecks

Very large redo logs may:

• Delay recovery operations
• Increase crash recovery time
• Slow standby synchronization

Oracle DBAs commonly resize redo logs when:

• Implementing Data Guard
• Migrating databases
• Improving performance
• Handling archive growth
• Optimizing recovery operations

Recommended Redo Log Size

Oracle recommends sizing redo logs so log switches occur approximately every 15–30 minutes during peak workload.

Typical enterprise environments use redo log sizes between:

• 500MB
• 1GB
• 2GB

The optimal size depends on transaction volume and archive generation rate.

Check Current Redo Log Configuration

Before resizing redo logs, review the existing configuration.

Check Redo Log Groups

SQL> SELECT group#, thread#, sequence#, bytes/1024/1024 MB, status
FROM v$log;

Check Redo Log Members

SQL> SELECT group#, member
FROM v$logfile;

Check Standby Redo Logs

SQL> SELECT group#, bytes/1024/1024 MB
FROM v$standby_log;

Important Considerations Before Resizing

• Take a database backup before modifications
• Verify Data Guard synchronization
• Ensure standby database is healthy
• Never drop CURRENT redo log group
• Perform changes during maintenance windows

Step-by-Step: Resize Redo Logs on Primary Database

Step 1: Add New Redo Log Groups

Create new redo log groups with the desired size.

SQL> ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/oradata/redo04.log') SIZE 1024M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 5
('/u01/oradata/redo05.log') SIZE 1024M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 6
('/u01/oradata/redo06.log') SIZE 1024M;

These commands create new 1GB redo log groups.

Step 2: Force Log Switches

Switch logs until old groups become inactive.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Repeat multiple times if necessary.

Step 3: Verify Log Status

SQL> SELECT group#, status
FROM v$log;

Ensure old redo log groups are:

• INACTIVE
• UNUSED

Step 4: Drop Old Redo Log Groups

Now remove old smaller redo log groups.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Important: Never drop ACTIVE or CURRENT redo logs.

Perform a checkpoint to resolve the above issue if occur:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

 System altered.

 SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

 Database altered.

Step-by-Step: Resize Standby Redo Logs

In Oracle Data Guard environments, standby redo logs must also match the primary database redo configuration.

Step 1: Check Existing Standby Redo Logs

SQL> SELECT group#, bytes/1024/1024 MB
FROM v$standby_log;

Step 2: Add New Standby Redo Logs

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
('/u01/oradata/stdbyredo07.log') SIZE 1024M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8
('/u01/oradata/stdbyredo08.log') SIZE 1024M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9
('/u01/oradata/stdbyredo09.log') SIZE 1024M;

Step 3: Verify Standby Log Status

SQL> SELECT group#, status FROM v$standby_log;

Step 4: Drop Old Standby Redo Logs

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

Real-World Production Scenario

A production Oracle Data Guard environment generated excessive archive logs every few minutes due to very small 50MB redo log files.

The frequent log switches caused:

• High archive generation
• Checkpoint performance issues
• Standby lag
• Increased storage consumption

The DBA resized redo logs from 50MB to 1GB on both primary and standby databases.

After resizing:

• Log switch frequency reduced significantly
• Archive generation stabilized
• Standby synchronization improved
• Database performance became more stable

How to Monitor Log Switch Frequency

SQL> SELECT sequence#, first_time
FROM v$log_history
ORDER BY first_time DESC;

This query helps determine whether redo logs are too small.

Oracle Data Guard Best Practices

• Maintain equal redo log sizes on primary and standby
• Configure standby redo logs properly
• Monitor archive log generation regularly
• Keep at least 3 redo log groups
• Monitor standby lag frequently
• Perform regular Data Guard health checks

Common DBA Mistakes

• Dropping CURRENT redo logs
• Using mismatched redo sizes
• Ignoring standby redo logs
• Resizing during peak production hours
• Not forcing log switches properly

Performance Benefits of Proper Redo Log Sizing

Proper redo log sizing helps:

• Reduce checkpoint overhead
• Improve database throughput
• Reduce archive log pressure
• Improve Data Guard transport
• Enhance recovery performance

Frequently Asked Questions (FAQ)

Why should redo logs be resized?

Redo logs should be resized to reduce excessive log switches and improve database performance.

Can redo logs be resized directly?

No. Oracle does not allow direct resizing of redo logs. New groups must be created and old groups dropped.

Should standby redo logs match primary logs?

Yes. Standby redo logs should match the primary redo log size and configuration.

How many redo log groups are recommended?

Oracle typically recommends at least 3 redo log groups.

What happens if redo logs are too small?

Small redo logs cause excessive log switches, checkpoint overhead, and archive log generation.

Related Posts

• ORA-01194 File Needs More Recovery
• ORA-03113 Communication Channel Error
• ORA-00600 Internal Error Code Arguments

👉 Check our complete guide: Oracle Error Codes Guide

Conclusion

Redo log sizing plays a critical role in Oracle Database performance, recovery, and Data Guard synchronization.

Improperly sized redo logs can lead to excessive archive generation, checkpoint overhead, and standby lag.

By following the step-by-step procedures explained in this guide, Oracle DBAs can safely resize redo logs on both primary and standby databases while maintaining Data Guard consistency.

Regular monitoring, proactive performance tuning, and proper redo log management are essential for stable Oracle production environments.

Your comments, especially which will help us improve the functionality, will be greatly appreciated. Do not forget to follow my Blog.