AWS Database Blog

Achieve minimal downtime for major and minor version upgrades of Amazon RDS for Oracle using Oracle GoldenGate

Amazon Relational Database Service (Amazon RDS) for Oracle provides new engine versions of databases and Oracle Critical Patch Updates for existing versions of databases, so you can keep your database instances up to date. These versions include bug fixes, security updates, and other enhancements.

In this post, we show you how to perform an upgrade of Amazon RDS for Oracle with minimal downtime using Oracle GoldenGate. You can use this solution for Amazon RDS for Oracle major version upgrades or minor version upgrades with Oracle Patch Set Update (PSU) or Release Update (RU) and major database reorganization on Amazon RDS for Oracle with minimal impact on the primary database.

Understanding Amazon RDS major and minor version upgrades

Amazon RDS for Oracle supports the following upgrades to an Oracle DB instance:

  • Major version upgrades – A major version upgrade for a database engine can introduce changes that aren’t compatible with existing applications. To upgrade your DB instance to a major version, you must perform the action manually. For this post, we demonstrate upgrading Oracle DB version 12.1.0.2 to 19.0.0.0. For more information, visit Oracle database engine release notes.
  • Minor version upgrades – A minor version upgrade includes only changes that are backward-compatible with existing applications. Upgrading the minor version of an RDS for Oracle database applies additional fixes and new features to an existing database. If you enable auto minor version upgrades on your DB instance, the minor version upgrade occurs automatically. In all other cases, you upgrade the DB instance manually. For this post, we demonstrate upgrading Oracle 12.1.0.2.v24 to 12.1.0.2.v25.

The duration of the outage from the upgrade depends on your engine version and instance size. Amazon RDS for Oracle provides an option to manually initiate a major version upgrade by modifying your DB instance. This is known as an in-place upgrade and requires downtime for your applications during the upgrade process. Additionally, you must restore the latest backup in the event of any issues with the upgrade.

Alternatively, you can use logical replication with either AWS Database Migration Service (AWS DMS) or Oracle GoldenGate. This method is known as an out-of-place upgrade and provides the least downtime for the DB engine version upgrade. For information about upgrading using AWS DMS, see Upgrading Amazon RDS for Oracle database engine with minimal downtime using AWS DMS.

Oracle GoldenGate

Oracle GoldenGate is a tool for real-time change data capture (CDC) and replication, and supports replicating data between Oracle database versions. For more information, visit Overview of the Oracle GoldenGate Architecture. Oracle GoldenGate creates trail files that contain the most recently changed data from the source database, and pushes these files to the destination database. You can use Oracle GoldenGate to perform minimal downtime data migration or nearly continuous data replication. Amazon RDS for Oracle is compatible with Oracle GoldenGate, and Oracle GoldenGate is a licensed software from Oracle.

Blue/green deployment

Blue/green deployment requires two identical environments. The blue environment represents the current database version serving production traffic, and the upgrade is performed on the green environment to the new database version without impacting the blue environment. The bi-directional replication between the blue and green environments allows you to perform database cutover or failback, if needed, with minimum downtime. When testing is complete on the green environment, live application traffic is directed to the green environment and the blue environment is deprecated. The desired change is applied on the green database environment only.

To avoid possible data inconsistency and a split-brain situation, never write to both blue and green environments at the same time. Replication doesn’t automatically recover the data changes, and it may be difficult to recover manually.

You can use blue/green deployment for database change management processes such as major and minor version upgrades, application changes, schema changes like creating new indexes or modifying indexes on a very large table, and data reorganization. The primary benefit of blue/green deployment is the ability to perform these maintenance activities with minimal downtime, while having the capability to fail back to the blue environment in case the upgrade doesn’t go well.

Solution overview

This post shows how you can perform a major and minor version upgrade of the RDS for Oracle database with minimal downtime using a blue/green deployment. The blue environment represents the current RDS for Oracle database version serving production traffic. We use an Amazon Elastic Compute Cloud (Amazon EC2) instance as the Oracle GoldenGate hub and start the Oracle GoldenGate Extract process for the blue environment.

The RDS for Oracle replica instance in the blue environment is promoted to a new standalone database when there is no replication lag. We perform an in-place major or minor version upgrade of the new standalone RDS for Oracle database in the green environment by modifying the database, and start the replication using Oracle GoldenGate from the blue to green environment to continuously replicate the data changes.

Make sure all the changes are replicated from the blue to the green environment. After the testing is complete on the green environment, application traffic is routed from the blue to the green environment.

The following diagram depicts the order of events, showing the steps to implement the solution.

  1. Promote the replica of the RDS for Oracle database in the blue environment to the new standalone database in the green environment.
  2. Perform an in-place upgrade of the new standalone RDS for Oracle database in the green environment from database version1 to version2.
  3. Set up replication using Oracle GoldenGate on the EC2 hub instance in the blue/green environment to replicate data changes.

To implement this solution, you complete the following high-level steps:

  1. Configure Oracle GoldenGate on an Amazon RDS for Oracle database in the blue environment.
  2. Configure Oracle GoldenGate on Amazon EC2 for the Amazon RDS for Oracle database in the blue environment.
  3. Start the Oracle GoldenGate Extract process in the blue environment.
  4. Promote the replica of the database in the blue environment.
  5. Upgrade the newly promoted database in the green environment.
  6. Configure Oracle GoldenGate on an Amazon RDS for Oracle database in the green environment.
  7. Configure Oracle GoldenGate on Amazon EC2 for the database in the green environment.
  8. Start the Oracle GoldenGate Replicat process for the green environment.
  9. Test the DDL and DML replication setup.
  10. Cut over from the blue to green environment.

Prerequisites

Before you get started, make sure you complete the following prerequisites:

  1. Create an RDS for Oracle database with a replica in the same AWS Region as the primary DB instance. For more information, check the following AWS CloudFormation sample template and Working with Oracle replicas for Amazon RDS. For this post, we create an RDS for Oracle database with a replica on version 12.1.0.2.
  2. Install and configure the Oracle GoldenGate hub on your EC2 instance (for this post, Oracle GoldenGate 19.1.0.0.0+ release version). For more information, see Oracle GoldenGate Certification Matrix 19c (19.1.*).

Configure Oracle GoldenGate on an Amazon RDS for Oracle database in the blue environment

Connect to the RDS for Oracle database in the blue environment as an Amazon RDS primary user (for this post, the user is admin).

We start by enabling the Oracle GoldenGate replication parameter on the source RDS for Oracle database.

  1. Create a new custom database parameter group or use an existing one with enable_goldengate_replication set to true in the source database:
SQL> show parameter enable_goldengate_replication

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication         boolean     TRUE
  1. Enable supplemental logging and increase the archive log retention on the source database with the following code:
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.
  1. Check that supplemental logging is enabled on the source database with the following code:
SQL> select supplemental_log_data_min, force_logging FROM v$database;

SUPPLEMENTAL_LOG_DATA_MIN FORCE_LOGGING
-------- ---------------------------------------
YES     YES
  1. Create the Oracle GoldenGate replication user (for more information, visit Using Oracle GoldenGate with Amazon RDS):
SQL> create user oggadmin identified by <PASSWORD> default tablespace <TABLESPACE NAME> quota unlimited on <TABLESPACE NAME>;
User created.

SQL> exec dbms_goldengate_auth.grant_admin_privilege (grantee=>'OGGADMIN', privilege_type=>'apply', grant_select_privileges=>true, do_grants=>TRUE);
PL/SQL procedure successfully completed.

SQL> GRANT CONNECT, RESOURCE TO OGGADMIN;
Grant succeeded.
  1. Grant the necessary account privileges to the GoldenGate user (for this post, the user is oggadmin) in the blue environment.

A best practice is to grant least privileges for the Oracle GoldenGate user for the RDS for Oracle database. For more information, visit Granting the Appropriate User Privileges.

Configure Oracle GoldenGate on Amazon EC2 for the database in the blue environment

The next step is to create an Oracle GoldenGate hub instance on Amazon EC2. Connect to Oracle GoldenGate on Amazon EC2 using the GGSCI utility and create subdirectories. For more information, visit Implement Oracle GoldenGate high availability in the AWS Cloud.

  1. On the EC2 instance running the Oracle GoldenGate setup, add TNS entries for the source in the blue environment and target in the green environment for the RDS for Oracle database. For example, see the following code: 
    RDSSOURCE=
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = <RDS for Oracle source db endpoint>)(PORT = <Oracle listener port>))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = <database name>))
)

RDSTARGET=
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = <RDS for Oracle target db endpoint>)(PORT = <Oracle listener port>))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = <database name>))
)
  2. Configure an Oracle GoldenGate credential store to maintain encrypted database passwords and user IDs: 
    GGSCI (ip-10-100-100-160.ec2.internal) 1> edit params mgr

GGSCI (ip-10-100-100-160.ec2.internal) 2> view params mgr

PORT 7809
dynamicportlist 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MINKEEPDAYS 3
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3

GGSCI (ip-10-100-100-160.ec2.internal) 2> start mgr
Manager started.


GGSCI (ip-10-100-100-160.ec2.internal) 3> status mgr

Manager is running (IP port TCP:ip-10-100-100-160.ec2.internal.7809, Process ID 1586).

GGSCI (ip-10-100-100-160.ec2.internal) 4> ADD CREDENTIALSTORE

Credential store created.

GGSCI (ip-10-100-100-160.ec2.internal) 11> ALTER CREDENTIALSTORE ADD USER oggadmin@rdssource password <PASSWORD> alias rdssource;

Credential store altered.

GGSCI (ip-10-100-100-160.ec2.internal) 7> dblogin USERIDALIAS rdssource
Successfully logged into database.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 8> INFO CREDENTIALSTORE

Reading from credential store:

Default domain: OracleGoldenGate

 Alias: rdssource
 Userid: oggadmin@rdssource
  3. Create an Oracle GoldenGate checkpoint table using the following code: 
    GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 26> ADD CHECKPOINTTABLE

No checkpoint table specified. Using GLOBALS specification oggadmin.ggchkpt...

Successfully created checkpoint table oggadmin.ggchkpt.
  4. In this post, we replicate the schema SOE from the source RDS for Oracle database in the blue environment to the green environment.

ADD SCHEMATRANDATA acts on all current and future tables in a given schema to automatically enable schema-level supplemental logging. ADD TRANDATA for Oracle enables the unconditional logging of the primary key and the conditional supplemental logging of all unique keys and foreign keys of the specified table.

To use the Oracle GoldenGate DDL replication feature, use the ADD SCHEMATRANDATA command to log the required supplemental data:

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 5> ADD SCHEMATRANDATA SOE

2021-08-11 17:04:21 INFO OGG-01788 SCHEMATRANDATA has been added on schema "SOE".

2021-08-11 17:04:21 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "SOE".

2021-08-11 17:04:21 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "SOE".

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 7> ADD TRANDATA SOE.LOGON ALLCOLS

2021-08-11 17:28:29 WARNING OGG-06439 No unique key is defined for table LOGON. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2021-08-11 17:28:29 INFO OGG-15130 No key found for table SOE.LOGON. All viable columns will be logged.

2021-08-11 17:28:29 INFO OGG-15132 Logging of supplemental redo data enabled for table SOE.LOGON.

2021-08-11 17:28:29 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SOE.LOGON.

2021-08-11 17:28:29 INFO OGG-15134 TRANDATA for all columns has been added on table SOE.LOGON.

2021-08-11 17:28:29 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SOE.LOGON.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 8> ADD TRANDATA SOE.ORDERENTRY_METADATA ALLCOLS

2021-08-11 17:29:41 WARNING OGG-06439 No unique key is defined for table ORDERENTRY_METADATA. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2021-08-11 17:29:41 INFO OGG-15130 No key found for table SOE.ORDERENTRY_METADATA. All viable columns will be logged.

2021-08-11 17:29:41 INFO OGG-15132 Logging of supplemental redo data enabled for table SOE.ORDERENTRY_METADATA.

2021-08-11 17:29:41 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SOE.ORDERENTRY_METADATA.

2021-08-11 17:29:41 INFO OGG-15134 TRANDATA for all columns has been added on table SOE.ORDERENTRY_METADATA.

2021-08-11 17:29:41 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SOE.ORDERENTRY_METADATA.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 6> info SCHEMATRANDATA SOE

2021-08-11 17:26:44 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SOE".

2021-08-11 17:26:44 INFO OGG-01980 Schema level supplemental logging is enabled on schema "SOE" for all scheduling columns.

2021-08-11 17:26:44 INFO OGG-10462 Schema "SOE" have 11 prepared tables for instantiation.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 10> info trandata soe.logon

2021-08-11 17:30:38 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SOE".

2021-08-11 17:30:38 INFO OGG-01980 Schema level supplemental logging is enabled on schema "SOE" for all scheduling columns.

Logging of supplemental redo log data is enabled for table SOE.LOGON.

All columns supplementally logged for table SOE.LOGON.

Prepared CSN for table SOE.LOGON: 811302
GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 11> info trandata soe.ORDERENTRY_METADATA

2021-08-11 17:30:58 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SOE".

2021-08-11 17:30:58 INFO OGG-01980 Schema level supplemental logging is enabled on schema "SOE" for all scheduling columns.

Logging of supplemental redo log data is enabled for table SOE.ORDERENTRY_METADATA.

All columns supplementally logged for table SOE.ORDERENTRY_METADATA.

Prepared CSN for table SOE.ORDERENTRY_METADATA: 811507

Start the Oracle GoldenGate Extract process in the blue environment

We configure the source data capture using Integrated Extract for the RDS for Oracle database in the blue environment to extract the changes on the RDS for Oracle DB instance in the blue environment. Start the Extract process using the following code:

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 31> edit params extsrc

GGSCI (ip-10-100-100-160.ec2.internal) 3> view param extsrc

Extract EXTSRC
UserIdAlias rdssource
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail ./dirdat/ex
ddl include all
ddloptions addtrandata, report
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
Table SOE.*;

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 32> register extract EXTSRC database;

2021-08-11 17:49:48 INFO OGG-02003 Extract EXTSRC successfully registered with database at SCN 814414.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 33> add extract EXTSRC , integrated tranlog, begin now
EXTRACT (Integrated) added.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 36> add exttrail ./dirdat/ex, extract EXTSRC , megabytes 10
EXTTRAIL added.EXTTRAIL added.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 39> edit param DPSRC

Extract DPSRC
UserIdAlias rdssource
rmthost localhost, mgrport 7809
rmttrail ./dirdat/dp
Table SOE.*;

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 52> add extract DPSRC, EXTTRAILSOURCE ./dirdat/ex
EXTRACT added.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 53> add rmttrail ./dirdat/dp, extract DPSRC, megabytes 10
RMTTRAIL added.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 55> start extract EXTSRC

Sending START request to MANAGER ...
EXTRACT EXTSRC starting

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 56> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED DPSRC 00:00:00 00:03:47
EXTRACT RUNNING EXTSRC 00:00:00 00:21:35

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 58> start extract DPSRC

Sending START request to MANAGER ...
EXTRACT DPSRC starting

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 60> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPSRC 00:00:00 00:00:01
EXTRACT RUNNING EXTSRC 00:00:06 00:00:06

Promote the replica of the database in the blue environment

Wait until the replication lag between your primary RDS for Oracle DB instance and its replica in the blue environment is zero, then create a green environment by promoting the replica of the RDS for Oracle database to a new standalone database. You can also restore a standalone RDS for Oracle DB instance in the green environment from a snapshot of the database in the blue environment. This post demonstrates the former method (promoting a replica).

Alternatively, you can promote a mounted replica, which doesn’t use Active Data Guard. For more information about read-only and mounted replicas, see Working with Oracle replicas for Amazon RDS.

To promote the replica to the new RDS for Oracle database, use the following AWS Command Line Interface (AWS CLI) command:

aws rds promote-read-replica --db-instance-identifier $TARGET_RDS_ID --region $AWS_REGION

The DB instance promotion process takes a few minutes. You can verify the status of the newly promoted database in the green environment changes to Available. For more information, see Viewing DB instance status. The RDS for Oracle database in the blue and green environments are on same DB version in this step (for this post, 12.1.0.2).

Note down the Oracle Sequence Change Number (SCN) when the standby becomes the primary from the alert log of the newly promoted DB instance. We use this SCN to start the replication from the blue to green environment.

For example, the following is the snippet from the alert log of when the newly promoted RDS for Oracle database and standby became primary (SCN number is 537939). The Oracle GoldenGate replication from the blue to green environment should be started from SCN number 537939 (your SCN number will be different!).

alert_<database instance name>.log

Tue Oct 19 19:36:57 2021
Backup controlfile written to trace file /rdsdbdata/log/diag/rdbms/orclnet_b/ORCLNET/trace/ORCLNET_ora_14179.trc
Standby terminal recovery start SCN: 537940
RESETLOGS after incomplete recovery UNTIL CHANGE 537941 time 10/19/2021 19:36:46
Online logfile pre-clearing operation disabled by switchover
Online log /rdsdbdata/db/ORCLNET_B/onlinelog/o1_mf_1_jpy540lj_.log: Thread 1 Group 1 was previously cleared
Online log /rdsdbdata/db/ORCLNET_B/onlinelog/o1_mf_2_jpy5423n_.log: Thread 1 Group 2 was previously cleared
Online log /rdsdbdata/db/ORCLNET_B/onlinelog/o1_mf_3_jpy542tm_.log: Thread 1 Group 3 was previously cleared
Online log /rdsdbdata/db/ORCLNET_B/onlinelog/o1_mf_4_jpy543yw_.log: Thread 1 Group 4 was previously cleared
Standby became primary SCN: 537939

Upgrade the newly promoted database in the green environment

Before you upgrade the newly promoted RDS for Oracle DB instance, we recommend reviewing the Oracle documentation for information about behavior changes and deprecated and desupported features, and also referring to Upgrading the Oracle DB engine for other considerations.

In this step, you can choose to perform either a major version upgrade or minor version upgrade of the RDS for Oracle database in the green environment. To upgrade the instance in the green environment, you modify the instance via one of the following methods:

The following code is an example of an Amazon RDS for Oracle in-place major version upgrade in the green environment from Oracle database version 12.1.0.2 to 19.0.0.0 using the AWS CLI modify-db-instance command:

aws rds modify-db-instance \
    --db-instance-identifier $TARGET_RDS_ID \
    --engine-version 19.0.0.0.ru-2021-07.rur-2021-07.r1 \
    --allow-major-version-upgrade --apply-immediately --region $AWS_REGION

The following code is an example of an Amazon RDS for Oracle in-place minor version upgrade in the green environment from Oracle database version 12.1.0.2.v24 to 12.1.0.2.v25 using the AWS CLI modify-db-instance command:

aws rds modify-db-instance \
    --db-instance-identifier $TARGET_RDS_ID \
    --engine-version 12.1.0.2.v25 \
    --apply-immediately --region $AWS_REGION

When the upgrade process is complete, verify that the status of the standalone RDS for Oracle database in the green environment is in an Available state.

Configure Oracle GoldenGate on an RDS for Oracle database in the green environment

Connect to the target RDS for Oracle database as an Amazon RDS primary user (for this post, the user is admin).

  1. Verify the Oracle GoldenGate replication parameter is set to true on the RDS for Oracle database in the green environment using the following code: 
    SQL> show parameter enable_goldengate_replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
  2. Verify the Oracle GoldenGate replication user created on the RDS for Oracle database and grant the necessary privileges to the GoldenGate user (for this post, the user is oggadmin) in the green environment.

Configure Oracle GoldenGate on Amazon EC2 for the database in the green environment

Configure Oracle GoldenGate on Amazon EC2 for the RDS for Oracle database in the green environment using the following code:

GGSCI (ip-10-100-100-160.ec2.internal) 3> ALTER CREDENTIALSTORE ADD USER oggadmin@rdstarget password <PASSWORD> alias rdstarget;

Credential store altered.

GGSCI (ip-10-100-100-160.ec2.internal) 5> dblogin USERIDALIAS rdstarget
Successfully logged into database.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 6> ADD CHECKPOINTTABLE

No checkpoint table specified. Using GLOBALS specification oggadmin.ggchkpt...

Successfully created checkpoint table oggadmin.ggchkpt.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 7> edit param REPTRG

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 9> view param REPTRG

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 23> view params REPTRG

Replicat REPTRG
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias rdstarget
Map soe.*, target soe.*;

Start the replication process

Start the replication using Oracle GoldenGate from the SCN number you noted from the alert log (537939 in our case):

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 8> Add Replicat REPTRG Integrated exttrail ./dirdat/dp
REPLICAT (Integrated) added.

GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 22> start replicat REPTRG ATCSN 537939

Sending START request to MANAGER ...
REPLICAT REPTRG starting

Connect to the RDS for Oracle database in the green environment to check the status of the Oracle GoldenGate replication:

SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
STATUS
--------
OGG$REPTRG
ENABLED

Test the DDL and DML replication setup

To test the replication setup, connect to the RDS for Oracle database in the blue environment and create sample data in the SOE schema using the following code:

SQL> create table dept (deptno number(4), dname varchar2(20));

Table created.

SQL> insert into dept values (10, 'Marketing');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into dept values (20, 'Finance');

1 row created.

SQL> insert into dept values (30,'Accounting');

1 row created.

SQL> commit;

Connect to the RDS for Oracle database in the green environment to verify the replication using the following code:

SQL> select * from dept;

 DEPTNO DNAME
---------- --------------------
 10 Marketing
 20 Finance
 30 Accounting

Cut over from the blue environment to the green environment

After the testing is complete from both a functional and nonfunctional standpoint, you’re ready to point your applications to the new major application version in the green environment. This post provides the following steps as a reference; you should add additional steps as required to tailor the solution to your environment.

Complete the steps in the following order:

  1. Make sure that all users and roles are set up with appropriate permissions in the green environment.
  2. Verify the green environment is configured with the correct instance type, custom DB parameters groups, and security groups.

This is the point at which your downtime starts.

  1. Check for any long-running transactions in the blue environment to stop or delete.
  2. Stop all applications that write to the blue environment. To stop the application writes on the RDS for Oracle database, complete the following steps:
    1. Stop all the applications and make sure all the write activities are stopped on the database and there is no application database connection.
    2. If you can’t easily stop application writes, you can work around it by blocking application access to the database. For example, modify security groups to block access to the database, and revoke write access to users on the database.
  3. Verify the data integrity, database object counts match (such as indexes and functions), and row counts match in the blue and green environment.
  4. When the data is in sync with zero replication lag between the blue and green environment, stop the Oracle GoldenGate Replicat process using the following code: 
    GGSCI (ip-10-100-100-160.ec2.internal as oggadmin@ORCL) 35> stop replicat REPTRG
Sending STOP request to REPLICAT REPTRG ...
Request processed.
  5. Manually sync the sequences on the RDS for Oracle database in the blue and green environments (you can also automate this process using scripts).
  6. Modify your application to point to the green environment and start the application to route production traffic to the green environment.

This is the point at which your downtime stops.

  1. Conduct necessary tests to verify that the application is working correctly.

After the switchover to the green environment, you can use the blue environment to perform post-cutover tasks like roll back to a previous database version if needed, or delete it to minimize costs.

Rollback plan

When migrating a database to a new version, it’s prudent to have a fallback strategy if the new system doesn’t work as expected. Make sure you have defined a success criteria for cutover along with a rollback plan before enabling application writes on the green environment. The rollback plan should include setting up Oracle GoldenGate replication from the old green (new blue) and old blue (new green) environment. Note down the Oracle SCN of the old green environment to start replication of data changes in the blue/green environment. For more information on setting up Oracle GoldenGate replication with an RDS for Oracle database, see Implement Oracle GoldenGate high availability in the AWS Cloud.

To avoid data inconsistency issues, never perform writes to both the blue and green environment at the same time. During the cutover, we start replication from the old green to blue environment for failback, before routing application write traffic to the green environment.

Clean up

To clean up the Oracle GoldenGate installation, see Uninstalling Oracle GoldenGate with Oracle Database. To delete the RDS for Oracle database, see Deleting a DB instance. Finally, terminate your Amazon EC2 instance.

Conclusion

In this post, I showed you how to upgrade your RDS for Oracle database version using Oracle GoldenGate with a blue/green deployment strategy with replicas to achieve minimal downtime. You can use this method for major version upgrades, minor version upgrades, major database reorganization, and schema changes on Amazon RDS for Oracle with minimal impact on the primary database.

We welcome your feedback. Share your experience and any questions in the comments.

About the Author

Nethravathi Muddarajaiah is a Database Specialist Solutions Architect. She works with AWS customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.