Before Adding Extract and Replicat Processes

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

Add TRANDATA

Valid for Db2 i, Db2 LUW, Db2 z/OS, Oracle, PostgreSQL, SQL Server, and Sybase.

Depending on the source database, supplemental logging must be enabled to capture DML operations and can be enabled through the Trandata menu of a database connection in the web interface, or in the Admin Client by issuing ADD TRANDATA or ADD SCHEMATRANDATA (for Oracle only).

Adding TRANDATA is not required on a source database for an initial load Extract. However, if both initial load Extract and change data capture (CDC) Extract will be used in conjunction, for an online instantiation, then TRANDATA should be added prior to starting the initial load Extract.

Enable TRANDATA or SCHEMATRANDATA for Oracle Database

This can be done at the table, schema, or global (database) level.

To enable supplemental logging, connect to the database from the DB Connections page, select the Trandata menu, then perform the following steps:

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, HR.EMP. You can also use wildcard instead of specific table name.
Click Submit.

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

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 in Command Line Interface Reference for Oracle GoldenGate.

Note:

Before you run the

ADD
                    TRANDATA

command, you need to first connect to the database where the Extract will be added, using the DBLOGIN command. In addition, run the ADD TRANDATA or ADD SCHEMATRANDATA commands before adding the Extract.

Enable TRANDATA for Non-Oracle Databases

Valid for Db2 i, Db2 LUW, Db2 z/OS, PostgreSQL, SQL Server, and Sybase.

This can be done at the table or global (database) level. To enable supplemental logging, connect to the database from the DB Connections page, select the Trandata menu, then perform the following steps:

Select the Table option 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, such as, EMPLOYEE. You can also use wildcard instead of specific table name.
Click Submit.

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 within the checkpoint section. To add a checkpoint table, connect to the target database from the DB Connections page, select Checkpoint, then follow the steps below.

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 record.

Each process in the replication stream updates the heartbeat record with tracking information which is then updated in the heartbeat table of the target database. These heartbeat records are inserted or updated into the heartbeat table at the target databases.

Note:

Creating the heartbeat table is optional but is recommended.

To add a heartbeat table, connect to each source and target database from the DB Connections page, select the Heartbeat menu, then perform the following steps:

Click the plus (+) sign next to add a heartbeat table.
Accept the default settings or modify the available values as needed.

Note:
For databases that have an option for Target Only, select this option if that database is only going to be used as a target database in the replication stream, to avoid creating unnecessary jobs that would be associated with a source database.
Click Submit.

To perform this task from the command line and review important database specific limitations,, see ADD HEARTBEATTABLE in Command Line Interface Reference for Oracle GoldenGate.

The following steps describe the commands to set up the heartbeat table.

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.

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.

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.

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.