Enabling TRANDATA for Oracle GoldenGate

One of the prerequisites for Oracle GoldenGate replication is to enable TRANDATA for the tables or whole schema. For other prerequisites, installation and complete process see here.

Use ADD TRANDATA to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records. This can be done by either ggsci or sqlplus command prompts.

Examples of both are shown below.

ggsci 1> dblogin userid ggs_dba Password ggs_dba
ggsci 2> add trandata SCHEMA.TABLE_NAME

or from database as
To specify an unconditional supplemental log group for PRIMARY KEY column(s):

SQL > ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

To specify an unconditional supplemental log group that includes ALL TABLE columns:

SQL > ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To specify an unconditional supplemental log group that includes SELECTED columns:

SQL> ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG GROUP t1_g1 (C1,C2) ALWAYS;

See this oracle documentation link for more details on why and how supplemental logging has to be enabled.

One can also use ADD SCHEMATRANDATA to enable supplemental logging for the whole schema and use exclusion parameter to exclude objects that are not required to be replicated. More about ADD SCHEMATRANDATA can be seen in this oracle documentation link

For our demo purpose, I’ll be replicating OGG_TEST table under SDAS user to be replicated

GGSCI (source) 3> add trandata sdas.ogg_test

2015-08-06 10:08:53  WARNING OGG-00869  No unique key is defined for table OGG_TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SDAS.OGG_TEST.

This is because we don’t have a unique or primary key defined on the table. I’ll now alter the table to drop the supplemental log group, create primary key on a field and then enable supplemental log group. Then let’s see how it behaves.

SQL> alter table sdas.ogg_test drop SUPPLEMENTAL LOG GROUP GGS_OGG_TEST_39719;

Table altered.

SQL> alter table sdas.ogg_test add constraint ogg_test_pk primary key (emp_id) using index;

Table altered.

Now try adding trandata..

GGSCI (source) 5> add trandata sdas.ogg_test

Logging of supplemental redo data enabled for table SDAS.OGG_TEST.

Now supplemental log data enabled using the unique key.

Happy OGG set up!!