36 Preparing the System for Oracle GoldenGate

This chapter contains guidelines for preparing the system to support Oracle GoldenGate:

Topics:

• Choosing the Oracle TimesTen Connectivity Type
  
• Setting the Environment Variables
  
• Configuring the TimesTen ODBC Connectivity
  
• Preparing Tables for Processing
  
• Configuring Oracle GoldenGate
  Learn about the prerequisites and tasks for configuring Oracle GoldenGate Replicat for Oracle TimesTen database.

Choosing the Oracle TimesTen Connectivity Type

Oracle TimesTen supports two distinct connectivity types for tools, utilities and applications; Direct mode and Client-Server mode.

Direct mode

Direct mode is a highly optimised local connectivity mechanism that eliminates interprocess communication (IPC) between the application and the database. It provides low latency and high throughput with low overhead. With Direct mode, the client application and the database must reside in the same host.

Client-Server mode

Client-Server mode is a traditional TCP/IP based connection mechanism. In this mode, the client application may reside in the same host as the database but more commonly it will execute on a different host and connect over the network. Client-Server mode has lower performance than Direct mode due to additional overhead and network round trips.

Oracle GoldenGate supports both types of connectivity for Oracle TimesTen, so you can use whichever is most appropriate, based on your requirements.

Setting the Environment Variables

Ensure that the required system environment variables are sourced before proceeding. The correct environment settings are needed for all sessions or processes that will interact with Oracle TimesTen. Every Oracle TimesTen instance (Server and Client) contains a script for setting the required environment variables. This script is located in instance_home_dir/bin and is named ttenv.[c]sh. It should always be dotted or sourced and never executed directly.

Example of setting the bash shell environment for a TimesTen instance homed in /instancepath/tt181:

source /instancepath/tt181/bin/ttenv.sh

Although it's possible to set the required environment variables manually, it is not recommended. Using the script:

• Ensures that all the necessary environment variables (there are several) are correctly set.

• Insulates you from the introduction of new variables in future Oracle TimesTen releases.

Configuring the TimesTen ODBC Connectivity

Oracle GoldenGate for TimesTen connects to TimesTen using the ODBC API (TimesTen's native API). ODBC connectivity defines the concept of a Data Source Name (DSN). A DSN is a logical name which applications use to specify the parameters to be used for connecting to a target database.

When using Oracle GoldenGate for TimesTen, you will specify the DSN of the target TimesTen database in various Oracle GoldenGate configuration settings, such as the SOURCEDB clause of the DBLOGIN command. For example:

DBLOGIN SOURCEDB database, USERIDALIAS useralias

Here, the value given for database will be the DSN of the target TimesTen database.

When using the Direct mode connectivity, connections must reference a server DSN defined in the sys.odbc.ini file of the Oracle TimesTen instance that hosts the database (the server instance).

When using the Client-Server mode, connections must reference a client DSN defined in the sys.odbc.ini file of either the Oracle TimesTen instance that manages the database (the server instance) or, more commonly, in the sys.odbc.ini of an Oracle TimesTen client instance, such as an Oracle GoldenGate hub server.

For information on defining Oracle TimesTen server and client DSNs, refer to TimesTen In-Memory Database Operations Guide.

Here is an example of the sys.odbc.ini entries to define a client DSN (myttdbcs) that connects to a database identified by the server DSN myttdb located on the host tthost1.mydomain.com. The TimesTen server's default listener port on that host is 6625.

[ODBC Data Sources]
myttdbcs=TimesTen 18.1 Client Driver
[myttdbcs]
TTC_SERVER=tthost1.mydomain.com/6625
TTC_SERVER_DSN=myttdb
ConnectionCharacterSet=AL32UTF8

Preparing Tables for Processing

This section describes the table attributes you must address in an Oracle GoldenGate environment with TimesTen.

Topics:

• Removing ON DELETE CASCADE Contraints
  
• Ensuring Row Uniqueness for Tables
  

Removing ON DELETE CASCADE Contraints

If a target table in Oracle TimesTen has a foreign key, which specifies the ON DELETE CASCADE clause, and if the table that is the target of that foreign key is also a target for Oracle GoldenGate replication then you must remove the ON DELETE CASCADE clause from the foreign key definition to avoid errors.

Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.

1. A delete is issued for emp_src.

2. It cascades a delete to salary_src.

3. Oracle GoldenGate sends both deletes to the target.

4. The parent delete arrives first and is applied to emp_targ.

5. The parent delete cascades a delete to salary_targ.

6. The cascaded delete from salary_src is applied to salary_targ.

7. The row cannot be located because it was already deleted in step 5.

Oracle TimesTen does not support the disabling of foreign key constraints or on delete cascade constraints. To remove the ON DELETE CASCADE, you must either drop the table and recreate it without the ON DELETE CASCADE clause or you can use ALTER TABLE to remove the foreign key constraint and recreate it without the ON DELETE CASCADE clause.

Ensuring Row Uniqueness for Tables

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

1. Primary key

2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.

   Note:

   If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

4. If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.

Configuring Oracle GoldenGate

Learn about the prerequisites and tasks for configuring Oracle GoldenGate Replicat for Oracle TimesTen database.

Topics:

• Configuring Oracle GoldenGate Replicat
  
• Additional Oracle GoldenGate Configuration Guidelines
  

Configuring Oracle GoldenGate Replicat

This section describes the Replicat parameters that are required for most target database types. For additional parameters that may be required, see the Oracle GoldenGate installation and configuration documentation for your target database and the Reference for Oracle GoldenGate.

Perform these steps on the target replication server or target database.

1. Configure the Manager process according to the instructions in Administering Oracle GoldenGate.
2. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the local trail.
3. Create a Replicat checkpoint table. There are multiple options for this purpose, see Administering Oracle GoldenGate.

   DBLOGIN SOURCEDB myttdbcs USERIDLIAS useralias
   ADD CHECKPOINTTABLE owner.oggcheckpointtable

4. Create a Replicat. For documentation purposes, this Replicat is called reptt.

   ADD REPLICAT reptt, EXTTRAIL ./dirdat/remote_trail, CHECKPOINTTABLE owner.oggcheckpointtable

   Use the EXTTRAIL argument to link the Replicat to the remote trail that you specified with the Data Pump Extract on the source Oracle GoldenGate installation.

5. Use the EDIT PARAMS command to create a parameter file for the Replicat group. Include the parameters shown in Example 33-1 plus any others that apply to your database environment.

Example 36-1 Parameters for the Replicat Group

REPLICAT reptt
-- Specify database login information as needed for the database: 
TARGETDB myttdbcs, USERIDALIAS useralias
-- Specify tables for delivery: 
MAP owner.sourcetable, TARGET owner.targettable;

Additional Oracle GoldenGate Configuration Guidelines

The following are additional considerations to make once you have installed and configured your Oracle GoldenGate environment.

Performing Initial Synchronization

Perform an initial synchronization of the source and target data before using Oracle GoldenGate to transmit transactional changes for the first time to configure an initial load. See Initial Synchronization in Administering Oracle GoldenGate.