• 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
Jinyu's Blog
Data Engineering and IT Management  

Capturing Data from Oracle Database on AWS RDS with Oracle GoldenGate

6/21/2016

0 Comments

 
Let's discuss how to set up a Oracle GoldenGate to capture data from Oracle Database on AWS RDS( BYOL license). We will explore the following topics:
  • Connecting to an Oracle Database on RDS
  • Configuring the Oracle RDS Instance for Oracle GoldenGate Replication
  • Setting up the Oracle GoldenGate Capture 
1. Connecting to an Oracle Database on RDS
After an Oracle Database 12c instance (with no multi-tenant support) [1] is created on RDS, you can connect to it using either SQL*Plus or Oracle SQL Developer. You first need to update the AWS inbound rule to enable the connection. This is configured in the security group assigned to the instance. An example setup is shown by the screenshot which allows requests from to any machine and connects to the database. The following is an example SQL*Plus connection (master user is awsuser) :
Picture
$ sqlplus awsuser/****@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host_name>)
(PORT=<port>))(CONNECT_DATA=(SID=<db_sid>)))"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 19 04:54:43 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Sun Jun 19 2016 04:25:12 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
Why do I get the "ORA-12170: TNS:Connect timeout occurred."error when connecting to Oracle Database on RDS?

Answer:The CIDR/IP added to the security group by Amazon AWS is not correct.Please use the CIDR/IP in the default security group assigned to the database instance. You can find it from the datail page of the RDS database instance.

2. Configuring the Oracle RDS Instance for Oracle GoldenGate Replication
Unlike on Oracle Cloud where the Oracle Database Cloud Service can pre-configured to enable GoldenGate replication. On AWS, you need to configure to enable GoldenGate replication in Oracle Database on RDS[2]. This includes updating the ENABLE_GOLDENGATE_REPLICATION system parameter to be TRUE, creating a GoldenGate administrator, and granting  the GoldenGate administrator privileges. 
To enable Oracle GoldenGate replication, you need to create a new parameter group and modify the instance to use the new parameter group. The following slideshows shows the steps: 
Why do I get the "ORA-01031: insufficient privileges" error?
Details: The error message is shown as follows:
SQL> alter system set enable_goldengate_replication=true scope=both;
alter system set enable_goldengate_replication=true scope=both
*
ERROR at line 1:
ORA-01031: insufficient privileges
Answer:This is because you can't change system parameter of Oracle Database outside of the parameter group.
In this example, we just grant the master user the Oracle GoldenGate administrator privilege:
SQL>execute dbms_goldengate_auth.grant_admin_privilege('awsuser',container=>'all');
Why do I get the "OGG-02060 The Oracle mining database is not configured properly to support integrated capture." error?

Answer:You need to reboot the instance to make the change available. Otherwise, you will get the following error:

GGSCI > register extract extdgrds database     
2016-06-20 20:31:27  ERROR   OGG-02060  The Oracle mining database is not configured properly to support integrated 
capture. The following configuration error must be fixed:  Operation not supported because enable_goldengate_replication
 is not set to true.
3. Setting Up the Oracle GoldenGate Capture 
The followings is the setup of Oracle GoldenGate extract parameter file: 
extract extgdrds
useridalias awsuser
EXTTRAIL ./dirdat/rd 
TABLE awsuser.*;
The other setup steps is similar to any integrated capture including the following steps:
Why do I get the "2016-06-20 22:01:56 ERROR OGG-02022 Logmining server does not exist on this Oracle database." error?

Answer: This normally is because of not correctly register the extract to the database.

To validate the result, you can insert a record in RDS and check the GoldenGate capture status as shown:
Insert new data in RDS Database
SQL> insert into world_region values(101,'Asia');
1 row created.
SQL> commit;

Check GGCS Capture
GGSCI> stats extgdrds
Sending STATS request to EXTRACT EXTGDRDS ...
Start of Statistics at 2016-06-20 22:39:19.
Output to ./dirdat/rd:
Extracting from AWSUSER.WORLD_REGION to AWSUSER.WORLD_REGION:

*** Total statistics since 2016-06-20 22:39:04 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-06-20 22:39:04 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-06-20 22:39:04 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-06-20 22:39:04 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.
4. Reference 
  1. ​Using Oracle GoldenGate with Amazon RDS - AWS Documenation
  2. Oracle on Amazon RDS - AWS Documentation
  3. Oracle GoldenGate Best Practices: Replication between Cloud and On-Premise Environments with Oracle GoldenGate (Doc ID 1996653.1)
5. Resources
  • ​add_extgdrds.oby
  • del_extgdrds.oby
  • extgdrds.prm
  • how_to_capture_from_oracle_db_on_rds.rtf
0 Comments



Leave a Reply.

    GoldenGate Cloud Service 

    Oracle GoldenGate Cloud Service (GGCS) is an extension of Oracle GoldenGate to provide rea-time replication in Cloud. 

    Jinyu is the product manager of Oracle GoldenGate Cloud Service. (Linkedin and Twitter)

    Links
    • GGCS Homepage
    • GGCS Documentation​ ​
    • Blog: Cloud Computing
    • Note: Cloud Services
    ​Buzzwords
    Disruption Dynamic Mobile Real-Time Security Data-Driven Globalization High Performance Digitization 
    ​
    Web Scale

    Last Updated

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

    Categories

    All
    Concept
    Feature
    GoldenGate Security
    How To
    Release
    Troubleshooting

    RSS Feed

Copyright © 2010-2027 Jinyu Wang.