Oracle saves filled groups of redo logs (the database transaction logs) to one or more offline destinations, collectively known as the archived redo log or the archive logs. Changes main in a primary database are replicated to logical or physical standby environments using archive logs.

A log switch happens when a redo log fills up and is archived. Debezium ingests changes across both redo and archive logs. Debezium requires access to the archive log to finish processing redo entries when the redo log is archived.

The Oracle container registry image used in the Install Oracle section does not have archive logging enabled. If you use another image or a pre-existing environment, you must check whether archive logging is enabled. To check the status, use the SYS user and the password defined during the installation for ORACLE_PWD to connect to the ORCLCDB database and execute the following query:

If the column contains ARCHIVELOG, then archive logging is enabled. If the column contains the value NOARCHIVELOG, archive logging isn’t enabled, and further configuration is necessary.

When setting up the Oracle archive log, not only do we need to enable the logging feature, but we’ll also need to specify a location on the disk to store the logs. If you are using a pre-existing environment, you will need to consult with your database administrator for this. Most database servers store archive log files using special paths, and you will need to know if Oracle Automatic Storage Management (ASM) is used or what volume has adequate space on the database server.

Let’s open a terminal to the Oracle database container. We want to connect to the database container using SQL*Plus to use a client that allows easy unmounting and restarting of the database. So in a new terminal, execute:

The above command will start Oracle’s SQL*Plus, a command line Oracle SQL client. The client will ask for your password, which will be the same as the ORACLE_PWD environment variable or oraclepw if you’re using the Oracle registry container. Use your environment’s SYS user password when connecting to an existing Oracle environment.

We need to set two database parameters:

To set these values, we’ll execute the following SQL commands inside the SQL*Plus terminal window where we’ve already connected to the database as the SYS user. Again, if you’re connecting to a pre-existing environment, please consult your database administrator before you proceed here.

The above ALTER statements adjust the database parameters, specifying that the retention of archive logs is up to a maximum of 10GB and that /opt/oracle/oradata/ORCLCDB is where the logs are stored.

The final output from SQL*Plus should show the following:

The configuration of the archive logs is complete, and when a database log switch occurs, the Oracle ARCH Process will convert the redo log to an archive log stored in the location specified on the disk.

Oracle’s transaction log is known as a redo log. These logs are vital as they’re used in a database crash or media failure to recover to a checkpoint. Unfortunately, Oracle container images often use a redo log configuration that isn’t useful for Debezium.

There are two log mining strategies for Debezium’s Oracle connector. The strategy controls how the connector interacts with Oracle LogMiner and how the connector ingests schema and table changes.

When using the online_catalog mode, you can safely skip this step entirely.

When using the redo_log_catalog mode (the default), the redo log size is critical to reducing the frequency of log switches. The LogMiner session restarts and the data dictionary is rebuilt in the redo logs when a log switch occurs. The dictionary is read back by LogMiner and used to track DDL changes when the session restarts, which can lead to a slight initial session delay while the dictionary tables are populated. Overall you gain better performance when the redo log is large enough to write the data dictionary to a single log file.

The Oracle container registry images come configured with a redo log size of 200MB. This default size is too small when using the default mining strategy, so we will adjust this so that the logs use a size of 400MB.

Before making any changes, it’s essential to examine the current state of your environment. In the same terminal where you enabled archive logging, execute the following SQL to determine the current log sizes.

The Oracle container registry image will return the following output:

This output tells us there are 3 log groups, and each group consumes 200MB of space per log. Additionally, the status associated with each group is crucial as it represents the current state of that log.

The following describes the log statues in detail:

Now, using the same terminal window, execute the following SQL to determine the filenames and locations of the redo logs.

The Oracle container registry image will return the following output:

We can glean from this that each log group consists of a single redo log. Oracle does support the notion of multiple logs per group, allowing for what is called multiplexing. You will generally only see this in a production environment and occasionally in a test environment, but it’s rare to see this in a development or container environment.

The goal is to adjust the BYTES column in the V$LOG table to have a value of 400MB. Unfortunately, the only way to make this adjustment is to drop and re-create the log group, and this is only possible if the STATUS of the group is either INACTIVE or UNUSED. Since log group 1 was INACTIVE above, we’ll start with it, but you can safely perform this procedure on the log groups in any order.

In the terminal where SQL*Plus is running, execute the following:

This will drop and re-create the log group with the size of 400MB. We will use the same log file name in the MEMBER column from the VLOGFILE table. If the database uses multiplexing, with multiple log files per log group, use a comma-delimited list of filenames to register each log file.

Continue the above procedure for all log groups, changing the log group and filenames accordingly until all INACTIVE or UNUSED groups have a size of 400MB. Once all that remains to be changed are those that are CURRENT, you can issue a log switch on the database to advance the database to the next redo log using the following SQL:

If you recheck the size of the logs in the V$LOG, you’ll see the output looks like the following:

We now need to wait for the database to eventually switch the status of log group 3 to INACTIVE. The switch could take several minutes, so be patient and recheck the size periodically. Once the status reaches INACTIVE, modify the final log group and filename using the same procedure.

One last check of the V$LOG table after the final log group, we’ll see everything looks in order:

At this point, we have modified all redo log sizes, reducing the frequency of log switches when Debezium executes the data dictionary build steps.

Oracle redo logs are used primarily for instance and media recovery because the data required for those operations gets recorded automatically. LogMiner cannot be used by default because Oracle does not provide any supplemental log data out of the box. Since Debezium relies on LogMiner, supplemental logging must be enabled at a minimum for Debezium to perform any change data capture for Oracle.

Two different strategies can be used to set supplemental logging:

For Debezium to interface with LogMiner and work with chained rows and various storage arrangements, database supplemental logging must be enabled at a minimum level. To enable this level, execute the following SQL in the current SQL*Plus terminal:

We will cover table-based supplemental logging in a later section when we discuss configuring the connector.

For the Debezium connector to capture change events, it must establish a JDBC connection to the database and execute a series of LogMiner APIs. A user account will require specific permissions to access these LogMiner APIs and gather data from the captured tables.

When using multi-tenant architecture, as is found with the Oracle container registry image, there are effectively two databases that we will have to work with, ORCLCDB (the container or root database) and ORCLPDB1 (the pluggable database). All captured tables will be created and maintained from within the PDB, but there will be moments when the connector will need to access the root database to read specific system tables.

Therefore in a multi-tenant architecture, we must first set up the two tablespaces that our user account will use. To create these tablespaces, execute the following SQL from within the SQL*Plus terminal:

Once the tablespaces exist, it is now time to create the user account itself. If you are using a multi-tenant environment, the user name must use the common-user prefix so that Oracle creates it in both the CDB root database and the PDB pluggable database; otherwise, the user name can be anything. Since we are working with a multi-tenant database installation with our container, we will create a user account called c##dbzuser.

The user account requires several permissions. At the time of this publication, the list of permissions included the following:

You can refer to the latest documentation to review whether the required grants may have changed. We have created the connector user we will use in the configuration and given the user all the necessary database permissions.