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 with enable_goldengate_replication set to true 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 the us-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 to true. See the following code:
[oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGSRC
SQL> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
SQL>
b.) Enable supplemental logging on the OGGSRC database with the following code:
[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>
c.) Check that supplemental logging is enabled on the OGGSRC database with the following code:
[oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGSRC
SQL> select supplemental_log_data_min, force_logging FROM v$database;
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES YES
SQL>
- 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:
[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.
SQL>
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 with enable_goldengate_replication set to true 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 the us-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:
[oracle@ip-172-31-30-239 admin]$ sqlplus admin/xxxxxxxxx@OGGTRG
SQL> create tablespace trgdata datafile size 50M autoextend on extent management local uniform size 256k;
Tablespace created.
SQL> create user OGGUSER identified by xxxxxxxxx default tablespace USERS temporary tablespace TEMP;
User created.
SQL> grant DBA to OGGUSER;
Grant succeeded.
SQL> create user OGGTRG identified by xxxxxxxxx default tablespace TRGDATA temporary tablespace TEMP;
User created.
SQL> alter user OGGTRG quota unlimited on TRGDATA;
User altered.
SQL> grant CONNECT,RESOURCE to OGGTRG;
Grant succeeded.
SQL>
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 the tnsnames.ora file on the EC2 hub instance with DNS endpoint entry for OGGSRC and OGGTRG databases as shown previously. See the following code:
[oracle@ip-172-31-19-223 ~]$ echo $TNS_ADMIN
/u01/app/oracle/product/12c/db_1/network/admin
[oracle@ip-172-31-19-223 ~]$ vi $TNS_ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
OGGTRG.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oggtrg1.xxxxxxxxxx.us-west-2.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = OGGTRG)
)
)
OGGSRC.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oggsrg1. xxxxxxxxxx.us-east-1.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = OGGSRC)
)
)
- Configure the OGG(OGGSRC) for source RDS DB with the following code:
GGSCI (ip-172-31-19-223.ec2.internal) 2>edit param mgr
PORT 7809
DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3
GGSCI (ip-172-31-19-223.ec2.internal) 3> start mgr
Manager started.
GGSCI (ip-172-31-19-223.ec2.internal) 4> info mgr detail
Manager is running (IP port ip-172-31-19-223.ec2.internal.7809, Process ID 4611).
GGSCI (ip-172-31-30-239.ec2.internal) 5>
- Configure Oracle GoldenGate Wallet to connect to the source RDS database with the following code:
GGSCI (ip-172-31-19-223.ec2.internal) 4> Create Wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
GGSCI (ip-172-31-19-223.ec2.internal) 6> Add CredentialStore
Credential store created in ./dircrd/.
GGSCI (ip-172-31-19-223.ec2.internal) 6> Alter CredentialStore Add User ogguser@oggsrc
Password xxxxxxx Alias ogg_user_src
Credential store in ./dircrd/ altered.
GGSCI (ip-172-31-19-223.ec2.internal) 7> Info CredentialStore
Reading from ./dircrd/:
Domain: OracleGoldenGate
Alias: ogg_user_src
Userid: ogguser@oggsrc
GGSCI (ip-172-31-19-223.ec2.internal) 8>
GGSCI (ip-172-31-19-223.ec2.internal) 8> DBLogin UserIDAlias ogg_user_src
Successfully logged into database.
- Configure an Oracle GoldenGate
TRANDATA option for the source database with the following code:
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 13> Add TranData OGGSRC.* ALLCOLS
2019-06-19 02:33:35 INFO OGG-15132 Logging of supplemental redo data enabled for table OGGSRC.ECONOMIC_DATA.
2019-06-19 02:33:35 INFO OGG-15133 TRANDATA for scheduling columns has been added on table OGGSRC.ECONOMIC_DATA.
2019-06-19 02:33:35 INFO OGG-15134 TRANDATA for all columns has been added on table OGGSRC.ECONOMIC_DATA.
2019-06-19 02:33:35 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table OGGSRC.ECONOMIC_DATA.
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 14> Info TranData OGGSRC.*
Logging of supplemental redo log data is enabled for table OGGSRC.ECONOMIC_DATA.
All columns supplementally logged for table OGGSRC.ECONOMIC_DATA.
Prepared CSN for table OGGSRC.ECONOMIC_DATA: 524105
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 15>
a.) Configure the Source Data Capture using Integrated Extract for the source database with the following code:
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 15> edit param EXTSRC
Extract EXTSRC
SETENV (ORACLE_SID='OGGSRC')
UserIdAlias ogg_user_src
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail ./dirdat/in
ddl include all
ddloptions addtrandata, report
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
Table OGGSRC.*;
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 16> dblogin useridalias ogg_user_src
Successfully logged into database.
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 17> register extract EXTSRC database
2019-06-19 02:38:34 INFO OGG-02003 Extract EXTSRC successfully registered with database at SCN 524573.
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 18> add extract EXTSRC , integrated tranlog, begin now
EXTRACT (Integrated) added.
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 19> add exttrail ./dirdat/in, extract EXTSRC , megabytes 10
EXTTRAIL added.
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 20>
b.) Configure an Optional Data Pump for the source database with the following code:
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 20> edit param PXTSRC
Extract PXTSRC
SETENV (ORACLE_SID=’OGGSRC’)
UserIdAlias ogg_user_src
rmthost localhost, mgrport 7909
rmttrail ./dirdat/pn
table OGGSRC.*;
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 21> add extract PXTSRC, exttrailsource ./dirdat/in
EXTRACT added.
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 22> add rmttrail ./dirdat/pn, extract PXTSRC, megabytes 10
RMTTRAIL added.
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:
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 23>) > start Extract EXTSRC
Sending START request to MANAGER ...
EXTRACT EINTA starting
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGSRC) 25> start extract PXTSRC
Sending START request to MANAGER ...
EXTRACT PINTA starting
GGSCI (host01.example.com) >
GGSCI (host01.example.com) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EINTA 20:44:16 00:00:01
EXTRACT RUNNING PINTA 00:00:00 00:00:03
GGSCI (host01.example.com) >
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:
GGSCI (ip-172-31-19-223.ec2.internal) 2> edit param mgr
Port 7909
DynamicPortList 20100-20199
PurgeOldExtracts ./dirdat/pe*, UseCheckPoints, MinKeepHours 2
Autostart Replicat R*
AUTORESTART Replicat *, WaitMinutes 1, Retries 3
GGSCI (ip-172-31-30-239.ec2.internal) 1> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (ip-172-31-19-223.ec2.internal) 2> start mgr
Manager started.
GGSCI (ip-172-31-19-223.ec2.internal) 3> info mgr detail
Manager is running (IP port ip-172-31-30-239.ec2.internal.7909, Process ID 5048).
GGSCI (ip-172-31-19-223.ec2.internal) 4>\
- Configure Oracle GoldenGate Wallet for a target RDS database with the following code:
GGSCI (ip-172-31-19-223.ec2.internal) 4> Create Wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
GGSCI (ip-172-31-19-223.ec2.internal) 6> Add CredentialStore
Credential store created in ./dircrd/.
GGSCI (ip-172-31-19-223.ec2.internal) 6> Alter CredentialStore Add User ogguser@oggtrg
Password oracle Alias ogg_user_trg
Credential store in ./dircrd/ altered.
GGSCI (ip-172-31-19-223.ec2.internal) 7> Info CredentialStore
Reading from ./dircrd/:
Domain: OracleGoldenGate
Alias: ogg_user_trg
Userid: ogguser@oggtrg
GGSCI (ip-172-31-19-223.ec2.internal) 8> DBLogin UserIDAlias ogg_user_trg
Successfully logged into database.
- Configure the target data delivery using Integrated Replicat. See the following code:
GGSCI (ip-172-31-19-223.ec2.internal) >Edit Param RXTTRG
Replicat RXTTRG
SETENV(ORACLE_SID=’OGGTRG’)
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias ogg_user_trg
Map oggsrc.*, target oggtrg.*;
GGSCI (ip-172-31-19-223.ec2.internal) > DBlogin UserIdAlias ogg_user_trg
Successfully logged into database.
GGSCI (ip-172-31-19-223.ec2.internal) > Add Replicat RXTTRG Integrated exttrail ./dirdat/pn
REPLICAT (Integrated) added.
GGSCI (ip-172-31-19-223.ec2.internal) > Start Replicat RXTTRG
Sending START request to MANAGER ...
REPLICAT RXTTRG starting
GGSCI (ip-172-31-19-223.ec2.internal) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RXTTRG 00:00:00 00:00:39
GGSCI (ip-172-31-19-223.ec2.internal) >
- Obtain information about Replicat and other processes with the following code:
GGSCI (ip-172-31-19-223.ec2.internal) > Info RXTTRG, Detail
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGTRG) 14> info RXTTRG, detail
REPLICAT RXTTRG Last Started 2019-06-19 03:07 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 14328
Log Read Checkpoint File /u01/app/oracle/product/ogg_trg/dirdat/pn000000000
First Record RBA 0
INTEGRATED Replicat
DBLOGIN Provided, inbound server name is OGG$RXTTRG in ATTACHED state
Current Log BSN value: (no data)
Low Watermark CSN value: (no data)
High Watermark CSN value: (no data)
Extract Source Begin End
/u01/app/oracle/product/ogg_trg/dirdat/pn000000000 * Initialized * First Record
/u01/app/oracle/product/ogg_trg/dirdat/pn000000000 * Initialized * First Record
./dirdat/pn000000000 * Initialized * First Record
Current directory /u01/app/oracle/product/ogg_trg
Report file /u01/app/oracle/product/ogg_trg/dirrpt/RXTTRG.rpt
Parameter file /u01/app/oracle/product/ogg_trg/dirprm/rxttrg.prm
Checkpoint file /u01/app/oracle/product/ogg_trg/dirchk/RXTTRG.cpr
Process file /u01/app/oracle/product/ogg_trg/dirpcs/RXTTRG.pcr
Error log /u01/app/oracle/product/ogg_trg/ggserr.log
GGSCI (ip-172-31-19-223.ec2.internal as ogguser@OGGTRG) 15>
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:
[oracle@ip-172-31-30-239 admin]$ sqlplus OGGSRC/xxxxxxxxx@OGGSRC
SQL> create table ECONOMIC_DATA
(
entity_id integer not null,
economic_entity varchar2(128) not null,
continent varchar2(20),
PRIMARY KEY(entity_id)
);
Table created.
SQL> Insert into ECONOMIC_DATA (ENTITY_ID,ECONOMIC_ENTITY,CONTINENT) values (29,'St Vincent and the Grenadines','AMERICAS');
1 row created.
SQL> commit;
- Perform DML and DDL with the following code:
[oracle@ip-172-31-30-239 admin]$ sqlplus OGGSRC/oracle@OGGSRC
SQL> select count(*) from ECONOMIC_DATA ;
COUNT(*)
----------
1
SQL> delete from ECONOMIC_DATA;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
a.) Use the stats command to request the Extract processes EXTSRC. See the following code:
GGSCI (ip-172-31-19-223.ec2.internal) 1> stats EXTSRC
Sending STATS request to EXTRACT EXTSRC ...
Start of Statistics at 2019-06-19 03:28:00.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to ./dirdat/in:
Extracting from OGGSRC.ECONOMIC_DATA to OGGSRC.ECONOMIC_DATA:
*** Total statistics since 2019-06-19 03:16:57 ***
Total inserts 0.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (ip-172-31-19-223.ec2.internal) 3>
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:
GGSCI (ip-172-31-19-223.ec2.internal) 1> stats RXTTRG
Sending STATS request to REPLICAT RXTTRG ...
Start of Statistics at 2019-06-19 03:32:52.
Integrated Replicat Statistics:
Total transactions 2.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 1.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 50.00%
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
GGSCI (ip-172-31-19-223.ec2.internal) 2>
c.) Connect the target database OGGTRG as OGGTRG user and verify replication. See the following code:
[oracle@ip-172-31-30-239 admin]$ sqlplus OGGTRG/oracle@OGGTRG
SQL> select count(*) from ECONOMIC_DATA ;
COUNT(*)
----------
0
SQL> desc ECONOMIC_DATA
Name Null? Type
----------------------------------------- -------- ----------------------------
entity_id NOT NULL NUMBER
economic_entity NOT NULL varchar2(128)
continent varchar2(20)
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.