Enable efficient load balancing and connection routing for Oracle database workloads in AWS using Global Data Services

export ORACLE_BASE=/u01/apps/oracle 

export ORACLE_HOME=/u01/apps/oracle/product/gsm 

export PATH=$ORACLE_HOME/bin:$PATH:$HOME/local/bin:$HOME/bin

SQL> connect <user>/<passwd>@gdscat 
SQL> show parameter db_unique 
SQL> alter user gsmcatuser account unlock; 
SQL> alter user gsmcatuser identified by <passwd>; 
SQL> create user gsmadmin identified by <passwd>; 
SQL> grant gsmadmin_role to gsmadmin; 
SQL> exit

In this example, we connect to the catalog database as a user that has administrator privileges to create and alter users and grant gsmadmin_role. You may also use SYS or SYSTEM for this purpose. You should use complex passwords for these users for your environment.

2. Set a remote listener in the GDS catalog to point to the GSM listener:

SQL> connect <user>/<passwd>@gdscat
SQL> alter system set remote_listener = ‘<your EC2 instance DNS name>:<your database port>′ SCOPE=BOTH;

In this example, the GSM listener will be running on the EC2 host ip-172-xx-xx-xxx.ec2.internal on port 1521. We discuss this configuration in more detail later in this post.

Prepare pool databases

1.    Make sure that the database listeners, pool databases, and GDS catalog database (gdscat) are set up. Then complete the following steps to prepare your pool databases:

SQL> conn <user>/<passwd>@<pooldb1>
SQL> show parameter db_unique
SQL> alter user gsmuser account unlock;
SQL> alter user gsmuser identified by <passwd>;
SQL> exit;

2.    Set a remote listener on all the pool databases to point to the GSM listener. Refer to Working with parameter groups for more details on how to set parameters in Amazon RDS for Oracle. Modify the parameter group to set the following parameter for the instance:

remote_listener:<your EC2 instance DNS name>:<your database port>

For self-managed databases on Amazon EC2 and Amazon RDS Custom for Oracle, run the following statement:

SQL> alter system set remote_listener = ‘<your EC2 instance DNS name>:<your db port>’ SCOPE=BOTH SID=’*’

3.    Repeat the preceding steps for all the primary databases in the pool.

Configure a GDS server

A GDS configuration consists of multiple GSMs within each region. These GSMs act as global listeners orchestrating inter-database service failovers and providing load balancing within the GDS environment.

It’s considered a best practice to have GSM listeners on a separate server, distinct from the one hosting the Oracle databases in the GDS configuration. This separation enhances the stability and reliability of the Oracle GDS infrastructure.

Complete the following steps to configure a GDS server:

1.    Add TNS entries of the GDS catalog and pool databases to tnsnames.ora on the GSM Server node. Sample TNS entries are shown in the following code. Make sure to add entries for all primary and standby databases (read replicas) involved in the use case.

GDSCAT =
(DESCRIPTION =
(address = (protocol = tcp)(host = <GDS catalog db DNS endpoint>)(port = <DB port>))
(CONNECT_DATA =
(SERVICE_NAME = CSTMTRG3)
)
)
ORADB2, ORADB_A =
(DESCRIPTION =
(address = (protocol = tcp)(host = <DNS endpoint for pool db 1>)(port = <DB port>))
(CONNECT_DATA =
(SERVICE_NAME = ORADB)
)
)
ORADB_R1, ORADB_B =
(DESCRIPTION =
(address = (protocol = tcp)(host = <DNS endpoint for pool db 2>)(port = <DB port>))
(CONNECT_DATA =
(SERVICE_NAME = ORADB)
)
)

2.    To create the GDS catalog using GDSCTL, from the terminal window of the GSM server node, run the following code:

$ gdsctl

GDSCTL> create catalog -database gdscat -user gsmadmin -configname thinkwithwp.com

3.    Add and start a GSM server. In the following code, we’re adding GSM server gsm1east1 and it will be listening on port 1521. You can use similar code to add a GSM server for other regions.

GDSCTL> add gsm -gsm <gsm server name> -listener <gsm listener port> -catalog gdscat
GDSCTL> start gsm -gsm <gsm server name>
For example:
GDSCTL> add gsm -gsm gsm1east1 -listener 1521 -catalog gdscat
GDSCTL> start gsm -gsm gsm1east1
GDSCTL> status gsm -gsm gsm1east1
Alias GSM1EAST1
Version 19.0.0.0.0
Start Date 01-SEP-2023 23:34:15
Trace Level off
Listener Log File /u01/apps/oracle/diag/gsm/ip-172-xx-xx-xxx/gsm1east1/alert/log.xml
Listener Trace File /u01/apps/oracle/diag/gsm/ip-172-xx-xx-xxx/gsm1east1/trace/ora_29110_139637412736064.trc
Endpoint summary (ADDRESS=(HOST=ip-172-xx-xx-xxx.ec2.internal)(PORT=1521)(PROTOCOL=tcp))
GSMOCI Version 3.0.180702
Mastership Y
Connected to GDS catalog Y
Process Id 29117
Number of reconnections 0
Pending tasks. Total 0
Tasks in process. Total 1
Alert: GSM listener rejected database registration.
Regional Mastership TRUE
Total messages published 0
Time Zone +00:00
Orphaned Buddy Regions:
None
GDS region regionora

4.    To check the configuration of the GSM server, use the following code:

GDSCTL> config gsm -gsm <gsm server name>
For example:
GDSCTL> config gsm -gsm gsm1east1
Catalog connection is established
Name: gsm1east1
Endpoint 1: (ADDRESS=(HOST=ip-172-xx-xx-xxx.ec2.internal)(PORT=1521)(PROTOCOL=tcp))
Local ONS port: 6123
Remote ONS port: 6234
ORACLE_HOME path: /u01/apps/oracle/product/gsm
GSM Host name: ip-172-xx-xx-xxx.ec2.internal
Region: regionora

The entries for your GSM server should be automatically added to gsm.ora and tnsnames.ora after you run the preceding step (under $ORACLE_HOME/network/admin).

For example, for GSM.ORA, the following is added:

GSM1EAST1 =
(configuration =
(listener =
(ADDRESS = (HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521)(PROTOCOL = tcp))
)
(cloud = thinkwithwp.com)
)

For TNSNAMES.ORA, the following is added:

GSM1EAST1 =
(DESCRIPTION =
(ADDRESS = (HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521)(PROTOCOL = tcp))
(CONNECT_DATA =
(SERVICE_NAME = GDS$CATALOG.thinkwithwp.com)
)
)
GSM1EAST1_CATALOG =
(DESCRIPTION =
(address = (protocol = tcp)(host = tsw.xxx.rds.amazonaws.com)(port = 1521))
(CONNECT_DATA =
(SERVICE_NAME = GDS$CATALOG.thinkwithwp.com)
)
)

5.    Allow the remote listener configured on the gdscat database as well as the pool databases to register with the GSM listener by adding host DNS endpoints of the respective servers to the allow list:

GDSCTL> config vncr

Name Group ID
---- --------
172.xx.xx.x

GDSCTL> add invitednode <DNS endpoint of catalog DB host>
GDSCTL> add invitednode <DNS endpoint of pool db1>
GDSCTL> add invitednode <DNS endpoint of pool db2>

Verify using config vncr. All added servers should show in the output.

GDSCTL> config vncr

Name Group ID
---- --------
172.xx.xx.x
tsw.xxx.rds.amazonaws.com
arch-r1.xxx.rds.amazonaws.com
arch-r2.xxx.rds.amazonaws.com

6.    Check the status of the GSM listener to verify that catalog database service as well as pool database services are registered with the listener:

$ lsnrctl status <GSM server name>

For example:

$ lsnrctl status GSM1EAST1

Listener Parameter File /u01/apps/oracle/product/gsm/network/admin/gsm.ora
Listener Log File /u01/apps/oracle/diag/gsm/ip-172-xx-xx-xxx/gsm1east1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-xx-xx-xxx.ec2.internal)(PORT=1521)))
Services Summary...
Service "CSTMTRG3_A" has 1 instance(s).
Instance "CSTMTRG3", status READY, has 1 handler(s) for this service...
Service "GDS$CATALOG.thinkwithwp.com" has 1 instance(s).
Instance "CSTMTRG3", status READY, has 1 handler(s) for this service...
Service "ORADB_A" has 1 instance(s).
Instance "ORADB", status READY, has 2 handler(s) for this service...
Service "ORADB_B" has 1 instance(s).
Instance "ORADB", status READY, has 2 handler(s) for this service...
Service "ORADB_C" has 1 instance(s).
Instance "ORADB", status READY, has 2 handler(s) for this service...

7.    Create the GDS regions. In the following code, we create two regions, east1 and east2:

GDSCTL>add region -region east1, east2

8.    Assign a region to each GSM:

GDSCTL>modify gsm –gsm gsm1east1 -region east1

9.    Optionally, create a pool to group the databases according to the application. In this example, we create a pool called orders and place all pool databases in it:

GDSCTL> add gdspool -gdspool <pool> 
For example: 
GDSCTL> add gdspool -gdspool orders

In the next sections, we describe how to add pool databases and services to the configuration for different use cases.

Use case 1: Transparent managed switchover (role-based service failover)

When conducting a managed switchover with a read replica, a manual intervention is typically necessary to redirect applications to the newly promoted primary. To address this challenge, Oracle GDS offers the capability to configure a role-based service failover. Oracle GDS automatically relocates the global services based on the role of the databases. In this scenario of a role-based global services failover, we set up two global services: order_entry_srvc, configured to operate on the primary database (ORADB_A), and order_reporting_srvc, targeted to run on the standby (ORADB_D). After you run the Data Guard switchover operation, you can observe that after the role change, order_entry_srvc seamlessly relocates to the new primary (ORADB_D) and order_reporting_srvc shifts to the new standby (ORADB_A).

The following diagram illustrates the architecture before the switchover.

The following diagram illustrates the architecture after the switchover.

From the GDS server, complete the following steps:

1.    Add the databases to the GDS pool. For this use case, we use the following pool databases:

a.     oradb2 (ORADB_A) – RDS for Oracle primary database

b.     oradb1 (ORADB_D) – RDS for Oracle Read replica

GDSCTL> add database -connect oradb1 -gdspool orders -region east1

GDSCTL> add database -connect oradb2 -gdspool orders -region east1

2.    To check that the databases are registered to the GSM, run the following code:

GDSCTL> databases
Database: "oradb_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Registered instances:
orders%11
Database: "oradb_d" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: east1
Registered instances:
orders%1

3.    Add and start the services order_entry_srvc (for read/write workload) and order_reporting_srvc (for reporting workload):

GDSCTL> add service -service order_entry_srvc -gdspool orders -preferred_all -role PRIMARY
GDSCTL> start service -service order_entry_srvc -gdspool orders
GDSCTL> add service -service order_reporting_srvc -gdspool orders -preferred_all -role PHYSICAL_STANDBY
GDSCTL> start service -service order_reporting_srvc -gdspool orders

4.    Check the services that are running and the databases where they are located:

GDSCTL> services
Service "order_entry_srvc.orders.thinkwithwp.com" has 1 instance(s). Affinity: ANYWHERE
Instance "orders%11", name: "ORADB", db: "ORADB_A", region: "east1", status: ready.
Service "order_reporting_srvc.orders.thinkwithwp.com" has 1 instance(s). Affinity: ANYWHERE
Instance "orders%1", name: "ORADB", db: "ORADB_D", region: "east1", status: ready.

GDSCTL> databases
Database: "oradb_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_srvc" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Service: "order_reporting_srvc" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Registered instances:
orders%11
Database: "oradb_d" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: east1
Service: "order_entry_srvc" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Service: "order_reporting_srvc" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Registered instances:
orders%1

5.    Add TNS entries for order_entry_srvc and order_reporting_srvc to the client’s tnsnames.ora file. Replace the hostname and port with your GSM server DNS endpoint and GSM listener port:

order_entry_srvc =
(DESCRIPTION =
(FAILOVER = ON)
(CONNECT_TIMEOUT = 3)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 2)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = order_entry_srvc.orders.thinkwithwp.com)
(REGION = east1)
)
)
order_reporting_srvc =
(DESCRIPTION =
(FAILOVER = ON)
(CONNECT_TIMEOUT = 3)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 2)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = order_reporting_srvc.orders.thinkwithwp.com)
(REGION = east1)
)
)

6.    Check connectivity from the client’s sqlplus using the services:

SQL> conn <user>/<passwd>@order_entry_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_A                        PRIMARY

SQL> conn <user>/<passwd>@order_reporting_srvc

SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_D                        PHYSICAL STANDBY

7.    Perform a switchover to the replica from the Amazon RDS console or using the AWS Command Line Interface (AWS CLI).

8.    Check if the services are relocated to the new primary and standby:

GDSCTL> services
Service "order_entry_srvc.orders.thinkwithwp.com" has 1 instance(s). Affinity: ANYWHERE
Instance "orders%1", name: "ORADB", db: "ORADB_D", region: "east1", status: ready.
Service "order_reporting_srvc.orders.thinkwithwp.com" has 1 instance(s). Affinity: ANYWHERE
Instance "orders%11", name: "ORADB", db: "ORADB_A", region: "east1", status: ready.

GDSCTL> databases
Database: "oradb_a" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: east1
Service: "order_entry_srvc" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Service: "order_reporting_srvc" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Registered instances:
orders%11
Database: "oradb_d" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_srvc" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Service: "order_reporting_srvc" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Registered instances
orders%1

9.    Check connectivity from the client’s sqlplus:

SQL> conn <user>/<passwd>@order_entry_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_D                        PRIMARY

SQL> exit
SQL> conn <user>/<passwd>@order_reporting_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME                 DATABASE_ROLE
-----------------------------  ----------------
ORADB_A                        PHYSICAL STANDBY

10.    Clean up the services:

GDSCTL> stop service -service order_entry_srvc -gdspool orders
GDSCTL> stop service -service order_reporting_srvc -gdspool orders
GDSCTL> remove service -service order_entry_srvc -gdspool orders
GDSCTL> remove service -service order_reporting_srvc -gdspool orders

Use case 2: Load balancing read traffic across multiple read replicas

When multiple read replicas are deployed to efficiently manage read workloads, the challenge lies in distributing these workloads evenly. Oracle GDS offers a solution by allowing the routing of read workloads to a single reader endpoint, effectively balancing the load across the reader farm. This approach enhances resource utilization and significantly boosts the scalability of read-intensive operations. In this use case, we discuss using Oracle GDS for efficient load balancing and connect routing for read replicas in different scenarios

Create a reader endpoint for a read replica farm

In this scenario, we create a global service called reader_endpoint_srvc. This service will run on the read replicas within the setup, maintaining efficient load distribution. Client connections will be directed to the most suitable database. In cases where none of the read replicas are accessible, the service will automatically route client connections to the primary database as a failover mechanism. The following diagram illustrates this architecture.

Complete the following steps:

1.    Add the following primary and read replicas to the pool:

a.     oradb2 (ORADB_A) – RDS for Oracle primary database

b.     oradb1 (ORADB_D) – RDS for Oracle read replica database

c.     oradb_r1 (ORADB_B) – RDS for Oracle read replica database

d.     oradb_r2 (ORADB_C) – RDS for Oracle read replica database

GDSCTL> add database -connect oradb1 -gdspool orders -region east1
GDSCTL> add database -connect oradb2 -gdspool orders -region east1
GDSCTL> add database -connect oradb_r1 -gdspool orders -region east1
GDSCTL> add database -connect oradb_r2 -gdspool orders -region east1

2.    To check that the databases are registered to the GSM, run the following code:

GDSCTL> databases

3.    Add the service reader_endpoint_svc to the orders pool and verify that the service is running on all read replicas:

GDSCTL> add service -service reader_endpoint_srvc -gdspool orders –preferred_all –role PHYSICAL_STANDBY –failover_primary 

GDSCTL> services 
Service "reader_endpoint_srvc.orders.thinkwithwp.com" has 3 instance(s). Affinity: ANYWHERE 
Instance "orders%21", name: "ORADB", db: "ORADB_B", region: "east1", status: ready. 
Instance "orders%31", name: "ORADB", db: "ORADB_C", region: "east1", status: ready. 
Instance "orders%41", name: "ORADB", db: "ORADB_D", region: "east1", status: ready.

4.    Add an entry for reader_endpoint_srvc in the client’s tnsnames.ora file. Replace the hostname and port with your GSM server DNS endpoint and GSM listener port:

reader_endpoint_srvc =
(DESCRIPTION =
(FAILOVER = ON)
(CONNECT_TIMEOUT = 3)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 2)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = reader_endpoint_srvc.orders.thinkwithwp.com)
(REGION = east1)
)
)

5.    Check connectivity from the sqlplus client using the service name:

SQL>conn <user>/<passwd>@reader_endpoint_srvc

SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_B                        PHYSICAL STANDBY

6.    Make a new connection and check if the connection is routed to a different read replica database:

SQL>conn <user>/<passwd>@reader_endpoint_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_C                        PHYSICAL STANDBY

Replication lag-based routing in a reader farm

Sometimes the Data Guard standby databases may lag behind the primary database due to various reasons. In this scenario, we create a global service called reader_endpoint_srvc with the -lag attribute set to 30 seconds. With this setting, the service will be started only if the replica instance’s lag is below the threshold. This configuration allows the application to achieve better data quality by connecting only to the databases that aren’t lagging beyond the set threshold.

Complete the following steps:

1.   Follow the same steps as the previous section to add a primary database (oradb2) and replicas (oradb_r1 and oradb_r2) to the GDS pool.
2.   Add a service with the -lag attribute set to 30 seconds:

GDSCTL> add service -service reader_endpoint_srvc -gdspool orders –preferred_all –role PHYSICAL_STANDBY -lag 30 -failover_primary

GDSCTL> services
Service "reader_endpoint_srvc.orders.thinkwithwp.com" has 3 instance(s). Affinity: ANYWHERE
Instance "orders%21", name: "ORADB", db: "ORADB_B", region: "east1", status: ready.
Instance "orders%31", name: "ORADB", db: "ORADB_C", region: "east1", status: ready.

For the purpose of this post, we induce a replication lag on the oradb_r2 replica. oradb_r2 is created with smaller resources (db.t3.medium and gp2 storage) to create a lag when a heavy transaction is run in the primary.

3.    Connect to oradb_r2 instance to query the replica lag:

set lines 150
SQL>select value from v$dataguard_stats b where b.name='apply lag';
VALUE
----------------------------------------------------------------
+00 00:03:37

At this stage, reader_endpoint_srvc stops on the oradb_r2 replica:

GDSCTL> services
Service "reader_endpoint_srvc.orders.thinkwithwp.com" has 3 instance(s). Affinity: ANYWHERE
Instance "orders%21", name: "ORADB", db: "ORADB_B", region: "east1", status: ready.

4.    Run a repeated connectivity check from the sqlplus client using the service, which shows that connections are not routed to oradb_r2 when it is lagging beyond 30 seconds:

SQL>conn <user>/<passwd>@reader_endpoint_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_B                        PHYSICAL STANDBY

Location-based routing in a reader farm

One of the requirements in the case of reader farms consisting of replicas in different locations is to route connections from the clients to replicas in the same region to reduce the latency. You can achieve this using Oracle GDS by tagging databases to specific regions and using the REGION parameter in the TNS entry.

Complete the following steps:

1.    Add the following primary and read replicas to the pool databases:

a. oradb2 (ORADB_A) – RDS for Oracle primary database in the east1 region

b. oradb_r1 (ORADB_B) – RDS for Oracle read replica database in the east1 region

c. oradb_r2 (ORADB_C) – RDS for Oracle read replica database in the east2 region

GDSCTL> add database -connect oradb2 -gdspool orders -region east1
GDSCTL> add database -connect oradb_r1 -gdspool orders -region east1
GDSCTL> add database -connect oradb_r2 -gdspool orders -region east2

2.    To check that the databases are registered to the GSM, run the following code:

GDSCTL> databases

3.    Add the service reader_endpoint_svc to the orders pool and verify the service:

GDSCTL> add service -service reader_endpoint_srvc -gdspool orders –preferred_all –role PHYSICAL_STANDBY –failover_primary

GDSCTL> services
Service "reader_endpoint_srvc.orders.thinkwithwp.com" has 3 instance(s). Affinity: ANYWHERE
Instance "orders%21", name: "ORADB", db: "ORADB_B", region: "east1", status: ready.
Instance "orders%31", name: "ORADB", db: "ORADB_C", region: "east2", status: ready.

4.    Add an entry for reader_endpoint_srvc_local in the client’s tnsnames.ora file in the east2 region. Replace the hostname and port with your GSM server DNS endpoint and GSM listener port:

reader_endpoint_srvc_local =
(DESCRIPTION =
(FAILOVER = ON)
(CONNECT_TIMEOUT = 3)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 2)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = reader_endpoint_srvc.orders.thinkwithwp.com)
(REGION = east2)
)

5.    Check connectivity from the sqlplus client using the service name:

SQL>conn <user>/<passwd>@reader_endpoint_srvc_local
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                  DATABASE_ROLE
------------------------------ ----------------
ORADB_C                         PHYSICAL STANDBY

6.    Clean up the service:

GDSCTL> stop service -service reader_endpoint_srvc -gdspool orders
GDSCTL> remove service -service reader_endpoint_srvc -gdspool orders

Use case 3: Load balancing on active-active deployments

Balancing workloads across a multi-primary setup requires adapting to dynamically changing traffic patterns, which can be challenging to predict and manage. Oracle GDS provides a comprehensive load balancing solution, offering both connect time and runtime load balancing capabilities that extend across regions. These capabilities cater to the work requests within a multi-primary setup, characterized by active-active GoldenGate replication between two databases. It’s worth noting that active-active replication brings additional challenges around conflict scenarios and ordering of transactions, which need to be handled by the replication solution or application.

In this scenario, we create a global service called order_entry_writer_srvc, which will be configured to run on all the active databases in the setup. Client connections will be intelligently redirected to the appropriate database based on connect time or runtime load balancing, ensuring optimal performance and resource utilization.

The following diagram illustrates this architecture.

Complete the following steps:

1.    For this use case, add both databases involved in the active-active setup to the pool:

a.  ORADB2 (ORADB_A) – Active RDS for Oracle database 1

b.  ORCL1 (ORCL_A) – Active RDS for Oracle database 2

GDSCTL> add database -connect oradb2 -gdspool orders -region east1
GDSCTL> add database -connect orcl1 -gdspool orders -region east1

2.    Add the service:

GDSCTL> add service -service order_entry_writer_srvc -gdspool orders -preferred oradb_a, orcl_a -role PRIMARY -clbgoal LONG
GDSCTL> start service -service order_entry_writer_srvc -gdspool orders

3.    Check the services and databases:

GDSCTL> services
Service "order_entry_writer_srvc.orders.thinkwithwp.com" has 2 instance(s). Affinity: ANYWHERE
Instance "orders%11", name: "ORADB", db: "ORADB_A", region: "east1", status: ready.
Instance "orders%41", name: "ORCL", db: "ORCL_A", region: "east1", status: ready.

GDSCTL> databases

Database: "oradb_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_writer_srvc" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
orders%11
Database: "orcl_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_writer_srvc" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
orders%41

4.    Add a TNS entry for order_entry_writer_srvc in the client’s tnsnames.ora file. Replace the hostname and port with your GSM server DNS endpoint and GSM listener port:

order_entry_writer_srvc =
(DESCRIPTION =
(FAILOVER = ON)
(CONNECT_TIMEOUT = 3)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 2)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = order_entry_writer_srvc.orders.thinkwithwp.com)
(REGION = east1)
)
)

5.    Test connectivity from the client’s sqlplus using the service:

SQL>conn <user>/<passwd>@order_entry_writer_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORCL_A                         PRIMARY

6.    Open a new connection and observe if it’s routed to the other database in an active-active setup:

SQL> conn <user>/<passwd>@order_entry_writer_srvc
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_A                        PRIMARY

7.    Clean up the service:

GDSCTL> stop service -service order_entry_writer_srvc -gdspool orders
GDSCTL> remove service -service order_entry_writer_srvc -gdspool orders

Use case 4: Blue/green deployment

A blue/green deployment strategy involves replicating a production database environment into a synchronized staging environment. With Amazon RDS blue/green deployments, you can make changes to the database in the staging environment (the green environment) without impacting the production setup (the blue environment). These changes can include major or minor DB engine version upgrades, database parameter adjustments, or schema modifications in the staging environment. When the alterations are complete and you’re ready to roll them out, the staging environment can take over as the new production database environment, minimizing any potential downtime. However, manual intervention is typically necessary to redirect applications to the new production database

For RDS for Oracle databases, you can implement a blue/green deployment by creating a staging environment using logical replication methods such as Oracle GoldenGate or AWS Database Migration Service (AWS DMS). With Oracle GDS, you can carry out the transition from the blue to the green environment transparently, without any manual intervention at the DNS or application layer.

For this particular scenario, we set up a global service named order_entry_srvc. This service is configured to run on the blue deployment of the database while remaining available on the green deployment. When a role switch occurs, order_entry_srvc will be stopped on the former blue deployment and initiated on the current blue deployment, providing a seamless transition for an application.

The following diagram illustrates the blue/green deployment architecture before the upgrade.

1.    Add the following databases to the pool:

a.  ORADB2 (ORADB_A) – RDS for Oracle active database (blue)

b.  ORCL1 (ORCL_A) – RDS for Oracle passive database (green)

GDSCTL> add database -connect oradb2 -gdspool orders -region east1
GDSCTL> add database -connect orcl1 -gdspool orders -region east1

2.    Add and start the service:

GDSCTL> add service -service order_entry_service -gdspool orders -preferred oradb_a -available orcl_a
GDSCTL> start service -service order_entry_service -gdspool orders

GDSCTL> services
Service "order_entry_service.orders.thinkwithwp.com" has 1 instance(s). Affinity: ANYWHERE
Instance "orders%11", name: "ORADB", db: "ORADB_A", region: "east1", status: ready.

GDSCTL> databases
Database: "oradb_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_service" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
orders%11
Database: "orcl_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_service" Globally started: Y Started: N
Scan: Y Enabled: Y Preferred: N
Registered instances:
orders%41

3.    Add the following entry to the client’s tnsnames.ora file. Replace the hostname and port with your GSM server DNS endpoint and GSM listener port:

order_entry_service =
(DESCRIPTION =
(FAILOVER = ON)
(CONNECT_TIMEOUT = 3)
(TRANSPORT_CONNECT_TIMEOUT = 3)
(RETRY_COUNT = 2)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-xx-xx-xxx.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = order_entry_service.orders.thinkwithwp.com)
(REGION = east1)
)
)

4.    Check the connectivity from the client:

SQL> conn <user>/<passwd>@order_entry_service
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
ORADB_A                        PRIMARY

5.    After the ORCL1 database is upgraded, wait until all changes from the source are applied, then stop the service on the current active database (ORADB_A) and start it on the upgraded database:

GDSCTL> stop service -service order_entry_service -gdspool orders -database ORADB_A

6.    Disconnect any sessions that are still connected to ORADB_A:

SQL> SELECT service_name, count(1) FROM v$session GROUP BY service_name;
SERVICE_NAME                    COUNT(1)
------------------------------ ----------
SYS$USERS                          6
order_entry_service                1
SYS$BACKGROUND                     68

SQL> exec sys.dbms_service.disconnect_session('order_entry_service',DBMS_SERVICE.POST_TRANSACTION);
PL/SQL procedure successfully completed.
SQL> SELECT service_name, count(1) FROM v$session GROUP BY service_name;
SERVICE_NAME                   COUNT(1)
----------------------------- ---------
SYS$USERS                         6
SYS$BACKGROUND                    68

7.    Start the service on the upgraded database ORCL1 (ORCL_A):

GDSCTL> start service -service order_entry_service -gdspool orders -database ORCL_A

8.    Check the services and databases:

GDSCTL> services
Service "order_entry_service.orders.thinkwithwp.com" has 1 instance(s). Affinity: ANYWHERE
Instance "orders%41", name: "ORCL", db: "ORCL_A", region: "east1", status: ready.

GDSCTL> databases
Database: "oradb_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_service" Globally started: Y Started: N
Scan: Y Enabled: Y Preferred: Y
Registered instances:
orders%11
Database: "orcl_a" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: east1
Service: "order_entry_service" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: N
Registered instances:
orders%41

9.    Check the connectivity from the client’s sqlplus:

SQL> conn <user>/<passwd>@order_entry_service
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME                  DATABASE_ROLE
------------------------------ ----------------
ORCL_A                          PRIMARY

10.    Clean up the service:

GDSCTL> stop service -service order_entry_service -gdspool orders
GDSCTL> remove service -service order_entry_service -gdspool orders

Migrate to Amazon RDS for Oracle

You can also use the architecture discussed for active-active deployment and blue/green deployment with Oracle GDS to migrate critical Oracle databases to Amazon RDS for Oracle with reduced downtime. Oracle GDS allows for a smooth transition of applications to the target RDS for Oracle instance by moving services.

Clean up

To cleanup the GDS configuration, remove the service, pool database, GDS pool, GSM server, and regions, and delete the catalog. Delete all EC2 instances and RDS instances you have created as a part of this post that you no longer need. For instructions, refer to Terminate your instance and Deleting a DB instance, respectively.

Conclusion

In this post, we discussed how Oracle GDS can provide a comprehensive solution that addresses the challenges of managing globally distributed or closely interconnected Oracle databases in AWS. Oracle GDS combined with managed database services offered by AWS empowers organizations to achieve optimized workload distribution, improved performance, and high availability, and provides uninterrupted access to data for users and applications, ultimately contributing to improved business operations and customer experiences.

If you have any feedback or questions, please leave it in the comments section.