Oracle GoldenGate: Passive-Alias Extract

Introduction

The Oracle GoldenGate (OGG) Passive-Alias Extract may be used for data replication between source databases located on servers in untrusted security zones and target database servers in trusted security zones. In this article we’ll configure Alias Extracts on a trusted zone OGG instance that will interact with Passive Extracts on untrusted zone OGG instances for data capture and delivery to the trusted zone database server.

The concepts, scripts, and information presented in this article are for educational purposes only. They are not supported by Oracle Development or Support, and come with no guarantee or warrant for functionality in any environment other than the test system used to prepare this article. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.

Main Article

The figure below depicts a simple Passive-Alias Extract architecture scheme.

The Passive Extract is controlled via the Alias Extract at the trusted target. Starting with the green arrows, this is how the replication flow works:

(1) A start command for the Alias Extract is intercepted by Manager on the trusted target.

(2) This command is forwarded to Manager on the untrusted source.

(3) Manager on the untrusted source spawns the Passive Extract.

(4) The Passive Extract sends a connection request back to Manager on the trusted target, and after security validation a Server process is spawned to facilitate data transfer between the Passive Extract and the trusted target.

(5) The Passive Extract will store data captured at the untrusted source in Trails on the trusted target.

(6) Replicat will read the Trail and apply all captured transactional data to the database at the trusted target.

The remainder of this article assumes (1) OGG has been installed and instances created on the untrusted source and trusted target servers, (2) firewall rules have been updated to allow two way communication between the OGG instances, and (3) the source and target databases have been properly configured to support OGG data capture and delivery.

Untrusted Source Configuration

We’ll start with the configuration of OGG at the untrusted source. The configuration steps consist of: (1) create the OGG credentialstore and setup database access for data capture, (2) configure the OGG Manager ACCESSRULE option to allow connectivity and process access from the trusted target, (3) configure the Passive Extract to perform data capture, OGG Trail data encryption, and encrypted data transmission to the OGG instance at the trusted target, and (4) add the Passive Extract to the OGG instance on the untrusted source.

Create the OGG CredentialStore

Start GGSCI and execute the add credentialstore command to create the encrypted files used to store database access credentials used by OGG. Use the alter credentialstore command to create an alias for the OGG user’s database access credentials.

GGSCI (hplap) 1> add credentialstore
.
Credential store created in ./dircrd/.
.
GGSCI (hplap) 2> alter credentialstore add user ggadmin alias ggalias
Password:
.
Credential store in ./dircrd/ altered.

Configure Manager

Create the OGG Manager parameter file.

GGSCI (hplap) 3> edit param mgr

The settings we’ll use for this test are:

port 16000
dynamicportlist 16001-16005
purgeoldextracts ./dirdat/*, usecheckpoints
--
-- access rules:
--
-- deny access to everything not explicitly granted
--
accessrule, prog *, deny, pri 99
--
-- allow access from this server, hplap
-- grant access to all OGG functionality
--
accessrule, prog *, ipaddr 127.0.0.1, allow, pri 1
accessrule, prog *, ipaddr 192.168.120.45, allow, pri 1
--
-- allow access from trusted target lpenton-lap
-- grants access for passive extract control
--
accessrule, prog *, ipaddr 192.168.120.155, allow, pri 1

Create the Passive Extract

Create the Passive Extract parameter file.

GGSCI (hplap) 4> edit param e1embk

The configuration settings we’ll use for this test are:

extract e1embk
--
-- use a credentialstore alias to access the database
--
useridalias ggalias
--
-- enable transmission encryption
--
rmthostoptions encrypt AES256
--
-- encrypt the ogg tail
--
encrypttrail
rmttrail ./dirdat/embk/ea
--
-- report number operations captured and
-- operations per second hourly
--
reportcount every 60 minutes, rate
--
-- create a daily report of activity
--
report at 00:01
--
-- reset internal stats counters whenever
-- a new report files is created
statoptions resetreportstats
--
-- roll the discard and report files weekly
--
discardrollover at 00:01 on sunday
reportrollover at 00:01 on sunday
--
-- capture records for these tables
--
table bunnypos.pos_machine;
table bunnypos.pos_history;
table bunnypos.pos_receipt;
table bunnypos.pos_sales;

Enable schema level supplemental logging required for update operation replication.

GGSCI (hplap) 5> dblogin useridalias ggalias
Successfully logged into database.
.
GGSCI (hplap as ggadmin@orcl) 6> add schematrandata bunnypos
2018-08-02 10:40:25  INFO    OGG-01788  SCHEMATRANDATA has been added on schema bunnypos.
2018-08-02 10:40:25  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema bunnypos

Add the Passive Extract to the OGG instance and register it with the database.

GGSCI (hplap as ggadmin@orcl) 7> add extract e1embk, passive, integrated tranlog, begin now
EXTRACT (Integrated) added.
.
GGSCI (hplap as ggadmin@orcl) 8> register extract e1embk1 database
.
2018-08-15 14:10:41  INFO    OGG-02003  Extract E1EMBK1 successfully registered with database at SCN 1723650

Add the OGG Remote Trail to the instance.

GGSCI (hplap as ggadmin@orcl) 9> add rmttrail ./dirdat/embk/ea, extract e1embk, megabytes 1000
RMTTRAIL added.

Start the Untrusted Source Manager

Complete the untrusted source setup by starting the OGG Manager.

GGSCI (hplap as ggadmin@orcl) 10> start mgr
Manager started.
.
GGSCI (hplap as ggadmin@orcl) 11> info mgr
.
Manager is running (IP port hplap.16000, Process ID 8035).

 

Trusted Target Configuration

To configure the trusted target we need to do: (1) create the OGG credentialstore and setup database access for data delivery, (2) create the OGG wallet and setup the Master Key for encrypted data transmission, (3) copy the OGG wallet to each untrusted source OGG instance, (4) configure the OGG Manager ACCESSRULE option to allow connectivity to Server from the untrusted source, (5) configure OGG Manager to automatically start the Alias Extracts, (6) Configure Replicat for data apply, and (7) add the Alias Extract and Replicat to the OGG instance.

Create the OGG CredentialStore

Start GGSCI and execute the add credentialstore command to create the encrypted files used to store database access credentials used by OGG. Use the alter credentialstore command to create an alias for the OGG user’s database access credentials.

GGSCI (LPENTON-LAP) 1> add credentialstore
Credential store created in .\dircrd\.
.
GGSCI (LPENTON-LAP) 2> alter credentialstore add user ggadmin alias ggalias
Password:
.
Credential store in .\dircrd\ altered.

Create the OGG Wallet

Create the OGG Wallet and MasterKey which will be used for data transmission encryption between the untrusted source and trusted target.

GGSCI (LPENTON-LAP) 3> create wallet
Created wallet at location ‘dirwlt’.
Opened wallet at location ‘dirwlt’.
.
GGSCI (LPENTON-LAP) 4> add masterkey
Master key ‘OGG_DEFAULT_MASTERKEY’ added to wallet at location ‘dirwlt’.

Copy the contents of the $OGG_HOME/dirwlt directory to the untrusted source.

Configure Manager

Create the OGG Manager parameter file.

GGSCI (LPENTON-LAP) 5> edit param mgr

On the trusted target, the OGG Manager configuration will look like this:

port 17000
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 2
purgeoldextracts ./dirdat/embk/*, usecheckpoints, minkeepdays 2

— Set ports for OGG Server connections from untrusted source Extracts

dynamicportlist 17010-17025

— allow access to server from untrusted source hplap

accessrule, prog server, ipaddr 192.168.120.45, allow, pri 1

— automatically start all OGG Groups whenever manager starts

autostart er *

— auto restart the alias extracts is the fail
— make 10 attempts, waiting 5 minutes between each
— after 120 minutes reset the counters and begin trying again

autorestart extract a*, retries 10, waitminutes 5, resetminutes 120

— this is a windows machine so have manager wait 5 minutes before
— it does the autostart. this gives things time to “calm down”
— in the os before ogg is started.
bootdelayminutes 5

— every 60 minutes check if there is lag on any ogg group

lagreportminutes 60

— for lag in excess of 5 minutes, write an informational message to
— the manager report file and syslogs
laginfominutes 5

— for lag in excess of 30 minutes, write a critical error message to
— the manager report file and syslogs
lagcriticalminutes 30

Start the OGG Manager

GGSCI (LPENTON-LAP) 6> start mgr
Manager started.
.
GGSCI (LPENTON-LAP) 7> info mgr
.
Manager is running (IP port LPENTON-LAP.17000, Process ID 4208)

Create the Alias Extract

The Alias Extract does not have a configuration file, it is created via the GGSCI add extract command.

GGSCI (LPENTON-LAP) 8> add extract a1embk, rmthost 192.168.120.45, mgrport 16000
, rmtname e1embk
EXTRACT added.

Any GGSCI command executed against the Alias Extract is forwarded to the untrusted source for execution against the Passive Extract.

GGSCI (LPENTON-LAP) 9> status a1embk
Forwarding command to E1EMBK@192.168.120.45:16000
EXTRACT E1EMBK: STOPPED

Create the Replicat

Complete the OGG replication feed by configuring and adding Replicat to the trusted target. For simplicity, I’m using Classic Replicat; however, in a production environment I would choose to either use Integrated Replicat for low to medium volume replication feeds, Parallel Apply for high volume environments, or Coordinated Apply where neither of those are supported.

GGSCI (LPENTON-LAP) 10> dblogin useridalias ggalias
Successfully logged into database.
.
GGSCI (LPENTON-LAP as ggadmin@lpenton) 11> edit param r1embk

My Replicat configuration is:

replicat r1embk

— use a credentialstore alias to access the database

useridalias ggalias

— report number operations captured and
— operations per second hourly

reportcount every 60 minutes, rate

— create a daily report of activity

report at 00:01

— reset internal stats counters whenever
— a new report files is created
statoptions resetreportstats

— roll the discard and report files weekly

discardrollover at 00:01 on sunday
reportrollover at 00:01 on sunday

— map untrusted source tables to
— tables in the trusted target database

map bunnypos.*, target bunnypos.*;

Create the OGG Checkpoint Table and add the Classic Replicat to the OGG instance.

GGSCI (LPENTON-LAP as ggadmin@lpenton) 12> add checkpointtable ggadmin.ggchkpoint
.
Successfully created checkpoint table ggadmin.ggchkpoint.
.
GGSCI (LPENTON-LAP as ggadmin@lpenton) 13> add replicat r1embk, exttrail ./dirdat/embk/ea, checkpointtable ggadmin.ggchkpt
REPLICAT added.

Test the Configuration

On the trusted source, start the Alias Extract.

GGSCI (LPENTON-LAP as ggadmin@lpenton) 14> start a1embk
.
Sending START request to MANAGER@192.168.120.45:16000 …
.
Sending START request to MANAGER@LPENTON-LAP:17000 …

Check the Passive Extract is running.

GGSCI (LPENTON-LAP as ggadmin@lpenton) 15> info a1embk
.
EXTRACT    A1EMBK    Last Started 2018-08-16 07:24   Status RUNNING
ALIAS      E1EMBK    192.168.120.45:16000
.
Forwarding command to E1EMBK@192.168.120.45:16000
.
EXTRACT    E1EMBK    Last Started 2018-08-16 07:24   Status RUNNING
Checkpoint Lag       00:00:09 (updated 00:00:10 ago)
Process ID           14750
Log Read Checkpoint  Oracle Integrated Redo Logs
2018-08-16 07:25:32
SCN 0.1864674 (1864674)

Start the Classic Replicat.

GGSCI (LPENTON-LAP as ggadmin@lpenton) 16> start r1embk
.
Sending START request to MANAGER …
REPLICAT R1EMBK starting

Check the Classic Replicat is running.

GGSCI (LPENTON-LAP as ggadmin@lpenton) 17> info r1embk
.
REPLICAT   R1EMBK    Last Started 2018-08-16 07:27   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           8968
Log Read Checkpoint  File ./dirdat/embk/ea000000000
2018-08-16 07:24:19.798486  RBA 1459

Start the application on the untrusted source and validate data is being replicated to the trusted target.

GGSCI (LPENTON-LAP as ggadmin@lpenton) 18> stats r1embk, totalsonly *.*
.
Sending STATS request to REPLICAT R1EMBK …
.
Start of Statistics at 2018-08-16 07:44:03.
.
Cumulative totals for specified table(s):
.
*** Total statistics since 2018-08-16 07:43:38 ***
Total inserts                                    694.00
Total updates                                    695.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1389.00
.
*** Daily statistics since 2018-08-16 07:43:38 ***
Total inserts                                    694.00
Total updates                                    695.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1389.00
.
*** Hourly statistics since 2018-08-16 07:43:38 ***
Total inserts                                    694.00
Total updates                                    695.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1389.00
.
*** Latest statistics since 2018-08-16 07:43:38 ***
Total inserts                                    694.00
Total updates                                    695.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1389.00
.
End of Statistics.

At the trusted target, we execute GGSCI commands to get information about the Passive Extract running on the untrusted source:

GGSCI (LPENTON-LAP as ggadmin@lpenton) 19> info a1embk
.
EXTRACT    A1EMBK    Last Started 2018-08-16 07:33   Status RUNNING
ALIAS      E1EMBK    192.168.120.45:16000
.
Forwarding command to E1EMBK@192.168.120.45:16000
.
EXTRACT    E1EMBK    Last Started 2018-08-16 07:33   Status RUNNING
Checkpoint Lag       00:00:07 (updated 00:00:08 ago)
Process ID           15420
Log Read Checkpoint  Oracle Integrated Redo Logs
2018-08-16 07:33:48
SCN 0.1865697 (1865697)

GGSCI (LPENTON-LAP as ggadmin@lpenton) 20> stats a1embk, totalsonly *.*
Forwarding command to E1EMBK@192.168.120.45:16000
.
Sending STATS request to EXTRACT E1EMBK …
.
Start of Statistics at 2018-08-16 07:46:34.
.
Output to ./dirdat/embk/ea:
.
Cumulative totals for specified table(s):
.
*** Total statistics since 2018-08-16 07:33:46 ***
Total inserts                                    838.00
Total updates                                    839.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1677.00
.
*** Daily statistics since 2018-08-16 07:33:46 ***
Total inserts                                    838.00
Total updates                                    839.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1677.00
.
*** Hourly statistics since 2018-08-16 07:33:46 ***
Total inserts                                    838.00
Total updates                                    839.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1677.00
.
*** Latest statistics since 2018-08-16 07:33:46 ***
Total inserts                                    838.00
Total updates                                    839.00
Total deletes                                      0.00
Total discards                                     0.00
Total operations                                1677.00
.
End of Statistics.

(Report file output truncated for brevity)

GGSCI (LPENTON-LAP as ggadmin@lpenton) 30> view report a1embk
Forwarding command to E1EMBK@192.168.120.45:16000
.
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:24:34
.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
.
.
Starting at 2018-08-16 07:33:40
***********************************************************************
.
Operating System Version:
Linux
Version #1 SMP Tue Aug 14 21:49:04 UTC 2018, Release 3.10.0-862.11.6.el7.x86_
Node: hplap
Machine: x86_64
.                        soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited
.
Process id: 15420
.
Description:
.
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

GGSCI GGSCI (LPENTON-LAP as ggadmin@lpenton) 31> stop a1embk
Forwarding command to E1EMBK@192.168.120.45:16000
.
Sending STOP request to EXTRACT E1EMBK …
Request processed.
.
GGSCI (LPENTON-LAP as ggadmin@lpenton) 32> status a1embk
Forwarding command to E1EMBK@192.168.120.45:16000
EXTRACT E1EMBK: STOPPED

Summary

In this article we demonstrated data replication between a target database in a trusted server zone and a source database in a less trusted server zone using Oracle GoldenGate Passive-Alias Extract functionality.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

Add Your Comment