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:

  1. Select the Table or Schema option as required and click plus sign to add.
  2. 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.
  3. 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:

  1. Select the Table option and click plus sign to add.

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

  3. Click Submit.

To Enable Change Capture for Db2 for i

To capture changes to a table in a journal, you can run the STRJRNPF command on the IBM i command line or run the ADD TRANDATA command from Admin Client. The ADD TRANDATA command calls STRJRNPF and is the recommended method to start journaling for tables, because it ensures that the required journal image attribute of Record Images (IMAGES): *BOTH is set on the STRJRNPF command.

The ADD TRANDATA command is:

ADD TRANDATA table_specification

Where: table_specification is one of the following: 

  • schema.table [JOURNAL library/journal]
  • library/file [JOURNAL  library/journal])

Specifying a Default Journal

To specify a default journal for multiple tables or files in the ADD TRANDATA command, instead of specifying the JOURNAL keyword, use the following command before issuing ADD TRANDATA. 

DEFAULTJOURNAL library/journal

Any ADD TRANDATA command used without a journal assumes the journal from DEFAULTJOURNAL.

To display the current setting of DEFAULTJOURNAL, you can issue the command with no arguments.

Removing a Default Journal Specification

To remove the use of a default journal, use the following command:

DEFAULTJOURNAL CLEAR

To Enable Change Capture for Db2 LUW

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 by update statements. Run the ALTER TABLE command as follows:

Issue the following command.

ADD TRANDATA owner.table

where owner.table is the fully qualified name of the table. You can use a wildcard to specify multiple table names.

ADD TRANDATA issues the following command, which includes logging the before image of LONGVAR columns:

ALTER TABLE name DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;

Example 7-1 To Exclude LONGVAR Logging:

To omit the INCLUDE LONGVAR COLUMNS clause from the ALTER TABLE command, use ADD TRANDATA with the EXCLUDELONG option. 

ADD TRANDATA owner.table, EXCLUDELONG

Note:

If LONGVAR columns are excluded from logging, the Oracle GoldenGate features that require before images, such as the GETUPDATEBEFORES, NOCOMPRESSUPDATES, and NOCOMPRESSDELETES parameters, might return errors if tables contain those columns. For a workaround, see the REQUIRELONGDATACAPTURECHANGES | NOREQUIRELONGDATACAPTURECHANGES options of the TRANLOGOPTIONS parameter.

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. A checkpoint either succeeds or fails with the transaction, so Replicat ensures that a transaction is only applied once, even if there is a failure of the process or the database.

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.

  1. Click the plus sign to enable adding a checkpoint table.
  2. Add the checkpoint table name in the format 
    table.checkpoint_table_name
    .
  3. 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 a Checkpoint Table with Journaling in Db2 for i

In the Db2 for i target database, each checkpoint that is written to the checkpoint table, must be journaled within the Replicat transaction.

A common method to create the checkpoint table with journaling is as follows:

  1. On the target system, create the Replicat checkpoint file.

    Set the name of the journal that is intended to be used for the checkpoint file. The default will be GGSCHEMA/OGGJRN.

    You can change it by setting the default for the current Admin Client session using the DEFAULTJOURNAL command. The syntax of the DEFAULTJOURNAL command is: 

    DEFAULTJOURNAL library_name/journal_name

    Where: library_name is the name of the library and journal_name is the name of the journal to be used for subsequent operations that may optionally have a journal specified.

  2. Add the checkpoint table.
    ADD CHECKPOINTTABLE library_name.chkptab
  3. Add journaling to the checkpoint table. 
    ADD TRANDATA library_name.CHKPTAB

For more information about creating a checkpoint table, see Add a Checkpoint Table.

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:

  1. Click the plus (+) sign next to add a heartbeat table.

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

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

  1. Launch the Admin Client from the command line.

  2. 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
  3. 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

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