Enabling Minimum Database-level Supplemental 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.

In addition, minimal supplemental logging, a database-level option, is required for an Oracle source database when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations.

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, minimal supplemental 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:
```
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> 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.
```
SQL> ALTER SYSTEM SWITCH LOGFILE;
```