We must have a list of the tables that we want to replicate between two environments.

Oracle GoldenGate requires supplemental logging to be enabled at the database level and table level. Use the following steps to enable the required supplemental logging:

Supplemental logging enables the database to add extra columns in the redo data that is required by GoldenGate to correctly identify the rows in the target database. We must enable database-level minimum supplemental logging before we can enable it at the table level. When we enable it at the table level, a supplemental log group is created for the table that consists of the columns on which supplemental logging is enabled. The columns which form a part of this group are decided based on the key constraints present on the table. These columns are decided based on the following priority order:

GoldenGate only considers unique keys which don't have any virtual columns, any user-defined types, or any function-based columns. We can also manually specify which columns we want to be a part of the supplemental log group.

GGSCI> ADD TRANDATA <SCHEMA>.*

There are two ways to enable supplemental logging. The first method is to enable it using GGSCI, using the ADD TRANDATA command. The second method is to use sqlplus and run the ALTER TABLE ADD SUPPLEMENTAL LOG DATA command. The latter method is more flexible and allows a person to specify the name of the supplemental log group. However, when you use Oracle GoldenGate to add supplemental logging it creates supplemental log group names using the format, GGS_<TABLE_NAME>_<OBJECT_NUMBER>. If the overall supplemental log group name is longer than 30 characters, GoldenGate truncates the table name as required. Oracle support recommends that we use the first method for enabling supplemental logging for objects to be replicated using Oracle GoldenGate. The GGS_* supplemental log group format enables GoldenGate to quickly identify the supplemental log groups in the database.

If you are planning to use GoldenGate to capture all transactions in the source database and convert them into INSERT for the target database, for example, for reporting/auditing purposes, you'll need to enable supplemental logging on all columns of the source database tables.