19 Additional Considerations
- Adding a Heartbeat Table
- Enabling Bi-Directional Loop Detection
- Deleting an Extract
- Removing Table-level Supplemental Logging
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 16-* 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.
-
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 theTARGETONLY
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
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.
*****PGPASSWORD="gguserpasswd" psql -U gguser -d dbname -h remotehost -p 5432 -c "select ggschema.gg_hb_job_run();" >/dev/null
2>&1
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 theGLOBALS
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) andFILTERTABLE
parameters, using the Replicat’s checkpoint table for the filtering table.TRANLOGOPTIONS IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ggadmin.oggcheck
Note:
Oracle GoldenGate for PostgreSQL supports only oneFILTERTABLE
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.
-
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
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 DELETE TRANDATA
command within GGSCI.
DELETE TRANDATA
from
GGSCI.GGSCI> DBLOGIN SOURCEDB dsn_name USERIDALIAS alias_name
GGSCI> DELETE TRANDATA schema.tablename
GGSCI> INFO TRANDATA schema.tablename
Parent topic: Additional Considerations