• Home
    • 2018
    • 2017
  • Oracle GoldenGate
    • Core Platform
    • Cloud Service
    • For Oracle DB
    • For Big Data
    • For MySQL
    • For DB2 z/OS
    • For DB2 LUW
    • For DB2 i
    • For Informix
    • For SQL Server
    • For Teradata
    • For AWS Aurora
    • Foundation Suite
    • Use Cases
  • Data Management
    • General
    • Big Data
    • Oracle Database
    • MySQL
    • MS SQL Server
    • IBM DB2
    • IBM Informix
    • Teradata
    • SAP ASE
  • Data Science
  • IT
    • IT Management
    • IT Technology
  • Reading
  • About
Jinyu's Blog
About Data, Database, Integration and IT 

Ports Used by Oracle GoldenGate

4/14/2017

0 Comments

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

9 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) 
Picture
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 
  1. Introducing Oracle Data Integrator and Oracle GoldenGate by Marco Ragogna
  2. Oracle GoldenGate Extract Recovery Process.
  3. About Checkpoints (Oracle GoldenGate 12.1.2 Documentation Appendix E)
  4. Oracle GoldenGate: Automated Recovery From TCP/IP Network Errors, Oracle A-Team Blog
  5. Application checkpointing, Wikipidia
Created: 7/1/2014 Last Updated: 4/3/2018
9 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 
  1. 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
  2. Using Oracle GoldenGate to Achieve Operational Reporting for Oracle Applications, July 2013
  3. Oracle Consulting Technical Upgrade Service for JD Edwards EnterpriseOne, 2013
  4. Gain More Business Value, Choice and Confidence with JD Edwards EnterpriseOne Managed Cloud Service, 2013
0 Comments

    Oracle GoldenGate

    This blog has updates, tips and tricks for Oracle GoldenGate.

    Categories

    All
    12.1 New Feature
    12.2 New Feature
    12.3 New Feature
    Azure
    Basic Concepts
    Best Practices
    Cloud
    Customer Story
    DB2 For ISeries
    DB2 For LUW
    DB2 For Z/OS
    Disaster Recovery
    Globalization
    GoldenGate Fundamentals
    GoldenGate Security
    How To
    HP NonStop
    Informix IDS
    Installation
    JD_Edward
    Manager
    MySQL
    Network
    News
    Oracle DB
    Release
    SQL Server
    Sybase
    Teradata
    Top
    Troubleshooting
    Tutorial
    Use Case
    User Story

    Archives

    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    May 2016
    April 2016
    March 2016
    February 2016
    December 2015
    November 2015
    October 2015
    July 2015
    June 2015
    May 2015
    March 2015
    February 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    October 2013

    RSS Feed

Copyright © 2010-2027 Jinyu Wang.