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 theADD
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.
-
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.
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 4-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. 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 aliasggeast
:(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.
“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
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.