Tuesday, May 28, 2024
No menu items!
HomeDatabase ManagementEnable efficient load balancing and connection routing for Oracle database workloads in...

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

Oracle Global Data Services (Oracle GDS) is a specialized service for managing connections in distributed databases within a global environment. It’s designed to provide high availability, scalability, and efficient load balancing across multiple Oracle database instances. Oracle GDS acts as an intermediary layer between application and database tiers, overseeing high availability, optimizing load balancing, and routing requests intelligently.

In this post, we show how you can integrate Oracle GDS with Oracle databases in AWS to address the following challenges of distributed databases:

Read workload load balancing – Load balancing is a fundamental component of Oracle GDS. It automatically distributes incoming workloads across multiple DB instances based on predefined criteria, such as current system load, location, or available resources. By doing so, Oracle GDS optimizes resource utilization, preventing overloads on individual databases and ensuring equitable performance across the entire system.
Workload routing – Oracle GDS offers intelligent workload routing, which means it can efficiently direct database requests to the most suitable DB instance within the network. This capability verifies that data transactions are processed with optimal performance, reducing response times and enhancing the overall user experience.
Replication lag-based routing – Oracle GDS’s replication lag-based routing is a critical feature for data synchronization. It enables Oracle GDS to direct database queries to replicas with minimal replication lag, verifying that users consistently access the most up-to-date data without compromising on data integrity.
Role-based global services failover – In the event of a database switchover, Oracle GDS provides seamless continuity of services by facilitating inter-database service failover. It redirects incoming requests to a new primary DB instance, minimizing downtime and safeguarding against service disruptions.
Centralized workload management – Oracle GDS centralizes workload management tasks, offering administrators a unified environment to oversee and control the distribution of database requests. This centralization helps simplify administrative tasks and enhances visibility into the performance and health of the entire database infrastructure.

Solution overview

Oracle GDS is a framework that extends the Oracle Database service model to a group of synchronized databases, collectively known as a GDS configuration. For database clients, this configuration appears as a virtual multi-instance database, granting access through global services. These global services function much like local database services found in single-instance or Oracle Real Application Clusters (RAC) databases, offering load balancing, high availability, and resource management capabilities. The key distinction is that although local services are confined to instances within a single database, global services span instances across multiple databases.

Oracle GDS has the following components:

GSM server and listener A Global Service Manager (GSM) is the central software component of Oracle GDS, providing service-level load balancing, failover, and centralized management of services in the GDS configuration. GDS clients use a GSM server to perform all GDS configuration operations. Multiple GSM servers can be configured for load balancing and high availability. A GSM server can be hosted on Amazon Elastic Compute Cloud (Amazon EC2) instances.
GDS catalog A GDS catalog is a repository that stores configuration data for a GDS configuration and all global services provided by that configuration. A catalog is associated with one—and only one—GDS configuration. You can create a GDS catalog as a self-managed database on Amazon EC2 or Amazon Relational Database Service (Amazon RDS) Custom for Oracle. You can’t use Amazon RDS for Oracle for your GDS catalog due to privilege restrictions. Review the licensing requirement when choosing Amazon RDS Custom for Oracle to host a GDS catalog. In this post, we use a catalog database called gdscat.le to host GDS catalog. In this post, we use a catalog database called gdscat.
GDS pool These databases offer a common set of global services. All pool databases in your GDS configuration must be Enterprise Edition (EE). These databases can be on Amazon RDS for Oracle, Amazon RDS Custom for Oracle, a self-managed Oracle database on Amazon EC2, or an Oracle database running in an on-premises environment.
GDS region This defines a group of databases and clients in close network proximity.
GDS service This refers to the database service provided by multiple databases with replicated data.
GDSCTL – This is the command line interface for configuring Oracle GDS.

The following diagram illustrates this architecture.

As mentioned in the Oracle documentation, there is no separate license required for GSM software. For a GDS catalog database, an EE license is not required if the GDS catalog is a schema in an existing repository (for example, an Oracle Enterprise Manager repository) database or hosted as a separate single-instance database. Additional licenses apply if the catalog database is using RAC or Data Guard features. When evaluating hosting options for your catalog database on Amazon RDS Custom for Oracle, it’s essential to take into account the licensing requirements. Amazon RDS Custom for Oracle operates under the bring-your-own-license (BYOL) licensing model. All pool databases in GDS configuration must be EE and licensed for Oracle Active Data Guard or Oracle GoldenGate. You should consider validating your licensing contract with Oracle to confirm that the usage of products and features discussed in this post doesn’t violate your license terms or conditions.

The architecture patterns and use cases discussed in this post can be applied to all deployment options for Oracle databases in AWS. We use the following in Amazon RDS for Oracle as pool databases to demonstrate the usage of Oracle GDS in AWS:

oradb2 (unique name ORADB_A) – RDS for Oracle primary database 1
oradb1 (unique name ORADB_D) – RDS for Oracle read replica database
oradb_r1(unique name ORADB_B) – RDS for Oracle read replica database
oradb_r2 (unique name ORADB_C) – RDS for Oracle read replica database
orcl1 (unique name ORCL_A) – RDS for Oracle database 2

The high-level steps to implement this solution are as follows:

Install Oracle GDS.
Create and prepare a GDS catalog database.
Prepare GDS pool databases.
Configure the GSM server.
Add pool databases to the GSM server.
Add services based on use case.
Configure and test client connectivity.

In this post, we describe how to add pool databases and services to the configuration for four different use cases.

Prerequisites

Each GDS pool database should be able to establish bidirectional communication with the listener ports of every GSM server. Additionally, these GSM listener ports must be open to the application or client tier, all GDS pool databases, the GDS catalog, and all other global service managers. The TNS listener port (which typically operates on the default port number 1521) of each GDS pool database should be open for bidirectional communication with the global service managers and the GDS catalog.

You can achieve this by configuring security group rules to allow inbound and outbound traffic. It’s advisable to specify security groups as the source or destination rather than using IP addresses. This approach enhances security and simplifies rule management.

If you’re running GDSCTL from a separate machine, you must verify that bidirectional communication ports are open from that machine to each GDS pool database that has been added to the configuration.

Client connection requests to the pool database may appear to hang or fail with TNS errors if required connectivity between Oracle GDS components and pool databases are not configured properly. This should be carefully considered, especially for cross-region deployments.

Install Oracle GDS on an EC2 server

You can install and configure Oracle GDS on Amazon EC2. For more information, refer to Planning an Installation. Following the installation, set up the following ORACLE_HOME and ORACLE_BASE environment variables:

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

Create and prepare a GDS catalog database

You have the option to either create a dedicated database to host the GDS catalog or use an existing repository database. Whether it’s hosted on a self-managed database on Amazon EC2 or Amazon RDS Custom for Oracle, the configuration steps remain consistent. To verify high availability, it’s advisable to implement Oracle’s high availability features, such as Oracle Data Guard, to protect the GDS catalog against possible outages. This approach provides a robust and reliable GDS catalog infrastructure.

After you have created the catalog database, complete the following steps to configure it:

In the GDS catalog database (gdscat), log in as a privileged user and set up GDS administrator accounts and privileges using the following commands:

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.

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

   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;

   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=’*’

   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:

   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)
)
)

   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 aws.amazon.com

   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

   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 = aws.amazon.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.aws.amazon.com)
)
)
GSM1EAST1_CATALOG =
(DESCRIPTION =
(address = (protocol = tcp)(host = tsw.xxx.rds.amazonaws.com)(port = 1521))
(CONNECT_DATA =
(SERVICE_NAME = GDS$CATALOG.aws.amazon.com)
)
)

   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

   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.aws.amazon.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…

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

GDSCTL>add region -region east1, east2

   Assign a region to each GSM:

GDSCTL>modify gsm –gsm gsm1east1 -region east1

   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:

   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

   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

   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

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

GDSCTL> services
Service “order_entry_srvc.orders.aws.amazon.com” has 1 instance(s). Affinity: ANYWHERE
Instance “orders%11”, name: “ORADB”, db: “ORADB_A”, region: “east1”, status: ready.
Service “order_reporting_srvc.orders.aws.amazon.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

   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.aws.amazon.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.aws.amazon.com)
(REGION = east1)
)
)

   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

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

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

GDSCTL> services
Service “order_entry_srvc.orders.aws.amazon.com” has 1 instance(s). Affinity: ANYWHERE
Instance “orders%1”, name: “ORADB”, db: “ORADB_D”, region: “east1”, status: ready.
Service “order_reporting_srvc.orders.aws.amazon.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

   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

   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:

   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

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

GDSCTL> databases

   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.aws.amazon.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.

   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.aws.amazon.com)
(REGION = east1)
)
)

   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

   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:

  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.
  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.aws.amazon.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.

   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.aws.amazon.com” has 3 instance(s). Affinity: ANYWHERE
Instance “orders%21”, name: “ORADB”, db: “ORADB_B”, region: “east1”, status: ready.

   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:

   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

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

GDSCTL> databases

   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.aws.amazon.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.

   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.aws.amazon.com)
(REGION = east2)
)

   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

   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:

   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

   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

   Check the services and databases:

GDSCTL> services
Service “order_entry_writer_srvc.orders.aws.amazon.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

   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.aws.amazon.com)
(REGION = east1)
)
)

   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

   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

   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.

The following diagram illustrates the architecture of the upgraded green environment and switchover.

The following diagram illustrates the architecture of the blue/green deployment after the upgrade and switchover.

The following diagram illustrates the final blue/green deployment architecture.

Assuming that GoldenGate uni-directional replication is already configured, with Extract running initially on ORDB_A (source) and Replicat running on ORCL_A (target), complete the following steps:

   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

   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.aws.amazon.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

   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.aws.amazon.com)
(REGION = east1)
)
)

   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

   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

   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

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

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

   Check the services and databases:

GDSCTL> services
Service “order_entry_service.orders.aws.amazon.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

   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

   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.

About the Authors

Archana Sharma is a Sr. Database Specialist Solutions Architect, working with Worldwide Public Sector customers. She has years of experience in relational databases, and is passionate about helping customers in their journey to the AWS Cloud with a focus on database migration and modernization.

Jobin Joseph is a Senior Database Specialist Solution Architect based in Toronto. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS. He is an Oracle Certified Master with over 20 years of experience with Oracle databases.

Read MoreAWS Database Blog

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments