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.

Prerequisites

  • Basic understanding of Relational Database Concepts
  • Good understanding of Oracle Database Architecture

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

There are three possible configurations based on which a Redo Log Based staging database can be set up. 

  1. 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.
  2. 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.
  3. 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. 
  • 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.

Also, for replication, an initial data dump must be taken from the source database and loaded to the target database before thenges that come in the way of a developer setting out to implement this replication process.

Method 2: Oracle Redo Log Based Replication using Hevo

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.

Experience the power and simplicity of implementing Oracle Redo Log Based Replication by signing up for a 14-day free trial with Hevo.

What is Oracle Redo Logs?

The following concepts in Oracle Redo Logs are:

  • Oracle Redo Logs: Oracle Redo Threads or Logs
  • Oracle Redo Logs: Oracle Redo Log Operation
  • Oracle Redo Logs: Redo Log Operation

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.

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.

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).

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.

D. Choosing 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.

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.

ViewDescription
V$LOGDisplays the redo log file information from the control file
V$LOGFILEIdentifies redo log groups and members and member status
V$LOG_HISTORYContains 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.

Jithin Gopal
Freelance Technical Content Writer, Hevo Data

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.

No-code Data Pipeline For Oracle Database

Try for free