Many organizations maintain one or more replicas of their production databases in local and/or geographically disparate data centers to meet various business requirements such as high availability, disaster recovery, content localization and caching, scalability, optimal performance for local clients or compliance with local laws. Oracle Active Data Guard and Oracle GoldenGate are the strategic replication technologies native to Oracle Database used to synchronize one or more replicated copies for such purposes.

Achieving high performance and high availability by distributing workload across multiple database replicas, however, presents challenges that extend beyond the capabilities of the replication technology. Workload must be intelligently load balanced to effectively utilize all resources and to achieve the best performance.

Oracle Global Data Services (GDS) is a feature of Oracle Database 12c that provides connect-time and run-time load balancing, region affinity, replication lag tolerance based workload routing, and enables inter-database service failover over a set of replicated databases.

In this blog post, I will talk about how you can perform load balancing of your read-only workload across the reader farm (a set of Active Data Guard standby databases or a set of Oracle GoldenGate replicas) using Oracle GDS.

GDS provides connect-time and run-time load balancing (within and across data centers) on a reader farm. In this, client connections are load balanced at the connect time. Connection pool based Oracle Integrated Clients can subscribe to the RLB events and can load balance the workload requests at run-time as well. GDS allows better resource utilization and higher scalability by balancing Read Only workload on an Active Data Guard or Oracle GoldenGate reader farm.

Figure 1: Load balancing of Read-Only workloads on a reader farm

The above pictorial depicts GDS enabled for an Active Data Guard or Oracle GoldenGate reader farm with physical standbys/replicas located in both local and remote data centers. Order Entry (Read Write) global service runs on the Primary/Master database. Reporting (Read Only) global Services run on the reader farm. Client connections are load balanced among the Read Only global services running on the reader farm (within or across data centers).

To load balance the read-only workload over the reader farm, the global service should be defined as shown below.

Oracle GoldenGate Example:

In this example, DB01 is the master and DB02, DB03, DB04 are the replicas.

GDSCTL>add service -service reporting_srvc -gdspool sales –preferred DB02, DB03, DB04 -clbgoal LONG –rlbgoal SERVICE_TIME

Active Data Guard Example:

GDSCTL>add service -service reporting_srvc -gdspool sales –preferred_all –role PHYSICAL_STANDBY -clbgoal LONG –rlbgoal SERVICE_TIME

Additional Notes on CLBGOAL and RLBGOAL attributes:

With the CLBGOAL attribute of a global Service, we can attain connect-time load balancing i.e. choosing the least loaded database instance for establishing a new connection.

CLBGOAL supports two values – LONG and SHORT. Use the ‪LONG connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. ‪LONG is the default connection load balancing goal. Use the ‪SHORT connection load balancing method for applications that have short-lived connections. When using connection pools that are integrated with FAN, set the ‪CLBGOAL to ‪SHORT.

Run-time load balancing is a feature of Oracle connection pools that can distribute client work requests across persistent connections that span databases. GDS supports Runtime load balancing feature of connection pool based clients (OCI, JDBC, ODP.NET, and WLS) that are integrated with the Oracle database, i.e. for a particular work request, “picking” a connection in the pool which belongs to the least loading instance.

RLBGOAL supports two values – SERVICE_TIME and THROUGHPUT. With the RLBGOAL set to SERVICE_TIME, Connection Pools route work requests to DB instances to minimize response time. The load balancing advisory is based on elapsed time for work requests using the service and network latency. With the RLBGOAL set to THROUGHPUT, Connection Pool routes work requests to DB instances to maximize total throughput of the system. The load balancing advisory is based on rate of work completion in the service plus available bandwidth to service.

To learn about how to setup and configure GDS, take a look at the GDS Cookbook: http://www.oracle.com/technetwork/database-features/database-ee/gds-with-ogg-cookbook-4004663.pdf .

For more info on GDS, do visit GDS OTN Portal