Oracle Standby Redo Logs vs Online Redo Logs: Comprehensive Analysis

Do you want to learn about Oracle standby redo logs and online redo logs? If yes, then you are in the right place. Oracle is a very popular relational database system sold by Oracle corporation based on proprietary technology and license. It can support different kinds of load patterns ranging from transactional workloads to data warehouse processing. Oracle provides a feature-rich querying layer capable of executing complex transformations over data.

One of the biggest reasons for Oracle’s popularity among enterprise companies is its ability to be highly available. Even when things go wrong, it is possible to recover an Oracle database instance in a very short time. It is made possible by its underlying architecture, which is based on redo logs.

Redo logs allows Oracle databases to be replicated as multiple masters reside in different geographical regions and ensure high availability. This post aims to clear the concepts around the kinds of redo logs that bring this extremely stable architecture to reality. By the end of this post, you will understand the concept behind standby redo logs and online redo logs, which are instrumental in Oracle’s replication magic. 

Understanding Oracle Online Redo Logs

Redo logs are essential for Oracle’s failure recovery and high availability. They contain redo records that document all changes made in the Oracle database. Replaying redo logs allows the database to be restored to any point in time from its base state.

Key Points

• Redo logs facilitate recovery from both software and hardware failures.
• They are crucial for Oracle replication, with many change data capture methods leveraging redo logs.
• By default, redo logs are overwritten when the log file is switched.
• Oracle’s ARCHIVELOG mode allows log file data to be archived, providing a larger recovery window.
• In ARCHIVELOG mode, log files are archived to remote or local locations instead of being overwritten.

In case you need to add more redo logs than the default configuration, it can be done with the following command:

ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M; 

This is often done to make separate processes work based on different redo logs. 

An important point to note here is that online redo logs always run on the master or the local system of the source database. There is another type of redo log called standby redo logs, that is configured to increase data protection in a replicated environment. 

Understanding Oracle Standby Redo Logs

A standby database is designed for data protection during disasters and aids in recovering the main database. It can also serve as a reporting database, helping to offload queries without impacting the performance of the transactional database. Standby databases can operate using either online redo logs or a separate log file known as a standby redo log file. Administrators must choose between a maximum protection standby configuration or a maximum performance configuration when setting up a standby database.

Key Points

• Replication Process:
  • An archival process archives the online redo log files.
  • Archived logs are then written to the standby database location.
  • A recovery process uses these archived logs to populate the standby database.
• Data Loss Risk:
  • A delay between the recovery process and writing logs to the standby database can lead to potential data loss, which can extend to minutes even with Oracle’s fast recovery times.
  • Configuring standby redo logs is recommended to mitigate this risk.
• Standby Redo Logs:
  • Created by a network server synchronization process, standby redo logs track online redo database logs and write continuously to a standby log file in the destination database.
  • The recovery process utilizes standby redo logs for writing entries to the standby database, reducing reliance on archived redo logs.
• Recommendation:
  • Oracle advises using standby redo logs in all replication setups to minimize the potential data loss interval from minutes to seconds.
  • Standby redo logs can be created using the command:
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

Difference between Standby Redo Logs vs Online Redo Logs

Feature	Standby Redo Logs	Online Redo Logs
Purpose	Used for data protection in standby databases.	Used for recovery and maintaining the current database state.
Location	Resides in the standby database.	Resides in the primary database.
Functionality	Captures changes applied to the standby database.	Captures changes made to the primary database.
Log Writing Process	Continuously written by the network server synch process.	Written by the log writer process during transactions.
Data Loss Window	Reduces potential data loss to seconds.	May lead to data loss in the event of a failure before logs are archived.
Recovery Process	Used to quickly apply changes to the standby database.	Used during instance recovery to apply changes to the primary database.
Configuration	Optional but recommended for replication setups.	Mandatory for all Oracle databases to ensure data integrity.

Conclusion

We have now learned the concepts behind the online redo logs and the standby redo logs. Summarizing what we have read above, standby redo logs are different from online redo logs in the following ways:

1. Standby redo logs are used to store data received from another database or the primary data source. 
2. Standby redo logs are present at the destination database end.

So the best way to accomplish Oracle replication is based on redo logs, but Oracle makes you go through a lot of configuration changes to implement it. The concepts of redo logs are a must-have tool for an ETL engineer who is using Oracle as the source database. You can sidestep all these concepts and related configurations using a cloud-based ETL tool like Hevo. 

Frequently Asked Questions