27 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:

• Prepare Database Users and Privileges
  
• Configure a Database Connection
  
• Preparing Tables for Processing
  
• Globalization Support
  

Prepare Database Users and Privileges

Learn about required database users, privileges, and permissions for Oracle GoldenGate for SQL Server, including supported SQL Server cloud databases.

• Oracle GoldenGate for SQL Server
  
• Amazon RDS User Permissions and Requirements
  
• Azure SQL Database
  
• Google Cloud SQL for SQL Server
  

Oracle GoldenGate for SQL Server

Oracle GoldenGate processes require a database user in order to capture from and apply data to a SQL Server database and it is recommended to create a dedicated database user to be used exclusively by Oracle GoldenGate processes.

Oracle GoldenGate for SQL Server supports SQL Server authentication for all of its certified platforms and Windows authentication for Classic Architecture only when Oracle GoldenGate is installed on a Windows server.

• To use Windows authentication for Oracle GoldenGate Classic Architecture, the Extract and Replicat processes inherit the login credentials of the Manager process. By default, the Manager process runs interactively as the user logged on to the Windows server or optionally can be added as a Windows Service with a default service name of GGSMGR. Whichever method that the Manager process is using, the user that it is running as needs to have the required SQL Server privileges listed above.

• To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed below.

SQL Server and Azure SQL Managed Instance

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a SQL Server or Azure SQL Managed Instance database.

1. Create a dedicated login for Oracle GoldenGate for SQL Server or Azure SQL Managed Instance.

2. Add the login as a user to the msdb database and to the source or target database.

3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 27-1 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
msdb Database Roles and Privileges
SQLAgentReaderRole	Yes	No	ALTER ROLE SQLAgentReaderRole ADD MEMBER gguser;
SQLAgentUserRole	Inherited	Yes	ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;
SELECT ON sysjobactivity	Yes	No	Required for Classic Architecture only. GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;
SELECT ON sysjobs	Yes	No	Required for Classic Architecture only. GRANT SELECT ON msdb.dbo.sysjobs TO gguser;
User Database Roles and Privileges
SYSADMIN	Yes	No	Required for a one time change to enable database level Change Data Capture (CDC) if not already enabled and can be revoked once TRANDATA has been enabled. ALTER SERVER ROLE sysadmin ADD MEMBER gguser; Database Administrators with sysadmin credentials can manually enable the database for CDC using the following, which would negate the need for the Extract user to have this privilege: EXEC msdb.sys.sp_cdc_enable_db ‘source_database’
DBOWNER	Yes	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Amazon RDS User Permissions and Requirements

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to an Amazon RDS for SQL Server database:

1. Create a dedicated login for Oracle GoldenGate for Amazon RDS for SQL Server.

2. Add the login as a user to the msdb database and to the source or target database.

3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 27-2 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
msdb Database Roles and Privileges
EXECUTE ON rds_cdc_enable_db	Yes	No	GRANT EXECUTE ON msdb.dbo.rds_cdc_enable_db TO gguser; Database administrators with master credentials can manually enable the database for Change Data Capture using the following command, which would negate the need for the Extract user to have this permission: EXEC msdb.dbo.rds_cdc_enable_db ‘source_database’
SQLAgentOperatorRole	Yes	No	ALTER ROLE SQLAgentOperatorRole ADD MEMBER gguser;
SQLAgentUserRole	Inherited	Yes	ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;
SELECT ON sysjobactivity	Yes	No	Required for Classic Architecture only. GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;
SELECT ON sysjobs	Yes	No	Required for Classic Architecture only. GRANT SELECT ON msdb.dbo.sysjobs TO gguser;
User Database Roles and Privileges
DBOWNER	Yes	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Azure SQL Database

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to apply to an Azure SQL Database:

1. Create a dedicated login for Oracle GoldenGate for Azure SQL Database.

2. Add the login as a user to the target database.

3. Create a schema in the target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. Enable the following privileges and permissions for the Oracle GoldenGate user.

Table 27-3 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
User Database Roles and Privileges
DBOWNER	NA	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Google Cloud SQL for SQL Server

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a Google Cloud SQL for SQL Server database:

1. Create a dedicated login for Oracle GoldenGate Google Cloud SQL for SQL Server. The user must be created from within the Users section of the Google Cloud dashboard for the database instance.

2. Add the user to the source or target database.

3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. If the database is to be used as a source for an Extract, manually enable the database for Change Data Capture (CDC):

   EXEC msdb.dbo.gcloudsql_cdc_enable_db 'source_database';

5. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 27-4 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
User Database Roles and Privileges
DBOWNER	Yes	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Configure a Database Connection

This section contains instructions for setting up the Extract and Replicat connections to a DB2 z/OS database.

Topics:

• Extract and Replicat Database Connectivity
  
• Creating a Database Connection on Linux
  
• Creating a Database Connection on Windows
  
• Connecting to the Listener of a SQL Server Always On Configuration
  

Extract and Replicat Database Connectivity

Extract and Replicat connect to a SQL Server database using a system ODBC DSN (Data Source Name) and use ODBC for its metadata queries and transactional data processing.

Creating a Database Connection on Linux

Before creating a database connection for Oracle GoldenGate processes running on Linux, install the latest version of Microsoft ODBC driver for SQL Server (Linux).

Select the following link for download and installation steps:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

For the installation, choose the steps listed under Red Hat Enterprise Linux and Oracle.

After the ODBC software is installed, follow the example below to create an ODBC DSN for Linux:

1. Create a template file for your data source(s):

   vi odbc_template_file.ini

2. Describe the data source in the template file. Multiple unique DSN entries can be listed in the template file, if needed.

   In the following example, mydsn_2019_source is the DSN name, which will be used with DBLOGIN and SOURCEDB or TARGETDB to connect to the Extract or Replicat to the database.

   [mydsn_2019_source]            
   Driver = ODBC Driver 18 for SQL Server            
   Server = myserver,1433            
   Database = source_database
   TrustServerCertificate=YES

3. Install the data source using the following command.

   odbcinst -i -s -f odbc_template_file.ini

   This command adds the DSN to the system odbc.ini file. For more information, select the following link:

   https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-2017

Creating a Database Connection on Windows

Before creating a database connection for Oracle GoldenGate processes running on Windows, install the latest version of Microsoft ODBC Driver for SQL Server.

Follow these steps to create a system DSN on the Windows server where Oracle GoldenGate is installed.

To create a SQL Server DSN

1. Open the ODBC Data Sources (64-bit) application.

2. In the ODBC Data Source Administrator dialog box, select the System DSN tab, and then click Add.

3. Under Create New Data Source, select the ODBC Driver {version} for SQL Server and then click Finish. The Create a New Data Source to SQL Server wizard appears.

4. Enter the following details, and 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: Type the SQL Server connection string or server\instance name. For Always On connections, use the listener\instance name of the Always On Availability Group.

5. For login authentication, select one of the following options, and then click Next:

   1. With Integrated Windows Authentication

   2. With SQL Server authentication using a login ID and password entered by the user

6. Select Change the default database to, and then select the source or target database from the list. Enable the Use ANSI settings. 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 for each SQL Server source and target database.

Connecting to the Listener of a SQL Server Always On Configuration

Extract and Replicat can connect to the listener of an Always On configuration or directly to the current primary replica of the group, depending on the DSN connection used.

The advantage of creating the connection to the listener is that Extract or Replicat can reconnect to the new primary replica upon failover without having to reconfigure the DSN to the new primary.

An Extract can also be configured to route its read-only queries to an available readable, synchronous mode secondary replica. By default, if Extract connects to a listener, all processing will be done against the primary replica, but if an Extract is configured with the TRANLOGOPTIONS ALWAYSONREADONLYROUTING parameter, its read-only queries are routed by the listener to an available readable secondary replica.

See TRANLOGOPTIONS and Requirements Summary for Capture and Delivery of Databases in an AlwaysOn Availability Group for more information.

If creating the DSN to connect to a Listener of an Always On configuration, enable the Multi-subnet failover option when creating the DSN. For Linux DSN connections, use the MultiSubnetFailover=Yes option in the DSN entry.

Preparing Tables for Processing

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

• Disabling Triggers and Cascade Constraints on the Target
  
• Replicat Consideration for Target Identity Columns, Triggers, and Constraints
  
• Setting the NOT FOR REPLICATION flag for Target Identity Columns, Triggers, and Constraints
  
• Ensuring Row Uniqueness in Source and Target Table
  
• Improving IDENTITY Replication with Array Processing
  

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.

Replicat Consideration for Target Identity Columns, Triggers, and Constraints

When replicating data to a target SQL Server database that has identity columns, triggers, and cascade and check constraints, consider the following:

• For columns that have an identity column, Replicat sets the IDENTITY_INSERT ON for the table, which may reduce delivery performance.

• For tables that have triggers or cascade constraints, execution of the trigger or cascade operation may result in a Replicat error if the Replicat is configured to deliver the same data that a trigger will insert or cascade constraint will update or delete.

  For example, TableA on the source has a trigger that inserts a record into TableB. The Extract is configured to capture records for both TableA and TableB. On the target, the Replicat will first insert a record for TableA, then the trigger for TableA fires and inserts into TableB, followed by the Replicat attempting to insert the same record into TableB, which will result in a Replicat error.

• Check any foreign key constraints are also enforced, which may reduce delivery performance.

To overcome these situations, there are several options that can be implemented based on the replication use case.

• For unidirectional implementations where a Replicat is the only process writing data to the target tables, consider the following options for Identity columns, triggers and constraints on the target tables.

  1. Disable or drop the Identity property, triggers and constraints on the target tables.

  2. Modify the identity property, triggers and constraints and set the NOT FOR REPLICATION option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.

• For multi-directional implementations where both a Replicat and application write data to the target tables, and triggers and constraints are enabled, modify the Identity property, triggers and constraints and set the NOT FOR REPLICATION option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.

  Additionally, to use IDENTITY columns in a multi-directional replication 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 three-database configuration would be as follows:

  Database1 set the seed value at 0 with an increment of 3.

  Database2 set the seed value at 1 with an increment of 3.

  Database3 set the seed value at 2 with an increment of 3.

Setting the NOT FOR REPLICATION flag for Target Identity Columns, Triggers, and Constraints

1. Set the NOT FOR REPLICATION flag on the following objects.

   • Foreign key constraints

   • Check constraints

   • IDENTITY columns

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

   For active-passive configurations, set it only on the passive database. For active-active configurations, set it on both databases.

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. Make sure to 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.

Ensuring Row Uniqueness in Source and Target Table

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.

• Using KEYCOLS to Specify a Custom Key
  

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.

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.

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.