Oracle GoldenGate: Coordinated Apply

Introduction

Oracle GoldenGate (OGG) version 12c introduced the Coordinated Apply. Coordinated Apply provides the ability to spawn multiple OGG Replicat processes from one parameter file. This article discusses configuration options, GGSCI commands, and troubleshooting for Coordinated Apply as of OGG version 12.1.2.0.0

Main Article

OGG 12c introduced the Coordinated Apply; which is a multi-threaded Replicat that performs parallel processing of data into a target database. In this Replicat target table dependencies are computed and handled by a coordinator while SQL statements are applied by the threads.

ogg_coord_applyA Coordinated Apply has a Unified Parameter File, which is read by each process thread and determines the operational configuration of each thread.

Each apply thread is independent of the other apply threads. Each thread opens the OGG Trail for shared read operations and has a unique entry in the OGG Checkpoint Table. Each thread also has a database session for executing SQL statements.

Although each thread functions independently, an unrecoverable error condition on any thread will cause all threads to terminate in the ABEND state.

The Coordinator computes dependencies for process tasks that require full barrier coordination, such as DDL apply, primary key updates, OGG SQLEXEC processing, and OGG Event Management Infrastructure (EMI) processing.

Full barrier coordination is not performed on foreign keys. Parent and child tables must be processed by the same apply thread.

Getting Started With Coordinated Apply

In its simplest form, a Unified Parameter File could look like this:

replicat r_sth
userid oggadmin, password Oracle1
discardfile ./dirrpt/r_sth.dsc, purge
assumetargetdefs
reportcount every 10 minutes, rate
batchsql
map east.*, target south.*;

Anyone running the classic OGG Replicat will already be familiar with this parameter file. It is important to note the five character Group Name, R_STH. Coordinated Apply group names must consist of no more than five alphanumeric characters.

To created the Coordinated Apply Group, specify the option COORDINATED on the GGSCI ADD REPLICAT command.

GGSCI (oelora114) 197> dblogin userid oggadmin, password Oracle1
Successfully logged into database.

GGSCI (oelora114) 198> add replicat r_sth, coordinated, exttrail ./dirdat/pe
REPLICAT (Coordinated) added.

The above ADD command created a group with a maximum of 25 threads, as may be seen via the GGSCI INFO command. A single Coordinated Apply can have up to 500 threads. The number of threads can only be set via the MAXTHREADS option of the ADD [group] COORDINATED command. The number of threads cannot be modified via the ALTER command.

Because the maximum number of threads cannot be altered, it is good practice to specify two times as many threads as initially needed. If your initial configuration calls for 15 threads, set MAXTHREADS 30 when adding the Coordinated Apply. The thread processes are not spawned until assigned, so this will provide room for growth.

GGSCI (oelora114) 202> info r_sth

REPLICAT   R_STH     Last Started 2014-03-10 14:30   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           15546
Log Read Checkpoint  File ./dirdat/pe000017
                     First Record  RBA 5149458

The GGSCI INFO DETAIL command shows one Coordinated Apply process is running.

GGSCI (oelora114) 203> info r_sth detail

REPLICAT   R_STH     Last Started 2014-03-10 14:30   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           15546
Log Read Checkpoint  File ./dirdat/pe000017
                     First Record  RBA 5149458

Lowest Log BSN value: <NULL>

Active Threads: 
ID  Group Name PID   Status   Lag at Chkpt  Time Since Chkpt
1   R_STH001   15559 RUNNING  00:00:00      00:00:06    

Current directory    /home/lpenton/OGG/OGG_East

Report file          /home/lpenton/OGG/OGG_East/dirrpt/R_STH.rpt
Parameter file       /home/lpenton/OGG/OGG_East/dirprm/r_sth.prm
Checkpoint file      /home/lpenton/OGG/OGG_East/dirchk/R_STH.cpr
Checkpoint table     ggchkpt
Process file         /home/lpenton/OGG/OGG_East/dirpcs/R_STH.pcr
Error log            /home/lpenton/OGG/OGG_East/ggserr.log

So far, this is no different than a classic OGG Replicat. But lets look at how Coordinated Apply can allow us to perform parallel data apply without creating an over-complicated OGG environment.

THREAD and THREADRANGE

THREAD is used on a MAP statement to specify the Replicat process thread that will handle filtering, manipulation, target delivery, and error handling for the specified tables. THREADRANGE is used to partition the workload of the target table evenly across a range of Replicat process threads. The partitioning is based on the values of the specified columns. If no column is specified the target table primary key or index is used for partitioning.

THREAD and THREADRANGE may be used with wildcard MAP statements.

Limitations and considerations for use of THREAD and THREADRANGE include:

  1. Do not specify tables that have referential dependencies in a thread range. Use the THREAD option and process all of those tables within the same thread.
  2. Do not use THREADRANGE to partition sequences.
  3. The thread with the lowest thread id always processes barrier transactions.
  4. Any work that is not explicitly assigned to a thread is processed through the thread with the lowest thread id.

To determine how I want to split the workload for my test database, lets first take a look at the sizes of my source tables.

SQL> set lin 300;
COL TABLE_NAME FORMAT A30;
COL NUM_ROWS FORMAT 999,999,999;
COL AVG_ROW_LEN FORMAT 999,999,999;
COL TOTAL_BYTES FORMAT 999,999,999,999,999;

SELECT a.table_name, a.num_rows, a.avg_row_len, b.total_bytes FROM 
(SELECT table_name, num_rows, avg_row_len FROM user_tables) a, 
(SELECT SUM (BYTES) total_bytes, segment_name AS table_name FROM user_extents 
WHERE segment_type = 'TABLE' GROUP BY segment_name) b 
WHERE a.table_name = b.table_name order by num_rows; 

TABLE_NAME                         NUM_ROWS  AVG_ROW_LEN          TOTAL_BYTES
------------------------------ ------------ ------------ --------------------
NEXT_ORDER                                1            8               65,536
NEXT_CUST                                 1            7               65,536
ORDERS_TOTAL                              3           54               65,536
CATEGORIES                               21           44               65,536
CATEGORIES_DESCRIPTION                   21           18               65,536
PRODUCTS_DESCRIPTION                     28          562               65,536
PRODUCTS                                 28           86               65,536
PRODUCTS_TO_CATEGORIES                   28            9               65,536
CUSTOMERS_LKUP                           36           46               65,536
CUSTOMERS                               917           74              131,072
CUSTOMERS_INFO                          917           37               65,536

TABLE_NAME                         NUM_ROWS  AVG_ROW_LEN          TOTAL_BYTES
------------------------------ ------------ ------------ --------------------
ORDERS                               22,001          311            8,388,608
ORDERS_STATUS_HISTORY                22,002           63            2,097,152
ORDERS_PRODUCTS                     288,987           59           20,971,520
MILLITABLE                          698,974          148          122,683,392

15 rows selected.

In my test Order Entry database, the busiest tables are ORDERS, ORDERS_STATUS_HISTORY, and ORDERS_PRODUCTS. The table MILLITABLE is loaded via an ETL batch process. Given this information, I have created a new Coordinated Apply Group, R_NTH, with the following configuration:

replicat r_nth
userid oggadmin, password Oracle1
discardfile ./dirrpt/r_nth.dsc, purge
assumetargetdefs
reportcount every 10 minutes, rate
batchsql
gettruncates

map east.orders, target north.orders, thread(1);

map east.orders_products, target north.orders_products, threadrange(2-3);
map east.orders_status_history, target north.orders_status_history, threadrange(2-3);

map east.millitable, target north.millitable, threadrange(4-6);

map east.*, target north.*, thread(20);

When setting up explicit and wildcard MAP statements, the order is important. In OGG 12c duplicate table mapping is turned off by default. Because MAP statements are reconciled in the order of placement in the parameter file, wildcard MAP statements must always be last. So in the above parameter file, the tables ORDER, ORDERS_PRODUCTS, ORDERS_STATUS_HISTORY, and MILLITABLE will be excluded automatically from the wildcard MAP, and a warning message written to the R_NTH020.rpt file:

WARNING OGG-02081  Detected duplicate TABLE/MAP entry for source table EAST.ORDERS and target 
table north.ORDERS. Using prior TABLE/MAP specification.

WARNING OGG-02081  Detected duplicate TABLE/MAP entry for source table EAST.ORDERS_PRODUCTS and 
target table north.ORDERS_PRODUCTS. Using prior TABLE/MAP specification.

WARNING OGG-02081  Detected duplicate TABLE/MAP entry for source table 
EAST.ORDERS_STATUS_HISTORY and  target table north.ORDERS_STATUS_HISTORY. Using prior TABLE/MAP 
specification.

When the Group is running, we see seven process threads created:

GGSCI (oelora114) 14> info r_nth detail

REPLICAT   R_NTH     Last Started 2014-03-11 09:25   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:02:52 (updated 00:00:03 ago)
Process ID           4805
Log Read Checkpoint  File ./dirdat/ej000010
                     2014-03-11 09:22:38.007162  RBA 21917

Lowest Log BSN value: (requires database login)

Active Threads: 
ID  Group Name PID   Status   Lag at Chkpt  Time Since Chkpt
1   R_NTH001   4818  RUNNING  00:00:00      00:00:03    
2   R_NTH002   4819  RUNNING  00:00:00      00:00:03    
3   R_NTH003   4820  RUNNING  00:00:00      00:00:03    
4   R_NTH004   4821  RUNNING  00:00:00      00:00:03    
5   R_NTH005   4822  RUNNING  00:00:00      00:00:03    
6   R_NTH006   4823  RUNNING  00:00:00      00:00:03    
20  R_NTH020   4824  RUNNING  00:00:00      00:00:00

You are probably asking why I assigned the wildcard MAP to Thread 20. This was personal preference as it allows me to quickly modify my parameter file THREAD settings in the event I need to make an on-the-fly change. For example, suppose there is a large batch load done on the table MILLITABLE. We can make a quick change to partition that table across THREADRANGE(4-9) without having to adjust any other MAP statements; thus eliminating a possible error condition. Likewise, I can now add additional MAP statements to adjust the workload for any tables processed via the wildcard MAP.

When determining the number of threads to create, consider the impact the processes may have on the server. Each thread process opens the OGG Trail file for read operations. The trail being processed by the R_NTH Coordinated Apply shows eight processes running against it; one Extract and seven Replicats:

[lpenton@oelora114 ~]$ lsof /home/lpenton/OGG/OGG_East/dirdat/ej000010
COMMAND   PID    USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
extract  4000 lpenton   20uW  REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4818 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4819 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4820 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4821 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4822 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4823 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010
replicat 4824 lpenton   16r   REG  253,0    21917 4457701 /home/lpenton/OGG/OGG_East/dirdat/ej000010

Too many processes accessing the OGG Trail files could result in disk level contention; which would adversely affect the Extract process writing to the Trail and the Replicat processes reading from it. The maximum number of Replicat threads will depend upon the disk subsystem and IO activity. Likewise, partitioning a table across too many apply threads could result in deadlocks. So it is always best practice to start small, test your configuration, and increase the number of process threads as needed.

Group Reports

Each Replicat thread will have a its own set of OGG Report and Discard files.

The report files are named [GROUP][THREAD][REPORT NUMBER].rpt and the discard files are named [ASSIGNED NAME][THREAD].[ASSIGNED SUFFIX]. Since my parameter file contains the setting discardfile ./dirrpt/r_nth.dsc, the report and discard files created at Group start up were:

[lpenton@oelora114 dirrpt]$ ls -l R_N*
-rw-r----- 1 lpenton lpenton 3247 Mar 11 10:22 R_NTH001.rpt
-rw-r----- 1 lpenton lpenton 3248 Mar 11 10:22 R_NTH002.rpt
-rw-r----- 1 lpenton lpenton 3248 Mar 11 10:22 R_NTH003.rpt
-rw-r----- 1 lpenton lpenton 3248 Mar 11 10:22 R_NTH004.rpt
-rw-r----- 1 lpenton lpenton 3248 Mar 11 10:22 R_NTH005.rpt
-rw-r----- 1 lpenton lpenton 3247 Mar 11 10:22 R_NTH006.rpt
-rw-r----- 1 lpenton lpenton 3247 Mar 11 10:22 R_NTH020.rpt
-rw-r----- 1 lpenton lpenton 3090 Mar 11 10:22 R_NTH.rpt
[lpenton@oelora114 dirrpt]$ ls -l r_n*
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth001.dsc
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth002.dsc
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth003.dsc
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth004.dsc
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth005.dsc
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth006.dsc
-rw-r----- 1 lpenton lpenton 118 Mar 11 10:22 r_nth020.dsc
-rw-r----- 1 lpenton lpenton 115 Mar 11 10:22 r_nth.dsc

The GGSCI command VIEW REPORT [GROUP][THREAD] will display the report file for the specified Replicat thread. If I wanted to review the report file for thread 2 of my Coordinated Apply Group, I execute the command:

GGSCI (oelora114) 45> view report r_nth002

***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
 Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
   Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:50:41

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2014-03-11 14:41:40
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Jul 17 10:58:36 PDT 2013, Release 2.6.32-400.29.2.el5uek
Node: oelora114
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 4315

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2014-03-11 14:41:41  INFO    OGG-03059  Operating system character set identifie
d as UTF-8.

2014-03-11 14:41:41  INFO    OGG-02695  ANSI SQL parameter syntax is used for pa
rameter parsing.
replicat r_nth
userid oggadmin, password *******
discardfile ./dirrpt/r_nth.dsc, purge
assumetargetdefs
reportcount every 10 minutes, rate
batchsql
gettruncates
map east.orders, target north.orders, thread(1);
map east.orders_products, target north.orders_products, threadrange(2-3);
map east.orders_status_history, target north.orders_status_history, threadrange(2-3);
map east.millitable, target north.millitable, threadrange(4-6);
map east.*, target north.*, thread(20);

2014-03-11 14:41:41  INFO    OGG-06451  Triggers will be suppressed by default.

2014-03-11 14:41:41  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /home/lpenton/OGG/OGG_East/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                                2G
CACHEPAGEOUTSIZE (default):               8M
PROCESS VM AVAIL FROM OS (min):           4G
CACHESIZEMAX (strict force to disk):   3.41G

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Database Language and Character Set:
NLS_LANGUAGE     = "AMERICAN" 
NLS_TERRITORY    = "AMERICA" 
NLS_CHARACTERSET = "WE8MSWIN1252" 
Replicat session info: SID 40, Serial # 47
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************

Opened trail file ./dirdat/ej000014 at 2014-03-11 14:41:42

2014-03-11 14:41:42  INFO    OGG-03522  Setting session time zone to source data
base time zone 'GMT'.

2014-03-11 14:41:42  INFO    OGG-03506  The source database character set, as de
termined from the trail file, is we8mswin1252.
Processed extract process graceful restart record at seq 14, rba 1401.

2014-03-11 14:41:42  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

Wildcard MAP resolved (entry east.*):
  map "EAST"."CATEGORIES", target north."CATEGORIES", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."CATEGORIES_DESCRIPTION", target north."CATEGORIES_DESCRIPTION", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."PRODUCTS", target north."PRODUCTS", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."PRODUCTS_DESCRIPTION", target north."PRODUCTS_DESCRIPTION", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."PRODUCTS_TO_CATEGORIES", target north."PRODUCTS_TO_CATEGORIES", thread(20);

2014-03-11 14:41:43  INFO    OGG-01971  The previous message, 'INFO OGG-06010', repeated 4 times.

2014-03-11 14:41:43  WARNING OGG-06439  No unique key is defined for table PRODU
CTS_TO_CATEGORIES. All viable columns will be used to represent the key, but may
 not guarantee uniqueness. KEYCOLS may be used to define the key.
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

2014-03-11 14:41:43  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

Wildcard MAP resolved (entry east.*):
  map "EAST"."CUSTOMERS", target north."CUSTOMERS", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."CUSTOMERS_INFO", target north."CUSTOMERS_INFO", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

MAP resolved (entry east.orders):
  map "EAST"."ORDERS", target north.orders, thread(1);
Skipping this mapping because it will only be applied by R_NTH001

Wildcard MAP resolved (entry east.*):
  map "EAST"."ORDERS", target north."ORDERS", thread(20);

2014-03-11 14:41:45  INFO    OGG-01971  The previous message, 'INFO OGG-06010', 
repeated 3 times.

2014-03-11 14:41:45  WARNING OGG-02081  Detected duplicate TABLE/MAP entry for s
ource table EAST.ORDERS and target table north.ORDERS. Using prior TABLE/MAP spe
cification.

2014-03-11 14:41:45  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

MAP resolved (entry east.orders_products):
  map "EAST"."ORDERS_PRODUCTS", target north.orders_products, threadrange(2-3);
Using following columns in default map by name:
  ORDERS_PRODUCTS_ID, ORDERS_ID, PRODUCTS_ID, PRODUCTS_MODEL, 
  PRODUCTS_NAME, PRODUCTS_PRICE, FINAL_PRICE, PRODUCTS_TAX, 
  PRODUCTS_QUANTITY
Using the following key columns for target table NORTH.ORDERS_PRODUCTS: ORDERS_P
RODUCTS_ID.

Wildcard MAP resolved (entry east.*):
  map "EAST"."ORDERS_PRODUCTS", target north."ORDERS_PRODUCTS", thread(20);

2014-03-11 14:41:45  INFO    OGG-01971  The previous message, 'INFO OGG-06010', 
repeated 1 times.

2014-03-11 14:41:45  WARNING OGG-02081  Detected duplicate TABLE/MAP entry for s
ource table EAST.ORDERS_PRODUCTS and target table north.ORDERS_PRODUCTS. Using p
rior TABLE/MAP specification.

2014-03-11 14:41:45  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

MAP resolved (entry east.orders_status_history):
  map "EAST"."ORDERS_STATUS_HISTORY", target north.orders_status_history, thread
range(2-3);
Using following columns in default map by name:
  ORDERS_STATUS_HISTORY_ID, ORDERS_ID, ORDERS_STATUS, DATE_ADDED, 
  CUSTOMER_NOTIFIED, COMMENTS
Using the following key columns for target table NORTH.ORDERS_STATUS_HISTORY: OR
DERS_STATUS_HISTORY_ID, ORDERS_ID, DATE_ADDED.

Wildcard MAP resolved (entry east.*):
  map "EAST"."ORDERS_STATUS_HISTORY", target north."ORDERS_STATUS_HISTORY", thread(20);

2014-03-11 14:41:46  INFO    OGG-01971  The previous message, 'INFO OGG-06010', 
repeated 1 times.

2014-03-11 14:41:46  WARNING OGG-02081  Detected duplicate TABLE/MAP entry for s
ource table EAST.ORDERS_STATUS_HISTORY and target table north.ORDERS_STATUS_HIST
ORY. Using prior TABLE/MAP specification.

2014-03-11 14:41:46  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

Wildcard MAP resolved (entry east.*):
  map "EAST"."ORDERS_TOTAL", target north."ORDERS_TOTAL", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."NEXT_CUST", target north."NEXT_CUST", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."NEXT_ORDER", target north."NEXT_ORDER", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

Wildcard MAP resolved (entry east.*):
  map "EAST"."CUSTOMERS_LKUP", target north."CUSTOMERS_LKUP", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

2014-03-11 14:42:16  INFO    OGG-01971  The previous message, 'INFO OGG-06010', 
repeated 3 times.

In the above report file, there is some information presented that may look odd:

2014-03-11 14:41:46  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

Wildcard MAP resolved (entry east.*):
  map "EAST"."NEXT_ORDER", target north."NEXT_ORDER", thread(20);
Skipping this mapping because it will only be applied by R_NTH001, R_NTH020

The first message implies some DDL is to be processed for the tables specific to R_NTH002; but this Replicat process thread is ignoring the data because it is not the default partition. Why is that?

The wildcard map is evaluated and thread 20 is identified as the Replicat process thread for applying data for the specified table. But then, why does the message say it will also be applied by R_NTH001?

The answer for both of these questions is the same. Earlier we mentioned that the Coordinator thread handles coordination of items requiring full barrier coordination. The lowest numbered thread is assigned as the Coordinator. So in this case Replicat process thread 1, R_NTH001, has full barrier coordination duties and will be responsible for primary key update coordination and table truncate operations since GETTRUNCATES is active. Table truncates are considered DDL operations.

This may be fine in a test environment, but for production having the lowest numbered thread process DML for a specific set of tables and perform coordination tasks may not be optimal.

USEDEDICATEDCOORDINATIONTHREAD

The parameter USEDEDICATEDCOORDINATIONTHREAD to forces Replicat to create a dedicated coordination thread for barrier transaction apply. This thread id is always 0. Using a dedicated thread for barrier transactions allows for the processing of barrier transactions without impacting the performance of the thread with the lowest id and provides an accurate view in OGG statistics of the number of barrier events and exposes the amount of work that is performed serially.

Best practices dictates the use of a dedicated coordination thread for busy production environments where DDL is being applied and tables exist without keys or indexes.

To create the dedicated coordination thread in my test environment, I modify the parameter file as follows:

GGSCI (oelora114) 3> edit param r_nth

replicat r_nth
userid oggadmin, password Oracle1
discardfile ./dirrpt/r_nth.dsc, purge
assumetargetdefs
reportcount every 10 minutes, rate
batchsql
gettruncates

--
-- Maintain a dedicated coordination thread (thread 0) 
-- to apply barrier transactions.
--
USEDEDICATEDCOORDINATIONTHREAD

map east.orders, target north.orders, thread(1);

map east.orders_products, target north.orders_products, threadrange(2-3);
map east.orders_status_history, target north.orders_status_history, threadrange(2-3);

map east.millitable, target north.millitable, threadrange(4-6);

map east.*, target north.*, thread(20);

When the Coordinated Apply is started, we see thread R_NTH000 is created.

GGSCI (oelora114) 8> info r_nth detail

REPLICAT   R_NTH     Last Started 2014-03-12 08:40   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Process ID           3897
Log Read Checkpoint  File ./dirdat/ej000014
                     First Record  RBA 0

Lowest Log BSN value: (requires database login)

Active Threads: 
ID  Group Name PID   Status   Lag at Chkpt  Time Since Chkpt
0   R_NTH000   3910  RUNNING  18:01:36      00:00:00    
1   R_NTH001   3911  RUNNING  18:01:36      00:00:00    
2   R_NTH002   3912  RUNNING  18:01:36      00:00:00    
3   R_NTH003   3913  RUNNING  18:01:36      00:00:00    
4   R_NTH004   3914  RUNNING  18:01:36      00:00:00    
5   R_NTH005   3915  RUNNING  18:01:36      00:00:00    
6   R_NTH006   3916  RUNNING  18:01:36      00:00:00    
20  R_NTH020   3917  RUNNING  18:01:36      00:00:00

The GGSCI STATS command, and report file statistics will show counts for all barrier transactions applied by the Coordinator.

GGSCI (oelora114) 19> stats replicat r_nth000, total, daily

Sending STATS request to REPLICAT R_NTH000 ...

Start of Statistics at 2014-03-12 08:49:06.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                        34.00
        Mapped operations                                 34.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00
        Errors                                             0.00
        Retried errors                                     0.00
        Discarded errors                                   0.00
        Ignored errors                                     0.00

DDL Apply

For databases where DDL capture is supported, DDL apply is enabled by default. However, I want to limit what DDL is applied by my Coordinated Apply Group and I want to see what DDL is executed, so I modify my Unified Parameter File as follows:

GGSCI (oelora114) 21> edit param r_nth

replicat r_nth
userid oggadmin, password Oracle1
discardfile ./dirrpt/r_nth.dsc, purge
assumetargetdefs
reportcount every 10 minutes, rate
batchsql
gettruncates

ddl include all objname north.*
DDLOPTIONS REPORT

--
-- Maintain a dedicated coordination thread (thread 0) to apply barrier 
-- transactions.
--
USEDEDICATEDCOORDINATIONTHREAD

map east.orders, target north.orders, thread(1);

map east.orders_products, target north.orders_products, threadrange(2-3);
map east.orders_status_history, target north.orders_status_history, threadrange(2-3);

map east.millitable, target north.millitable, threadrange(4-6);

map east.*, target north.*, thread(20);

DDL operations in the OGG Trail will be processed by the Coordinator thread, R_NTH000, and the DML for any new tables will be processes by Replicat process thread 20 (R_NTH020) because of the wildcard MAP. Since DDLOPTIONS REPORT is active, the report file R_NTH020 will contain:

2014-03-12 09:23:11  INFO    OGG-00482  DDL found, operation [create table nopktbl (
   ColA  TIMESTAMP (6), 
   ColB  VARCHAR2(100 CHAR), 
   ColC  TIMESTAMP (6), 
   ColD  NUMBER (10,2), 
   ColE  VARCHAR2(50 CHAR))  (size 161)].

2014-03-12 09:23:11  INFO    OGG-00489  DDL is of mapped scope, after mapping new 
operation [create table "NORTH"."NOPKTBL" (
   ColA  TIMESTAMP (6), 
   ColB  VARCHAR2(100 CHAR), 
   ColC  TIMESTAMP (6), 
   ColD  NUMBER (10,2), 
   ColE  VARCHAR2(50 CHAR))  (size 171)].

2014-03-12 09:23:11  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

2014-03-12 09:23:11  INFO    OGG-00482  DDL found, operation [create table testtbl (
   ColA  NUMBER (15,0) not null, 
   ColB  VARCHAR2(250 CHAR), 
   ColC  TIMESTAMP (6),
CONSTRAINT testtbl_pk PRIMARY KEY (ColA)
)  (size 159)].

2014-03-12 09:23:11  INFO    OGG-00489  DDL is of mapped scope, after mapping new 
operation [create table "NORTH"."TESTTBL" (
   ColA  NUMBER (15,0) not null, 
   ColB  VARCHAR2(250 CHAR), 
   ColC  TIMESTAMP (6),
CONSTRAINT testtbl_pk PRIMARY KEY (ColA)
)  (size 169)].

2014-03-12 09:23:11  INFO    OGG-06010  Not the default Partition, not applying 
DDL record. This is informational only.

Wildcard MAP resolved (entry east.*):
  map "EAST"."NOPKTBL", target north."NOPKTBL", thread(20);

2014-03-12 09:23:12  WARNING OGG-06439  No unique key is defined for table NOPKT
BL. 
All viable columns will be used to represent the key, but may not guarantee 
uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
  COLA, COLB, COLC, COLD, COLE
Using the following key columns for target table NORTH.NOPKTBL: COLA, COLB, COLC
, COLD, COLE.

Wildcard MAP resolved (entry east.*):
  map "EAST"."TESTTBL", target north."TESTTBL", thread(20);
Using following columns in default map by name:
  COLA, COLB, COLC
Using the following key columns for target table NORTH.TESTTBL: COLA.

The report file for the Coordinator thread, R_NTH000, will show the DDL applied with barrier coordination.

2014-03-12 09:23:05  INFO    OGG-00482  DDL found, operation [create table nopktbl (
   ColA  TIMESTAMP (6), 
   ColB  VARCHAR2(100 CHAR), 
   ColC  TIMESTAMP (6), 
   ColD  NUMBER (10,2), 
   ColE  VARCHAR2(50 CHAR))  (size 161)].

2014-03-12 09:23:05  INFO    OGG-00489  DDL is of mapped scope, after mapping new 
operation [create table "NORTH"."NOPKTBL" (
   ColA  TIMESTAMP (6), 
   ColB  VARCHAR2(100 CHAR), 
   ColC  TIMESTAMP (6), 
   ColD  NUMBER (10,2), 
   ColE  VARCHAR2(50 CHAR))  (size 171)].

2014-03-12 09:23:11  INFO    OGG-00487  DDL operation included [include all objname north.*], 
optype [CREATE], objtype [TABLE], objowner [NORTH], objname [NOPKT
BL].

2014-03-12 09:23:11  INFO    OGG-01407  Setting current schema for DDL operation
 to [NORTH].

2014-03-12 09:23:11  INFO    OGG-00484  Executing DDL operation.

2014-03-12 09:23:11  INFO    OGG-00483  DDL operation successful.

2014-03-12 09:23:11  INFO    OGG-01408  Restoring current schema for DDL operation
 to [oggadmin].

2014-03-12 09:23:11  INFO    OGG-00482  DDL found, operation [create table testtbl (
   ColA  NUMBER (15,0) not null, 
   ColB  VARCHAR2(250 CHAR), 
   ColC  TIMESTAMP (6),
CONSTRAINT testtbl_pk PRIMARY KEY (ColA)
)  (size 159)].

2014-03-12 09:23:11  INFO    OGG-00489  DDL is of mapped scope, after mapping new 
operation [create table "NORTH"."TESTTBL" (
   ColA  NUMBER (15,0) not null, 
   ColB  VARCHAR2(250 CHAR), 
   ColC  TIMESTAMP (6),
CONSTRAINT testtbl_pk PRIMARY KEY (ColA)
)  (size 169)].

2014-03-12 09:23:11  INFO    OGG-00487  DDL operation included [include all 
objname north.*], optype [CREATE], objtype [TABLE], objowner [NORTH], objname 
[TESTTBL].

2014-03-12 09:23:11  INFO    OGG-01407  Setting current schema for DDL operation
 to [NORTH].

2014-03-12 09:23:11  INFO    OGG-00484  Executing DDL operation.

2014-03-12 09:23:11  INFO    OGG-00483  DDL operation successful.

2014-03-12 09:23:11  INFO    OGG-01408  Restoring current schema for DDL operation
 to [oggadmin].

Troubleshooting

In my test environment I replay OGG Trails, which can lead to some interesting ABEND situations. Because each Coordinated Apply thread has its own report file, we need to determine which process encountered an unrecoverable condition. First view the main group report file and look for an ERROR statement just before the Run Time Statistics section. This will indicate which Replicat process thread caused the ABEND.

GGSCI (oelora114) 65> status r_nth
REPLICAT R_NTH: ABENDED

GGSCI (oelora114) 66> view report r_nth
***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
 Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
   Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:50:41

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2014-03-12 09:54:18
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Jul 17 10:58:36 PDT 2013, Release 2.6.32-400.29.2.el5uek
Node: oelora114
Machine: x86_64

.
.
.
2014-03-12 10:06:41  ERROR   OGG-06004  Coordinated Replicat thread R_NTH020 
exited unexpectedly. Please check the report file of R_NTH020 for more details.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

View the designated report file to see the actual error.

GGSCI (oelora114) 80> view report r_nth020
.
.
.
2014-03-12 10:06:41  WARNING OGG-00869  OCI Error ORA-00001: unique constraint (
NORTH.TESTTBL_PK) violated (status = 1), SQL <INSERT INTO "NORTH"."TESTTBL" ("CO
LA","COLB","COLC") VALUES (:a0,:a1,:a2)>.

2014-03-12 10:06:41  WARNING OGG-01004  Aborted grouped transaction on 'NORTH.TE
STTBL', Database error 1 (OCI Error ORA-00001: unique constraint (NORTH.TESTTBL_
PK) violated (status = 1), SQL <INSERT INTO "NORTH"."TESTTBL" ("COLA","COLB","CO
LC") VALUES (:a0,:a1,:a2)>).

2014-03-12 10:06:41  WARNING OGG-01003  Repositioning to rba 1461 in seqno 17.

2014-03-12 10:06:41  WARNING OGG-01154  SQL error 1 mapping EAST.TESTTBL to NORT
H.TESTTBL OCI Error ORA-00001: unique constraint (NORTH.TESTTBL_PK) violated (st
atus = 1), SQL <INSERT INTO "NORTH"."TESTTBL" ("COLA","COLB","COLC") VALUES (:a0
,:a1,:a2)>.

.
.
.
2014-03-12 10:06:41  ERROR   OGG-01296  Error mapping from EAST.TESTTBL to NORTH
.TESTTBL.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

In the R_NTH020 report file, we see the falure was caused by a unique constraint violation for a record being applied to the table TESTTBL. As with classic Replicat, I address the data issue and restart Coordinated Apply to continue processing.

Summary

Coordinated Apply provides a way to streamline Oracle GoldenGate environments by having multiple Replicat processes spawn from one Unified Parameter File. This article presented a high level overview of Cordinated Apply configuration, processing, and troubleshooting.

Add Your Comment