Oracle Redo Logs are one of the principal components in the recovery operations of an Oracle database. The Redo Logs consist of two or more pre-configured files that log all the changes made to the database. This helps the database instance in the recovery of data in case of failure.
This tutorial will help you to understand the concepts of Oracle Redo Logs and their importance in database management. Also, it will enable you to manage the Redo Logs as a DBA and do operations such as creating, adding, and dropping the files, managing groups, and members, etc.
We, at Hevo, endeavor to help our clients make the best use of the tools such as the Oracle database in their data stack. This post will help you get the best out of your Oracle Database using Redo Logs.
Table of Contents
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ sources (including 40+ free data sources) such as Oracle to your desired data warehouse or a destination of your choice and visualize it in a BI Tool.
Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Connectors: Hevo supports 100+ Integrations to SaaS platforms, files, databases, and BI tools. It supports various destinations including Amazon Redshift, Google BigQuery, Snowflake, and Firebolt Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, and PostgreSQL databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
Prerequisites
- Basic understanding of Relational Database Concepts
- Good understanding of Oracle Database Architecture
What is Oracle Redo Logs?
The Redo Log, which consists of two or more preallocated files that contain all changes made to the database as they occur, is the most essential component for recovery operations. Every Oracle Database instance has a redo log to safeguard the database in the event of an instance failure.
The following concepts in Oracle Redo Logs are:
1) Oracle Redo Threads or Logs
When speaking in the context of multiple database instances, the redo log for each database instance is also referred to as a Redo Thread. In typical configurations, only one database instance accesses an Oracle Database, so only one thread is present.
In an Oracle Real Application Clusters environment, however, two or more instances concurrently access a single database and each instance has its own thread of redo. A separate redo thread for each instance avoids contention for a single set of Oracle Redo logs files, thereby eliminating a potential performance bottleneck.
2) Oracle Redo Log Operation
The Log Writer Process (LGWR) is responsible for writing into the redo log files of the database. As shown in the picture, the files are getting written in a circular fashion: when the current file fills, it gets written into the next file, and so on.
What happens when the Redo Logs are filled? The availability of a filled Redo Log to the LGWR depends upon the archive mode of the database.
- NOARCHIVELOG: Archiving is disabled i.e. the Redo Log files become available after changes are written to the datafiles.
- ARCHIVELOG: Archiving is enabled i.e. the Redo Log files are available only after changes are written to the data files and the file is archived.
3) Redo Log Contents
Redo log files are overflowing with redo records. A redo record, also known as a redo entry, is made up of a collection of change vectors, each of which provides a description of a change made to a single database block. If you change a salary value in an employee table, for example, you produce a redo record including change vectors describing changes to the table’s data segment block, the undo segment data block, and the transaction table of the undo segments.
Redo entries record data that may be used to recreate all database changes, including undo segments. As a result, the redo log also safeguards rollback data. When you use redo data to restore the database, the database reads the change vectors in the redo records and applies the changes to the appropriate blocks.
The Log Writer (LGWR) database background process buffers redo records in a circular way in the SGA’s redo log buffer and write them to one of the redo log files. When a transaction is committed, LGWR sends the transaction redo records from the SGA’s redo log buffer to a redo log file and assigns a System Change Number (SCN) to each committed transaction’s redo records. The user process is told that the transaction has been committed only after all redo records related to a specific transaction are securely on disk in the online logs.
Before committing a transaction, redo records can also be written to a redo log file. LGWR flushes all redo log entries in the redo log buffer to a redo log file if the redo log buffer is full or another transaction commits, even if some redo records are not committed. The database can undo these modifications if required.
How Does Oracle Database Write to the Redo Log?
A database’s redo log is made up of two or more redo log files. The database requires at least two files to ensure that one is always available for writing while the other is archived (the database is in NOARCHIVELOG mode).
LGWR writes in a circular method to redo log files. LGWR begins writing to the next available redo log file when the current redo log file is full. When the final available redo log file is full, LGWR returns to the first available redo log file and writes to it, thus restarting the cycle. The figure below depicts the cyclic writing of the redo log file. The numbers after each line represent the order in which LGWR writes to each redo log file.
Filled redo log files are available to LGWR for reuse depending on whether archiving is enabled.
- If archiving is turned off (the database is in NOARCHIVELOG mode), a full redo log file is available after the changes recorded in it have been written to the data files.
- If archiving is enabled (the database is in ARCHIVELOG mode), LGWR has access to a filled redo log file after the changes recorded in it have been written to the data files and the file has been archived.
Image Source
Important Terms Associated With Oracle Redo Logs
The following terms will help you in understanding different types of Redo Log operations:
- Current Redo Log File: This is the file that LGWR is currently writing.
- Active Redo Log File: Redo Log file that is required for instance recovery.
- Inactive Redo Log Files: Redo Log files that are no longer required for instance recovery.
- Log Switch: This is the point at which the LGWR stops writing to a file and starts writing to another.
- Log Sequence Number: Every time a log switch occurs and LGWR begins writing to a Redo Log file, it gets assigned a sequence number. The archived log retains this number. When the Redo Log file is cycled back, it gets a new sequence number again
Planning the Redo Logs
It provides guidelines on configuring a database instance redo log and contains the following information:
A) Multiplexing Redo Log Files
Oracle Database supports a multiplexed redo log, which means that two or more identical copies of the redo log can be automatically kept in separate locations to defend against a failure involving the redo log itself. These areas should be on different disks for the most advantage.
Even if all copies of the redo log are stored on the same disk, redundancy can assist protect against I/O errors, file corruption, and other issues. LGWR continuously writes the same redo log information to several identical redo log files when redoing log files are multiplexed, therefore removing a single point of redo log failure.
Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as a group 1, group 2, and so on.
Image Source
A_LOG1 and B_LOG1 are both in Group 1, A_LOG2 and B_LOG2 are both in Group 2, and so on. A group’s members must all be the same size.
B) Placing Redo Log Members on Different Disks
When setting up a multiplexed redo log, place members of a group on different physical disks. If a single disk fails, then only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function.
Spread redo log members among discs if you archive the redo log to avoid contention between the LGWR and ARCn background processes. For instance, if you have two groups of multiplexed redo log members (a duplexed redo log), store each member on a separate disk and specify your archiving destination to a fifth disk. This will avoid conflict between LGWR (writing to members) and ARCn (reading the members).
To decrease congestion while writing data blocks and redo records, datafiles should be put on distinct disks than redo log files.
C) Setting the Size of Redo Log Members
Consider whether you will archive the redo log when determining the size of the redo log files. Redo log files should be sized so that a whole group may be archived to a single unit of offline storage media (such as a tape or disk) with as little space on the medium as possible.
Assume that only one filled redo log group can fit on a tape and that 49% of the tape storage capacity is unused. In this instance, it is preferable to significantly reduce the size of the redo log files so that two log groups may be stored on each tape.
The size of all members of the same multiplexed redo log group must be the same. Members of various organizations might be of varying sizes. Changing the file size across groups, on the other hand, is pointless. If checkpoints are not configured to occur between log switches, make all groups the same size to ensure that they occur at regular intervals.
A redo log file can be a minimum of 4 MB in size.
D) Choosing the Number of Redo Log Files
Testing multiple configurations is the best technique to discover the proper amount of redo log files for a database instance. The optimal design includes as few groups as possible while still allowing LGWR to write redo log information.
A Database instance may only require two groups in specific instances. In some cases, an extra group may be required by a database instance to ensure that a recycled group is always available to LGWR. Examining the contents of the LGWR trace file and the database alert log during testing is the simplest approach to examine whether the current redo log configuration is adequate.
Add groups if messages show that LGWR regularly has to wait for a group because a checkpoint has not been completed or a group has not been archived. Before you set up or change the configuration of an instance redo log, you can decide the parameters that will restrict the number of redo log files.
The following parameters limit the amount of redo log files that may be added to a database:
- The MAXLOGFILES parameter in the CREATE DATABASE statement specifies the maximum number of groups of redo log files that can be created for each database. The value of a group can range from 1 to MAXLOGFILES. When the compatibility level is less than 10.2.0, the only option to bypass this upper restriction is to recreate the database or its control file. As a result, it is essential to address this constraint before creating a database. You can exceed the MAXLOGFILES limit when compatibility is set to 10.2.0 or later, and the control files extend as needed. If the MAXLOGFILES parameter is not supplied in the CREATE DATABASE command, the database uses the operating system’s default value.
- The maximum number of members for each group is determined by the MAXLOGMEMBERS parameter in the CREATE DATABASE statement. The only way to override this upper limit, as with MAXLOGFILES, is to re-create the database or control file. As a result, it is essential to address this constraint before creating a database. If the MAXLOGMEMBERS argument for the CREATE DATABASE command is not specified, the database uses the operating system default value.
E) Controlling Archive Lag
You can compel all enabled redo log threads to swap their current logs at regular intervals. Changes are made available to the standby database in a primary/standby database arrangement by archiving redo logs at the primary site and then shipping them to the standby database.
The changes applied by the standby database may lag behind the primary database’s changes because the standby database must wait for the changes in the primary database’s redo log to be archived (into the archived redo log) and then shipped to it. You can lessen this lag by setting the ARCHIVE LAG TARGET initialization parameter. Setting this parameter allows you to determine how long the lag might be in seconds (time).
Setting the ARCHIVE_LAG_TARGET Initialization Parameter
When you specify the ARCHIVE_LAG_TARGET initialization parameter, the database examines the instance’s current redo log on a regular basis. If any of the following circumstances are satisfied, the instance will change the log:
- The current log was created prior to n seconds (time) ago, and the expected archiving time for the current log is m seconds (proportional to the number of redo blocks utilized in the current log), where n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
- Redo records can be found in the current log.
If the Oracle Data Guard environment is not configured in a no-data-loss mode, the ARCHIVE_LAG_TARGET initialization parameter indicates how many seconds of redo the standby might lose in the case of a primary shutdown or failure. It also specifies a time limit (in seconds) for the current log of the primary database. This is not the precise log switch time because the expected archiving time is also taken into account.
The initialization parameter setting given below sets the log switch interval to 30 minutes.
ARCHIVE_LAG_TARGET = 1800
A value of 0 disables the capability of time-based log switching. This is the default setting.
Even if there is no standby database, you can set the ARCHIVE_LAG_TARGET initialization parameter. The ARCHIVE_LAG_TARGET parameter, for example, can be used to compel logs to be switched and archived.
The ALTER SYSTEM SET command can be used to set the dynamic parameter ARCHIVE_LAG_TARGET.
Methods to Setup Oracle Redo Log Based Replication
Users can set up Oracle Redo Log Based Replication by using one of the following methods:
Method 1: Setting up Oracle Redo Log Based Replication Manually
Replication using Oracle Redo Logs involves setting up a staging table that captures every change that happens in the original database and then using a custom application to poll the staging table and insert entries to the target Data Warehouse.
A staging table is used here so that there is minimal interference in the original table that is being used for transaction processing. There are three possible configurations based on which a Redo Log Based staging database can be set up.
- Asynchronous hot-log configuration: In this configuration, the staging database is located in the same hardware instance as the source database. This is the simplest to set up and has the lowest latency, but exerts the maximum load on the source database.
- Asynchronous distributed hot-log configuration: This differs from the above configuration because of the fact that the staging database is in a separate hardware instance and changes captured from the redo logs are transported to the staging databases through an existing database connection between the two databases.
- Autolog configuration: In this configuration, a standby Redo Log is configured in the staging database which tracks the changes in the source database. Change sets to the staging database are inserted based on the standby redo log in the staging database. This has the highest latency and is the most complicated to set up, but works completely independent of the source database without exerting any load on it.
In this article, we will deal with setting up an asynchronous hot log configuration to replicate an Oracle database to an external data warehouse. Below is a 12 step process to setup OracleRedo Log Based Replication:
- Step 1: Use the below command to configure the database to operate in FORCE LOGGING mode
ALTER DATABASE FORCE LOGGING;
- Step 2: Enable logging to capture the UPDATE statements.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
- Step 3: Use the below command to enable logging for all columns of the concerned table.
ALTER TABLE ts.products_data ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
- Step 4: Use the PREPARE_TABLE_INSTANTIATION procedure to prepare the source tables for log data capture.
BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'ts.products_data'); END;
- Step 5: Create a change set that will track the logs originating at the default HOTLOG_SOURCE. This log source is a predefined one provided by Oracle.
BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'PRODUCT_DATA_SET', description => 'Change data set for product info', change_source_name => 'HOTLOG_SOURCE', stop_on_ddl => 'y', begin_date => sysdate, end_date => sysdate+5); END;
- Step 6: Create a change table where all the change information will be loaded as individual rows.
BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( owner => 'cdcpub', change_table_name => 'products_data_ct', change_set_name => 'PRODUCT_DATA_SET', source_schema => 'TS', source_table => 'PRODUCTS_DATA', column_type_list => 'ID NUMBER(6), NAME VARCHAR2(50), PRICE NUMBER(8,2)', capture_values => 'both', rs_id => 'y', row_id => 'n', user_id => 'n', timestamp => 'n', object_id => 'n', source_colmap => 'n', target_colmap => 'y', options_string => 'TABLESPACE TS_PRODUCT_DATA_SET'); END;
- Step 7: Use the ALTER_CHANGE_SET procedure to enable the change set.
BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET( change_set_name => 'PRODUCT_DATA_SET', enable_capture => 'y'); END;
- Step 8: To read the change data, a subscription to change data must be created.
BEGIN DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION( change_set_name => 'PRODUCT_DATA_SET', description => 'Change data for PRODUCTS', subscription_name => 'PRODUCT_DATA_SUB'); END;
- Step 9: Use the SUBSCRIBE procedure to use the subscription that was created in the previous step.
BEGIN DBMS_CDC_SUBSCRIBE.SUBSCRIBE( subscription_name => 'PRODUCT_DATA_SUB', source_schema => 'SH', source_table => 'PRODUCTS', column_list => 'ID, NAME,PRICE', subscriber_view => 'PRODUCT_DATA_VIEW'); END;
- Step 10: Use the below command to activate the subscription.
BEGIN DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION( subscription_name => 'PRODUCT_DATA_SUB'); END;
- Step 11: Set up a collection window to process all the changes added from the last subscription.
BEGIN DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW( subscription_name => 'PRODUCT_DATA_SUB'); END;
The above command acts as a period polling job, that is this command results in new change data that was created since the last call of this command to be added to the subscription window.
- Step 12: We can now use a select query to read all the changes from the view we created in step 9.
SELECT ID, NAME, PRICE FROM PRODUCT_DATA_VIEW;
This step is to be made part of an automated script that keeps using the select statements to gather all the changes and then uses the appropriate method for the target data warehouse to insert the data.
Now that was a long list of steps to set up an Oracle Redo Log-Based Replication. But the job is not over yet. The developer still needs to have a custom script to insert the data into a target data warehouse or database.
Also, for replication, an initial data dump must be taken from the source database and loaded to the target database before the automated process can start capturing the changes. Let’s now look into the typical challenges that come in the way of a developer setting out to implement this replication process.
Limitations of Setting up Oracle Redo Log Based Replication Manually
- We did not consider any configuration changes related to memory management and optimization here. In reality, there is an umpteen number of configuration changes that need to be done to run this process efficiently.
- The engineers still need to implement a custom script to use the change data, process them and insert them into their target databases.
- This implementation is best handled by an engineer with expert knowledge of oracle administration, target database, and programming skills. This means there is a steep learning curve before creating a clean execution.
An alternative to going through all these hardships will be to use a cloud-based data automated pipeline tool like Hevo which can execute such replications in a matter of a few clicks.
Method 2: Oracle Redo Log Based Replication using Hevo
Image Source
Hevo is a completely self-serve, fully managed, automated data pipeline that can help you implement Oracle Redo Log Based Replication without having to write any code. Hevo’s point and click interface ensures the lowest time to production possible.
Here’s how simple it is to set up Oracle Data Replication with Hevo:
- Authenticate and connect your Oracle data source.
- Select the replication mode as “Redo Log Based Replication”.
- Point to the destination Data Warehouse or database where you want to move data.
Hevo supports Oracle change data capture out of the box and will ensure that your data is loaded from Oracle to your target database or Data Warehouse in real-time.
Get Started with Hevo for Free
Here are more reasons to try Hevo:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
Experience the power and simplicity of implementing Oracle Redo Log Based Replication by signing up for a 14-day free trial with Hevo.
Forcing Log Switches
When LGWR stops writing to one redo log group and begins writing to another, then a log switch occurs. By default, a log switch occurs automatically when the current redo log file group is full.
You can force a log switch to deactivate the currently active group and make it available for redo log maintenance procedures.
You must have the ALTER SYSTEM privilege to force a log switch. You should use the SWITCH LOGFILE clause with the ALTER SYSTEM statement.
Verifying Blocks in Redo Log Files
You may configure the database to utilize checksums to validate blocks in redo log files. When you set the initialization parameter DB_BLOCK_CHECKSUM to TRUE, the database computes a checksum for each database block after being written to disk, including each redo log block written to the current log. The checksum is stored in the block’s header.
The checksum is used by Oracle Database to detect corruption in a redo log block. When a redo log block is read from an archived log during recovery or written to an archive log file, the database verifies it. If corruption is detected, an error is generated and recorded in the alert log.
If the system detects corruption in a redo log block while attempting to archive it, it attempts to read the block from another member of the group. If the block is corrupted in all members of the redo log group, archiving won’t be able to proceed.
TRUE is the default value for DB_BLOCK_CHECKSUM. The ALTER SYSTEM statement can be used to modify the value of this parameter dynamically.
You can also refer to Oracle Database Reference for an in-depth understanding of the DB_BLOCK_CHECKSUM initialization parameter.
Viewing Redo Log Information
The views which provide information on Redo Logs are as follows.
| View | Description |
|---|
V$LOG | Displays the redo log file information from the control file |
V$LOGFILE | Identifies redo log groups and members and member status |
V$LOG_HISTORY | Contains log history information |
The query below returns the control file information for a database’s redo log.
SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 10605 1048576 1 YES ACTIVE 11515628 16-APR-00
2 1 10606 1048576 1 NO CURRENT 11517595 16-APR-00
3 1 10603 1048576 1 YES INACTIVE 11511666 16-APR-00
4 1 10604 1048576 1 YES INACTIVE 11513647 16-APR-00
To see the names of all of the members of a group, you can use a query similar as showcased below:
SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER
------ ------- ----------------------------------
1 D:ORANTORADATAIDDB2REDO04.LOG
2 D:ORANTORADATAIDDB2REDO03.LOG
3 D:ORANTORADATAIDDB2REDO02.LOG
4 D:ORANTORADATAIDDB2REDO01.LOG
If STATUS is blank for a member, then the file is in use.
For further information on Oracle Redo Logs, you can visit here.
Conclusion
In this article, you have learned the following:
- Understand Oracle database.
- Understand Oracle Redo Logs and how they work.
- Use SQL commands to work with Redo Logs.
If you want an easier alternative to transfer data from your Oracle database without having to use Oracle Redo Logs described above, you can use an automated data integration solution such as Hevo.
Visit our Website to Explore Hevo
Hevo Data, a No-code Data Pipeline, helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.
Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!
Tell us about your experience of learning about Oracle Redo Logs. Share your thoughts in the comments section below.
With a problem-solving mindset and a flair for analytical thinking, Jithin loves writing about data integration and analysis, and offers well-researched articles about data analysis and integration.