AWS Database Blog
Implementing cross-region disaster recovery using Oracle GoldenGate for Amazon RDS for Oracle
Many AWS users take advantage of the managed service offerings available in the AWS portfolio to do the heavy lifting in their day-to-day activities. Amazon RDS is one of these services and is ideal for your relational database deployments. With RDS, you can significantly reduce the administrative overhead of managing and maintaining a relational database.
This post shows how to set up cross-region disaster recovery (DR) for Amazon RDS for Oracle for a database instance running from one Region to another. The solution uses Oracle GoldenGate installed on an Amazon EC2 instance hub that is configured with integrated capture mode to do DDL replication.
Overview
There are multiple ways to implement DR, depending on the following factors:
- Recovery time objective (RTO) and recovery point objective (RPO)
- Cost and the administrative tasks of setting up and maintaining the DR site
- Location of the DR site for geographic diversity
Amazon RDS for Oracle provides Multi-AZ deployment options to provide enhanced availability and durability for database (DB) instances and is often an effective DR solution for some customer use cases. If you need to spread the DR site across two different Regions, you can’t use Multi-AZ for DR. However, there are several ways to implement such a solution depending on the preceding factors.
Solution architecture
The solution consists of the following components:
- An EC2 instance (RHEL 7.4 AMI) configured as a GoldenGate hub instance.
- The hub instance has Oracle GoldenGate software version 12.3.0.1.4 configured with two different Oracle GoldenGate homes (OGGSRC and OGGTRG), one for the source DB and one for the target DB.
- The hub instance also has Oracle Database version 12.1.0.2.
- A source RDS for an Oracle DB instance running 12.1.0.2.v14 version in the us-east-1
- A Target RDS for an Oracle DB instance running 12.1.0.2.v14 version in the us-west-2
This post tested the solution on RDS Oracle DB version 12.1.0.2.v14 and Oracle GoldenGate version 12.3.0.1.4. However, you should use the latest supported DB engine version for RDS for Oracle
The following diagram illustrates the solution architecture.
Deploying the solution
The high-level deployment steps are as follows:
- Create a source RDS Oracle database.
- Create a target RDS Oracle database.
- Configure a GoldenGate hub on EC2 for the source database.
- Configure a GoldenGate hub on EC2 for the target database.
- Test the DDL and DML replication setup.
Creating a source RDS Oracle database
To set up a source RDS Oracle database, complete the following steps:
- Enable the GoldenGate replication parameter on the source RDS database.
Create a new database parameter group withenable_goldengate_replication
set totrue
in the source Regions. For details on how to create a parameter group, see Creating a DB Parameter Group. - Create the source RDS for your Oracle DB instance.
Using your new custom DB parameter group, create a source RDS for an Oracle DB instance (OGGSRC) with engine version 12.1.0.2v14 in theus-east-1
Region. Make sure to enable automatic backups on the source RDS Oracle DB instance. For this post, the databases are publicly accessible. For more information, see Creating an Oracle DB Instance and Connecting to a Database on an Oracle DB Instance.
The following screenshot shows the endpoint (DNS name) and port number of the source and target RDS DB instances:RDS console screenshot to show the connectivity information for the Database
- Set up the source database for replication with GoldenGate.
a.) Connect to the source database OGGSRC as RDS master user (for this post, the user is admin) and verify the GoldenGate replication parameter is set totrue
. See the following code:b.) Enable supplemental logging on the OGGSRC database with the following code:
c.) Check that supplemental logging is enabled on the OGGSRC database with the following code:
- Set up an Oracle GoldenGate replication user on the source database.
Connect to the source database OGGSRC as admin user to set up Oracle GoldenGate replication user (OGGUSER). See the following code:For the sake of simplicity, this post assigns DBA privilege to the GoldenGate user. The best practice is to not grant DBA privilege, but instead to grant the specific GoldenGate privileges required. For more information, see Granting the Appropriate User Privileges on the Oracle website.
Creating a target RDS Oracle database
To set up your target RDS Oracle database, complete the following steps:
- Enable the GoldenGate replication parameter on the target RDS databases.
Create a new database parameter group withenable_goldengate_replication
set totrue
in target Regions. For more information, see Creating a DB Parameter Group. - Create the target RDS for your Oracle DB instance in the target Region.
Using the new custom DB parameter group created in the previous step, create a target RDS for your Oracle DB instance (OGGTRG) with engine version 12.1.0.2v14 in theus-west-2
Region. Make sure to enable automatic backups on the target RDS for your Oracle DB instance. For this post, the databases are publicly accessible. For more information, see Creating an Oracle DB Instance and Connecting to a Database on an Oracle DB Instance.
The following screenshot shows the endpoint (DNS name) and port number of the target RDS DB instances:RDS console screenshot to show the connectivity information for the Database
- Set up an Oracle GoldenGate replication user on the target database.
Connect to the target database OGGTRG as admin user to set up Oracle GoldenGate replication user (OGGUSER). See the following code:
Configuring a GoldenGate hub on EC2 for the source database
The next step is to create a GoldenGate hub instance on EC2 with full installation of Oracle Database 12c 12.1.0.2.0 version and GoldenGate 12.3.0.1.4 software installed in the same Region as the source OGGSRC database. For more information on creating an EC2 Instance, see Create Your EC2 Resources and Launch Your EC2 Instance. For more information about installing GoldenGate, see the Oracle documentation library.
Oracle Database 12c software is installed on the EC2 hub instance following the Optimal Flexible Architecture (OFA) standard; ORACLE_HOME is located at “/u01/app/oracle/product/12c/db1
”.
You have two Oracle GoldenGate homes on the same EC2 host:
- OGG home for source “
/u01/app/oracle/product/ogg_src
” - OGG home for target “
/u01/app/oracle/product/ogg_trg
”
To configure your GoldenGate hub instance, complete the following steps:
- Establish connectivity from your EC2 hub instance to the source and target DB.
Configure an EC2 hub instance to connect to the source and target database using OGGUSER. Update thetnsnames.ora
file on the EC2 hub instance with DNS endpoint entry for OGGSRC and OGGTRG databases as shown previously. See the following code: - Configure the OGG(OGGSRC) for source RDS DB with the following code:
- Configure Oracle GoldenGate Wallet to connect to the source RDS database with the following code:
- Configure an Oracle GoldenGate
TRANDATA
option for the source database with the following code:a.) Configure the Source Data Capture using Integrated Extract for the source database with the following code:
b.) Configure an Optional Data Pump for the source database with the following code:
The Data Pump process is a secondary optional component of the GoldenGate replication software, and is recommended primarily to safeguard against network and target failures. For more information, see Overview of the Oracle GoldenGate Architecture on the Oracle website.
c.) Start the Primary Extract and the Data Pump processes for the source database with the following code:
Configuring a GoldenGate hub on EC2 for the target database
To set up a GoldenGate hub for your target database, complete the following steps:
- Configure the OGG(OGGTRG) for the target RDS DB.
Configure the manager with the following parameters for the target database: - Configure Oracle GoldenGate Wallet for a target RDS database with the following code:
- Configure the target data delivery using Integrated Replicat. See the following code:
- Obtain information about Replicat and other processes with the following code:
Testing the DDL and DML replication setup
To test your replication setup, complete the following steps:
- Create sample data to test replication on the source.
Connect to the source database OGGSRC as OGGSRC user and create test data with the following code: - Perform DML and DDL with the following code:
a.) Use the
stats
command to request the Extract processes EXTSRC. See the following code:b.) Use the
stats
command to request the Replicat process RXTTRG. Please note that the stats output is truncated to show the DDL replication stats only. See the following code:c.) Connect the target database OGGTRG as OGGTRG user and verify replication. See the following code:
Summary
This post covered how to implement cross-region disaster recovery for an RDS for Oracle database running in two different Regions, using GoldenGate integrated capture. Oracle GoldenGate version 12.1 and above with integrated capture is simple to configure, supports native DDL replication, and requires no additional database object creation on the source or target RDS Database. For more information about implementing low-cost, cross-region disaster recovery for RDS for Oracle, see Cross-Region Automatic Disaster Recovery on Amazon RDS for Oracle Database Using DB Snapshots and AWS Lambda.
About the Authors
Sameer Malik is a Principal database solutions architect with Amazon Web Services.
Sahil Thapar is an Enterprise solutions architect with Amazon Web Services.