Before Adding Extract and Replicat Processes

Learn about the prerequisite configurations required before creating Extract and Replicat processes for an Oracle GoldenGate deployment.

Access the Configurations Page

Configure connections to the database from Oracle GoldenGate by setting up database user credentials from the Configurations page of the Administration Service left-navigation pane.

See Add Database Credentials for steps to create credentials for the database and test the connection. You can set up database credentials to set up connections to multiple databases, as required by the Extract and Replicat processes.

Enable TRANDATA

Valid for Oracle and Non-Oracle databases.

Depending on the source database, supplemental logging must be enabled. This can be done at the table, schema, or global (database) level.

You can skip ADD TRANDATA in case of initial load without CDC.

Oracle: Enable TRANDATA or SCHEMATRANDATA

Valid for Oracle.

Depending on the source database, supplemental logging must be enabled. This can be done at the table, schema, or global (database) level.

To enable supplemental logging at the table and schema level, on Configuration page:

Select the Table or Schema option as required and click plus sign to add.
Enter the name of the table for which you need to set up supplemental logging. Make sure to enter the full table name with schema name, such as, schema.table1. You can also use wildcard instead of specific table name.
Select the Add TRANDATA Information in the background? option as required.
Click Submit.

You can also use the commands ADD TRANDATA and ADD SCHEMATRANDATA for setting up trandata and schema level trandata. For details, see ADD TRANDATA and ADD SCHEMATRANDATA. You can skip ADD TRANDATA in case of initial load without CDC.

Db2 z/OS: Enable Change Capture

Follow these steps to configure Db2 to log data changes in the expanded format that is supplied by the DATA CAPTURE CHANGES feature of the CREATE TABLE and ALTER TABLE commands. This format provides Oracle GoldenGate with the entire before and after images of rows that are changed with update statements.

From the Oracle GoldenGate directory, start the Admin Client.

Log on to Db2 as a user that has ALTER TABLE privileges.

DBLOGIN SOURCEDB DSN, USERID user[, PASSWORD password][, encryption_options]

Issue the following command. where table is the fully qualified name of the table. You can use a wildcard to specify multiple table names but not owner names.
```
ADD TRANDATA table
```
By default, ADD TRANDATA issues the following command:
```
ALTER TABLE name DATA CAPTURE CHANGES;
```

SQL Server: Enable Supplemental Logging and Other Features

A database user must issue the ADD TRANDATA command to enable supplemental logging on the source database in an Oracle GoldenGate configuration. A database login command (DBLOGIN) is issued from the command line interface before ADD TRANDATA is issued.

The database user that enables TRANDATA must have sysadmin rights.

Extract can run with dbowner permissions. However, you also need sysadmin rights to issue the ADD/ALTER/ DELETE/INFO HEARTBEATTABLE commands, or to create the Oracle GoldenGate CDC Cleanup job using the ogg_cdc_cleanup_setup.bat batch file.

Add a Checkpoint Table

A checkpoint table is required for all non-parallel Replicats and must be created in the database prior to adding a Replicat. You can view the checkpoint table from the Checkpoint section of the Configurations page.

To add a checkpoint table:

Click the plus sign to enable adding a checkpoint table.
Add the checkpoint table name in the format
```
table.checkpoint_table_name
```
.
Click Submit. After the checkpoint is created, you'll be able to see in the list of checkpoint tables.

To perform this task from the command line, see ADD CHECKPOINTTABLE in the Command Line Interface Reference for Oracle GoldenGate.

Add Heartbeat Table

Heartbeat tables are used to monitor lag throughout the data replication cycle. Automatic heartbeats are sent from each source database into the replication streams, by updating the records in a heartbeat seed table and a heartbeat table, and constructing a heartbeat history table. Each of the replication processes in the replication path process these heartbeat records and update the information in them. These heartbeat records are inserted or updated into the heartbeat table at the target databases.

To create the heartbeat table, you have to follow these steps on the source and target system:

Note:

Creating the heartbeat table is optional but is recommended.

From the Administration Service, select Configuration from the navigation pane.
Select the + sign next to the Heartbeat section of the Database tab. You'll need to enter the values for the heartbeat frequency, retention time, and purge frequency.

Here are the steps to add a heartbeat table from the Admin Client:

Launch the Admin Client from the command line.

Connect to the deployment from the Admin Client.

CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password

Here's an example:

CONNECT https://remotehost:16000 DEPLOYMENT ggdep_postgres AS ggadmin PASSWORD P@ssWord

Connect to the source and target databases using the DBLOGIN USERIDALIAS command. The following example shows the connection to the source database with credential alias ggeast:
```
(https://remotehost:16000 ggdep_postgres)> DBLOGIN USERIDALIAS ggeast
```

Add the heartbeat table:

(https://remotehost:16000 ggdep_postgres)> ADD HEARTBEATTABLE

Optionally, for a target only database, one that is used for unidirectional replication only, you can include the TARGETONLY option which will not create a heartbeat record update function.

See ADD HEARTBEATTABLE for details about command options.

Create the Oracle GoldenGate CDC Cleanup Task

For SQL Server users, there is a requirement to create Oracle GoldenGate CDC Cleanup tasks before adding an Extract. You can do so by performing the steps in Details of the Oracle GoldenGate CDC Cleanup Process.

Running the Heartbeat Update and Purge Function for PostgreSQL

Oracle GoldenGate for PostgreSQL supports a heartbeat table configuration, with some limitations regarding the update and purge tasks.

The heartbeat table and associated functions are created from the ADD HEARTBEATTABLE command, however for PostgreSQL, there is no automatic scheduler to call the functions.

One main function controls both the heartbeat record update and the heartbeat history table purge functions. The default settings for both of these features are 60 seconds for the update frequency and 1 day for the history record purge, which deletes all records older than 30 days by default.

To call the main heartbeat record function, users should create an operating system level job that executes

“select ggschema.gg_hb_job_run();”

. When this function is called, it will take into account the update frequency settings and history record purge settings and use those values regardless of the scheduler settings for the function call.

For example, users can create a Cron Job with the following syntax, and have it run every minute.

*****PGPASSWORD="gguserpasswd" psql -U gguser -d dbname -h remotehost -p 5432 -c "select ggschema.gg_hb_job_run();" >/dev/null
 2>&1

Windows Task Scheduler, pgAdmin, or pg_cron are other programs that could be used to schedule the function call.

PostgreSQL: Extract Considerations for Remote Deployment

For a remote deployment, the source database and Oracle GoldenGate are installed on separate servers. Remote deployments are the only option available for supporting cloud databases, such as Azure for PostgreSQL or Amazon Aurora PostgreSQL.

For remote deployments, operating system endianness between the database server and Oracle GoldenGate server need to be the same.

Server time and time zones of the Oracle GoldenGate server should be synchronized with that of the database server. If this is not possible, then positioning of an Extract when creating or altering one will need to be done by LSN.

In remote capture use cases, using SQLEXEC may introduce additional latency, as the SQLEXEC operation must be done serially for each record that the Extract processes. If special filtering that would require a SQLEXEC is done by a remote hub Extract and the performance impact is too severe, it may become necessary to move the Extract process closer to the source database.

With remote deployments, low network latency is important, and it is recommended that the network latency between the Oracle GoldenGate server and the source database server be less than 1 millisecond.