21 Preparing the System for Oracle GoldenGate
Topics:
- Prepare Database Users and Privileges
- Configuring 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.
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 21-1 Privileges and Permissions for Oracle GoldenGate User
Privilege | Extract | Replicat | Syntax |
---|---|---|---|
msdb Database Roles and Privileges |
|||
|
Yes |
No |
|
|
Inherited |
Yes |
|
|
Yes |
No |
|
|
Yes |
No |
|
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 21-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 |
|
|
Yes |
No |
|
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 21-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
Configuring a Database Connection
This section contains instructions for setting up the Extract and Replicat connections to a SQL Server database.
- Configuring an Extract Database Connection
- Configuring a Replicat Database Connection
- Configuring a Database Connection on Linux
- Configuring a Database Connection on Windows
Parent topic: Preparing the System for Oracle GoldenGate
Configuring an Extract Database Connection
Extract connects to a source SQL Server database through an ODBC (Open Database Connectivity) connection. To create this connection, set up a data source name (DSN) through the Data Sources (ODBC) control panel. See Configuring a Database Connection on Windows for instructions.
Connecting to the Listener in an Always On Environment
Extract can connect to the Listener of an Always On
environment and can 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 the 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.
Parent topic: Configuring a Database Connection
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 if the SQL Server driver supports it.
-
Through OLE DB as the SQL Server replication user.
NOT FOR REPLICATION
must be set onIDENTITY
columns, foreign key constraints, and triggers.
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
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:
Parent topic: Configuring a Replicat Database Connection
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 with
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.
USEREPLICATIONUSER
, follow these steps:
Note:
For SQL Server CDC for Linux for Oracle GoldenGate, theUSEREPLICATIONUSER
option is not
allowed.
Note:
For Replicat, connections using a Microsoft ODBC driver, install the Microsoft OLE DB Driver 18 for SQL Server to support theUSEREPLICATIONUSER
option:
https://www.microsoft.com/en-us/download/details.aspx?id=56730
Parent topic: Configuring a Replicat Database Connection
Configuring a Database Connection on Linux
Oracle GoldenGate for SQL Server database configuration provides the same support
for Linux and Windows. However, you need the msodbcsql13*
or
msodbcsql17*
drivers to set up the connections in a Linux
environment.
See:
-
Create a template file for your data source:
vi odbc_template_file.ini
-
Describe the data source in the template file. In the following example,
myserver_ss2017_source
is used as the name withDBLOGIN
andSOURCEDB
to connect to the database.:[myserver_SS2017_source] Driver = ODBC Driver 17 for SQL Server Server = myserver,1433 Database = source_database User = ssuser Password = ssuserpassword
-
Install the data source using the command:
odbcinst -i -s -f odbc_template_file.ini
For more information, see:
Parent topic: Configuring a Database Connection
Configuring a Database Connection on Windows
Follow these instructions to create a SQL Server system data source name (DSN) for a source or a target SQL Server database. A DSN stores information about how to connect to a SQL Server database through ODBC (Open Database Connectivity).
To create a SQL Server DSN
Parent topic: Configuring 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
- Assigning Row Identifiers
- 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
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. 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.
- How Oracle GoldenGate Determines the Kind of Row Identifier to Use
- Using KEYCOLS to Specify a Custom Key
Parent topic: Preparing Tables for Processing
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:
Parent topic: Assigning Row Identifiers
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: Assigning Row Identifiers
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