27 Preparing the System for Oracle GoldenGate
Topics:
- Prepare Database Users and Privileges
- Configure a Database Connection
- Preparing Tables for Processing
- Globalization Support
Parent topic: Using Oracle GoldenGate for SQL Server
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
Parent topic: Preparing the System for Oracle GoldenGate
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
-
Create a dedicated login for Oracle GoldenGate for SQL Server or Azure SQL Managed Instance.
-
Add the login as a user to the
msdb
database and to the source or target database. -
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 theGLOBALS
parameter file. -
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 |
|||
|
Yes |
No |
|
|
Inherited |
Yes |
|
|
Yes |
No |
Required for Classic Architecture only.
|
|
Yes |
No |
Required for Classic Architecture only.
|
User Database Roles and Privileges |
|||
|
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
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’ |
|
Yes |
Yes |
ALTER ROLE db_owner ADD MEMBER
gguser; |
Parent topic: Prepare Database Users and Privileges
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:
-
Create a dedicated login for Oracle GoldenGate for Amazon RDS for SQL Server.
-
Add the login as a user to the
msdb
database and to the source or target database. -
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 theGLOBALS
parameter file. -
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 |
|||
|
Yes |
No |
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:
|
|
Yes |
No |
|
|
Inherited |
Yes |
|
|
Yes |
No |
Required for Classic Architecture only.
|
|
Yes |
No |
Required for Classic Architecture only.
|
User Database Roles and Privileges |
|||
|
Yes |
Yes |
|
Parent topic: Prepare Database Users and Privileges
Azure SQL Database
-
Create a dedicated login for Oracle GoldenGate for Azure SQL Database.
-
Add the login as a user to the target database.
-
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 theGLOBALS
parameter file. -
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 |
|||
|
NA |
Yes |
|
Parent topic: Prepare Database Users and Privileges
Google Cloud SQL for SQL Server
-
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.
-
Add the user to the source or target database.
-
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 theGLOBALS
parameter file. -
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';
-
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 |
|||
|
Yes |
Yes |
|
Parent topic: Prepare Database Users and Privileges
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
Parent topic: Preparing the System for Oracle GoldenGate
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.
Parent topic: Configure a Database Connection
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:
For the installation, choose the steps listed under Red Hat Enterprise Linux and Oracle.
-
Create a template file for your data source(s):
vi odbc_template_file.ini
-
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 withDBLOGIN
andSOURCEDB
orTARGETDB
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
-
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:
Parent topic: Configure a Database Connection
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
-
Open the ODBC Data Sources (64-bit) application.
-
In the ODBC Data Source Administrator dialog box, select the System DSN tab, and then click Add.
-
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.
-
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.
-
-
For login authentication, select one of the following options, and then click Next:
-
With Integrated Windows Authentication
-
With SQL Server authentication using a login ID and password entered by the user
-
-
Select Change the default database to, and then select the source or target database from the list. Enable the Use ANSI settings. Click Next.
-
Leave the next page set to the defaults. Click Finish.
-
Click Test Data Source to test the connection.
-
If the test is successful, close the confirmation box and the Create a New Data Source box.
-
Repeat this procedure for each SQL Server source and target database.
Parent topic: Configure a Database Connection
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.
Parent topic: Configure a Database Connection
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
Parent topic: Preparing the System for Oracle GoldenGate
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.
-
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 haveNOT 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 theDBOPTIONS USEREPLICATIONUSER
parameter in Replicat.
Parent topic: Preparing Tables for Processing
Replicat Consideration for Target Identity Columns, Triggers, and Constraints
-
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 intoTableB
. The Extract is configured to capture records for both TableA and TableB. On the target, the Replicat will first insert a record forTableA
, then the trigger forTableA
fires and inserts intoTableB
, followed by the Replicat attempting to insert the same record intoTableB
, 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.
-
Disable or drop the Identity property, triggers and constraints on the target tables.
-
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 theIDENTITY
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.
Parent topic: Preparing Tables for Processing
Setting the NOT FOR REPLICATION flag for Target Identity Columns, Triggers, and Constraints
-
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.
-
-
Partition
IDENTITY
values for bidirectional configurations. -
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 ExtractTABLE
parameters.Note:
If referenced tables are omitted from theMAP
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.
Parent topic: Preparing Tables for Processing
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.
KEYCOLS
clause is used in the
TABLE
or MAP
statement, Oracle GoldenGate selects a row identifier to use in
the following order of priority:
-
Primary key
-
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
-
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 efficientWHERE
clause. -
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 ReplicatMAP
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
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.
Parent topic: Ensuring Row Uniqueness in Source and Target Table
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
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.
Parent topic: Preparing the System for Oracle GoldenGate