Oracle GoldenGate: DDL Replication To Oracle Targets With Dissimilar Data Structure

Introduction

Oracle GoldenGate for Oracle Databases provides for the replication of DDL operations. In most environments DDL replication is used only where the source and target tables structures are the same. The challenge comes when the source and target tables have a dissimilar table structure; as may exist in a Data Warehouse or Reporting Database. In this article we shall demonstrate the use of Oracle GoldenGate coupled with the Oracle Database DBMS_STREAMS.SET_TAG procedure to simultaneously replicate DDL to (1) a Data Warehouse consisting of the same table structures as the source and (2) a Reporting Database of dissimilar data structure.

The concepts, scripts, and information presented in this article are for educational purposes only. They are not supported by Oracle Development or Support, and come with no guarantee or warrant for functionality in any environment other than the test system used to prepare this article. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.

Main Article

The source Oracle Database schema consists of 12 tables that makeup a simple order entry database. The target Data Warehouse contains the same schema and table structures. The source to target table mapping for this replication stream will be:

   Source Target
Pluggable Database:  PDBORCL PDBNOREAST
Schema: TPC TPC
Tables: CATEGORIES CATEGORIES
CATEGORIES_DESCRIPTION CATEGORIES_DESCRIPTION
CUSTOMERS CUSTOMERS
CUSTOMERS_INFO CUSTOMERS_INFO
NEXT_ORDER NEXT_ORDER
ORDERS ORDERS
ORDERS_PRODUCTS ORDERS_PRODUCTS
ORDERS_STATUS_HISTORY ORDERS_STATUS_HISTORY
ORDERS_TOTAL ORDERS_TOTAL
PRODUCTS PRODUCTS
PRODUCTS_DESCRIPTION PRODUCTS_DESCRIPTION
PRODUCTS_TO_CATEGORIES PRODUCTS_TO_CATEGORIES

 

The source to target table mapping for the Reporting Database replication stream will be:

Source  Target
Pluggable Database: PDBORCL PDBNOREAST
 Schema: TPC TPCRPT
 Tables: CUSTOMERS_INFO REPORTING_CUSTOMERS
ORDERS REPORTING_ORDERS
ORDERS_PRODUCTS REPORTING_PRODUCTS

 

The target Reporting Database table structure differs from the source as follows:

Source  Target
CREATE TABLE customers_info  (
customers_info_id number(38) NOT NULL,
customers_info_last_logon timestamp(6),
customers_info_number_logons number(5),
customers_info_created_ts timestamp(6),
customers_info_last_modified timestamp(6),
global_product_notifications number(1),
PRIMARY KEY (customers_info_id)
using index
);
CREATE TABLE reporting_customers  (
customers_id number(38) NOT NULL,
customers_last_logon timestamp(6),
customers_created_ts timestamp(6),
customers_last_modified timestamp(6),
PRIMARY KEY (customers_id)
using index
);
CREATE TABLE orders  (
orders_id number(38) NOT NULL,
customers_id number(38) NOT NULL,
customers_name varchar2(255) NOT NULL,
customers_company varchar2(255),
customers_street_address varchar2(255) NOT NULL,
customers_suburb varchar2(255),
customers_city varchar2(255) NOT NULL,
customers_postcode varchar2(255) NOT NULL,
customers_state varchar2(255),
customers_country varchar2(255) NOT NULL,
customers_telephone varchar2(255) NOT NULL,
customers_email_address varchar2(255) NOT NULL,
customers_address_format_id number(5) NOT NULL,
delivery_name varchar2(255) NOT NULL,
delivery_company varchar2(255),
delivery_street_address varchar2(255) NOT NULL,
delivery_suburb varchar2(255),
delivery_city varchar2(255) NOT NULL,
delivery_postcode varchar2(255) NOT NULL,
delivery_state varchar2(255),
delivery_country varchar2(255) NOT NULL,
delivery_address_format_id number(5) NOT NULL,
billing_name varchar2(255) NOT NULL,
billing_company varchar2(255),
billing_street_address varchar2(255) NOT NULL,
billing_suburb varchar2(255),
billing_city varchar2(255) NOT NULL,
billing_postcode varchar2(255) NOT NULL,
billing_state varchar2(255),
billing_country varchar2(255) NOT NULL,
billing_address_format_id number(5) NOT NULL,
payment_method varchar2(255) NOT NULL,
cc_type varchar2(20),
cc_owner varchar2(255),
cc_number varchar2(32),
cc_expires varchar2(4),
last_modified timestamp(6),
date_purchased timestamp(6),
orders_status number(5) NOT NULL,
orders_date_finished timestamp(6),
currency char(3),
currency_value number(14,6),
PRIMARY KEY (orders_id)
using index
);
CREATE TABLE reporting_orders  (
orders_id number(38) NOT NULL,
customers_city varchar2(255) NOT NULL,
customers_postcode varchar2(255) NOT NULL,
customers_state varchar2(255),
customers_country varchar2(255) NOT NULL,
delivery_city varchar2(255) NOT NULL,
delivery_postcode varchar2(255) NOT NULL,
delivery_state varchar2(255),
delivery_country varchar2(255) NOT NULL,
date_purchased timestamp(6),
PRIMARY KEY (orders_id)
using index
);
CREATE TABLE orders_products  (
orders_products_id number(38) NOT NULL,
orders_id number(38) NOT NULL,
products_id number(38) NOT NULL,
products_model varchar2(12),
products_name varchar2(64) NOT NULL,
products_price number(15,4) NOT NULL,
final_price number(15,4) NOT NULL,
products_tax number(7,4) NOT NULL,
products_quantity number(2) NOT NULL,
PRIMARY KEY (orders_products_id)
using index
);
CREATE TABLE reporting_products  (
line_id number(38) NOT NULL,
orders_id number(38) NOT NULL,
products_id number(38) NOT NULL,
products_model varchar2(12),
products_name varchar2(64) NOT NULL,
products_price number(15,4) NOT NULL,
products_quantity number(2) NOT NULL,
PRIMARY KEY (line_id)
using index
);

Define Data Base Tags

We’ll be using the Oracle Database DBMS_STREAMS.SET_TAG procedure to identify DDL operations Oracle GoldenGate should, or should not replicate. Any valid tag may be used, with the exception of “00” as it is reserved for use by Oracle GoldenGate Replicat to ensure data is not ping-ponged in active-active replication environments. The tags we’ll use are:

Tag  Purpose
 87  Replicate to Data Warehouse only. Database operations identified by this tag will not replicate to the Reporting Database.
 88  Replicate to Reporting only. Database operations identified by this tag will not replicate to the Data Warehouse.
 89  Do not capture. Database operations identified by this tag will not be captured for replication.

Source Oracle GoldenGate Configuration

The source Oracle GoldenGate instance consists of one Integrated Extract and two Extract Data Pumps:

Integrated Extract  Extract Data Pump Extract Data Pump
extract epdborcl
useridalias ggroot
exttrail ./dirdat/ea
reportcount every 5 minutes, rate
reportrollover at 00:01
report at 00:01
statoptions resetreportstats
ddl include mapped
ddloptions report
warnlongtrans 15m, checkinterval 15m
table pdborcl.tpc.*;
extract p_dw
rmthost 192.168.120.44, mgrport 15000
rmttrail ./dirdat/dw
reportcount every 5 minutes, rate
reportrollover at 00:01
report at 00:01
statoptions resetreportstats
excludetag 88
excludetag 89
table pdborcl.tpc.*;
extract p_rptg
rmthost 192.168.120.44, mgrport 15000
rmttrail ./dirdat/rp
reportcount every 5 minutes, rate
reportrollover at 00:01
report at 00:01
statoptions resetreportstats
excludetag 87
excludetag 89
table pdborcl.tpc.orders_products;
table pdborcl.tpc.customers_info;
table pdborcl.tpc.orders;

Extract Data Pump p_dw will send all captured data downstream to the Data Warehouse Oracle GoldenGate instance, and p_rptg will send data for the three tables listed to the Reporting Database GoldenGate instance.

Integrated Extract epdborcl has the parameter DDL INCLUDE MAPPED enabled. This combined with the wildcard in the TABLE parameter ensures all database DDL operations executed against the TPC schema in the PDBORCL database instance will be captured. Whenever DDL is being captured, it is best practice to set the parameter DDLOPTIONS REPORT. This settings causes Integrated Extract to write a step-by-step history of all DDL operations captured to it’s report file; which is very useful when troubleshooting DDL replication issues.

Note the use of EXCLUDETAG in each Extract Data Pump; this determines what database operations (both DDL and DML) will be sent downstream for replication. By placing EXCLUDETAG in the Extract Data Pumps, we efficiently utilize database resources by having one Integrated Extract capture and queue all source database operations. The Extract Data Pumps will then process the data queued in the Oracle GoldenGate EXTTRAIL and determine if it should be transmitted downstream for replication.

Target Oracle GoldenGate Configuration

The target Oracle GoldenGate instance consists of two Replicats. I am using Integrated Replicats; however, the data apply mechanism may be any supported by the Oracle GoldenGate release (Classic, Coordinated, Parallel Apply, or Integrated Parallel Apply).

 Integrated Replicat Integrated Replicat
replicat r_dw
useridalias ggalias
reportcount every 5 minutes, rate
ddl include mapped
ddloptions report
map pdborcl.tpc.*, target pdbnoreast.tpc.*;
replicat r_rptg
useridalias ggalias
reportcount every 120 seconds, rate
ddl include mapped
ddloptions report
ignoredeletesmap pdborcl.tpc.orders_products, target pdbnoreast.tpcrpt.reporting_products,
colmap (usedefaults,
line_id = orders_products_id
)
;
map pdborcl.tpc.customers_info, target pdbnoreast.tpcrpt.reporting_customers,
colmap (usedefaults,
customers_id = customers_info_id,
customers_last_logon = customers_info_last_logon,
customers_created_ts = customers_info_created_ts,
customers_last_modified = customers_info_last_modified
);
map pdborcl.tpc.orders, target pdbnoreast.tpcrpt.reporting_orders; 

Integrated Replicat r_dw will apply all data sent downstream by Extract Data Pump p_dw to the TPC schema in the PDBNOREAST Oracle Database instance. Since we are replicating all source tables to the target Data Warehouse, and the tables have the same data structure; we can use wildcards in the MAP parameter.

Integrated Replicat r_rptg will apply all data sent downstream by Extract Data Pump p_rptg to the TPCRPT schema in the PDBNOREAST Oracle Database instance. We’re only replicating three tables; two of which have column names dissimilar to the source, so we must use the COLMAP option to the MAP parameter and explicity identify what source data goes into which target column. The target REPORTING_ORDERS table contains a subset of the columns in the source ORDERS table; we do not need to explicitly map the columns target to source columns because the target table columns have the same name and data type definition as the source. In this instance the Oracle GoldenGate Replicat will automatically reconcile and map the source and target columnar data; while ignoring source columnar data that has to correlation in the target table.

Each Integrated Replicat has the parameter DDL INCLUDE MAPPED enabled. This ensures all database DDL operations captured at the source are applied at the target for all tables being relpicated. Whenever DDL is being captured, it is best practice to set the parameter DDLOPTIONS REPORT. This settings causes Integrated Replicat to write a step-by-step history of all DDL operations being applied to it’s report file; which is very useful when troubleshooting DDL replication issues.

Build The Replication Environment

Starting from the source side, let’s build the replication environment. Start GGSCI and use the DBLOGIN command to start a database session (my source is an Oracle 12c Pluggable Database, so I login to the root container).

Source DBLogin

 

Enable Supplemental Logging For The Source Schema

We use the ADD SCHEMATRANDATA command to enable supplemental logging at the schema level whenever (1) data capture is done via Integrated Capture, (2) data apply is done via Integrated Replicat, or (3) DDL replication is enabled and there exists the possibility that DML operations will be concurrent with DDL that creates tables or alters key columns.

DDL ADD SCHEMATRANDATA

A couple of notes about ADD SCHEMATRANDATA:

(a) ADD SCHEMATRANDATA enables supplemental logging on a table’s columns in the following order of priority: (1) primary key, (2) all unique indexes (including those that are disabled, unusable, or invisible) if no primary key is defined, and then (3) all scalar columns if there is no primary key nor unique index.

(b) If a table has multiple unique indexes and no primary key, ADD SCHEMATRANDATA causes the database to log all of the unique constraint columns. To avoid the extra logging, designate one of the unique constraints as a primary key.

(c) Use ADD TRANDATA with the COLS option after executing ADD SCHEMATRANDATA to log additional non-key columns of tables when the FILTER or KEYCOLS options in the TABLE or MAP parameters are used.

Create And Register The Integrated Extract

Add the Integrated Extract to the Oracle GoldenGate instance and register it with the database.

ljpddl3

Create The Extract Data Pumps

Add the Extract Data Pumps to the Oracle GoldenGate instance.

ljpddl4

Create The Replicats

On the target side, complete the replication stream setup. Start GGSCI, use the DBLOGIN command to start a database session, and add the Replicats to the Oracle GoldenGate instance (Note: Because I am using a multi-tenant Oracle Database, my Oracle GoldenGate instance connects to both source and target from a centralized location).

ljpddl5

Test The Replication Stream

Now I can start my Oracle GoldenGate instances:

ljpddl6

Ensure the replication components are running and processing data:

ljpddl7

The STATS command may be used to obtain information about the number of operations each Replicat has processed:

ljpddl8

 

ljpddl9

Test DDL Replication

Now that we have a functional replication stream and have validated DML replication, let’s do some DDL.

Test #1: Add A New Table

Add a new table to the source database and replicate it to both the data warehouse and reporting databases. The DDL for this test table is:

create table ddltest (
cola number(38) not null,
colb timestamp(6) not null,
colc varchar2(100) not null,
primary key (cola, colb)
using index
);

In the Extract Data Pump and Replicat for the reporting replication stream, we’ve exclusively listed the tables to be replicated; so we need to alter the Oracle GoldenGate configuration.

 Extract Data Pump Integrated Replicat
extract p_rptg
rmthost 192.168.120.44, mgrport 15000
rmttrail ./dirdat/rp
reportcount every 5 minutes, rate
reportrollover at 00:01
report at 00:01
statoptions resetreportstats
excludetag 87
excludetag 89
table pdborcl.tpc.orders_products;
table pdborcl.tpc.customers_info;
table pdborcl.tpc.orders;
table pdborcl.tpc.ddltest;
replicat r_rptg
useridalias ggalias
reportcount every 120 seconds, rate
ddl include mapped
ddloptions report
ignoredeletesmap pdborcl.tpc.orders_products, target pdbnoreast.tpcrpt.reporting_products,
colmap (usedefaults,
line_id = orders_products_id
)
;
map pdborcl.tpc.customers_info, target pdbnoreast.tpcrpt.reporting_customers,
colmap (usedefaults,
customers_id = customers_info_id,
customers_last_logon = customers_info_last_logon,
customers_created_ts = customers_info_created_ts,
customers_last_modified = customers_info_last_modified
);
map pdborcl.tpc.orders, target pdbnoreast.tpcrpt.reporting_orders;
map pdborcl.tpc.ddltest, target pdbnoreast.tpcrpt.ddltest; 

Stop and restart the reporting Extract Data Pump and Integrated Replicat.

ljpddl10

Execute the DDL:

SQL> create table ddltest (
cola number(38) not null,
colb timestamp(6) not null,
colc varchar2(100) not null,
primary key (cola, colb)
using index
);
commit;
Table created.

The Oracle GoldenGate Report files will show the following:

 Integrated Extract Integrated Replicat R_DW Integrated Replicat R_RPTG 
 

2018-03-05 13:54:07  INFO    OGG-01487  DDL found, operation [CREATE TABLE DDLTEST (
COLA NUMBER(38) NOT NULL,
COLB TIMESTAMP(6) NOT NULL,
COLC VARCHAR2(100) NOT NULL,
PRIMARY KEY (COLA, COLB)
USING INDEX
) (size 155)], start SCN [44575716], commit SCN [44575740] instance [ (1)], DDL seqno [0], marker seqno [0].

2018-03-05 13:54:07  INFO    OGG-10451  DDL operation included [include mapped], optype [CREATE], objtype [TABLE], catalog [PDBORCL], objowner [TPC], objname [DDLTEST].

2018-03-05 13:54:07  INFO    OGG-00497  Writing DDL operation to extract trail file.

 

2018-03-05 13:54:12  INFO    OGG-00482  DDL found, operation [create table ddltest (
cola number(38) not null,
colb timestamp(6) not null,
colc varchar2(100) not null,
primary key (cola, colb)
using index
) (size 154)].

2018-03-05 13:54:12  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [create table “TPC”.”DDLTEST” (
cola number(38) not null,
colb timestamp(6) not null,
colc varchar2(100) not null,
primary key (cola, colb)
using index
) (size 162)].

2018-03-05 13:54:12  INFO    OGG-10451  DDL operation included [include mapped], optype [CREATE], objtype [TABLE], catalog [PDBORCL], objowner [TPC], objname [DDLTEST].

2018-03-05 13:54:12  INFO    OGG-01407  Setting current schema for DDL operation to [TPC].

2018-03-05 13:54:12  INFO    OGG-00484  Executing DDL operation.

2018-03-05 13:54:12  INFO    OGG-00483  DDL operation successful.

2018-03-05 13:54:12  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].

 

2018-03-05 13:54:12  INFO    OGG-00482  DDL found, operation [create table ddltest (
cola number(38) not null,
colb timestamp(6) not null,
colc varchar2(100) not null,
primary key (cola, colb)
using index
) (size 154)].

2018-03-05 13:54:12  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [create table “TPCRPT”.”DDLTEST” (
cola number(38) not null,
colb timestamp(6) not null,
colc varchar2(100) not null,
primary key (cola, colb)
using index
) (size 165)].

2018-03-05 13:54:12  INFO    OGG-10451  DDL operation included [include mapped], optype [CREATE], objtype [TABLE], catalog [PDBORCL], objowner [TPCRPT], objname [DDLTEST].

2018-03-05 13:54:12  INFO    OGG-01407  Setting current schema for DDL operation to [TPCRPT].

2018-03-05 13:54:12  INFO    OGG-00484  Executing DDL operation.

2018-03-05 13:54:12  INFO    OGG-00483  DDL operation successful.

2018-03-05 13:54:12  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].

Validate the table creation in the target schemas:

ljpddl11

Before continuing I want to add some data to the source table and replicate it.

ljpddl12

Test #2: Add A Column To The Source And Reporting Database Tables

For this test we are adding a new column to the tables PDBORCL.TPC.DDLTEST and PDBNOREAST.TPCRPT.DDLTEST. We’ll use the Oracle Database DBMS_STREAMS.SET_TAG procedure to ensure this change is not replicated by Oracle GoldenGate to the data warehouse.

Execute the DDL:

ljpddl13

Validate the target table structures:

ljpddl14

Before continuing, I want to clear the database tag; so I logout of the database and then log back in and execute DML:

ljpddl15

Validate the data in the source and target tables. Because the table in the data warehouse was not changed, Replicat R_DW ignores the incoming data for the unmapped column, COLD.

Data in source table:

ljpddl18

Data in target data warehouse table:

ljpddl16

Data in target reporting table:

ljpddl17

Test #3: Add An Unique Constraint To The Source And Data Warehouse Tables

For this test we are altering the source TPC.ORDERS table to add an unique constraint and replicating the change to the data warehouse only.

Execute the DDL:

ljpddl19Querying the data dictionary for constraints on the source table returns:

ljpddl20Querying the data dictionary for constraints on the data warehouse table returns:

ljpddl21

Querying the data dictionary for constraints in the reporting database returns:

ljpddl22

Test #4: Manual Changes That Are Not Replicated

Sometimes there may be a need to make manual table changes that will not be replicated by Oracle GoldenGate. For this test, we’ll make a change to the reporting table while DML is being replicated from the source, and then make the same change to the source table.

Alter the target table, it is important to note that in this use case the column being added cannot be “not null”:

ljpddl23Generate source data:

ljpddl24

Data in the target reporting table:

ljpddl25Make the same change to the source table and generate data. Do not replicate the DDL change; however, we do want to replicate the data to the data warehouse and reporting databases:

ljpddl26

Data in the reporting database table:

ljpddl27Data in the data warehouse table:

ljpddl28

 

Summary

In this article we demonstrated the use of Oracle GoldenGate coupled with the Oracle Database DBMS_STREAMS.SET_TAG procedure to simultaneously replicate DDL to (1) a Data Warehouse consisting of the same table structures as the source and (2) a Reporting Database of dissimilar data structure.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

Add Your Comment