Enabling supplemental logging in the source database

Oracle GoldenGate replication can be used to continuously replicate the changes from the source database to the target database. GoldenGate mines the redo information generated in the source database to extract the changes. In order to update the correct rows in the target database, Oracle needs sufficient information to be able to identify them uniquely. Since it relies on the information extracted from the redo buffers, it requires extra information columns to be logged into the redo records generated in the source database. This is done by enabling supplemental logging in the source database. This recipe explains how to enable supplemental logging in the source database.

Getting ready

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

How to do it…

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:

1. Enable database supplemental logging through sqlplus as follows:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   

2. Switch a database LOGFILE to bring the changes into effect:

   ALTER DATABASE SWITCH LOGFILE;
   

3. From the GoldenGate Home, log in to GGSCI:

   ./ggsci
   

4. Log in to the source database from ggsci using a user which has privileges to alter the source schema tables as shown in the following command:

   GGSCI> DBLOGIN USERID <USER> PASSWORD <PW>
   

5. Enable supplemental logging at the table level as follows:

   GGSCI> ADD TRANDATA <SCHEMA>.<TABLE_NAME>
   

6. Repeat step 5 for all the tables that you want to replicate using GoldenGate.

How it works…

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:

1. Primary key

2. First unique key alphanumerically with no nullable columns

3. First unique key alphanumerically with nullable columns

4. All columns

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.

Tip

You can enable supplemental logging on all tables of a schema using the following single command:

GGSCI> ADD TRANDATA <SCHEMA>.*

If possible, do create a primary key in each source and target table that is part of the replication. The pseudo key consisting of all columns, created by GoldenGate, can be quite inefficient.

There's more…

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.

See also

• For information about how to replicate changes to a target database and maintain an audit record, refer to the recipe Mapping the changes to a target table and storing the transaction history in a history table in Chapter 4, Mapping and Manipulating Data