Enabling 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;
```

To perform dictionary validation, run the following command:

SELECT con_id, MINIMAL, SUBSET_REP, PRIMARY_KEY, UNIQUE_INDEX, FOREIGN_KEY, ALL_COLUMN FROM CDB_SUPPLEMENTAL_LOGGING;

The output of this query should be YES.

SUBSET_REP = YES

For the following query:

SELECT NAME, LOG_MODE, FORCE_LOGGING, SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI,
SUPPLEMENTAL_LOG_DATA_FK FK, 
SUPPLEMENTAL_LOG_DATA_ALL,
SUPPLEMENTAL_LOG_DATA_SR FROM V$DATABASE;

For the query for SUPPLEMENTAL_LOG_DATA_SR the output should be YES and for SUPPLEMENTAL_LOG_DATA_MIN the output should be IMPLICIT.

To switch from earlier minimum supplemental logging to the new subset supplemental logging:

Drop the earlier higher levels on CDB$ROOT.

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; 
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Add only subset database replication mode:
```
ALTER PLUGGABLE DATABASE pdbname ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
```
Ensure that all PDBs inherit this subset database replication mode.