16 Preparing the System for Oracle GoldenGate

This chapter contains steps to take so that the database with which Oracle GoldenGate interacts is correctly configured to support Oracle GoldenGate capture and delivery. Some steps apply only to a source system, some only to a target, and some to both.

Topics:

Parent topic: Using Oracle GoldenGate with SQL Server

16.1 Configuring a Database Connection

This section contains instructions for setting up the Extract and Replicat connections to a SQL Server database.

Parent topic: Preparing the System for Oracle GoldenGate

16.1.1 Configuring an Extract Database Connection

Extract connects to a source SQL Server database through an ODBC (Open Database Connectivity) connection. To create this connection, you set up a data source name (DSN) through the Data Sources (ODBC) control panel. See Configuring an ODBC Connection for instructions.

When using Transport Layer Security (TLS) 1.2 in your database environment, include the following parameter in Extract to force it to use a supported connection protocol:

DBOPTIONS DRIVER SQLNCLI11

Parent topic: Configuring a Database Connection

16.1.2 Configuring a Replicat Database Connection

Replicat can connect to the target database to perform DML operations in the following ways:

  • Through ODBC.

  • Through OLE DB. This is the default and provides slightly better performance than using ODBC.

  • Through OLE DB as the SQL Server replication user. NOT FOR REPLICATION must be set on IDENTITY columns, foreign key constraints, and triggers.

    Note:

    Because Replicat always uses ODBC to query for metadata, you must configure a target ODBC connection.

Before you select a method to use, review the following guidelines and procedures to evaluate the advantages and disadvantages of each.

Parent topic: Configuring a Database Connection

16.1.2.1 Using ODBC or Default OLE DB

If Replicat connects through ODBC or through the default OLE DB connection, the following limitations apply:

  • To keep IDENTITY columns identical on source and target when using ODBC or default OLE DB, Replicat creates special operations in its transaction to ensure that the seeds are incremented on the target. These steps may reduce delivery performance.

  • You must adjust or disable triggers and constraints on the target tables to eliminate the potential for redundant operations.

To use Replicat with either ODBC or OLE DB, follow these steps:

  1. To use ODBC exclusively, include the DBOPTIONS parameter with the USEODBC option in the Replicat parameter file. (To use the default OLE DB connection, no parameter is required.)
  2. Disable triggers and constraints on the target tables. See Disabling Triggers and Cascade Constraints.
  3. To use IDENTITY columns in a bidirectional SQL Server configuration, define the IDENTITY columns to have an increment value equal to the number of servers in the configuration, with a different seed value for each one. For example, a two-server installation would be as follows:

    • Sys1 sets the seed value at 1 with an increment of 2.

    • Sys2 sets the seed value at 2 with an increment of 2.

    A three-server installation would be as follows:

    • Sys1 sets the seed value at 1 with an increment of 3.

    • Sys2 sets the seed value at 2 with an increment of 3.

    • Sys3 sets the seed value at 3 with an increment of 3.

  4. Configure an ODBC data source. See Configuring an ODBC Connection.

Note:

OLE DB uses the ODBC connection settings to derive connection information for OLE DB together with information on which driver to use.

16.1.2.2 Using OLE DB with USEREPLICATIONUSER

If Replicat connects as the SQL Server replication user through OLE DB with the USEREPLICATIONUSER option, and NOT FOR REPLICATION is enabled for IDENTITY, triggers, and foreign key constraints, the following benefits and limitations apply.

  • IDENTITY seeds are not incremented when Replicat performs an insert. For SQL Server bidirectional configurations, stagger the seed and increment values like the example in Step 3 of the previous section.

  • Triggers are disabled for the Replicat user automatically on the target to prevent redundant operations. However triggers fire on the target for other users.

  • Foreign key constraints are not enforced on the target for Replicat transactions. CASCADE updates and deletes are not performed. These, too, prevent redundant operations.

  • CHECK constraints are not enforced on the target for Replicat transactions. Even though these constraints are enforced on the source before data is captured, consider whether their absence on the target could cause data integrity issues.

    Note:

    Normal IDENTITY, trigger, and constraint functionality remains in effect for any users other than the Replicat replication user.

To use Replicat with USEREPLICATIONUSER, follow these steps:

  1. In SQL Server Management Studio (or other interface) set the NOT FOR REPLICATION flag on the following objects. For active-passive configurations, set it only on the passive database. For active-active configurations, set it on both databases.

    • Foreign key constraints

    • Check constraints

    • IDENTITY columns

    • Triggers (requires textual changes to the definition; see the SQL Server documentation for more information.)

  2. Partition IDENTITY values for bidirectional configurations.
  3. In the Replicat MAP statements, map the source tables to appropriate targets, and map the child tables that the source tables reference with triggers or foreign-key cascade constraints. Triggered and cascaded child operations are replicated by Oracle GoldenGate, so the referenced tables must be mapped to appropriate targets to preserve data integrity. Include the same parent and child source tables in the Extract TABLE parameters.

    Note:

    If referenced tables are omitted from the MAP statements, no errors alert you to integrity violations, such as if a row gets inserted into a table that contains a foreign key to a non-replicated table.

  4. In the Replicat parameter file, include the DBOPTIONS parameter with the USEREPLICATIONUSER option.
  5. Configure an ODBC data source. See Configuring an ODBC Connection.

16.1.3 Configuring an ODBC Connection

Follow these instructions to create a SQL Server system data source name (DSN) for a source SQL Server database and for a target SQL Server database. A DSN stores information about how to connect to a SQL Server database through ODBC (Open Database Connectivity).

Note:

Even when using OLE DB as the apply connection method, Replicat always uses ODBC to query the target database for metadata. Therefore Replicat always requires a DSN.

To create a SQL Server DSN

  1. To run the ODBC client, select Control Panel, select Administrative Tools, and then select Data Sources (ODBC).
  2. In the ODBC Data Source Administrator dialog box of the ODBC client, select the System DSN tab, and then click Add.
  3. Under Create New Data Source, select the correct SQL Server driver for the edition, and then click Finish. The Create a New Data Source to SQL Server wizard appears.
  4. Supply the following, and then click Next:

    • Name: Can be of your choosing. In a Windows cluster, use one name across all nodes in the cluster.

    • Description: (Optional) Type a description of this data source.

    • Server: Select the SQL Server instance name.

  5. For login authentication, do one of the following, and then click Next:
    1. Select With Integrated Windows Authentication for Oracle GoldenGate to use Windows authentication.
    2. To use database credentials, select With SQL Server authentication using a login ID and password entered by the user, and supply the login information.
  6. If the default database is not set to the one that Oracle GoldenGate will connect to, click Change the default database to, and then select the correct name. Set the other settings to use ANSI. Click Next.
  7. Leave the next page set to the defaults. Click Finish.
  8. Click Test Data Source to test the connection.
  9. If the test is successful, close the confirmation box and the Create a New Data Source box.
  10. Repeat this procedure on each SQL Server source and target system.

Parent topic: Configuring a Database Connection

16.2 Preparing Tables for Processing

The table attributes in the following sections must be addressed in your Oracle GoldenGate environment.

Parent topic: Preparing the System for Oracle GoldenGate

16.2.1 Disabling Triggers and Cascade Constraints on the Target

In an environment where SQL Server is the target, consider triggers and cascade constraints that may repeat an operation that occurred on the source. For example, if the source has an insert trigger on TableA that inserts a record into TableB, and Oracle GoldenGate is configured to capture and deliver both TableA and TableB, the insert trigger on the target table, TableA, must be disabled. Otherwise, Replicat inserts into TableA, and the trigger fires and insert into TableB. Replicat will then try to insert into TableB, and then terminate abnormally.

When a trigger or cascade constraint repeats an operation that occurred on the source, you do not have to disable the trigger or constraint when the following conditions are both true:

  • You use the DBOPTIONS USEREPLICATIONUSER parameter in Replicat.

  • You use OLE DB connection for Replicat. The use of the OLE DB connection is the default configuration. Note that the trigger, constraint, or IDENTITY property must have NOT FOR REPLICATION enabled.

In the following scenario, disable the triggers and constraints on the target:

  • Uni-directional replication where all tables on the source are replicated.

In the following scenarios, enable the triggers and constraints on the target:

  • Uni-directional replication where tables affected by a trigger or cascade operation are not replicated, and the only application that loads these tables is using a trigger or cascade operation.

  • Uni-directional or -bi-directional replication where all tables on the source are replicated. In this scenario, set the target table cascade constraints and triggers to enable NOT FOR REPLICATION, and use the DBOPTIONS USEREPLICATIONUSER parameter in Replicat.

Parent topic: Preparing Tables for Processing

16.2.2 Assigning Row Identifiers

Oracle GoldenGate requires unique row identifiers on the source and target tables to locate the correct target rows for replicated updates and deletes. Source tables can have any kind of key listed in How Oracle GoldenGate Determines the Kind of Row Identifier to Use, except for tables of a SQL Server Standard Edition instance, which require a primary key. If there is no primary key identified on a table that has fixed-length columns, the length of one of the fixed-length columns must be below 3800 bytes.

Parent topic: Preparing Tables for Processing

16.2.2.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use

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 (required for tables of a Standard Edition instance).
  2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
  3. If neither of these key types exist , Oracle GoldenGate constructs a pseudokey 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. For SQL Server, Oracle GoldenGate requires the row data in target tables that do not have a primary key to be less than 8000 bytes.

    Note:

    If there are types of keys on a table or if there are no keys at all on a table, Oracle GoldenGate logs a 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.

16.2.2.2 Using KEYCOLS to Specify a Custom Key

If a table does not have an applicable row identifier, or if you prefer that identifiers are not used, you can define a substitute key, providing that 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 overrides any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate.

16.2.3 Improving IDENTITY Replication with Array Processing

Because only one table per session can have IDENTITY_INSERT set to ON, Replicat must continuously toggle IDENTITY_INSERT when it applies IDENTITY data to multiple tables in a session. To improve the performance of Replicat in this situation, use the BATCHSQL parameter. BATCHSQL causes Replicat to use array processing instead of applying SQL statements one at a time.

Parent topic: Preparing Tables for Processing

16.3 Globalization Support

Oracle GoldenGate provides globalization support that lets it process data in its native language encoding. The Oracle GoldenGate apply process (Replicat) can convert data from one character set to another when the data is contained in character column types. For more information, see Administering Oracle GoldenGate.

Parent topic: Preparing the System for Oracle GoldenGate