Preparing the Database for Oracle GoldenGate

Database Configuration

To support Oracle GoldenGate, the following parameters in the PostgreSQL database configuration file, $PGDATA/postgresql.conf, needs to be configured.

For remote connectivity of an Extract or Replicat, set the PostgreSQL listen_addresses to allow for remote database connectivity. For example:

listen_addresses=remotehost_ip_address

Note:
Ensure that client authentication is set to allow connections from an Oracle GoldenGate host by configuring the pg_hba.conf file. For more information, refer to this document: The pg_hba.conf File
To support Oracle GoldenGate capture, write-ahead logging must be set to logical, which adds information necessary to support transactional record decoding.

The number of maximum replication slots must be set to accommodate one open slot per Extract, and in general, no more than one Extract is needed per database. If for example PostgreSQL Native Replication is already in use and is using all of the currently configured replication slots, increase the value to allow for the registration of an Extract.

Maximum write-ahead senders should be set to match the maximum replication slots value.

Optionally, commit timestamps can be enabled in the write-ahead log, which when set at the same time logical write-ahead logging is enabled, will track the first DML commit record from that point on, with the correct timestamp value. Otherwise, the first record encountered by Oracle GoldenGate capture will have an incorrect commit timestamp.
```
wal_level = logical            # set to logical for Capture

max_replication_slots = 1      # max number of replication slots,
                               # one slot per Extract/client

max_wal_senders = 1            # one sender per max repl slot

track_commit_timestamp = on    # optional, correlates tx commit time
                               # with begin tx log record (useful for      
                               # timestamp-based positioning)
```
After making any of the preceding changes, restart the database.

Database Settings for PostgreSQL Cloud Databases

Parent topic: Preparing the Database for Oracle GoldenGate

Database Settings for PostgreSQL Cloud Databases

Use these instructions to manage the database settings for Azure Database for PostgreSQL, Amazon Aurora PostgreSQL, Amazon RDS for PostgreSQL, and Google Cloud SQL for PostgreSQL.

Azure Database for PostgreSQL

When configuring Oracle GoldenGate for PostgreSQL Capture against an Azure Database for PostgreSQL, logical decoding must be enabled and set to LOGICAL.

Read the Microsoft Documentation for instructions.

Other database settings for Azure Database for PostgreSQL can be managed through the Server parameters section of the database instance.

For connections to an Azure Database for PostgreSQL instance, the default Azure Connection Security settings require SSL connections. To adhere to this requirement, further steps are required to support SSL connections with Oracle GoldenGate. Follow the content listed under Configuring SSL Support for PostgreSQL for more information.

Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

For Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL, database settings are modified within parameter groups. Review the Amazon AWS documentation for information on how to edit database settings within a new parameter group and assign it to a database instance.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

Ensure that the database configuration settings listed previously are correct, by verifying them in the parameter group assigned to the instance.
The wal_level setting for Amazon database services is configured with a parameter called rds.logical_replication, whose default is 0 and should be set to 1 if the database is to be used as source database for Oracle GoldenGate Capture.

Google Cloud SQL for PostgreSQL

When configuring an Oracle GoldenGate for PostgreSQL Extract for a Google Cloud SQL for PostgreSQL database, logical decoding must be set and is done by setting the cloudsql.logical_decoding variable to ON. Follow the instructions provided by Google on how to enable this database flag. For more information, see https://cloud.google.com/sql/docs/postgres/flags#postgres-l.

Parent topic: Database Configuration

Establishing Oracle GoldenGate Credentials

Learn how to create database users for the processes that interact with the database, assign the correct privileges, and secure the credentials from any unauthorized user.

Topics:

Parent topic: Preparing the Database for Oracle GoldenGate

Assigning Credentials to Oracle GoldenGate

Oracle GoldenGate processes require a database user to capture and deliver data to a PostgreSQL database and it is recommended to create a dedicated PostgreSQL database user for Extract and Replicat.

The following database user privileges are required for Oracle GoldenGate to capture from and apply to a PostgreSQL database.

Privilege	Extract	Replicat	Purpose
Database Replication Privileges
`CONNECT`	Yes	Yes	Required for database connectivity. `GRANT CONNECT ON DATABASE dbname TO gguser;`
`WITH REPLICATION`	Yes	NA	Required for the user to register Extract with a replication slot. `ALTER USER gguser WITH REPLICATION;`
`WITH SUPERUSER`	Yes	NA	Required to enable table level supplemental logging (`ADD TRANDATA`) but can be revoked after TRANDATA is enabled for the table(s). `ALTER USER gguser WITH SUPERUSER;` For Azure Database for PostgreSQL, only the Admin user has SUPERUSER authority and is the only user that can enable TRANDATA.
`USAGE ON SCHEMA`	Yes	Yes	For metadata access to tables in the schema to be replicated. `GRANT USAGE ON SCHEMA tableschema TO gguser;`
`SELECT ON TABLES`	Yes	Yes	Grant select access on tables to be replicated. `GRANT SELECT ON ALL TABLES IN SCHEMA tableschema TO gguser;`
`INSERT, UPDATE, DELETE,TRUNCATE` on target tables. Alternatively, if replicating every table, then you can use the `GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA TO...` to the Replicat user, instead of granting `INSERT, UPDATE, DELETE` to every table.	NA	Yes	Apply replicated DML to target objects. `GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tablename TO gguser;`
Heartbeat and Checkpoint Table Privileges
`CREATE ON DATABASE`	Yes	Yes	Required by the Extract and Replicat user to add an Oracle GoldenGate schema for heartbeat and checkpoint table creation. `GRANT CREATE ON DATABASE dbname TO gguser;` Alternatively, if `GGSCHEMA` is the same as the user, then the objects can be created under the user by issuing `CREATE SCHEMA AUTHORIZATION ggsuser;`
`CREATE, USAGE ON SCHEMA`	Yes	Yes	For heartbeat and checkpoint table creation/deletion if the Extract or Replicat user does not own the objects. `GRANT CREATE, USAGE ON SCHEMA ggschema TO gguser;`
`EXECUTE ON ALL FUNCTIONS`	Yes	Yes	For heartbeat update and purge function execution if the user calling the functions does not own the objects. `GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ggschema TO gguser;`
`SELECT, INSERT, UPDATE, DELETE`	Yes	Yes	For heartbeat and checkpoint table inserts, updates and deletes if the user does not own the objects. `GRANT SELECT, INSERT, UPDATE, DELETE, ON ALL TABLES IN SCHEMA ggschema TO gguser;`

Parent topic: Establishing Oracle GoldenGate Credentials

Securing the Oracle GoldenGate Credentials

To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on as, or operate as, an Oracle GoldenGate database user.

Oracle GoldenGate provides different options for securing the log-in credentials assigned to Oracle GoldenGate processes. The recommended option is to use a credential store. You can create one credential store and store it in a shared location where all installations of Oracle GoldenGate can access it, or you can create a separate one on each system where Oracle GoldenGate is installed.

The credential store stores the user name and password for each of the assigned Oracle GoldenGate users. A user ID is associated with one or more aliases, and it is the alias that is supplied in commands and parameter files, not the actual user name or password. The credential file can be partitioned into domains, allowing a standard set of aliases to be used for the processes, while allowing the administrator on each system to manage credentials locally.

Parent topic: Establishing Oracle GoldenGate Credentials

Configuring a Database Connection

Oracle GoldenGate connects to a PostgreSQL database through an ODBC (Open Database Connectivity) driver and requires a system Data Source Name (DSN) be created with the correct database connection details for each source and target PostgreSQL database.

Ensure that you have installed and configured the driver prior to creating a DSN, by following the Installing the DataDirect driver for PostgreSQL instructions in Installing Oracle GoldenGate .

Note:

Do not use PgBouncer setup for Extract connections to the PostgreSQL database because PgBouncer does not understand the replication protocol, because of which the Extract connection is not identified as replication connection.

This section contains instructions for setting up the DSN connections that Extract and Replicat will use.

Topics:

Parent topic: Preparing the Database for Oracle GoldenGate

Configuring a Database Connection in Linux

To create a database connection in Linux, set up a data source name (DSN) inside the /etc/odbc.ini file.

Create a DSN for each source or target database in the /etc/odbc.ini file.
sudo vi /etc/odbc.ini
```
#Sample DSN entries
[ODBC Data Sources]
PG_src=DataDirect 7.1 PostgreSQL Wire Protocol
PG_tgt=DataDirect 7.1 PostgreSQL Wire Protocol
```
```
[ODBC]
IANAAppCodePage=4
InstallDir=/u01/app/ogg
```
```
[PG_src]
Driver=/u01/app/ogg/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=sourcedb
HostName=remotehost
PortNumber=5432
```
```
[PG_tgt]
Driver=/u01/app/ogg/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=targetdb
HostName=remotehost
PortNumber=5432
```
In the preceding examples:

PG_src and PG_tgt are user defined names of a source and target database DSN that will be referenced by Oracle GoldenGate processes, such as Extract or Replicat. DSN names are allowed up to 32 alpha-numeric characters in length, excluding special keyboard characters except for the underscore and dash.

IANAAppCodePage=4 is the default setting but can be modified according to the following guidance, when the database character set is not Unicode.

https://docs.progress.com/bundle/datadirect-connect-odbc-71/page/IANAAppCodePage_9.html#IANAAppCodePage_9

InstallDir is the location of the Oracle GoldenGate installation folder.

Driver is the location of the Oracle GoldenGate installation home, $OGG_HOME/lib/GGpsql25.so file.

Database is the name of the source or target database.

HostName is the database host IP address or host name.

PortNumber is the listening port of the database.

You can also provide a LogonID and Password for the Extract or Replicat user, but these will be stored in clear text and it is recommended instead to leave these fields out of the DSN and instead store them in the Oracle GoldenGate wallet as a credential alias, and reference them with the USERIDALIAS parameter in Extract and Replicat.
Save and close the odbc.ini file.

Parent topic: Configuring a Database Connection

Configuring a Database Connection on Windows

To create a database connection in Windows, use the Windows ODBC Data Source Administrator to create a system DSN for each source and target database.

On the Windows system, open the Control Panel folder.
Open the Administrative Tools folder.
Open ODBC Data Sources (64-bit). The ODBC Data Source Administrator dialog box is displayed.
Select the System DSN tab, and then click Add.
Under Create New Data Source, select the Oracle GoldenGate PostgreSQL Wire Protocol driver and click Finish.
The Create a New Data Source wizard is displayed.
Supply the following:
- For Data Source Name, type a name for the DSN, up to 32 alpha-numeric characters in length, excluding special keyboard characters except for the underscore and dash.
- (Optional) For Description, type a description of this DSN.
- Provide the database server’s Host Name, the database Port Number, and Database Name.
Click OK to close the dialog box.

You can also provide the User Name information under the Security tab but it is recommended instead to leave this field empty and instead store the user name and password in the Oracle GoldenGate wallet as a credential alias, and reference them with the USERIDALIAS parameter in Extract and Replicat.

Parent topic: Configuring a Database Connection

Configuring SSL Support for PostgreSQL

SSL can be enabled by configuring the PostgreSQL configuration file ($PGDATA/postgresql.conf). For details, see Configuring SSL Support (PostgreSQL) in the Securing the Oracle GoldenGate Environment.

Note:

Azure Database for PostgreSQL defaults to enforce SSL connections. To adhere to this requirement, perform the requirements listed here, or optionally, you can disable enforcing SSL connections from the Connection security settings of the database instance using the Microsoft Azure Portal.

Parent topic: Configuring a Database Connection

Preparing Tables for Processing

The following table attributes must be addressed in an Oracle GoldenGate environment for PostgreSQL.

Topics:

Parent topic: Preparing the Database for Oracle GoldenGate

Disabling Triggers and Cascade Constraints on the Target

If Oracle GoldenGate is configured to capture DML operations from source tables that occur due to trigger operations or cascade constraints, then disable the triggers and cascade delete and cascade update constraints on the target tables.

If not disabled, the same trigger or constraint gets activated on the target table and becomes redundant because of the replicated data. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ

A delete is issued for emp_src.
It cascades a delete to salary_src.
Oracle GoldenGate sends both deletes to the target.
The parent delete arrives first and is applied to emp_targ.
The parent delete cascades a delete to salary_targ.
The cascaded delete from salary_src is applied to salary_targ.
The row cannot be located because it was already deleted in step 5.

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 must be mapped to appropriate targets to preserve data integrity. Include the same parent and child source tables in the Extract TABLE parameters.

Parent topic: Preparing Tables for Processing

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:

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. For PostgreSQL LOB types such as text, json, xml, bytea, char, varchar, Oracle GoldenGate supports these columns as a primary key in source or target tables up to a length of 8191 bytes.

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.
For tables that have no uniqueness and have repeat rows with the same values, Replicat will Abend on update and delete operations for these rows.
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.

Parent topic: Preparing Tables for Processing

Enabling Table-Level Supplemental Logging

Enabling Supplemental logging is a process in which Oracle GoldenGate sets source database table level logging to support change data capture of source DML operations, and depending on the level of logging, to include additional, unchanged columns which would be needed in cases such as bi-directional replication with conflict detection and resolution configured.

There are four levels of table level logging in PostgreSQL, which equate to the REPLICA IDENTITY setting of a table, and those include NOTHING, USING INDEX, DEFAULT, and FULL.

Oracle GoldenGate requires FULL logging for use cases that require uncompressed trail records and Conflict Detection and Resolution, but in cases where tables have a Primary Key or Unique Index whose changes are being replicated in a simple uni-directional configuration or where full before-images or uncompressed records are not needed, then the DEFAULT level is acceptable. NOTHING and USING INDEX logging levels are not supported by Oracle GoldenGate and cannot be set with ADD TRANDATA.

Supplemental logging can be enabled within the Microservices Architecture web interface from the Administration Service, Configuration page, under the Credential created for a source database, or can be issued with the ADD TRANDATA command within GGSCI or the Microservices Architecture Admin Client.

The following is the syntax for issuing ADD TRANDATA from GGSCI.

GGSCI> DBLOGIN SOURCEDB dsn_name USERIDALIAS alias_name
 GGSCI> ADD TRANDATA schema.tablename ALLCOLS

Note:

For tables that have a primary key or unique index, the ALLCOLS option is required in order to set FULL logging for the table, otherwise DEFAULT logging is set.

FULL logging is always set for tables without a primary key or unique index, regardless of whether ALLCOLS is specified or not.

To check the level of supplemental logging:

GGSCI> INFO TRANDATA schema.tablename

Parent topic: Preparing Tables for Processing