Thinking Out Loud

January 30, 2012

My Bi-directional Replication Using GoldenGate Adventure, Ep2

Filed under: 11g,GoldenGate,oracle — mdinh @ 6:25 am

Create GLOBALS file in GG_HOME:

> cat GLOBALS
 -- Specifies the name of the schema that contains the database objects that support DDL
 -- synchronization for Oracle
 GGSCHEMA gguser
-- Specifies a default checkpoint table
 CHECKPOINTTABLE ggs_checkpoint

MACRO:

According to the Reference Guide (11g Release 1 Patch Set 1 (11.1.1.1)), MACRO are valid for Extract and Replicat.

The following SYNTAX must be used in the order shown:

MACRO <macrochar><macro name>
PARAMS (<macrochar><paramname> [, …])
BEGIN
<macro body>
END;

My MACRO example:

/u01/app/ggs/dirmac
 > cat macrolib.mac
 MACRO #dbconnect
 BEGIN
 userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
 END;
MACRO #dbenv
 BEGIN
 SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
 SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.3/dbhome_1")
 END;
MACRO #ggenv
 BEGIN
 STATOPTIONS RESETREPORTSTATS
 REPORT AT 00:01
 REPORTROLLOVER AT 00:01
 REPORTCOUNT EVERY 60 MINUTES, RATE
 DISCARDROLLOVER AT 00:01
 END;

Enable table-level supplemental logging

This can be done with ADD TRANDATA or ADD SCHEMATRANDATA (additional requirements must be met since this is a New Feature)

ADD SCHEMATRANDATA:

SCHEMAUSER@oracle> create table t as select * from dba_objects;
GGSCI> ADD SCHEMATRANDATA schemauser

In the above example, I create table without any PK and added enabled supplemental logging for all tables in the schema, existing and new, using GGSCI.

The caveat for using ADD SCHEMATRANDATA is that the supplemental logging information will NOT be in USER_LOG_GROUPS and USER_LOG_GROUP_COLUMNS.

Instead, the function logmnr$always_suplog_columns is used to determine supplemental logging.

Verify supplemental logging:

SCHEMAUSER@oracle> select * from table(logmnr$always_suplog_columns('SCHEMAUSER','T'));

OWNER                          TABLE_NAME                     COLUMN_NAME                        INTCOL     SEGCOL    USERCOL
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------
SCHEMAUSER                     T                              OWNER                                   1          1          1
SCHEMAUSER                     T                              OBJECT_TYPE                             6          6          6
SCHEMAUSER                     T                              TEMPORARY                              11         11         11
SCHEMAUSER                     T                              SECONDARY                              13         13         13
SCHEMAUSER                     T                              OBJECT_NAME                             2          2          2
SCHEMAUSER                     T                              NAMESPACE                              14         14         14
SCHEMAUSER                     T                              OBJECT_ID                               4          4          4
SCHEMAUSER                     T                              DATA_OBJECT_ID                          5          5          5
SCHEMAUSER                     T                              LAST_DDL_TIME                           8          8          8
SCHEMAUSER                     T                              SUBOBJECT_NAME                          3          3          3
SCHEMAUSER                     T                              CREATED                                 7          7          7
SCHEMAUSER                     T                              TIMESTAMP                               9          9          9
SCHEMAUSER                     T                              STATUS                                 10         10         10
SCHEMAUSER                     T                              GENERATED                              12         12         12
SCHEMAUSER                     T                              EDITION_NAME                           15         15         15

Noticed ALL the columns are logged because there is no PK for the table. So let’s add one and query the information again.

Add PK and Verify supplemental logging:

SCHEMAUSER@oracle> alter table t add constraint tpk primary key(object_id);
SCHEMAUSER@oracle> select * from table(logmnr$always_suplog_columns('SCHEMAUSER','T'));

OWNER                          TABLE_NAME                     COLUMN_NAME                        INTCOL     SEGCOL    USERCOL
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------
SCHEMAUSER                     T                              OBJECT_ID                               4          4          4

For the project I am working on, I will be using SCHEMATRANDATA.

If the application creates a lot of staging or temporary tables without any PK, then SCHEMATRANDATA will generate a lot of REDO.

Please reference the section Additional considerations for using ADD SCHEMATRANDATA in the Reference Guide for more details.

ADD TRANDATA:

MDINH@oracle> create table t as select * from dba_objects;
GGSCI> ADD TRANDATA mdinh.*
WARNING OGG-00869 No unique key is defined for table T. All viable columns will be used to represent the key, but may not guarantee uniquene.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table MDINH.T.

Verify supplemental logging:

MDINH@oracle> select * from USER_LOG_GROUPS;
OWNER      LOG_GROUP_NAME       TABLE_NAME LOG_GROUP_TYPE  ALWAYS      GENERATED
---------- -------------------- ---------- --------------- ----------- --------------
MDINH      GGS_T_13904          T          USER LOG GROUP  ALWAYS      USER NAME
MDINH@oracle> select * from USER_LOG_GROUP_COLUMNS;
OWNER      LOG_GROUP_NAME       TABLE_NAME COLUMN_NAME            POSITION LOGGIN
---------- -------------------- ---------- -------------------- ---------- ------
MDINH      GGS_T_13904          T          OWNER                         1 LOG
MDINH      GGS_T_13904          T          OBJECT_NAME                   2 LOG
MDINH      GGS_T_13904          T          SUBOBJECT_NAME                3 LOG
MDINH      GGS_T_13904          T          OBJECT_ID                     4 LOG
MDINH      GGS_T_13904          T          DATA_OBJECT_ID                5 LOG
MDINH      GGS_T_13904          T          OBJECT_TYPE                   6 LOG
MDINH      GGS_T_13904          T          CREATED                       7 LOG
MDINH      GGS_T_13904          T          LAST_DDL_TIME                 8 LOG
MDINH      GGS_T_13904          T          TIMESTAMP                     9 LOG
MDINH      GGS_T_13904          T          STATUS                       10 LOG
MDINH      GGS_T_13904          T          TEMPORARY                    11 LOG
MDINH      GGS_T_13904          T          GENERATED                    12 LOG
MDINH      GGS_T_13904          T          SECONDARY                    13 LOG
MDINH      GGS_T_13904          T          NAMESPACE                    14 LOG
MDINH      GGS_T_13904          T          EDITION_NAME                 15 LOG
15 rows selected.

Add PK and Verify supplemental logging:

MDINH@oracle> alter table t add constraint tpk primary key(object_id);
Table altered.
MDINH@oracle> select * from USER_LOG_GROUP_COLUMNS;
OWNER      LOG_GROUP_NAME       TABLE_NAME COLUMN_NAME            POSITION LOGGIN
---------- -------------------- ---------- -------------------- ---------- ------
MDINH      GGS_T_13904          T          OWNER                         1 LOG
MDINH      GGS_T_13904          T          OBJECT_NAME                   2 LOG
MDINH      GGS_T_13904          T          SUBOBJECT_NAME                3 LOG
MDINH      GGS_T_13904          T          OBJECT_ID                     4 LOG
MDINH      GGS_T_13904          T          DATA_OBJECT_ID                5 LOG
MDINH      GGS_T_13904          T          OBJECT_TYPE                   6 LOG
MDINH      GGS_T_13904          T          CREATED                       7 LOG
MDINH      GGS_T_13904          T          LAST_DDL_TIME                 8 LOG
MDINH      GGS_T_13904          T          TIMESTAMP                     9 LOG
MDINH      GGS_T_13904          T          STATUS                       10 LOG
MDINH      GGS_T_13904          T          TEMPORARY                    11 LOG
MDINH      GGS_T_13904          T          GENERATED                    12 LOG
MDINH      GGS_T_13904          T          SECONDARY                    13 LOG
MDINH      GGS_T_13904          T          NAMESPACE                    14 LOG
MDINH      GGS_T_13904          T          EDITION_NAME                 15 LOG
15 rows selected.

Notice ALL columns logging is still enabled. In order to resolve this, DELETE TRANDATA and ADD TRANDATA.

GGSCI> info trandata mdinh.*
Logging of supplemental redo log data is enabled for table MDINH.T
GGSCI> delete trandata mdinh.t
Logging of supplemental redo log data disabled for table MDINH.T.
GGSCI> add trandata mdinh.*
Logging of supplemental redo data enabled for table MDINH.T.

MDINH@xgoldengate01> select * from USER_LOG_GROUP_COLUMNS;
OWNER      LOG_GROUP_NAME       TABLE_NAME COLUMN_NAME            POSITION LOGGIN
---------- -------------------- ---------- -------------------- ---------- ------
MDINH      GGS_T_13904          T          OBJECT_ID                     1 LOG

To  avoid having to DELETE and ADD TRANDATA, the following option can be added to EXTRACT parameter

DDLOPTIONS ADDTRANDATA, REPORT

When to use ADD TRANDATA for an Oracle source database

Use ADD TRANDATA only if you are not using the Oracle GoldenGate DDL replication feature.

If you are using the Oracle GoldenGate DDL replication feature, use the ADD SCHEMATRANDATA command to log the required supplemental data. It is possible to use ADD TRANDATA when DDL support is enabled, but only if you can guarantee one of the following:

  • You can stop DML activity on any and all tables before users or applications perform DDL on them.
  • You cannot stop DML activity before the DDL occurs, but you can guarantee that:
    • There is no possibility that users or applications will issue DDL that adds new tables whose names satisfy an explicit or wildcarded specification in a TABLE or MAP statement.
    • There is no possibility that users or applications will issue DDL that changes the key definitions of any tables that are already in the Oracle GoldenGate configuration.

ADD SCHEMATRANDATA ensures replication continuity should DML ever occur on an object for  which DDL has just been performed. For more information, see “ADD SCHEMATRANDATA” on page 81. You can use ADD TRANDATA even when using ADD SCHEMATRANDATA if you need to use the COLS option to log any non-key columns, such as those needed for FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters.

When to use ADD SCHEMATRANDATA

ADD SCHEMATRANDATA should be used when DDL replication is active and DML is concurrent with DDL that creates new tables or alters key columns. It best handles scenarios where DML can be applied to objects very shortly after DDL is issued on them. ADD SCHEMATRANDATA causes the appropriate key values to be logged in the redo log atomically with each DDL operation, thus ensuring metadata continuity for the DML when it is captured from the log, despite any lag in Extract processing.

Retrieved from http://docs.oracle.com/cd/E22355_01/doc.11111/e21512.pdf on January 29,  2012

REFERENCE: >>Oracle GoldenGate Windows and UNIX Reference Guide

Advertisements

11 Comments »

  1. Hi I am using a 10gR2 setup on Linux (using em’s) and then a 11g. I have a goldengate setup. Everything seems to be working fine except when we add a table at the source it doesnt get replicated on the target?

    my extract param

    –extract ext1 param file–
    extract ext1
    TRANLOGOPTIONS LOGRETENTION DISABLED
    –connection to database–
    userid XXXXXXXX, password XXXXXXXX
    –hostname and port for trail–
    EXTTRAIL /u02/gg/dirdat/ea
    discardfile /u02/gg/discard/ext1_discard.txt,purge
    DDL INCLUDE ALL
    table SCOTT.*;
    table HR.*
    package SCOTT.*;

    my replicat param is

    replicat rep1
    –source and target definitions
    ASSUMETARGETDEFS
    –target database login —
    userid ggate, password manager16
    –file for dicarded transaction —
    discardfile /mnt/gg/discard/rep1_discard.txt, append, megabytes 10
    –ddl support
    DDL INCLUDE ALL
    DDLERROR DEFAULT IGNORE RETRYOP
    –Specify table mapping —
    map SCOTT.*, target SCOTT.*;

    Comment by aj gulati — February 15, 2012 @ 9:04 pm | Reply

    • Add to the extract
      DDLOPTIONS ADDTRANDATA, REPORT

      Comment by mdinh — February 15, 2012 @ 11:21 pm | Reply

  2. Here is the most recent replicat, we tried adding that line and it still hasnt worked. I am not sure why a drop would work and a create wont. Thanks so much for your help

    replicat rep1
    –source and target definitions
    ASSUMETARGETDEFS

    –HANDLECOLLISIONS

    –target database login —
    userid xxxxx, password xxxxx
    –file for dicarded transaction —
    discardfile /mnt/gg/discard/rep1_discard.txt, append, megabytes 10
    –ddl support
    DDL INCLUDE ALL
    DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3
    –Specify table mapping —

    map HR., target HR. ;
    map SCOTT., target SCOTT.;

    And the extract

    extract ext1 param file
    extract ext1
    TRANLOGOPTIONS LOGRETENTION DISABLED
    connection to database
    userid xxxxx, password xxxxx
    hostname and port for trail
    EXTTRAIL /u02/gg/dirdat/ea
    discardfile /u02/gg/discard/ext1_discard.txt,purge
    DDL INCLUDE ALL
    DDLOPTIONS ADDTRANDATA, REPORT
    table SCOTT.*;
    table HR.*;

    Comment by aj gulati — February 16, 2012 @ 5:02 pm | Reply

  3. So we figured out the problem. Its moving the table without a primary key. But not the one with a primary key ? any suggestions

    Thanks

    Comment by aj gulati — February 16, 2012 @ 5:09 pm | Reply

    • I would try INFO TRANDATA SCOTT.* to make sure supplemental logging is enabled for all the tables.

      Since you originally did not have DDLOPTIONS ADDTRANDATA, REPORT, then each table would have to have supplemental logging added by using ADD TRANDATA SCOTT.table_name.

      Not sure if it makes a difference, but in your replicat try

      map HR.*, target HR.*;
      map SCOTT.*, target SCOTT.*;

      HTH

      Comment by mdinh — February 21, 2012 @ 4:35 am | Reply

  4. Hey its a good blog with very good example…What about the tables that are created after issuing the add trandata? will their columns will be logged or do we have delete the trandata and add it again??

    Thanks in advance

    Comment by Gowtham — October 11, 2012 @ 8:54 pm | Reply

    • Here is my test case for having BOTH ADD SCHEMATRANDATA and ADD TRANDATA with DDLOPTIONS ADDTRANDATA, REPORT

      CREATE TABLE t1(a int CONSTRAINT t_pk PRIMARY KEY);
      alter table t1 add(b int);
      CREATE TABLE t2(a int);
      alter table t2 add(b int);

      GGSCI> info SCHEMATRANDATA scott

      2012-10-11 19:19:29 INFO OGG-01785 Schema level supplemental logging is enabled on schema scott.

      GGSCI> info trandata scott.*

      Logging of supplemental redo log data is enabled for table scott.T1.

      Columns supplementally logged for table scott.T1: A. <== only PK column, just happens to be A

      Logging of supplemental redo log data is enabled for table scott.T2.

      Columns supplementally logged for table scott.T2: ALL. <== ALL columns

      select TABLE_NAME,COLUMN_NAME from table(logmnr$always_suplog_columns(USER,'T1'));

      TABLE_NAME COLUMN_NAME
      —————————— ——————————
      T1 A

      SQL> select TABLE_NAME,COLUMN_NAME from table(logmnr$always_suplog_columns(USER,'T2'));

      TABLE_NAME COLUMN_NAME
      —————————— ——————————
      T2 A
      T2 B

      select * from USER_LOG_GROUPS;
      ==============================
      OWNER : scott
      LOG_GROUP_NAME : SYS_C004244
      TABLE_NAME : T2
      LOG_GROUP_TYPE : ALL COLUMN LOGGING <== Note: ALL
      ALWAYS : ALWAYS
      GENERATED : GENERATED NAME
      ==============================
      OWNER : scott
      LOG_GROUP_NAME : GGS_21724
      TABLE_NAME : T1
      LOG_GROUP_TYPE : USER LOG GROUP
      ALWAYS : ALWAYS
      GENERATED : USER NAME
      ==============================

      select * from USER_LOG_GROUP_COLUMNS;
      ==============================
      OWNER : scott
      LOG_GROUP_NAME : GGS_21724
      TABLE_NAME : T1
      COLUMN_NAME : A
      POSITION : 1
      LOGGING_PROPERTY : LOG
      ==============================

      Comment by mdinh — October 12, 2012 @ 2:30 am | Reply

      • Thank you for the reply..I would like to know the following scenario..
        Suppose you have added “add trandata schemaname.*” for a schema at table level before you start extract. That means it logs all the columns if there are no keys and logs key columns if there are any keys. Now suppose you have created two tables in the source with no keys and with keys, now do we have to delete and add trandata again or will it automatically logs the columns?
        I read the document, it says there are some limitations in using the add trandata and i can not use add schematrandata as it is not available for 10.2.0.4 (windows). what do we have to do in this scenario?

        thanks you very much in advance..

        Comment by Gowtham — October 12, 2012 @ 12:32 pm

  5. Hello Gowtham,

    You would have to test this since I have already noticed a change in behavior for “select * from USER_LOG_GROUP_COLUMNS” when I created the post and when I tested 2nd time. Notice the absence of row data for tables without PK. The correct approach is to always have PK on tables and there should not be a reason why this cannot be done for tables being replicated. Make it a requirement; otherwise it will affect performance. Without PK and If ggs can be stopped, then I would suggest delete trandata for the specific table and add trandata again. It would be much easier and much better performance to have tables with PK.

    HTH

    Comment by mdinh — October 12, 2012 @ 1:29 pm | Reply

    • Thank you for the reply…yes what you said is right, we have to delete the trandata and add it again, but the thing here is we don`t know when the tables are created because developers will not tell us when they create tables. And another worst case is I can not use schematrandata because of my database version. On the other side there are tables which don`t have any keys, but then i could not search them all tables and create primary keys for them as there are many. I was wondering if there are any other alternatives for these kind of scenarios. Any ways the above example is very helpful for me and than you very much for such a wonderful blog.

      Comment by Gowtham — October 12, 2012 @ 2:08 pm | Reply

      • One approach is to audit and another approach is to enable ddl logging which might not be available depending on your database version.

        I think add trandata should work, but you need to test and verify for your environment.

        Good Luck

        Here is an example for enable_ddl_logging=TRUE and what is in the alert log
        > tail -3 alert_db01.log
        Fri Oct 12 07:42:35 2012
        create table t(id1 int)
        alter table t add(id2 int)

        Comment by mdinh — October 12, 2012 @ 2:51 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: