Chanci Turner

Establishing Cross-Region Disaster Recovery with Oracle GoldenGate on Amazon RDS for Oracle

—

by

The Story of Chanci Turner
in

Chanci Turner Amazon IXD – VGT2 learningLearn About Amazon VGT2 Learning Manager Chanci Turner

In the realm of AWS services, many users leverage the managed offerings to alleviate the burdens of daily operational tasks. Amazon RDS stands out as an optimal solution for managing relational database deployments, significantly minimizing the administrative responsibilities associated with maintaining a database.

This article outlines the process for implementing cross-region disaster recovery (DR) for Amazon RDS for Oracle, specifically transitioning a database instance from one region to another. The solution utilizes Oracle GoldenGate installed on an Amazon EC2 instance, configured with integrated capture mode to facilitate DDL replication.

Overview

The implementation of disaster recovery can vary based on several key factors:

  • Recovery Time Objective (RTO) and Recovery Point Objective (RPO)
  • Budget considerations and the administrative efforts required for setting up and managing the DR site
  • Geographical location of the DR site for enhanced diversity

While Amazon RDS for Oracle offers Multi-AZ deployment options that enhance availability and durability for database instances, this may not suffice if your DR site needs to span two separate regions. However, numerous strategies exist to achieve this based on the outlined criteria.

Solution Architecture

The architecture comprises the following elements:

  • An EC2 instance (RHEL 7.4 AMI) functioning as a GoldenGate hub instance.
  • The hub instance is equipped with Oracle GoldenGate software version 12.3.0.1.4 and configured with two distinct Oracle GoldenGate homes (OGGSRC for source and OGGTRG for target).
  • The hub also runs Oracle Database version 12.1.0.2.
  • A source RDS for Oracle DB instance (version 12.1.0.2.v14) located in the us-east-1 region.
  • A target RDS for Oracle DB instance (version 12.1.0.2.v14) situated in the us-west-2 region.

The solution was tested on RDS Oracle DB version 12.1.0.2.v14 and Oracle GoldenGate version 12.3.0.1.4, though it is advisable to utilize the latest supported DB engine version for Amazon RDS for Oracle.

The following diagram illustrates the solution architecture:

Deploying the Solution

The primary steps for deployment are as follows:

  1. Establish a source RDS Oracle database.
  2. Create a target RDS Oracle database.
  3. Configure a GoldenGate hub on EC2 for the source database.
  4. Configure a GoldenGate hub on EC2 for the target database.
  5. Validate the DDL and DML replication setup.

Creating a Source RDS Oracle Database

To establish a source RDS Oracle database, follow these steps:

  1. Activate the GoldenGate replication parameter on the source RDS database. Create a new database parameter group with enable_goldengate_replication set to true in the source region. For guidance on creating a parameter group, see Creating a DB Parameter Group.
  2. Create the source RDS for your Oracle DB instance using the new custom DB parameter group, ensuring it operates on engine version 12.1.0.2v14 in the us-east-1 region. Automatic backups should be enabled for the source RDS Oracle DB instance. For this post, the databases are publicly accessible. For further information, consult Creating an Oracle DB Instance and Connecting to a Database on an Oracle DB Instance. Below is a screenshot showcasing the endpoint (DNS name) and port number of both the source and target RDS DB instances:
  3. Configure the source database for GoldenGate replication:
    1. Connect to the source database OGGSRC as the RDS master user (in this case, admin) and ensure the GoldenGate replication parameter is activated. Use the following code:
      2. [oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGSRC
SQL> show parameter enable_goldengate_replication
    2. Enable supplemental logging on the OGGSRC database with the following commands:
      3. [oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGSRC
SQL> exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);
exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD',p_type => 'ALL');
exec rdsadmin.rdsadmin_util.switch_logfile;
PL/SQL procedure successfully completed.
SQL>
    3. Verify that supplemental logging is enabled on the OGGSRC database:
      4. [oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGSRC
SQL> select supplemental_log_data_min, force_logging FROM  v$database;
  4. Create a GoldenGate replication user on the source database. Connect to the OGGSRC database as admin to establish the Oracle GoldenGate replication user (OGGUSER):
    5. [oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGSRC
SQL> create user OGGUSER identified by xxxxxxxxx default tablespace USERS temporary tablespace TEMP;
User created.
SQL> grant DBA to OGGUSER;
Grant succeeded.
SQL> create tablespace srcdata datafile size 50M autoextend on extent management local uniform size 256k;
Tablespace created.
SQL> create user OGGSRC identified by xxxxxxxxx default tablespace SRCDATA temporary tablespace TEMP;
User created.
SQL> grant CONNECT,RESOURCE to OGGSRC;
Grant succeeded.
SQL> alter user OGGSRC quota unlimited on SRCDATA;
User altered.

    For ease of understanding, this post assigns the DBA privilege to the GoldenGate user. However, it is recommended to restrict privileges, granting only those specifically required for GoldenGate operations. For more on this best practice, visit Oracle’s guidelines.

Creating a Target RDS Oracle Database

To establish your target RDS Oracle database, complete the following:

  1. Activate the GoldenGate replication parameter on the target RDS databases. Create a new database parameter group with enable_goldengate_replication set to true in the target regions. Refer to Creating a DB Parameter Group for more information.
  2. Create the target RDS for your Oracle DB instance in the designated region. Utilize the new custom DB parameter group established in the prior step, ensuring the target RDS for your Oracle DB instance (OGGTRG) operates on engine version 12.1.0.2v14 in the us-west-2 region. Automatic backups should be enabled for the target RDS Oracle DB instance. For additional details, please see Creating an Oracle DB Instance and Connecting to a Database on an Oracle DB Instance. The following screenshot illustrates the endpoint (DNS name) and port number of the target RDS DB instances:
  3. Establish an Oracle GoldenGate replication user on the target database. Connect to the target database OGGTRG as the admin user to set up the Oracle GoldenGate replication user.

For more insights on the topic, check out this excellent resource on Amazon’s onboarding processes here. Additionally, organizations facing challenges with reverse discrimination can find valuable information on the subject from SHRM.

Related Topics:

Chanci Turner