Transaction Log Settings and Requirements

Oracle GoldenGate relies on the redo logs to capture the data that it needs to replicate source transactions. The Oracle redo logs on the source system must be configured properly before you start Oracle GoldenGate processing.

This section addresses the following logging levels that apply to Oracle GoldenGate. The logging level that you use depends on Oracle GoldenGate features that you are using.

Note:

Redo volume is increased as the result of this required logging. You can wait until you are ready to start Oracle GoldenGate processing to enable the logging.

This table shows the Oracle GoldenGate use cases for the different logging properties.

Logging option	Command Name	What it does	Use case
Forced logging mode	`ALTER DATABASE FORCE LOGGING`;	Forces the logging of all transactions and loads.	Strongly recommended for all Oracle GoldenGate use cases. `FORCE LOGGING` overrides any table-level `NOLOGGING` settings.
Minimum database-level supplemental logging See Enable Subset Database Replication Logging.	`ALTER DATABASE ADD SUPPLEMENTAL LOG DATA`	Enables minimal supplemental logging to add row-chaining information to the redo log.	Required for all Oracle GoldenGate use cases
Schema-level supplemental logging	`ADD SCHEMATRANDATA`	Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. All of these keys together are known as the scheduling columns. This setting also ensures that any new object created in this schema will have the appropriate supplemental logging.	Enables the logging for all current and future tables in the schema. If the primary key, unique key, and foreign key columns are not identical at both source and target, use `ALLCOLS`.
Schema-level supplemental logging with unconditional logging for all supported columns. (See Enable Schema-level Supplemental Logging for non-supported column types.)	`ADD SCHEMATRANDATA` with `ALLCOLS` option	Enables unconditional supplemental logging of all of the columns in a table, for all of the tables in a schema. This setting also ensures that any new object created in this schema will have the appropriate supplemental logging.	Used for bidirectional and active-active configurations where all column values are checked, not just the changed columns, when attempting to perform an update or delete. This takes more resources though allows for the highest level of real-time data validation and thus conflict detection. This method should also be used if they are going to be using the `HANDLECOLLISIONS` parameter for initial loads.
Schema-level supplemental logging, minimal setting	`ADD SCHEMATRANDATA` with `NOSCHEDULINGCOLS` option	Enables unconditional supplemental logging of the primary key and all valid unique indexes of all tables in a schema. This setting also ensures that any new object created in this schema will have the appropriate supplemental logging.	Use only for non-integrated Replicat. This is the minimum required schema-level logging.
Table-level supplemental logging with built-in support for integrated Replicat See Enable Table-level Supplemental Logging	`ADD TRANDATA`	Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. All of these keys together are known as the scheduling columns.	Required for all Oracle GoldenGate use cases unless schema-level supplemental logging is used. If the primary key, unique key, and foreign key columns are not identical at both source and target, use `ALLCOLS`.
Table-level supplemental logging with unconditional logging for all supported columns. (See Enable Table-level Supplemental Logging for non-supported column types.)	`ADD TRANDATA` with `ALLCOLS` option	Enables unconditional supplemental logging of all of the columns of the table.	Used for bidirectional and active-active configurations where all column values are checked, not just the changed columns, when attempting to perform an update or delete. This takes more resources though allows for the highest level of real-time data validation and thus conflict detection. It can also be used when the source and target primary, unique, and foreign keys are not the same or are constantly changing between source and target.
Table-level supplemental logging, minimal setting	`ADD TRANDATA` with `NOSCHEDULINGCOLS` option	Enables unconditional supplemental logging of the primary key and all valid unique indexes of a table.	Use for non-integrated Replicat and non-parallel Replicat. This is the minimum required table-level logging.
Table-level supplemental logging for JSON Relational Duality Views and JSON Collection Tables.	`ADD TRANDATA` with `Duality View name` and `Collection Table name`	Enables supplemental logging for the specified Duality Views and Collection Tables.	See Enable Supplemental Logging for JSON Relational Duality Views and JSON Collection Tables for guidelines. Also see, Details of Support for JSON-Relational Duality Views and JSON Collection Tables to know more.

Enable Subset Database Replication Logging

Oracle strongly recommends putting the Oracle source database into forced logging mode. Forced logging mode forces the logging of all transactions and loads, overriding any user or storage settings to the contrary. This ensures that no source data in the Extract configuration gets missed.

There is a fine-granular database supplemental logging mode called Subset Database Replication available in LogMiner, which is the basic recommended mode for all Oracle GoldenGate and XStream clients. It replaces the previously used Minimum Supplemental Logging mode.

To know more, see ALTER DATABASE in the Oracle Database SQL Language Reference.

The subset database replication logging is enabled at CDB$ROOT (and all user-PDBs inherit it) currently.

Note:

Database-level primary key (PK) and unique index (UI) logging is only discouraged if you are replicating a subset of tables. You can use it with Live Standby, or if Oracle GoldenGate is going to replicate all tables, like to reduce the downtime for a migration or upgrade.

Perform the following steps to verify and enable, if necessary, subset database replication logging and forced logging.

Log in to SQL*Plus as a user with ALTER SYSTEM privilege.
Issue the following command to determine whether the database is in supplemental logging mode and in forced logging mode. If the result is YES for both queries, the database meets the Oracle GoldenGate requirement.
```
SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;
```

If the result is NO for either or both properties, continue with these steps to enable them as needed:

ALTER PLUGGABLE DATABASE pdbname ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;;
ALTER DATABASE FORCE LOGGING;

Issue the following command to verify that these properties are now enabled.
```
SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;
```
The output of the query must be YES for both properties.
Switch the log files.
```
ALTER SYSTEM SWITCH LOGFILE;
```

Enable Schema-level Supplemental Logging

Oracle GoldenGate supports schema-level supplemental logging. Schema-level logging is required for an Oracle source and target databases when using the Oracle GoldenGate DDL replication feature, or on the source database if DDL can be performed (or executed) on the tables being replicated. In all other use cases, it is optional, but then you must use table-level logging instead (see Enable Table-level Supplemental Logging).

By default, schema-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. Options enable you to alter the logging as needed.

Note:

Oracle strongly recommends using schema-level logging rather than table-level logging, because it ensures that any new tables added to a schema are captured if they satisfy wildcard specifications. This method is also recommended because any changes to key columns are automatically reflected in the supplemental log data too. For example, if a key changes, there is no need to issue ADD TRANDATA.

Perform the following steps on the source system to enable schema-level supplemental logging.

Start the command line on the source system.
Issue the DBLOGIN command with the alias of a user in the credential store who has privilege to enable schema-level supplemental logging.
```
DBLOGIN USERIDALIAS alias
```
See USERIDALIAS in Parameters and Functions Reference for Oracle GoldenGate for more information about USERIDALIAS and additional options.
When using ADD SCHEMATRANDATA or ADD TRANDATA on a multitenant database, you can either log directly into the PDB and perform the command. Alternately, if you are logging in at the root level (using a C## user), then you must include the PDB. Issue the ADD SCHEMATRANDATA command for each schema for which you want to capture data changes with Oracle GoldenGate.
```
ADD SCHEMATRANDATA pdb.schema [ALLCOLS | NOSCHEDULINGCOLS]
```
Where:
- Without options, ADD SCHEMATRANDATA schema enables the unconditional supplemental logging on the source system of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of all current and future tables in the given schema. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The default is optional to support nonintegrated Replicat but is required to support integrated Replicat because primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies.
- ALLCOLS can be used to enable the unconditional supplemental logging of all of the columns of a table and applies to all current and future tables in the given schema. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.)
- NOSCHEDULINGCOLS logs only the values of the primary key and all valid unique indexes for existing tables in the schema and new tables added later. This is the minimal required level of schema-level logging and is valid only for Replicat in nonintegrated mode.
In the following example, the command enables default supplemental logging for the hr schema.
```
ADD SCHEMATRANDATA pdbeast.hr ALLCOLS
```
In the following example, the command enables the supplemental logging only for the primary key and valid unique indexes for the HR schema.
```
ADD SCHEMATRANDATA pdbeast.hr NOSCHEDULINGCOLS
```

Enable Table-level Supplemental Logging

Enable table-level supplemental logging on the source system in the following cases:

To enable the required level of logging when not using schema-level logging (see Enable Schema-level Supplemental Logging). Either schema-level or table-level logging must be used. By default, table-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. Options enable you to alter the logging as needed.
To prevent the logging of the primary key for any given table.
To log non-key column values at the table level to support specific Oracle GoldenGate features, such as filtering and conflict detection and resolution logic.
If the key columns change on a table that only has table-level supplemental logging, you must perform ADD TRANDATA on the table prior to allowing any DML activity on the table.
To enable supplemental logging for JSON Relational Duality Views (JSON DVs) and JSON Collection Tables, you need to follow the guidelines in Enable Supplemental Logging for JSON Relational Duality Views and JSON Collection Tables.

Perform the following steps on the source system to enable table-level supplemental logging or use the optional features of the command.

Run the command line on the source system.
Issue the DBLOGIN command using the alias of a user in the credential store who has privilege to enable table-level supplemental logging.
```
DBLOGIN USERIDALIAS alias
```
See USERIDALIAS in Parameters and Functions Reference for Oracle GoldenGatefor more information about DBLOGIN and additional options.
Issue the ADD TRANDATA command.
```
ADD TRANDATA [PDB.]schema.table [, COLS (columns)] [, NOKEY] [, ALLCOLS | NOSCHEDULINGCOLS]
```
Where:
- PDB is the name of the root container or pluggable database if the table is in a multitenant container database.
- schema is the source schema that contains the table.
- table is the name of the table. See Specifying Object Names in Oracle GoldenGate Input for instructions for specifying object names.
- ADD TRANDATA without other options automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of the table. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The default is optional to support nonintegrated Replicat (see also NOSCHEDULINGCOLS) but is required to support integrated Replicat because primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies.
- ALLCOLS enables the unconditional supplemental logging of all of the columns of the table. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.)
- NOSCHEDULINGCOLS is valid for Replicat in nonintegrated mode only. It issues an ALTER TABLE command with an ADD SUPPLEMENTAL LOG DATA ALWAYS clause that is appropriate for the type of unique constraint that is defined for the table, or all columns in the absence of a unique constraint. This command satisfies the basic table-level logging requirements of Oracle GoldenGate when schema-level logging will not be used. See Ensuring Row Uniqueness in Source and Target Table for how Oracle GoldenGate selects a key or index.
- COLS columns logs non-key columns that are required for a KEYCOLS clause or for filtering and manipulation. The parentheses are required. These columns will be logged in addition to the primary key unless the NOKEY option is also present.
- NOKEY prevents the logging of the primary key or unique key. Requires a KEYCOLS clause in the TABLE and MAP parameters and a COLS clause in the ADD TRANDATA command to log the alternate KEYCOLS columns.
If using ADD TRANDATA with the COLS option, create a unique index for those columns on the target to optimize row retrieval. If you are logging those columns as a substitute key for a KEYCOLS clause, make a note to add the KEYCOLS clause to the TABLE and MAP statements when you configure the Oracle GoldenGate processes.

Enable Supplemental Logging for JSON Relational Duality Views and JSON Collection Tables

Before replicating JSON Relational Duality Views and JSON Collection Tables, you need to set up supplemental logging in Oracle GoldenGate. By default, supplemental logging is not enabled for JSON Relational Duality Views and JSON Collection Tables. You can use the following guidelines when enabling supplemental logging for Duality Views and Collection Tables:

Enable Logical Replication: Enabling logical replication (using ADD TRANDATA) is mandatory for capturing changes made to Duality Views and Collection Tables. This process enables the supplemental logging needed to track changes at the JSON document level.

See ADD TRANDATA for details.

On the Oracle Database 23ai side, a clause has been added in the CREATE/ALTER DDL for JSON Relational Duality Views and JSON Collection Tables. This clause enables supplemental logging at the view level. This logging is in addition to any existing logging on the base tables.

For details, see the logical_replication_clause section in CREATE TABLE/CREATE JSON RELATIONAL DUALITY VIEW and ALTER TABLE/ALTER JSON RELATIONAL DUALITY VIEWS commands in Oracle Database SQL Language Reference.
DML changes for JSON Duality Views and JSON Collection Tables: For INSERT and UPDATE operations, the full JSON document and the _id key attribute is supplementally logged. For DELETE operations, only the key _id attribute is logged.
Transaction Commit Logging for JSON Relational Duality Views: Supplemental logging for Duality Views occurs at commit time. Only the Net changes are logged.

For example, if you INSERT a record and UPDATE this record twice in a transaction conatining a JSON Duality View, then only one logical change will exist, containing an INSERT with the updated record.
JSON Duality Views to JSON Duality Views Replication using Underlying Tables: Replication changes at the underlying relational table level of Duality Views are mostly efficient. A Duality View that is on top of the underlying relational table automatically reflects those changes as it is a view. In this case, no additional supplemental logging on the Duality Views is needed.

For steps to set up table-level supplemental logging, see Enable Table-level Supplemental Logging.

Remove Table-level Supplemental Logging

If a table is no longer required to be captured by Oracle GoldenGate and the TABLE parameter for the table has been removed from the Extract parameter file, or TABLEEXCLUDE is used to exclude the table from a wildcard statement, then supplemental logging can be removed from the table.

Note:

If the Extract resolves a table that does not have supplemental logging enabled, it will abend depending on the type of DML operation.

Using DELETE TRANDATA to remove supplemental logging sets the Replicat Identity level of the table to NOTHING. Supplemental logging can be disabled using the Microservices Architecture web interface from the Administration Service, Configuration page, under the Credential created for a source database, or can be issued with the DELETE TRANDATA command.

The following is the syntax for issuing DELETE TRANDATA.

DBLOGIN USERIDALIAS alias_name
DELETE TRANDATA schema.tablename

To check the level of supplemental logging:

INFO TRANDATA schema.tablename