Additional Considerations

21 Additional Considerations

Parent topic: Using Oracle GoldenGate for PostgreSQL

Adding a Heartbeat Table

Oracle GoldenGate for PostgreSQL supports a heartbeat table configuration, with some limitations regarding the update and purge tasks, which will be pointed out later. Adding a heartbeat table to both the source and target systems is optional but is useful in determining latency issues and to which process in the replication stream such issues may be occurring.

To add a heartbeat table for a database, review the required privileges in the Table 17-* and ensure that the correct database privileges are assigned to the Extract or Replicat user.

A schema in the database needs to be created and this should match the name of the schema used for the GGSCHEMA parameter of the GLOBALS file. The schema can be a unique schema that is not the same as the Extract or Replicat user, or can be the same as that user, but should always be reserved for Oracle GoldenGate objects only and should not be part of the user table schemas being replicated.

Microservices Architecture

Using the Admin Client, connect to the deployment, then connect to the credential alias for the source and target databases.

OGG> CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password
OGG (https://remotehost:16000postgresql_target)> DBLOGIN USERIDALIAS alias

Add the heartbeat table:

OGG (https:// remotehost:16000postgresql_source/target)> 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.

You can also add a heartbeat table from the Oracle GoldenGate MA web interface. See Create a Heartbeat Table in the Step by Step Data Replication Using Oracle GoldenGate Microservices guide.

Classic Architecture

Using GGSCI, connect to the DSN for the source and target databases.
```
GGSCI> DBLOGIN SOURCEDB dsn USERIDALIAS alias
```
Add the heartbeat table using the GGSCI command.
```
GGSCI> 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.
```
GGSCI> ADD HEARTBEATTABLE TARGETONLY
```

To learn about heartbeat update and purge functions, see:

Running the Heartbeat Update and Purge Function

Parent topic: Additional Considerations

Running the Heartbeat Update and Purge Function

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.

Parent topic: Adding a Heartbeat Table

Enabling Bi-Directional Loop Detection

Loop detection is a requirement for bi-directional and multi-directional implementations of Oracle GoldenGate, so that an Extract for one source database does not recapture transactions sent by a Replicat from another source database.

With the CDC Extract capture method, by default, any transaction committed by a Replicat into a database where an Extract is configured, will recapture that transaction from the Replicat as long as supplemental logging is enabled for those tables that the Replicat is delivering to.

In order to ignore recapturing transactions that are applied by a Replicat, you must use the TRANLOGOPTIONS FILTERTABLE parameter for the CDC Extract. The table used as the filtering table will be the Oracle GoldenGate checkpoint table that you must create for the Replicat.

To create a Filter Table and enable Supplemental Logging:

On each source database, ensure that a checkpoint table for use by Replicats has been created. For example:
```
ADD CHECKPOINTTABLE ggadmin.oggcheck
```
It is recommended that you use the same schema name as used in the GGSCHEMA parameter of the GLOBALS file.
Enable supplemental logging for the checkpoint table. For example:
```
ADD TRANDATA ggadmin.oggcheck ALLCOLS
```
Ensure that the Replicat is created with the checkpoint table information.
```
ADD REPLICAT reptgt1, EXTTRAIL ./dirdat/e2, CHECKPOINTTABLE ggadmin.oggcheck
```
Configure each Extract with the IGNOREREPLICATES (on by default) and FILTERTABLE parameters, using the Replicat’s checkpoint table for the filtering table.
```
TRANLOGOPTIONS IGNOREREPLICATES
TRANLOGOPTIONS FILTERTABLE ggadmin.oggcheck
```
Note:
Oracle GoldenGate for PostgreSQL supports only one FILTERTABLE statement per Extract, so for multi-directional implementations, ensure each Replicat uses the same checkpoint table in the database that they deliver to.

Parent topic: Additional Considerations

Deleting an Extract

When removing an individual Extract from use against a source PostgreSQL database, or uninstalling Oracle GoldenGate, the Extract that was registered with a replication slot in the database must be unregistered in order to remove the replication slot entry, otherwise an ever-increasing database log size can occur.

Perform the following steps to remove and unregister the Extract when no longer needed.

Microservices Architecture

Using the Admin Client, connect to the deployment, then connect to the credential alias for the source and target databases.

OGG> CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password
OGG (https://remotehost:16000postgresql_target)> DBLOGIN USERIDALIAS alias

Delete the Extract first.
```
GGSCI> DELETE EXTRACT extname
```
Unregister the Extract.
```
GGSCI> UNREGISTER EXTRACT extname
```

Classic Architecture

Using GGSCI, connect to the DSN for the source and target databases.
```
GGSCI> DBLOGIN SOURCEDB dsn USERIDALIAS alias
```
Delete the Extract first.
```
GGSCI> DELETE EXTRACT extname
```
Unregister the Extract.
```
GGSCI> UNREGISTER EXTRACT extname
```

Parent topic: Additional Considerations

Removing Table-level Supplemental Logging

If a table is no longer required to be captured by Oracle GoldenGate and the TABLE parameter for the table has been removed from the Extract parameter file, or TABLEEXCLUDE is used to exclude the table from a wildcard statement, then supplemental logging can be removed from the table.

Note:

If the Extract resolves a table that does not have supplemental logging enabled, it will Abend depending on the type of DML operation.

Using DELETE TRANDATA to remove supplemental logging sets the Replicat Identity level of the table to NOTHING. Supplemental logging can be disabled using 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 DELETE TRANDATA command within GGSCI or the Microservices Architecture Admin Client.

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

GGSCI> DBLOGIN SOURCEDB dsn_name USERIDALIAS alias_name
 GGSCI> DELETE TRANDATA schema.tablename

To check the level of supplemental logging:

GGSCI> INFO TRANDATA schema.tablename

Parent topic: Additional Considerations