Blog Archives

Ports Used by Oracle GoldenGate

4/14/2017

Oracle GoldenGate Manager is the controller process that instantiates the Oracle GoldenGate processes. Oracle GoldenGate manager requires opening ports for both the itself and the local processes.

Found this picture on twitter. It's fun while intuitive way of explaining TCP/IP. :-)

First, each Oracle GoldenGate manager process has to have a unique port. This port is used to communicate between the manager process and other Oracle GoldenGate processes. This is configured in the manager parameter file (mgr.prm) using the PORT parameter.

Second, for Oracle GoldenGate local processes to bind with remote communications, there is a need for a range of ports. This range can start starting at port 7840 or a customized range of up to 5000 other ports (as of GoldenGate 12.2). This is configured with the DYNAMICPORTLIST parameter. The followings are the processes needs ports allocated by DYNAMICPORTLIST:

Collector: to communicate with a remote Extract to receive incoming data.
Replicat: to communicate with a remote Extract to receive data during an initial load task.
Passive Extract: to communicate with a remote Collector
GGSCI: to issue remote commands

The following is an example manager parameter file:

port 7809
DynamicPortList 7820-7830, 7833, 7835

Fully understand how GoldenGate uses and manages the ports will help you speed up the setup process especially in a cloud environment where opening ports is highly restricted.

0 Comments

Oracle GoldenGate Checkpoints

4/13/2017

10 Comments

Checkpointing is a technique that Oracle GoldenGate uses to add fault tolerance into the replication process. GoldenGate extract, data pump, and replicat processes record their read and write positions and time along the data flow. These positions are called Checkpoints. This blog discusses the details of what checkpoint is, how to create checkpoint tables and how to use checkpoints when recovering an Oracle GoldenGate process.

Best Practices: Creating Checkpoint Tables for Oracle GoldenGate Replicats
Using checkpoint table is the preferred approach.To avoid specifying checkpoint table name each time creating a new replicat, you can set the checkpoint table info in the ./GLOBALS file. (i.e. CHECKPOINTTABLE test.checkpoint_tbl)

1. What are Oracle GoldenGate Checkpoints?
Checkpoints are used in all of the GoldenGate processes [3]:

Extract (Integrated): checkpoints are kept in both the source database and the checkpoint file (*.cpe file). The checkpoint files are used with higher priority. There are three types of checkpoints: Recovery Checkpoint showing the start of the oldest unprocessed (open) transaction, Read Checkpoint (or Current Checkpoint) indicating the current read position related to the database log file, and Write Checkpoint showing the last transaction position that is saved into the trail file.
Pump: checkpoints are kept in the checkpoint file. There are two types of checkpoints: Read Checkpoint showing the current read position from the trail file, and Write Check showing the last transaction that are saved to the trail file on the target.
Replicat: the checkpoint can be stored in a database or a checkpoint file ( *.cpr file). The checkpoint table is shared by all replicat groups in one Oracle GoldenGate instance. There is one checkpoint: Read Checkpoint showing the position of the last record in the trail file read by the replicat and has been delivered to the target. For recoverability, you also need to know high watermark (LOG_CSN) and low watermark of replicat(LOG_CMPLT_CSN). Any transaction with a CSN greater than the high watermark has not been processed, and any transaction with a CSN lower than the low watermark has already been processed. The role of replicat checkpoint is when the replicat process is restarted, Oracle GoldenGate can apply only transactions since the last checkpoint.

There is another type of checkpoint called the Startup Checkpoint. The startup checkpoint is the first checkpoint that is made in the data source when the replication process starts.

Info: Integrated Capture for Oracle Database can only use checkpoint files.

As discuss earlier, there two ways to save of checkpoints:

Checkpoint File: a binary file (*.cpe - Extract, *.cpb, *.cpr - Replicat) is stored in the $OGG_HOME/dirchk/ directory.
Checkpoint Table: a table created in the database. The checkpoint table can be shared across processes or per each process.The advantage of the checkpoint table is that the table can be flashed back along with the data.

TIP: Can I avoid creating the checkpointtable on the target database server?

Yes, but you have to turn off the checkpoint table option with the NODBCHECKPOINT when adding the replicat. For example: "ADD REPLICAT sales, EXTTRAIL dirdat\rt, NODBCHECKPOINT"

2. How to Create and Manage Checkpoint Tables
The followings are examples on how to create and manage checkpoint tables for the replicat.

2.1 Create Checkpoint Table

GGSCI 1> dblogin sourcedb test@localhost:3305 userid root password mysql
Successfully logged into database.
GGSCI 2> create checkpointtable checkpoint_56_tbl
Successfully created the checkpoint table checkpoint_56_tbl.

2.2 Check the Checkpoint Table

GGSCI 6> info checkpointtable checkpoint_tbl
Checkpoint table checkpoint_tbl created 2014-07-22 20:29:17.

2.3 Delete the Checkpoint Table

GGSC 1> dblogin sourcedb test@localhost:3305 userid root password mysql
Successfully logged into database.
GGSCI 2> delete checkpointtable checkpoint_56_tbl
This checkpoint table may be required for other installations.  
Are you sure you want to delete this checkpoint table? yes
Successfully deleted checkpoint table checkpoint_56_tbl.

The following is an example checkpoint table definition in MySQL DB:

mysql> desc checkpoint_tbl
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| group_name     | varchar(8)    | NO   | PRI | NULL    |       |
| group_key      | decimal(19,0) | NO   | PRI | NULL    |       |
| seqno          | decimal(10,0) | YES  |     | NULL    |       |
| rba            | decimal(19,0) | NO   |     | NULL    |       |
| audit_ts       | varchar(29)   | YES  |     | NULL    |       |
| create_ts      | datetime      | NO   |     | NULL    |       |
| last_update_ts | datetime      | NO   |     | NULL    |       |
| current_dir    | varchar(255)  | NO   |     | NULL    |       |
| log_bsn        | varchar(128)  | YES  |     | NULL    |       |
| log_csn        | varchar(128)  | YES  |     | NULL    |       |
| log_xid        | varchar(128)  | YES  |     | NULL    |       |
| log_cmplt_csn  | varchar(128)  | YES  |     | NULL    |       |
| log_cmplt_xids | varchar(2000) | YES  |     | NULL    |       |
| version        | decimal(3,0)  | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

3. Checkpoint and Oracle GoldenGate Process Recovery
Let's discuss how checkpoints are used during the Oracle GoldenGate recovery. The example scenario is:

The network connection between the source and target is broken. Oracle GoldenGate data pump abends after several retries for the reconnection(defined in the tcperrs files [4]) . The extract keeps running and creating new trail files. After some time of this network outage, the machine hosting the GoldenGate extract runs out of disk space, and the Oracle GoldenGate extract process abends.

After this issue is discovered, Oracle GoldenGate recovery is needed as described and the checkpoints are used during the recovery process:

Frist, the network connect is fixed. The pump is restarted so that the existing trail files then can be delivered to the target database. The pump process will use the write checkpoint on the target to resend the trail files. After the transactions are applied to the target, the old trail files are then purged. (If fixing network connect takes time and you have to start GoldenGate capture immediately, you can consider to add new disk spaces. )
Restart the extract. The extract will scan the source redo/archive log files from the recovery checkpoint to recover long running open transactions. After reaching to the read checkpoint, all transactions are then captured, the new trail files are created, and the write checkpoint moves forward.

Note: To allow Oracle GoldenGate to recover, you need the following setups:

Make sure no trail file is purged before replication completed on the target. You need to configure GoldenGate manager to purge trail files based on checkpoints using PURGEOLDEXTRACTS ...USECHECKPOINTS configuration.
Make sure you keep all the log files/archive log files till the recovery checkpoint. For Oracle database, integrated capture by default enables RMAN to hold on source DB from removing archive logs if they are still needed by Oracle GoldenGate replication.

References

Introducing Oracle Data Integrator and Oracle GoldenGate by Marco Ragogna
Oracle GoldenGate Extract Recovery Process.
About Checkpoints (Oracle GoldenGate 12.1.2 Documentation Appendix E)
Oracle GoldenGate: Automated Recovery From TCP/IP Network Errors, Oracle A-Team Blog
Application checkpointing, Wikipidia

Created: 7/1/2014 Last Updated: 4/3/2018

10 Comments

What's Oracle GoldenGate's support for Oracle JD Edward Enterprise One?

4/10/2017

0 Comments

Oracle GoldenGate supports creating operational reporting for Oracle JD Edwards EnterpriseOne [1][2]. In this deployment, you can select tables for Oracle GoldenGate to replicate from the primary JD Edwards EnterpriseOne database to a secondary instance. By creating an operational reporting repository, you can:

Offload queries from the primary instance
Optimize reporting system based on the use cases such as creating better sort areas, partitions, and indexes

Using Oracle GoldenGate for migration is not a supported use case for JD Edwards EnterpriseOne by the product but it is a service supported by Oracle consulting [3][4]. You would also need to contact Oracle Consulting for any customized implementations.

Resources

Oracle Support Document 1112406.1 (Deploying Oracle GoldenGate to Achieve Operational Reporting for Oracle JD Edwards) can be found at: https://mosemp.us.oracle.com/epmos/faces/DocumentDisplay?id=1112406.1
Using Oracle GoldenGate to Achieve Operational Reporting for Oracle Applications, July 2013
Oracle Consulting Technical Upgrade Service for JD Edwards EnterpriseOne, 2013
Gain More Business Value, Choice and Confidence with JD Edwards EnterpriseOne Managed Cloud Service, 2013

0 Comments

Ports Used by Oracle GoldenGate

Oracle GoldenGate Checkpoints

﻿What's Oracle GoldenGate's support for Oracle JD Edward Enterprise One?

Oracle GoldenGate

Categories

Archives

What's Oracle GoldenGate's support for Oracle JD Edward Enterprise One?