Capturing Data from Oracle Database on AWS RDS with Oracle GoldenGate

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) :

$ 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