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
Thinking Out Loud 