Unable to Add Trandata in Oracle Golden Gate (OGG)

In the process of data replication using Oracle GoldenGate (OGG ) the first step and important is assuring that all the needed information and changes have been written to the database redo log files. By default, the database does not write all the information that might be needed by the OGG replication process. That’s why some additional supplemental logging is needed. Supplemental logging is being added by the ADD TRANDATA command.

Step 1. Login to OGG and Add Trandata: You can verify no logging is enabled in the table using the info trandata command. But when we try to add supplement log using add trandata command we received a message “Logging of supplemental redo log data is already enabled for table“.

E:\ggate\ucasii>ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.19 18124625 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140209.2142
Windows x64 (optimized), Oracle 11g on Feb  9 2014 23:26:40

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (test-machine01) 2> dblogin userid ggs password ggs
Successfully logged into database.

GGSCI (test-machine01) 13> info trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo log data is disabled for table GGS_TEST.SA_MENU_ROTATION.

GGSCI (test-machine01) 14>

GGSCI (test-machine01) 14> add trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo log data is already enabled for table GGS_TEST.SA_MENU_ROTATION.

GGSCI (test-machine01) 15>

Step 2. Try to delete Trandata and then Add: You can delete logging using the delete trandata command. After deleting Trandata still issues remain the same.

GGSCI (test-machine01) 15> delete trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo log data disabled for table GGS_TEST.SA_MENU_ROTATION.

GGSCI (test-machine01) 16> add trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo data already enabled for table GGS_TEST.SA_MENU_ROTATION.

GGSCI (test-machine01) 17>

GGSCI (test-machine01) 17> info trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo log data is disabled for table GGS_TEST.SA_MENU_ROTATION.

GGSCI (test-machine01) 18>

Step 3. Verify-in Data Dictionary: You can query DBA dictionary table DBA_LOG_GROUPS & DBA_LOG_GROUP_COLUMNS and verify any log group already exists for the same table.

SQL>
SQL> select * from DBA_LOG_GROUPS where owner='GGS_TEST' and table_name='SA_MENU_ROTATION';

OWNER                          LOG_GROUP_NAME                 TABLE_NAME                     LOG_GROUP_TYPE               ALWAYS      GENERATED
------------------------------ ------------------------------ ------------------------------ ---------------------------- ----------- --------------
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               USER LOG GROUP               ALWAYS      USER NAME

SQL> select * from DBA_LOG_GROUP_COLUMNS where owner='GGS_TEST' and table_name='SA_MENU_ROTATION';

OWNER                          LOG_GROUP_NAME                 TABLE_NAME                     COLUMN_NAME                                POSITION LOGGIN
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---------- ------
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               ROTATION                                          6 LOG
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               FROM_DATE                                         7 LOG
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               MENU_CODE                                         1 LOG
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               ISSU_NO                                   2 LOG
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               CAT_CODE                                          3 LOG
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               CUSTOMER_CODE                                     4 LOG
GGS_TEST                       GGS_90108                      SA_MENU_ROTATION               SEGMENT_CODE                                      5 LOG

Step 4. Delete log group: Use command drop supplemental log data to drop the already existing log group.

ALTER TABLE <USER>.<TABLE_NAME> DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE <USER>.<TABLE_NAME> DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE <USER>.<TABLE_NAME> DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> alter table GGS_TEST.SA_MENU_ROTATION  drop supplemental log data (ALL) columns;
Table altered.

Step 5. Add Trandata: After deleting log group Trandata added successfully and can be verified using info trandata command.

GGSCI (test-machine01) 25> add trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo data enabled for table GGS_TEST.SA_MENU_ROTATION.

GGSCI (test-machine01) 26> info trandata ggs_test.SA_MENU_ROTATION

Logging of supplemental redo log data is enabled for table GGS_TEST.SA_MENU_ROTATION.

Columns supplementally logged for table GGS_TEST.SA_MENU_ROTATION: CAT_CODE, CUSTOMER_CODE, ROTATION, FROM_DATE, MENU_CODE, SEGMENT_CODE, ISSU_NO.

GGSCI (test-machine01) 27>

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.