8 Working with Oracle GoldenGate Sharding

Oracle GoldenGate provides a cohesive platform for a sharded Oracle Database, allowing data replication across various sharded database topologies.

All the functionality of a sharded database, in addition to providing pre-configured Oracle GoldenGate replication as part of the GDSCTL DEPLOY command, is included.

• Oracle GoldenGate With a Sharded Database
  Sharding is only available with Oracle Database 12.2.0.1 or later, over a secure MA deployment.
• How to Configure Sharding in Oracle GoldenGate
  If you enable sharding, you must set up a secure deployment.

Oracle GoldenGate With a Sharded Database

Sharding is only available with Oracle Database 12.2.0.1 or later, over a secure MA deployment.

You need to make sure that you setup your SSL certificate before you setup sharding. To configure a sharded Oracle Database with Oracle GoldenGate, see Configuring Sharding for Oracle GoldenGate.

Advantages of Oracle GoldenGate Sharding

Oracle GoldenGate provides a complete data replication platform for sharded databases.

This is a powerful capability with the following advantages:

• Horizontally partitions data and workload across numerous discrete Oracle databases that do not share hardware or software

• Enables automatic partitioning and replication, elastic scaling, rebalancing, data-dependent routing for single-shard and cross-shard queries

• Provides an enterprise-class database platform for new generation developers who:

  • Explicitly design applications to scale linearly with fault tolerance

  • Assume schema flexibility with JSON

  • See benefits in the power of relational SQL and ACID

• Active replication within and across shardgroups

• Flexible Deployment, which could have single shardgroup for high availability, multiple shardgroups with varying replication factors

• Different shardgroups can have different replication factors, different number of shards, different hardware platforms and OS versions, or different database versions and patch sets.

How to Configure Sharding in Oracle GoldenGate

If you enable sharding, you must set up a secure deployment.

Prerequisites

Before you begin with the sharding setup, you must adhere to the following prerequisites:

• Complete Oracle Database install for the catalog and each shard database.

• Create ggshd_wallet directory for storing Oracle GoldenGate client certificate under $ORACLE_BASE/admin (if $ORACLE_BASE is defined) or $ORACLE_HOME/admin (when $ORACLE_HOME is defined).

• Add one microservices deployment per host where shard catalog or shards is set up.

• Generate Oracle GoldenGate MA server and client wallets and certificates.

• Authorize a sharding client user identified by SSL certificate.

  (Recommended) Assign only one Oracle GoldenGate deployment for each shard for High Availability and simplified patching of shards.

For more information on generating security certificates, see Setting Up a Secure Deployment.

Sharding Configuration in Oracle GoldenGate

As a best practice, a deployment should be dedicated to each shard. This ensures high availability. For more information on the advantages of using Oracle GoldenGate sharding, see How Does Oracle GoldenGate Work for a Sharded Database.

The following steps are required to configure sharding in cases where you add a shard from a shardcatalog or create a shard:

1. Add a deployment using Oracle GoldenGate Configuration Assistant (OGGCA) in secure mode. See How to Create Deployments.

2. Import the client certificate to ggshd_wallet. Ensure Oracle GoldenGate MAservers are up and running on Shards.

3. Prepare to set up a sharded database by connecting to the Oracle Sharding Coordinator (catalog database).

4. Load the Oracle GoldenGate sharding bootstrap scripts located in the $OGG_HOME/lib/sql/sharding directory. This is a one-time task.

5. Run the following command from the Oracle Sharding Coordinator:

   shardcatalog load (as SYS):

   $OGGHOME/lib/sql/sharding/ggsys_setup.sql

6. Before adding shards, load the following command (as SYS):

   $OGGHOME/lib/sql/sharding/orashard_setup.sql 
   A <serviceManagerURI>/<OGGDeployName> 
   <ggadmin_password> <shardconnect_string>

   Note:

   This command is not required when you create a shard.

There are two ways to configure shards for Oracle GoldenGate:

• Add shards: It converts an existing single instance database into a shard. However, the instance must not contain any user data and should be an empty database.

• Create shard: It sets up a new database at runtime. These commands are issued from the GDSCTL shell interface. See Sharded Database Deployment in Oracle Database Using Oracle Sharding Guide.

create shardcatalog -database bpodb12s:1521/sdbcat1 -user gsmcatuser/gsmcatuser -repl OGG -sharding SYSTEM -chunks 36

add gsm -gsm gsm1 -listener 1540 -catalog bpodb12s:1521/sdbcat1 -pwd gsmcatuser

add shardgroup -shardgroup shgrp1 -repfactor 3
add shardgroup -shardgroup shgrp2 -repfactor 2
…
create shard -shardgroup shgrp1 -destination host01 -CREDENTIAL gds_oracle -netparam none 
-gg_service host01:9000/deploy1
-gg_password ggadmin pw
create shard -shardgroup shgrp1 -destination host02 -CREDENTIAL gds_oracle -netparam none 
-gg_service host02:9000/deploy2 -gg_password ggadmin status
configure
add service ...
start service ..