Validated April 22, 2021 with GG19.1
Oracle GoldenGate can now be deployed in a private subnet via Oracle Cloud Infrastructure Marketplace and autonomous databases can be deployed with a private endpoint. Prohibiting direct access from the internet increases security for cloud services.
Oracle GoldenGate is a comprehensive software package for enabling the replication of data in heterogeneous data environments. The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.
Oracle Cloud Infrastructure's Autonomous Database is a fully managed, preconfigured database environment with three workload types available, Autonomous Transaction Processing, Autonomous Data Warehouse and Autonomous JSON Database. You do not need to configure or manage any hardware or install any software.
This post is a step-by-step guide for replicating autonomous databases with private endpoints using Oracle GoldenGate Marketplace Microservices Edition deployed in a private subnet. It builds upon the post Deploying GoldenGate Marketplace in a Private Subnet and is a companion future posts on using GoldenGate natively in OCI with a private endpoint.
Using autonomous databases for replication demonstrates the use of database listeners configured with TLS/SSL security and the TCPS protocol.
April 22, 2021 with GG 19.1
Before You Begin
Deploying Additional Components
Preparing GoldenGate for Autonomous Databases
Preparing Autonomous Databases for GoldenGate
Configuring the GoldenGate Extraction
Configuring the GoldenGate Replication
Validating Autonomous Database Replication
|VCN||Virtual Cloud Network|
|TLS||Transport Layer Security|
|OCI||Oracle Cloud Infrastructure|
|FQDN||Fully Qualified Domain Name|
|HTTPS||Hypertext Transfer Protocol Secure|
|ATP||Autonomous Transaction Processing|
|DNS||Domain Name System|
|ACL||Access Control List - Security List|
|SCN||Database System Change Number|
This post assumes:
GG marketplace has been deployed in a private subnet on OCI and is accessible via SSH and a public bastion compute instance.
The Source and Target ATPs are hosted in separate OCI regions in the same tenancy
There is no private connection (VPN, FastConnect, Remote Peering) between the regions and between clients and OCI.
Access to the private resources is provided by public load balancers acting as proxies.
A user account in an OCI tenancy for managing compute, database, object storage and network resources.
SSH private key and GG credentials for access to GG.
Admin credentials if using existing ATPs.
The individual Shell and SQL commands used in this post are consolidated in the Appendix
This section describes the additional OCI components necessary for the examples used in the post. The following table lists them with links for deployment reference.
The post examples use GG and the Source ATP in one OCI region (Phoenix)and the target ATP in another (Ashburn)
|VCN||Host Target Resources||link|
|INTERNET GATEWAY||For Public Subnet Outbound Traffic||link|
|NAT GATEWAY||For GG Private Subnet Outbound Traffic||link|
|SERVICE GATEWAY||For GG Private Access to Object Storage||link|
|SUBNET||Host LB and ATP Resources||link|
|LOAD BALANCER||Proxy for GG and ATP Access||link|
|NSG||Required for ATP provisioning||link|
|ATP||Source and Target Databases||link|
|ROUTE TABLE||Routes Source and Target VCN Traffic||link|
|ACCESS CONTROL||Allows Ingress and Egress for VCN Traffic||link|
|OS BUCKET||Stores Initial Exported ATP Source Data||link|
|AUTH TOKEN||For Access to OS||link|
Component Key Values
The sections following use component key values as variables. They are read from a parameter file obtained here. Download the file as my-gg19-components.sh and update it with your key values as you deploy the components. An example file is below:
The additional components are depicted below.
The following tables show component examples both needed and previously deployed (greyed out).
|PHX-VCN Public SN||10.221.8.0/27|
|PHX-VCN Private SN||10.221.8.32/27|
|ASH-VCN Public Subnet||10.221.12.0/27|
|ASH-VCN Private SN||10.221.12.32/27|
Note the Public IP address of the NAT Gateway for route table use
|Load Balancer||Public IP||PROTOCOL||PORT||TARGET IP|
Create a flexible load balancer in each region. This post has GG and the Source ATP in one region and the target ATP in another. Navigate to Networking > Load Balancers, select the Compartment and Region and click Create Load Balancer.
Only initial configurations are created here. After the ATPs are provisioned they are updated.
Select Load Balancer Type
Choose Load Balancer
Click Create Load Balancer
Choose your Compartment, VCN, and Public Subnet
Change the Health Check Policy Protocol to TCP
Change the Port to 1522
Change the Listener Name to LSNR-TCP-1522
Change the Listener Traffic Type to TCP
Change the Port to 1522
Update the Key Component File with the Public IP address
! Repeat for the Target Region
A Network Security Group must exist in each VCN where an ATP is deployed. However they do not require configuration. The examples use Security Lists rather than NSGs.
Create a NSG in both the source and target VCNs. Navigate to Networking > Virtual Cloud Networks, select the Compartment and Region and click the VCN Name.
Under Resources click Network Security Groups
Click Create Network Security Group
Enter a Name and click Next
Click Create without providing rules
In the OCI Console navigate to Autonomous Transaction Processing, create an ATP in the source and target VCNs and download the credentials from OCI.
Update the Key Component File with the Private IP addresses, Wallet Names and TNS Prefixes for the _low service.
For both ATPs, uncompress the downloaded credential packages (.zip file) and change the hostnames and ports in the tnsnames.ora files to the appropriate LB listening IP address and port.
Update the Key Component File with the modified Wallet Names
Update the load balancer in each region. Navigate to Networking > Load Balancers, select the Compartment and source Region and click on your load balancer Name.
Under Resources click Backend Sets and then click the Backend Set Name
Under Resources click Backends and then click Create Backend
Choose IP Addresses
Enter the ATP IP Address e.g. 10.nnn.nnn.nnn
Enter 1522 as the Port
Repeat for the Target Region
Navigate to Networking > Load Balancers, select the Compartment and Source Region and click on your load balancer Name.
Under Resources click Backend Sets and then click Create Backend Set
Enter BKND-SET-443 as the Name
Change the Health Check Protocol to TCP
Change the Port to 443
Click Create Backend Set
Click the new Backend Set Name
Under Resources click Backends and then click Add Backend
Choose Compute Instances
Check your GG instance
Check Manually configure security lists
Return to Load Balancer Details.
Under Resources click Listeners and then click Create Listener
Enter LSNR-TCP-443 as the Listener Name
Change the Protocol to TCP
Change the Port to 443
Select BKND-SET-443 from the Backend Set dropdown
Click Create Listener
|NAMESPACE||REGION||API||Public IP CIDR||Bucket|
Object Storage is used for the initial source ATP backup before GG extraction
Navigate to Object Storage > Object Storage, select the Compartment and Source Region and click Create Bucket
Enter a Bucket Name and click Create
Click on the bucket Name and note the Namespace
Find the source region's object storage API here.
Update the Key Component File with the Bucket Name, Namespace and Region API
Auth tokens are Oracle-generated token strings that are used to authenticate when accessing Object Storage
Identify a user with read and write access to the OS bucket created above e.g. dcarley
Note: You might consider using a local IAM user
Login to OCI as the above user, click on the User icon and then click User Settings.
Under Resources Click Auth Token
Click Generate Token
Add a Description and click Generate Token
Click Show and Copy the token to a notepad as it is not shown again
Update the Key Component File with the Auth Token User and the Auth Token
Attach an existing or new route table with the following rules to each subnet. Change the CIDR blocks for your network.
|SRC-VCN-PUBLIC||188.8.131.52/16||Internet Gateway||Internet Clients|
|SRC-VCN-PRIVATE||OCI <Source Region> Object Storage||Service Gateway||Source Initial Backup Before Extraction|
|SRC-VCN-PRIVATE||184.108.40.206/32||NAT Gateway||Target Load Balancer|
|TRGT-VCN-PUBLIC||220.127.116.11/32||Internet Gateway||GG VCN NAT Gateway|
Attach existing or new security lists with the following rules to each subnet. Change the CIDR blocks for your network.
|SUBNET||TYPE||SOURCE / DESTINATION||PORT||PROTOCOL||DESCRIPTION|
|SRC-VCN-PUBLIC||Ingress||18.104.22.168/16||22||TCP||Internet Client SSH to Bastion|
|SRC-VCN-PUBLIC||Ingress||22.214.171.124/16||443||TCP||Internet Client Browser to LB|
|SRC-VCN-PUBLIC||Egress||10.221.8.34/32||22||TCP||LB SSH to GG|
|SRC-VCN-PUBLIC||Egress||10.221.8.34/32||443||TCP||LB Browser to GG|
|SRC-VCN-PUBLIC||Egress||126.96.36.199/16||443||TCP||Internet Client Responses|
|SRC-VCN-PRIVATE||Ingress||10.221.8.3:22||22||TCP||From Bastion to GG|
|SRC-VCN-PRIVATE||Ingress||10.221.8.0/27||443||TCP||From LB Subnet to GG|
|SRC-VCN-PRIVATE||Ingress||10.221.8.34/32||1522||TCP||From GG to Source ATP|
|SRC-VCN-PRIVATE||Egress||10.221.8.36/32||1522||TCP||From GG to Source ATP|
|SRC-VCN-PRIVATE||Egress||188.8.131.52/19||443||TCP||From GG to Object Storage|
|SRC-VCN-PRIVATE||Egress||10.221.8.0/27||443||TCP||From GG to Source LB Subnet|
|SRC-VCN-PRIVATE||Egress||184.108.40.206/32||1522||TCP||From GG to Target LB|
|TRGT-VCN-PUBLIC||Ingress||220.127.116.11/32||1522||TCP||From GG VCN NAT Gateway|
|TRGT-VCN-PUBLIC||Egress||10.221.12.36/32||1522||TCP||To ATP from LB|
|TRGT-VCN-PRIVATE||Ingress||10.221.12.0/27||1522||TCP||From LB Subnet to ATP|
This section describes the GG updates required for replication.
Note: The Source ATP modified credentials are not necessary for GG because they are in the same or peered VCN. However they are still useful for access from a remote client. For GG use the original source credentials.
On Your Client copy the deployed component file and the modified compressed ATP credential files to your client home directory. Copy them to the GG server using the SSH configuration file created in the GG deployment post.
Display the GG LB Service Manager URL and default credentials
Use the URL in a browser e.g. Firefox, login with the default credentials, click on the User icon and then click Change Password. This post uses YOurPwd123_# for development. You are logged out.
Log in to the Service manager again with the new password. Click on the Source Administration Port link. Login to the Administration Server with the default password. Click on the User icon and then click Change Password. Change it to the same new password. You are logged out.
Change the URL back to the Service Manager URL. Login in with new password if prompted. Click on the Target Administration Port link. Login to the Administration Server with the default password. Click on the User icon and then click Change Password. Change it to the same new password. You are logged out.
Use the GG Admin Client to create a credential for the GGADMIN database user and password. This user is used in the Admin Client to connect to the Autonomous Database to perform commands that require a database connection. It is also used in the USERIDALIAS parameter for the Extract database connection.
GG Marketplace is installed with a self-signed certificate. Admin client allows connections when the server uses a self-signed certificate though this is not the default. Admin Client does not allow connecting to a server through HTTPS when the self-signed certificate is invalid. To override this behavior, use the "!" modifier with the CONNECT command.
This section describes the ATP updates required for extraction and replication. They are applied via the DB client in the GG instance.
Sample result: 37180061349397
This section describes the GG source extraction configuration. The extract is configured but not started.
This section describes the GG target replication configuration. The replication is configured but not started.
This section describes updating the source ATP table data, starting the extract and replication processes, creating a new source table and viewing the replicated changes.
This mimics changes that occur in the source ATP before the GG extract and replication processes have started.
Creating a new table demonstrates DDL replication.
The replication flow is shown below.
This post provided a step-by-step guide for replicating autonomous databases with private endpoints using Oracle GoldenGate Marketplace Microservices Edition deployed in a private subnet.
For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley
Below is the client list of commands used in this post. It is available for download here or send them to your clipboard by clicking in the text area and then clicking the copy button.
Below is a list of the GG commands. It is available for download here or send them to your clipboard using the copy button.