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.
