Forum Stats

  • 3,769,837 Users
  • 2,253,027 Discussions
  • 7,875,220 Comments

Discussions

List of not enabled supplemental log

AATP
AATP Member Posts: 324 Blue Ribbon
edited Mar 15, 2021 1:13PM in GoldenGate

Hi Experts ,

We have enabled DML replication between two oracle databases , for that we have enabled the supplemental log data for all columns with below command ,

alter table emp add supplmental log data(all) columns ;

There are around 4000 tables , We have executed this above command for each table .But as part of new requirement team has created 5 more new tables , at the time of creation the supplemental log is not enabled , We can able to get the list of tables which are already enabled from DBA_LOG_GROUPS view , is there any option can enable this automatically like DDL parameter add trandata option for DML setup also ?



Please advise


Thanks ,

Arun .

Comments

  • Sreesha Nair-Oracle
    Sreesha Nair-Oracle Member Posts: 77 Employee

    Hi Arun,

    Please use ADD SCHEMTRANDATA parameter to enable schema-level supplemental logging for a table. ADD SCHEMATRANDATA acts on all of the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification.

    ADD SCHEMATRANDATA schema ALLCOLS

    ALLCOLS enables the unconditional supplemental logging of all supported key and non-key columns for all current and future tables in the given schema.

    Regards,

    Sreesha

    ORASCN
  • ORASCN
    ORASCN Member Posts: 1,825 Gold Trophy

    As Sreesha mentioned, you can go with SCHEMATRANDATA.

    ADD SCHEMATRANDATA allcols

    This has some benefits.

    1. Don't need to recreate the supplemental log (re-add trandata) if any DDL changes in the table like, column addition, column deletion, modifying the columns attribute etc.,
    2. Don't need to add trandata when a new table is added to the schema

    But, there is a con also in this, when you enable schematrandata with ALLCOLS option. If you have 5000 tables and you need to capture only 4000 tables, then unnecessarily, trandata will be added of non-replicated 1000 tables. This will be a little overhead to the redo logs.

    Unless, you use Bi-Directional Replication or History Transaction maintenance, you don't need to use ALLCOLS option. For a simple Uni-directional replication, ALLCOLS is not required.


    Regards,

    Veera