X

Best Practices from Oracle Development's A‑Team

Oracle GoldenGate: Teradata Instantiation

Introduction

When working with Teradata customers, we are always asked for recommendations of how to instantiate the target Teradata database. There are numerous ways to accomplish this; including various third-party applications that most customers have in-house. In this article, we will demonstrate a zero down-time data replication implementation from an Oracle 12c Pluggable Database to Teradata using Oracle GoldenGate for real-time data replication along with Oracle GoldenGate advanced functionality to create Teradata FastLoad and MultiLoad input files.

Main Article

We are making an assumption in this article that Oracle GoldenGate has been installed as specified in the Installing and Configuring Oracle GoldenGate for Oracle and Installing and Configuring Oracle GoldenGate for Teradata reference guides, which are available at Oracle TechNetwork; and the source (Oracle) and target (Teradata) tables exist.

Configure Source GoldenGate Instance

In the source GoldenGate instance, we will create the (1) an Integrated Extract to capture real-time database transactions, (2) an Extract Data Pump to move the captured data to the target GoldenGate instance, and (3) an Extract Initial Load Task for each table being replicated. My source side Oracle GoldenGate configuration looks like this:

Change Data Capture Components

For real-time change data capture, I create an Integrated Extract with the following settings:

extract epdborcl
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
exttrail ./dirdat/ea
logallsupcols
updaterecordformat compact
reportcount every 5 minutes, rate
table pdborcl.tpc.*;

An Extract Data Pump is used to send the data captured by Integrated Extract to the target Oracle GoldenGate instance. My Extract Data Pump settings are:

extract ptdata
rmthost 192.168.120.163, mgrport 7809, compress
rmttrail ./dirdat/aa
reportcount every 5 minutes, rate
table pdborcl.tpc.categories;
table pdborcl.tpc.categories_description;
table pdborcl.tpc.customers;
table pdborcl.tpc.customers_info;
table pdborcl.tpc.orders;
table pdborcl.tpc.orders_products;
table pdborcl.tpc.orders_status_history;
table pdborcl.tpc.orders_total;
table pdborcl.tpc.products;
table pdborcl.tpc.products_description;
table pdborcl.tpc.product_to_categories;

Initial Load Tasks

We need an Extract Task for each table being replicated. For brevity, I am showing only one of the eleven parameter files here:

extract tcat
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
rmthost 192.168.120.163, mgrport 7809, compress
--
-- Set the format of the output file written on the target
--
formatascii, delimiter '|', nonames, noquote, nohdrfields, notranstmts
rmtfile ./dirdat/iload/categories/ct, megabytes 2000, purge, maxfiles 999999
--
-- Override the default output format for timestamps to one acceptable by Teradata.
--
map pdborcl.tpc.categories, target pdborcl.tpc.categories,
colmap (usedefaults,
date_added = @IF (@COLTEST (date_added, NULL, MISSING), @COLSTAT (NULL),
@DATE('YYYY-MM-DD HH:MI:SS.FFFFFF', 'YYYY-MM-DD HH:MI:SS.FFFFFF', date_added)),
last_modified = @IF (@COLTEST (last_modified, NULL, MISSING), @COLSTAT (NULL),
@DATE('YYYY-MM-DD HH:MI:SS.FFFFFF', 'YYYY-MM-DD HH:MI:SS.FFFFFF', last_modified)),
);

This Extract will read from the source PDBORCL.TPC.CATEGORIES table and create a series of files named ct000000 through ct999999 in the GoldenGate dirdat/iload/categories folder on the target instance; purging any files of matching names if they already exists. The first file created will be named ct000000 and will be a maximum of 2 GB in size. Subsequent files; i.e., ct000001 will be created as needed if the amount of data read is greater than 2 GB.

This file is not the typical Oracle GoldenGate Trail file we normally use. FORMATASCII specifies the file will be created as an ASCII text file, otherwise known as an "edit" file in that it may be opened and modified by a text editor. The options to the FORMATASCII parameter specify that columnar data will be separated by a "|" character (delimiter), table column names will not be listed (nonames), character data will not be surrounded by single-quotes (noquote), operation type identifiers and record type indicators will not be written to the file (nohdrfields), and transaction information will be excluded (notranstmts).

The MAP statement is used to modify TIMESTAMP data from the default output format of YYYY-MM-DD:HH:MI:SS:FFFFFFFFF to one acceptable to Teradata, if there is data in the column.

With these settings, the file contents created by Oracle GoldenGate will look like this: which may be used as input to Teradata FastLoad or Teradata MultiLoad.

1|category_hardware.gif|0|1|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.985021
2|category_software.gif|0|2|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.990480
3|category_dvd_movies.gif|0|3|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.991124
4|subcategory_graphic_cards.gif|1|0|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.991654
5|subcategory_printers.gif|1|0|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.992163
6|subcategory_monitors.gif|1|0|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.992702
7|subcategory_speakers.gif|1|0|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.993214
8|subcategory_keyboards.gif|1|0|2011-04-04 08:31:00.000000   |2017-06-16 08:26:21.993738

Configure Target GoldenGate Instance

In the target GoldenGate instance, we will create a single Coordinated Apply to replicate the captured Oracle transactions into the Teradata database. The Coordinate Apply configuration looks like this:

replicat rtpc
targetdb TD_OGG
reportcount every 5 minutes, rate
usededicatedcoordinationthread
dboptions nocatalogconnect
-- Initial Load Params: Remove for ongoing CDC
HANDLECOLLISIONS
END RUNTIME
--
-- Add this back for on-going CDC
--batchsql bytesperqueue 1000000
map pdborcl.tpc.categories, target tpc.categories, thread (1);
map pdborcl.tpc.categories_description, target tpc.categories_description, thread (1);
map pdborcl.tpc.customers, target tpc.customers, thread (2);
map pdborcl.tpc.customers_info, target tpc.customers_info, thread (2);
map pdborcl.tpc.orders, target tpc.orders, thread (3);
map pdborcl.tpc.orders_products, target tpc.orders_products, thread (3);
map pdborcl.tpc.orders_status_history, target tpc.orders_status_history, thread (3);
map pdborcl.tpc.orders_total, target tpc.orders_total, thread (3);
map pdborcl.tpc.products, target tpc.products, thread (4);
map pdborcl.tpc.products_description, target tpc.products_description, thread (4);
map pdborcl.tpc.product_to_categories, target tpc.product_to_categories, thread (4);

Create Teradata FastLoad Script

For each table being replicated, we will need a Teradata FastLoad script to perform the instantiation. For brevity, I am showing one of the eleven files here:

LOGON 192.168.120.164/dbc,dbc;
DATABASE TPC;
BEGIN LOADING TPC.CATEGORIES
ERRORFILES Categories_ET, Categories_UV
CHECKPOINT 10;
SET RECORD VARTEXT "|";
DEFINE in_CATEGORIES_ID (VARCHAR(39)),
in_CATEGORIES_IMAGE (VARCHAR(64)),
in_PARENT_ID (VARCHAR(39)),
in_SORT_ORDER(VARCHAR(3)),
in_DATE_ADDED (VARCHAR(29)),
in_LAST_MODIFIED (VARCHAR(29)),
FILE = D:\GoldenGate\Teradata\dirdat\iload\categories\ct000000;
INSERT INTO CATEGORIES (
CATEGORIES_ID,
CATEGORIES_IMAGE,
PARENT_ID,
SORT_ORDER,
DATE_ADDED,
LAST_MODIFIED
)
VALUES (
:in_CATEGORIES_ID,
:in_CATEGORIES_IMAGE,
:in_PARENT_ID,
:in_SORT_ORDER,
:in_DATE_ADDED (TIMESTAMP(6), FORMAT 'YYYY-MM-DDbHH:MI:SS.S(6)'),
:in_LAST_MODIFIED (TIMESTAMP(6), FORMAT 'YYYY-MM-DDbHH:MI:SS.S(6)')
);
END LOADING;
LOGOFF;

Create GoldenGate Instances

To create the source instance, we'll need to execute a lot of GGCSI commands; so I build an obey file that will be executed from within GGSCI:

-- Login to the database
dblogin userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
--
-- Add the CDC Extract
add extract epdborcl, integrated tranlog, begin now
add exttrail ./dirdat/ea, extract epdborcl, megabytes 500
--
register extract epdborcl database, container (pdborcl)
--
-- Add the Extract Data Pump
add extract ptdata, exttrailsource ./dirdat/ea
add rmttrail ./dirdat/aa, extract ptdata, megabytes 500
--
-- Add the initial load Extract Tasks
add extract tcat, sourceistable
add extract tcatd, sourceistable
add extract tcust, sourceistable
add extract tcusti, sourceistable
add extract tord, sourceistable
add extract tordp, sourceistable
add extract tords, sourceistable
add extract tordt, sourceistable
add extract tprod, sourceistable
add extract tprodd, sourceistable
add extract tprodc, sourceistable

Run the obey file:

GGSCI (centos0ra12) 1> obey ./dirprm/add_groups

Check the instance was created:

GGSCI (centos0ra12 as c##ggadmin@orcl/CDB$ROOT) 27> status all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EPDBORCL    00:00:00      00:01:24
EXTRACT     STOPPED     PTDATA      00:00:00      00:01:01

GGSCI (centos0ra12 as c##ggadmin@orcl/CDB$ROOT) 28> info * task
-
EXTRACT    TCAT      Initialized   2017-06-16 12:27   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TCATD     Initialized   2017-06-16 12:27   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TCUST     Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TCUSTI    Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TORD      Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TORDP     Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TORDS     Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TORDT     Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TPROD     Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TPRODC    Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TPRODD    Initialized   2017-06-16 12:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE

On the target side, create the directories to hold the files created by the Extract Tasks:

Directory of D:\GoldenGate\Teradata\dirdat\iload
06/16/2017  12:53 PM    <DIR>          .
06/16/2017  12:53 PM    <DIR>          ..
06/16/2017  12:48 PM    <DIR>          categories
06/16/2017  12:53 PM    <DIR>          customers
06/16/2017  12:53 PM    <DIR>          orders
06/16/2017  12:53 PM    <DIR>          products

Add the Coordinated Apply:

GGSCI (LPENTON-LAP) 10> dblogin sourcedb td_ogg
2017-06-16 13:02:55  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.
2017-06-16 13:02:55  INFO    OGG-03037  Session character set identified as windows-1252.
Successfully logged into database.

GGSCI (LPENTON-LAP) 11> add checkpointtable ggadmin.ggchkpt
Successfully created checkpoint table ggadmin.ggchkpt.

GGSCI (LPENTON-LAP) 12> add replicat rtpc, coordinated, exttrail ./dirdat/aa, checkpointtable ggadmin.ggchkpt
REPLICAT (Coordinated) added.

Start Data Capture

On the source side, start all of the Extracts; because we have quite a few, I created a GGCSI obey file:

start extract epdborcl
start extract ptdata
start extract tcat
start extract tcatd
start extract tcust
start extract tcusti
start extract tord
start extract tordp
start extract tords
start extract tordt
start extract tprod
start extract tprodd
start extract tprodc

GGSCI (centos0ra12 as c##ggadmin@orcl/CDB$ROOT) 31> obey ./dirprm/start_groups

Check the status of the instance:

GGSCI (centos0ra12 as c##ggadmin@orcl/CDB$ROOT) 47> status all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EPDBORCL    00:18:09      00:00:03
EXTRACT     RUNNING     PTDATA      00:00:00      00:20:54

GGSCI (centos0ra12 as c##ggadmin@orcl/CDB$ROOT) 68> info *, task
-
EXTRACT    TCAT      Last Started 2017-06-16 12:57   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table PDBORCL.TPC.CATEGORIES
2017-06-16 12:57:06  Record 21
Task                 SOURCEISTABLE
-
EXTRACT    TCATD     Initialized   2017-06-16 12:27   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TCUST     Last Started 2017-06-16 12:57   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table PDBORCL.TPC.CUSTOMERS
2017-06-16 12:57:08  Record 609
Task                 SOURCEISTABLE
-
EXTRACT    TCUSTI    Initialized   2017-06-16 12:28   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 5
Task                 SOURCEISTABLE
-
EXTRACT    TORD      Last Started 2017-06-16 12:57   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table PDBORCL.TPC.ORDERS
2017-06-16 12:57:10  Record 1511
Task                 SOURCEISTABLE
-
EXTRACT    TORDP     Last Started 2017-06-16 12:57   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table PDBORCL.TPC.ORDERS_PRODUCTS
2017-06-16 12:57:11  Record 38823
Task                 SOURCEISTABLE
-
EXTRACT    TORDS     Last Started 2017-06-16 12:57   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table PDBORCL.TPC.ORDERS_STATUS_HISTORY
2017-06-16 12:57:12  Record 1512
Task                 SOURCEISTABLE
-
EXTRACT    TORDT     Initialized   2017-06-16 12:28   Status STARTING
Checkpoint Lag       Not Available
Process ID           11759
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TPROD     Initialized   2017-06-16 12:28   Status STARTING
Checkpoint Lag       Not Available
Process ID           11766
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TPRODC    Initialized   2017-06-16 12:28   Status STARTING
Checkpoint Lag       Not Available
Process ID           11781
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE
-
EXTRACT    TPRODD    Initialized   2017-06-16 12:28   Status STARTING
Checkpoint Lag       Not Available
Process ID           11773
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE

Wait for all of the Extract Tasks to go into the STOPPED state, with record counts greater than 0.

Run FastLoad

Since I am loading multiple tables, I created a script to run FastLoad:

@ECHO OFF setlocal enableextensions set MYLOC=%~dp0 for /R %MYLOC% %%i IN (*.*) DO fastload < %%i >> fload_out.txt

Execute the script, wait for it to complete, and check the output file for load errors. Repair and rerun any failed load scripts before continuing.

D:\GoldenGate\Teradata\dirsql>runfload.bat

Start Coordinated Apply

Start the Coordinated Apply to complete the instantiation.

GGSCI (LPENTON-LAP) 20> start rtpc
Sending START request to MANAGER ...
REPLICAT RTPC starting

Wait for all Coordinated Apply threads to go into the STOPPED state.

GGSCI (LPENTON-LAP) 21> status rtpc*
REPLICAT RTPC: RUNNING
REPLICAT RTPC001: RUNNING
REPLICAT RTPC002: RUNNING
REPLICAT RTPC003: RUNNING
REPLICAT RTPC004: RUNNING

GGSCI (LPENTON-LAP) 22> !
status rtpc*
REPLICAT RTPC: STOPPED
REPLICAT RTPC001: STOPPED
REPLICAT RTPC002: STOPPED
REPLICAT RTPC003: STOPPED
REPLICAT RTPC004: STOPPED

Edit the parameter file; remove the configuration options HANDLECOLLISIONS and END RUNTIME, and enable the option BATCHSQL. The parameter file now looks like this:

replicat rtpc
targetdb TD_OGG
reportcount every 5 minutes, rate
usededicatedcoordinationthread
dboptions nocatalogconnect
batchsql bytesperqueue 1000000
map pdborcl.tpc.categories, target tpc.categories, thread (1);
map pdborcl.tpc.categories_description, target tpc.categories_description, thread (1);
map pdborcl.tpc.customers, target tpc.customers, thread (2);
map pdborcl.tpc.customers_info, target tpc.customers_info, thread (2);
map pdborcl.tpc.orders, target tpc.orders, thread (3);
map pdborcl.tpc.orders_products, target tpc.orders_products, thread (3);
map pdborcl.tpc.orders_status_history, target tpc.orders_status_history, thread (3);
map pdborcl.tpc.orders_total, target tpc.orders_total, thread (3);
map pdborcl.tpc.products, target tpc.products, thread (4);
map pdborcl.tpc.products_description, target tpc.products_description, thread (4);
map pdborcl.tpc.product_to_categories, target tpc.product_to_categories, thread (4)

Restart the Coordinated Apply.

The Teradata instantiation is now complete and real-time replication from Oracle to Teradata is taking place.

Instantiating Large Tables

This solution works well for tables where the Oracle GoldenGate Tasks create a single, 2 GB load file. But, what about tables containing millions or billions of rows? This data may not fit into a single file, and FastLoad is designed to only load empty Teradata tables.

Our current solution may be used to address this situation; with one minor change, we will need to use the Teradata MultiLoad utility to perform the Teradata table load. But then again, the challenge is not so much in loading the Teradata target; but, unloading the Oracle source table quickly. Oracle GoldenGate provides functionality to accomplish this.

Consider the following table:

SQL> select count(*) from orders_products;   COUNT(*) ----------    5871721

The Initial Load Extract Task, ran for two minutes and created a load file 401 Mb in size (NOTE: The source database was live so not all records were read by the Initial Load Extract Task.)

Report at 2017-06-20 13:37:52 (activity since 2017-06-20 13:35:55) Output to ./dirdat/iload/orders/op: From Table PDBORCL.TPC.ORDERS_PRODUCTS:        #                   inserts:   5400941

Directory of D:\GoldenGate\Teradata\dirdat\iload\orders

06/20/2017  01:47 PM    <DIR>          .
06/20/2017  01:47 PM    <DIR>          ..
06/20/2017  01:37 PM       401,471,722 op000000

This is small in comparison to most production databases; however, we can still use this able to demonstrate our point.

First, I wanted to know the max and median data value for the table's primary key.

SQL> select max(orders_products_id) from tpc.orders_products; MAX(ORDERS_PRODUCTS_ID) -----------------------   5871721 SQL> select median(orders_products_id) from tpc.orders_products; MEDIAN(ORDERS_PRODUCTS_ID) --------------------------      2935861

Then I need to decide upon how many Initial Load Extract Tasks I want to use to unload the source data. For simplicity, I am going to use two; with the following configurations:

extract tordp
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
rmthost 192.168.120.80, mgrport 7809, compress
--
formatascii, delimiter '|', nonames, noquote, nohdrfields, notranstmts
rmtfile ./dirdat/iload/orders/op, megabytes 2000, purge, maxfiles 999999
--
table pdborcl.tpc.orders_products, sqlpredicate 'where orders_products_id < 2935861';

extract tordp1
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
rmthost 192.168.120.80, mgrport 7809, compress
--
formatascii, delimiter '|', nonames, noquote, nohdrfields, notranstmts
rmtfile ./dirdat/iload/orders/pa, megabytes 2000, purge, maxfiles 999999
--
table pdborcl.tpc.orders_products, sqlpredicate 'where orders_products_id > 2935860';

The option SQLPREDICATE tells the Extract to specify a database WHERE clause as part of the select statement it uses to unload the source data, and I specify the data breakpoint using the median value. There are other ways of doing this including the @RANGE Column Conversion Function, the Oracle GoldenGate FILTER option, and the Oracle GoldenGate WHERE option; however, for Oracle databases SQLPREDICATE is the most efficient option.

If I had wanted to use three Initial Load Extract Task groups for the unload, I could have taken the max value returned and divided it by the number of groups to get the starting data point to use in the TABLE statements:

Extract TORDP:  table pdborcl.tpc.orders_products, sqlpredicate 'where orders_products_id < 1957240';
Extract TORDP1:  table pdborcl.tpc.orders_products, sqlpredicate 'where orders_products_id > 1957239 and orders_products_id < 3914481';
Extract TORDP2:  table pdborcl.tpc.orders_products, sqlpredicate 'where orders_products_id > 3914480';

When I run the two extracts, each takes about one minute to unload their data and the load files created are 216 and 220 Mb, respectively.

Report at 2017-06-20 14:31:19 (activity since 2017-06-20 14:30:17) Output to ./dirdat/iload/orders/op: From Table PDBORCL.TPC.ORDERS_PRODUCTS:        #                   inserts:   2935860 Report at 2017-06-20 14:31:45 (activity since 2017-06-20 14:30:44) Output to ./dirdat/iload/orders/pa: From Table PDBORCL.TPC.ORDERS_PRODUCTS:        #                   inserts:   2935861  Directory of D:\GoldenGate\Teradata\dirdat\iload\orders 06/20/2017  02:31 PM       216,420,578 op000000 06/20/2017  02:31 PM       220,380,292 pa000000

Create a new Teradata MultiLoad script:

.LOGTABLE TPC.ORDERS_PRODUCTS_mlog;
.LOGON 192.168.120.215/dbc,dbc;
.BEGIN IMPORT MLOAD TABLES TPC.ORDERS_PRODUCTS SESSIONS 5;
.LAYOUT ORDERS_PRODUCTS;
.FIELD ORDERS_PRODUCTS_ID * VARCHAR(39);
.FIELD ORDERS_ID * VARCHAR(39);
.FIELD PRODUCTS_ID * VARCHAR(39);
.FIELD PRODUCTS_MODEL * VARCHAR(12);
.FIELD PRODUCTS_NAME * VARCHAR(64);
.FIELD PRODUCTS_PRICE * VARCHAR(20);
.FIELD FINAL_PRICE * VARCHAR(20);
.FIELD PRODUCTS_TAX * VARCHAR(12);
.FIELD PRODUCTS_QUANTITY * VARCHAR(3);
.DML LABEL InsertDML;
INSERT INTO TPC.ORDERS_PRODUCTS (
ORDERS_PRODUCTS_ID,
ORDERS_ID,
PRODUCTS_ID,
PRODUCTS_MODEL,
PRODUCTS_NAME,
PRODUCTS_PRICE,
FINAL_PRICE,
PRODUCTS_TAX,
PRODUCTS_QUANTITY
) VALUES (
:ORDERS_PRODUCTS_ID,
:ORDERS_ID,
:PRODUCTS_ID,
:PRODUCTS_MODEL,
:PRODUCTS_NAME,
:PRODUCTS_PRICE,
:FINAL_PRICE,
:PRODUCTS_TAX,
:PRODUCTS_QUANTITY
);
.IMPORT INFILE D:\GoldenGate\Teradata\dirdat\iload\orders\pa000000
FORMAT VARTEXT '|'
DISPLAY ERRORS
LAYOUT ORDERS_PRODUCTS
APPLY InsertDML;
.END MLOAD;
LOGOFF;

Then run the two scripts to load the Teradata table.

D:\GoldenGate\Teradata\dirsql>fastload < floadordersprod.txt

D:\GoldenGate\Teradata\dirsql>mload < mloadordersprod.txt

 

Summary

In this article we performed a zero-downtime data replication implementation from an Oracle 12c Pluggable Database to Teradata using Oracle GoldenGate and Teradata Utilities.

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content