Best Practice: How To Handle DDL Changes in a DML Only Replication

The Challenge

In DML replication with  real-time reporting  from an Oracle to an Oracle RDBMS,  the target table structure may differ from the source table structure, i.e. Replicat uses column mapping and requires a source definitions file.

If the source DB is maintained by a different group than is the target table, it can that the source database group makes a change to the table structure of the source DB which invalidates the target side’s sourcedefs file, causing replicat to abend.

When a source side DDL change happen, it is required to:

1) Bounce the Extract to reread the changed table structure

2) Add the same change(s)  to the target table as were made to the source table, as required

3) Modify any Replicat’s mapping configuration

4) Obtain a new source definition file to reflect the changed structure

This requires coordination to make certain that a Replicat processes the proper DML changes using the appropriate sourcedefs.

That is: When the DDL change happened at let’s say SCN 456 the Replicat needs to continue to use the former sourcedefs file for transactions before SCN 456 but needs to use the new sourcedefs file from SCN 457 following. The problem is  to know the exact point when the DDL change happened so that the replicat may be bounced the new sourcedefs file implemented at the appropriate etime.

Here is the accepted solution:

1) Configure Extract with DDL capture:

EXTRACT ggs_ext

USERID gg01, PASSWORD gg01

EXTTRAIL ./dirdat/et

-- Capture DDL for tables only, but not changes to indexes or sequences in the same schema because they wouldn’t hurt the DML replication.

DDL INCLUDE MAPPED, OBJTYPE 'TABLE'

DDLOPTIONS REPORT, ADDTRANDATA

TABLE gg01.customers;

TABLE gg01.products;

2) Configure Replicat with BOTH sourcedefs and DDL replication – even though  we don’t want to apply DDL replication!

REPLICAT ggs_rep

USERID gg02, PASSWORD gg02

--use the sourcedefs file to read the trail

SOURCEDEFS ./dirdef/source.def

DISCARDFILE ./dirrpt/ggs_rep.dsc, purge

--set DDL replication that will fail!

DDL INCLUDE MAPPED

DDLSUBST 'GG01' with 'DDLCHANGEONGG01'

DDLOPTIONS REPORT

--complex mapping example

--filtering example, only USA CUSTOMERS

--transformation example - split contact name into 2 columns

MAP GG01.CUSTOMERS, TARGET GG02.US_CUSTOMERS, COLMAP (USEDEFAULTS, CONTACTFIRST = @STREXT (CONTACTNAME, 1, (CONTACTNAME, @STRFIND (CONTACTNAME," ") + 1, @STRLEN (CONTACTNAME))) WHERE (COUNTRY = "USA");

With this configuration, DML only records are replicated normally.

The DDLSUBST line in the above config is added  to be certain that no DDL will ever go through to the target but every DDL operation will cause the Replicat to abend with an error.

When Replicat detects the DDL it abends with error message:

ERROR   OGG-00513  Table with SOURCEDEF cannot have DDL operations (table [GG02.PRODUCTS]).

Here’s a complete example of a Replicat’s report file after the DDL was detected:

***********************************************************************

**                     Run Time Messages                             **

***********************************************************************

Opened trail file ./dirdat/rt000000 at 2011-08-04 17:48:27

2011-08-04 17:48:27  INFO    OGG-00482  DDL found, operation [alter table products add test varchar(10)  (size 42)].

2011-08-04 17:48:27  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [alter table "GG02"."PRODUCTS" add test varchar(10)  (size 51)].

2011-08-04 17:48:27  INFO    OGG-00487  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [TABLE], objowner [GG02], objname [PRODUCTS].

2011-08-04 17:48:27  INFO    OGG-00504  DDL substitution [GG02] with [DDLCHANGEONGG02] included [include all (default)], new operation [alter table "DDLCHANGEONGG02"."PRODUCTS" add test varchar(10)  (size 62)].

Source Context :

  SourceModule            : [ggapp.ddl]

  SourceID                : [/scratch/sganti/view_storage/sganti_core_lin32/oggcore/OpenSys/src/gglib/ggapp/ddlrep.c]

  SourceFunction          : [processMapped]

  SourceLine              : [1596]

  ThreadBacktrace         : [10] elements

                          : [/home/ggi/ogg_target/replicat(CMessageContext::AddThreadContext()+0x26) [0x820f026]]

                          : [/home/ggi/ogg_target/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x817) [0x82057a7]]

                          : [/home/ggi/ogg_target/replicat(_MSG_ERR_DDL_SOURCEDEFS_NOT_ALLOWED(CSourceContext*, DBString<777> const&, CMessageFactory::MessageDisposition)+0x81) [0x81c0471]]

                          : [/home/ggi/ogg_target/replicat [0x842c22f]]

                          : [/home/ggi/ogg_target/replicat(DDLREP_process(char*, __std_rec_hdr*, unsigned short)+0x1833) [0x8432aa5]]

                          : [/home/ggi/ogg_target/replicat [0x84d4583]]

                          : [/home/ggi/ogg_target/replicat [0x84da956]]

                          : [/home/ggi/ogg_target/replicat(main+0x6f6) [0x8135a86]]

                          : [/lib/tls/libc.so.6(__libc_start_main+0xd3) [0x942de3]]

                          : [/home/ggi/ogg_target/replicat(__gxx_personality_v0+0x1bd) [0x8112311]]

2011-08-04 17:48:27  ERROR   OGG-00513  Table with SOURCEDEF cannot have DDL operations (table [GG02.PRODUCTS]). Either remove SOURCEDEF or filter out table from DDL operations.

This is the desired outcome; the interruption of the Replicat at the point appropriate to changing the target table and sourcedefs.

The last ERROR says there was a DDL operation on a table that we replicate.

The INFO above tells us the exact DDL statement “alter table GG02.PRODUCTS …”

Now we can – at the exact right point in the trail – perform the required steps:

- Add the same column to our target table (so that our USEDEFAULTS mapping keeps working)

- Obtain a new source definitions file from the source DB of its current state

- Modify mappings if required

- Restart Replicat with ggsci command START REPLICAT SKIPTRANSACTION

[Note: A DDL operation doesn’t require a commit, so it can be assumed that every DDL operation is its own single-operation transaction. The skiptransaction parameter skips exactly one transaction, so it’s safe to use at this point].

We get all the good things of DDL replication:

- Auto refresh of table definition at source

- Auto add trandata

- Filtering to watch only for DDLs of mapped scope

Possible Improvements:

When using wildcards for the TABLE parameters in Extract’s configuration the DDL INCLUDE MAPPED parameter should be extended to filter for table objects only, so that changes to indexes or sequences in the same schema are not captured at all because these changes wouldn’t hurt the DML replication.

====================================================================