Thursday, September 7, 2017

Implementation of a GoldenGate Replicate Hub: GoldenGate Remote Capture and Deliver Goldengate processes

Oracle GoldenGate can run on a machine separate from the source and/or target database server. The setup is called GoldenGate remote capturing and delivery. A creation of a GoldenGate Replicate Hub. On Oracle Databases the remote capture can be a remote integrated capture or downstream capture. For remote delivery can be an integrated delivery, a coordinated delivery or classic delivery.

Architect overview

GoldenGate Replicatie Hub - Remote Capture and Delivery


Oracle GoldenGate Remote Capture and Delivery Support Overview

GoldenGate Replicate Hub or GoldenGate Remote Capture and Delivery deployment allows us to offload the GoldenGate processes from the source and target databases servers. The Remote Capture and Delivery process uses the SQL*Net for connection to the source and target database. The GoldenGate Manager, the Capture and Delivery processes runs on the GoldenGate Replicate Hub including the maintenance of the trail and other goldengate files in the Goldengate file-structure on the GoldenGate server. The GoldenGate capture process uses the Logmining server at the source database to mine the redo logs. The GoldenGate Replicate Hub Capture process does not offload the source database for log mining like the GoldenGate Downstream deployment does.

Hereby a summary what is supported by Oracle GoldenGate
  1. What is Supported
    • As of 12.1.2.+ version, the following Oracle GoldenGate packages support remote capture and delivery:
      • Oracle GoldenGate for Oracle DB            (capture/delivery) 
      • Oracle GoldenGate for MySQL                (delivery) 
      • Oracle GoldenGate for DB2 LUW            (capture/delivery)
      • Oracle GoldenGate for Informix               (capture/delivery) 
      • Oracle GoldenGate for Big Data               (delivery) 
      • Oracle GoldenGate for Teradata               (delivery) 
      • Oracle GoldenGate for iSeries                  (delivery) 
      • Oracle GoldenGate for SQL Server          (delivery) 
      • Oracle GoldenGate for JMS                      (capture/delivery)
    • GoldenGate NOT support remote capture.
      • Oracle GoldenGate for DB2 z/OS
      • Non-Stop
      • DB2 iSeries
      • SQL Server  
  2. How to Use Remote Capture and Delivery
    • To run remote capture/delivery, different databases use different approaches. 
      • Oracle GoldenGate for Oracle DB
        You can perform remote capture with the following two methods: 
        1.  Integrated Capture with  SQL*Net connection 
        2.  Downstream capture in either real-time or archive log mode.
          For downstream data capture mode, you need to setup Oracle Data Guard  redo log files shipment  to the downstream database [link]. It's required the source database to be 10.2.0.4+ and the downstream database to be 11.2.0.3.0+. 
      • Oracle GoldenGate for DB2 LUW
        With DB2 connect, user can setup the remote DB2 as if they are an local database instance. Oracle GoldenGate then can capture from the remote DB2 database via the local accessing point setup by the DB2 connect. 
      • Oracle GoldenGate for Informix
        You can setup the ODBC connection to access the informix database on the remote server Oracle GoldenGate machine for access. The followings requirements need to be met: Endian order of both the systems should be same and the second is the bit width (32bit or 64bit) has to be same. For example from Linux to Linux, Windows to Windows, Solaris to Solaris but not across.
      • Oracle GoldenGate for MySQL
        For Oracle GoldenGate to delivery to MySQL database remotely, you just need to use the TARGETDB to specify the target database connection with the username and password. The MySQL user should have the remote access privileges. 

Technical Questions to Ask Before Choosing setup the Remote Capture and Delivery

What should you ask yourself before implementing a Goldengate Remote capture and delivery architecture or GoldenGate Replicate Hub 

Questions to ask and validate for implementing GoldenGate Remote Capture and Delivery
  1. Does Oracle GoldenGate support the remote capture and delivery for the source and target databases? 
    • Validate the Remote capture and delivery Matrix for Source and Target database types/versions
    • Which Goldengate Version can handle remote capture or delivery processes
      • Answer should be => Yes
  2. Do you have a high standard on the replication throughput and latency? 
    • Oracle remote capture and delivery is general 15-20 slower than a nativeGoldengate implementation, because of network communications
      • Answer should be => No
  3. Do your source/target databases operating systems have the same endian format as the Goldengate Replicate hub?
    • The server running Oracle GoldenGate and the server running the database or database server have to have the same Endiannes
      • Answer should be => Yes 

How to Configure Remote Capture and Delivery for Oracle Database

The implementation og Remote GoldeGate capture and delivery process does not change from the steps of Native Goldengate implementation. The only thing that is different, Goldengate is not installed on a Database server. 
  1. Configure the OGG server hub for installing the Goldengate binaries
  2. Setup a SQL*Net on the OGG Replicate server Hub
    • Source DB SQL*Net

      source_db =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = sourcedb.joords.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = source_db)
          )
        )
      
      
    • Target DB SQL*Net

      target_db =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = targetdb.joords.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = target_db)
          )
        )
      
      
  3. Setup the credential store
    • Add Credential store

      add credentialstore
      
      
      • Source alias

        alter credentialstore add user source_db_admin@source_db password XXX alias oggadmin_source

      • Target Alias

        alter credentialstore add user source_db_admin@target_db password XXX alias oggadmin_target
        
        
  4. Create extract     =>  Remote Capture Process
  5. EXTRACT EXT01
    useridalias  oggadmin_source
    TranlogOptions IntegratedParams (max_sga_size 256)
    ExtTrail ./dirdat/in
    DiscardFile ./dirrpt/ext01.dsc, Append Megabytes 50
    LOGALLSUPCOLS
    UPDATERECORDFORMAT COMPACT
    REPORTCOUNT EVERY 2 HOURS, RATE
    Table JOORDS_src.*;
    
    
  6. Create  replicate =>  Remote Delivery Process
  7. REPLICAT REP01
    useridalias oggadmin_target
    DBOPTIONS INTEGRATEDPARAMS (parallelism 6)
    DISCARDFILE ./dirrpt/REPIN.dsc, Purge
    REPORTCOUNT EVERY 5 records, RATE
    MAP JOORDS_src.*, TARGET JOORDS_trg.*;
    
    
  8. Register and Start the processes in GoldenGate Replicate Hub instance

The GoldenGate Replicate Hub is not a GoldenGate Downstream implementation.

  • GoldenGate Downstream deployment allows you to offload the source database extract load to intermediate or target server. The source database ships its redo logs to a downstream database, and Extract uses the logmining server at the downstream database to mine the redo logs. A downstream mining database can accept both archived logs and online redo logs from a source database. On the Downstream server there is a Native GoldenGate operational.
References: