Looking for something? Try here..

Sunday, August 23, 2015

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!!

2 comments:

  1. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Golden Gate, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on in Oracle Golden Gate. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Nitesh Kumar
    MaxMunus
    E-mail: nitesh@maxmunus.com
    Skype id: nitesh_maxmunus
    Ph:(+91) 8553912023
    http://www.maxmunus.com/


    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Golden Gate.kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Oracle Golden Gate. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

    For Free Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete