To plan your Oracle Sharding configuration you need an understanding of the objects that make up a sharded database configuration, so that you can best configure and deploy them to meet your requirements.

The sharded database configuration consists of the sharding method, replication (high availability) technology, the default number of chunks to be present in the sharded database, the location and number of shard directors, the numbers of shardgroups, shardspaces, regions, and shards in the sharded database, and the global services that will be used to connect to the sharded database.

Oracle Database Global Data Services Architecture

Because the Oracle Sharding feature is built on the Oracle Database Global Data Services feature, to plan your Oracle Sharding topology you might benefit from an understanding of the Global Data Services architecture. See Introduction to Global Data Services for conceptual information about Global Data Services.

Before you install any software, review these hardware, network, and operating system requirements for Oracle Sharding.

• Oracle Database Enterprise Edition is required when running an Oracle Sharded Database.

• Hardware and operating system requirements for shards are the same as those for Oracle Database. See your Oracle Database installation documentation for these requirements.

• Hardware and operating system requirements for the shard catalog and shard directors are the same as those for the Global Data Services catalog and global service manager. See Oracle Database Global Data Services Concepts and Administration Guide for these requirements.

• Network Low Latency GigE is strongly recommended

• Port communication requirements are as follows.

  • Each and every shard must be able to reach each and every shard director's listener and ONS ports. The shard director listener ports and the ONS ports must also be opened to the application/client tier, all of the shards, the shard catalog, and all other shard directors.

    The default listener port of the shard director is 1522, and the default ONS ports on most platforms are 6123 for the local ONS and 6234 for remote ONS.

  • Each and every shard must be able to reach the TNS Listener port (default 1521) of the shard catalog (both primary and standbys).

  • The TNS Listener port of each shard must be opened to all shard directors and the shard catalog.

  • All of the port numbers listed above are modifiable during the deployment configuration. However, the port numbers to be used must be known before setting up the host software.

• Host name resolution must be successful between all of the shard catalog, shards, and shard director hosts. Operating system commands such as ‘ping’ must succeed from a given host to any other host when specifying any host names provided during sharded database configuration commands.

Number and Sizing of Host Systems

Depending on your specific configuration, the hosts that are needed may include the following:

• Shard catalog host. The shard catalog host runs the Oracle Database that serves as the shard catalog. This database contains a small amount of sharding topology metadata and any duplicated tables that are created for your application. In addition, the shard catalog acts as a multi-shard query coordinator for cross-shard queries and services connections for applications that have not been written to be sharding-aware. In general, the transaction workload and size of this database are not particularly large.

• Shard catalog database standbys (replicas). At least one more host to contain a replica or standby of the primary shard catalog database is recommended. This host is necessary in case of a failure of the primary catalog host. In addition, while acting as a standby database, this host can also be configured to be a query coordinator for cross-shard queries.

• Shard director host. The shard director (global service manager) software can reside on a separate host, or it can be co-located on the same host as the shard catalog. This component of the sharding system is comprised of a network listener and several background processes used to monitor and configure a sharded configuration. If it is co-located on the same host as the catalog database, the shard director must be installed in a separate Oracle Home from the catalog database, because the installation package is different than the one used for Oracle Database.

• Multiple shard directors. For high-availability purposes, it is recommended that you have more than one shard director running in a sharded system. Any additional shard directors can run on their own hosts or on the hosts running the standby shard catalog databases.

• Shards. In addition to the above hosts, each shard that is configured in the system should also run on its own separate host. The hosts and their configurations chosen for this task should be sized in the same way as a typical Oracle Database host depending on how much load is put on each particular shard.

• Shard standbys (replicas). Again, for high-availability and disaster recovery purposes, use Oracle Data Guard and replicas created for all sharded data. Additional hosts will be needed to run these replica or standby databases.

  Note:

  Oracle GoldenGate replication support for Oracle Sharding High Availability is deprecated in Oracle Database 21c.

When the number of hosts and capacity requirements for each host have been determined, provision your hardware resources as appropriate for your environment using whatever methodologies you choose.

Before installing any software, you must confirm that the hosts can communicate with each other though the ports as described above. Because a sharding configuration is inherently a distributed system, it is crucial that this connectivity between and among all of the hosts is confirmed before moving on to the next steps in the deployment process. Failure to set up port access correctly will lead to failures in subsequent commands.

The multi-shard query coordinator, a component of the shard catalog, can be kept highly available and scaled to meet its workload with these recommendations.

The availability of the multi-shard coordinator impacts proxy-routing based workloads, so it is highly recommended that the coordinator be protected with Data Guard in Maximum Availability protection mode (zero data loss failover) with fast-start failover enabled. The coordinator may optionally be Oracle RAC-enabled for additional availability and scalability.

To improve the scalability and availability of multi-shard query workloads, Oracle Active Data Guard standby shard catalog databases in read-only mode can act as multi-shard query coordinators. For each active replica of the catalog database, a special coordinator service, GDS$COORDINATOR.cloud_name (where cloud_name is the value specified for the configname parameter in the GDSCTL CREATE SHARDCATALOG command, and is oradbcloud by default) is running and registered on all shard directors.

Clients can connect to this service on any of the replicas and perform multi-shard queries, allowing shard directors to distribute the multi-shard query workload with respect to runtime load balancing and decrease the load on in the primary shard catalog, which is the central component of the Oracle Sharding framework.

Additionally, if the database’s region is set, and the client specifies the region in the connection string, a shard director routes a connection with respect to regional affinity.

Availability of the multi-shard query coordinator has zero impact on workloads using direct routing.

Use the GDSCTL CREATE SHARDCATALOG command to create metadata describing the sharded database topology in the shard catalog database.

CATALOG_CONNECT_STRING=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = primary_catalog)(PORT = 1521))
      (ADDRESS = (PROTOCOL = tcp)(HOST = standby_catalog)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = catpdb.example.com)
    )
  )

• Run CREATE SHARDCATALOG with the settings appropriate for your planned sharding topology.

  System-Managed Sharding Method

  In the following example, the sharded database metadata is created for a system-managed sharding configuration with two regions named region1 and region2. Because system-managed is the default sharding method, it does not need to be specified with the -sharding parameter.

  GDSCTL> create shardcatalog -database catalog_connect_string
   -user mysdbadmin/mysdbadmin_password -repl DG -region region1,region2
  

  Note also that if -shardspace is not specified, a default shardspace named shardspaceora is created. If -region is not specified, the default region named regionora is created. If the single default region is created along with the default shardspace, then a default shardgroup named shardspaceora_regionora is also created in the shardspace.

  Composite Sharding Method

  The following example shows you how to create shard catalog metadata for a composite sharded database with Data Guard replication in MaxAvailability protection mode, 60 chunks per shardspace, and two shardspaces.

  GDSCTL> create shardcatalog -database catalog_connect_string
   -user mysdbadmin/mysdbadmin_password -sharding composite -chunks 60 
   -protectmode maxavailability -shardspace shardspace1,shardspace2
  

  User-Defined Sharding Method

  The next example shows you how to create shard catalog metadata for a user-defined sharded database with Data Guard replication.

  GDSCTL> create shardcatalog -database catalog_connect_string
   -user mysdbadmin/mysdbadmin_password -sharding user
   -protectmode maxperformance 
  

GDSCTL> connect mysdbadmin/mysdbadmin_password@catalog_connect_string

Add to the configuration the shard directors, which will monitor the sharding system and run background tasks in response to GDSCTL commands and other events, and start them.

1. Add and start a shard director (GSM), as shown in the following example.

   GDSCTL> connect mysdbadmin/mysdbadmin_password@catalog_connect_string
   GDSCTL> add gsm -gsm sharddirector1 -catalog catalog_connect_string -pwd gsmcatuser_password
   GDSCTL> start gsm -gsm sharddirector1
   

   The value for the -gsm parameter is the name that you will be using to reference this shard director in later GDSCTL commands. The values for the -catalog and -pwd parameters should be the same used when you created the shard catalog database.

   Use the -listener, -localons, and -remoteons parameters as described in the GDSCTL reference to override the default port numbers of 1522, 6123, and 6234, respectively. Always confirm that the port numbers to be used, whether default or user-specified, are available on the host and do not conflict with other running software or Oracle listeners.

2. Repeat the ADD GSM and START GSM commands for any additional shard directors on each shard director host.

   Replace the shard director name (that is, sharddirector1 in the example) with an appropriate value for each shard director.

   If more than one shard director is used, then multiple regions must have been created for them in the CREATE SHARDCATALOG command, or you can add them later by running ADD REGION.

   Specify a region for each shard director with the -region parameter on each ADD GSM command, as shown here.

   GDSCTL> add gsm -gsm sharddirector2 -catalog catalog_connect_string -pwd gsmcatuser_password -region dc2

GDSCTL> set gsm -gsm sharddirector1

If you are using composite or user-defined sharding, and you need to add more shardspaces to complete your desired sharding topology, use the ADD SHARDSPACE command to add additional shardspaces.

• Run ADD SHARDSPACE as shown here.

  GDSCTL> add shardspace -shardspace shardspace2 

  By default, the ADD SHARDSPACE command inherits the -chunks and -protectmode values that you used in the CREATE SHARDCATALOG command. You can specify, on a per-shardspace basis,the number of chunks and the Data Guard protection mode by using the -chunks and -protectmode parameters with ADD SHARDSPACE.

If your sharded database topology uses the system-managed or composite sharding method, you can add any necessary additional shardgroups for your application.

• Run ADD SHARDGROUP to add shardgroups to the configuration.

  GDSCTL> add shardgroup -shardgroup shardgroup_primary -shardspace shardspace1
   -deploy_as primary -region region1
  GDSCTL> add shardgroup -shardgroup shardgroup_standby -shardspace shardspace1
   -deploy_as active_standby -region region2
  

  Note that when you run ADD SHARDGROUP you can specify one of three types of shardgroups: primary, standby (mounted, not open), and active_standby (open, available for queries) using the -deploy_as parameter (the default is standby).

  Any shards subsequently added to the shardgroup must be opened in the mode corresponding to the -deploy_as setting for the shardgroup. For example, read-write for primary shardgroups, mounted for standby shardgroups, or read-only with apply for active standby shardgroups.

  After shards are deployed, their current mode is monitored by the shard directors and communicated to the shard catalog such that it is possible and expected that shards of different open modes may be in the same shardgroup, depending upon subsequent switchover or failover operations.

Before adding information about your shard databases to the catalog, verify that your sharding topology is correct before proceeding by using the various GDSCTL CONFIG commands.

• Run GDSCTL CONFIG to view overall configuration information.

  GDSCTL> config
  
  Regions
  ------------------------
  region1                       
  region2                       
  
  GSMs
  ------------------------
  sharddirector1                          
  sharddirector2                          
  
  Sharded Database
  ------------------------
  orasdb                     
  
  Databases
  ------------------------ 
  
  Shard Groups
  ------------------------
  shardgroup_primary                         
  shardgroup_standby                         
  
  Shard spaces
  ------------------------
  shardspaceora                         
  
  Services
  ------------------------
  
  GDSCTL pending requests
  ------------------------
  Command                   Object                  Status
  -------                   ------                  ------
  
  Global properties
  ------------------------
  Name: oradbcloud
  Master GSM: sharddirector1
  DDL sequence #: 0

  You can use the various GDSCTL CONFIG commands to display more information about shardspaces, shardgroups, and other shard catalog objects. For a complete list of GDSCTL CONFIG command variants, see the GDSCTL reference documentation or run GDSCTL HELP.

Add the CDBs containing the shard PDBs to the sharding configuration with the ADD CDB command.

1. Run the ADD CDB command as shown here.

   GDSCTL> add cdb -connect cdb_connect_string -pwd gsmrootuser_password

   This command causes GDSCTL to connect to GSMROOTUSER/gsmrootuser_password@cdb_connect_string as SYSDG to validate settings and to retrieve the DB_UNIQUE_NAME of the CDB, which will become the CDB name in the shard catalog.

2. Repeat the ADD CDB command for all of the CDBs that contain a shard PDB in the configuration.
3. When all of the CDBs are added, run GDSCTL CONFIG CDB to display a list of CDBs in the catalog.

   GDSCTL> config cdb

Use the ADD SHARD command to add the shard PDB information to the shard catalog, then verify it with the CONFIG SHARD command.

1. Run ADD SHARD with the usage appropriate to your sharding method, as shown in the following examples.

   For system-managed or composite sharding, run ADD SHARD with the parameters shown here.

   
   GDSCTL> add shard -connect shard_connect_string -pwd gsmuser_password 
   -shardgroup shardgroup_name -cdb cdb_name
   

   For user-defined sharding, the command usage is slightly different.

   GDSCTL> add shard -connect shard_connect_string -pwd gsmuser_password 
   -shardspace shardspace_name -deploy_as db_mode -cdb cdb_name
   

   The -cdb parameter specifies the name of the CDB in which the shard PDB exists, -shardgroup or -shardspace specifies the location of the shard in your sharding topology, and -deploy_as specifies the open mode (primary, standby, active_standby) of the shard.

   Note:

   It is highly recommended that you set server=dedicated in the connect string.

   When you run ADD SHARD, GDSCTL connects to GSMUSER/gsmuser_password@shard_connect_string as SYSDG to validate the settings on the shard, re-runs dbms_gsm_fix.validateShard to check for errors, and constructs the shard name using the convention db_unique_name_of_CDB_PDB_name (for example cdb1_pdb1).

   Finally, the metadata that describes the shard is added to the shard catalog.

2. Run GDSCTL CONFIG SHARD to view the shard metadata on the shard catalog.

   GDSCTL> config shard
   Name      Shard Group          Status    State    Region    Availability
   --------- -------------------  ------    -----    ------    ------------
   cdb1_pdb1 shardgroup_primary   U         none     region1   -
   cdb2_pdb1 shardgroup_standby   U         none     region2   -
   cdb3_pdb2 shardgroup_primary   U         none     region1   -
   cdb4_pdb2 shardgroup_standby   U         none     region2   -
   

   Note that the value for Status is U for “undeployed”, and State and Availability are none and - until the DEPLOY command is successfully run.

Add all of the host names and IP addresses of your shard hosts to the shard catalog.

1. View a list of trusted hosts.

   By default, the ADD SHARD command adds the default host name of the shard host to the shard catalog metadata, so that any registration requests from that host to the shard director will be accepted. You can view the list of trusted hosts by running the GDSCTL CONFIG VNCR command.

   GDSCTL> config vncr

2. Ping from all of the hosts in the configuration to verify successful host name resolution.

   Any hosts listed in the CONFIG VNCR output must be reachable by name from all of the other hosts in the topology. Use the ping command from the shard, shard catalog, and shard director hosts to verify that hostname resolution succeeds for all of the host names listed.

   To resolve any issues, use operating system commands or settings to ensure that all of the host names can be resolved.

3. Run the REMOVE INVITEDNODE command to manually remove any host names that are not necessary and cannot be resolved from all of the hosts.
4. Run the ADD INVITEDNODE command to manually add all host names and IP addresses of your shard hosts to the shard catalog metadata.

   GDSCTL> add invitednode 127.0.0.1

Consider the following system-managed sharded database configuration, where shardgroup sg1 contains the primary shards, while shardgroups sg2 and sg3 contain standby replicas.

In addition, let’s assume that the replicas in shardgroup sg2 are Oracle Active Data Guard standbys (that is, databases open for read-only access), while the replicas in shardgroup sg3 are mounted databases that have not been opened.

Description of the illustration sharding-topology-system-managed.eps

Do the following steps to deploy the example system-managed sharded database with multiple replicas, using Oracle Data Guard for high availability.

1. Provision and configure the following hosts: cathost, cathost1, cathost2, gsmhost1, gsmhost2, and hosts shardhost1 through shardhost9.

   See Provision and Configure Hosts and Operating Systems for details.

2. Install the Oracle Database software on the following hosts: cathost, cathost1, cathost2, and shardhost1 through shardhost9.

   See Install the Oracle Database Software for details.

3. Install the shard director software on hosts gsmhost1 and gsmhost2.

   See Install the Shard Director Software for details.

4. Create the shard catalog database and start an Oracle TNS Listener on cathost.

   Additionally, create standby replicas of the catalog on cathost1 and cathost2, and verify that changes made to the primary catalog are applied on these standbys.

   See Create the Shard Catalog Database for details.

5. Create the 3 primary databases that will contain the sharded data on hosts shardhost1, shardhost2 and shardhost3.

   Create the corresponding replicas, located and named as listed here.

   • shardhost1 (cdb1/pdb1) replicas on shardhost4 (cdb4) and shardhost7 (cdb7)
   • shardhost2 (cdb2/pdb2) replicas on shardhost5 (cdb5) and shardhost8 (cdb8)
   • shardhost3 (cdb3/pdb3) replicas on shardhost6 (cdb6) and shardhost9 (cdb9)

   The db_unique_name of the 9 container databases (CDB) should be cdb1 through cdb9, in which the PDB names should be pdb1, pdb2 and pdb3 on the three primaries and their replicas.

   The service names for the CDBs should be cdb1 through cdb9, which the service names for the PDB shards are pdb1, pdb2, and pdb3.

   See Create the Shard Databases for details.

6. Assuming that all port numbers are the defaults, to configure the sharded database topology, issue the following GDSCTL commands, replacing domains and passwords with the appropriate values.

   1. On host gsmhost1, run the following commands in GDSCTL.

      create shardcatalog -database cathost.example.com:1521/catpdb.example.com -user mydbsadmin/mydbsadmin_password -region dc1,dc2
      
      add gsm -gsm gsm1 -region dc1 -catalog cathost.example.com:1521/catpdb.example.com -pwd gsmcatuser_password
      start gsm -gsm gsm1

      See Create the Shard Catalog and Add and Start Shard Directors for details.

   2. On host gsmhost2, run the following commands in GDSCTL.

      connect mydbsadmin/mydbsadmin_password@cathost.example.com:1521/catpdb.example.com
      add gsm -gsm gsm2 -region dc2 -catalog cathost.example.com:1521/catpdb.example.com -pwd gsmcatuser_password
      start gsm -gsm gsm2
      

      See Add and Start Shard Directors for details.

   3. Back on host gsmhost1, run the following from GDSCTL to complete the sharded database setup.

      add shardgroup -shardgroup sg1 -deploy_as primary -region dc1
      add shardgroup -shardgroup sg2 -deploy_as active_standby -region dc1
      add shardgroup -shardgroup sg3 -deploy_as standby -region dc2
      add cdb -connect shardhost1.example.com:1521/cdb1.example.com -pwd gsmrootuser_password
      add cdb -connect shardhost2.example.com:1521/cdb2.example.com -pwd gsmrootuser_password

      Repeat the ADD CDB command for shardhost3 through shardhost9 and cdb3 through cdb9, then run the following commands.

      add shard -connect shardhost1.example.com:1521/pdb1.example.com -pwd gsmuser_password -shardgroup sg1 -cdb cdb1
      add shard -connect shardhost2.example.com:1521/pdb2.example.com -pwd gsmuser_password -shardgroup sg1 -cdb cdb2
      add shard -connect shardhost3.example.com:1521/pdb3.example.com -pwd gsmuser_password -shardgroup sg1 -cdb cdb3
      add shard -connect shardhost4.example.com:1521/pdb1.example.com -pwd gsmuser_password -shardgroup sg2 -cdb cdb4
      add shard -connect shardhost5.example.com:1521/pdb2.example.com -pwd gsmuser_password -shardgroup sg2 -cdb cdb5
      add shard -connect shardhost6.example.com:1521/pdb3.example.com -pwd gsmuser_password -shardgroup sg2 -cdb cdb6
      add shard -connect shardhost7.example.com:1521/pdb1.example.com -pwd gsmuser_password -shardgroup sg3 -cdb cdb7
      add shard -connect shardhost8.example.com:1521/pdb2.example.com -pwd gsmuser_password -shardgroup sg3 -cdb cdb8
      add shard -connect shardhost9.example.com:1521/pdb3.example.com -pwd gsmuser_password -shardgroup sg3 -cdb cdb9

      See Add Shardgoups If Needed, Add the Shard CDBs, and Add the Shard PDBs for details.

   4. Use the CONFIG VNCR and ADD INVITEDNODE commands to validate that all of the VNCR entries are valid and sufficient for a successful deployment.

      See Add Host Metadata for details.

   5. Run DEPLOY from GDSCTL to complete the configuration of the sharded database.

      See Deploy the Sharding Configuration for details.

   6. Add and start services for read-write and read-only access to the sharded database.

      add service -service oltp_rw_srvc -role primary
      start service -service oltp_rw_srvc
      add service -service oltp_ro_srvc -role physical_standby
      start service -service oltp_ro_srvc
      

      See Create and Start Global Database Services for details.

7. You can use the GDSCL CONFIG, CONFIG SHARD, and CONFIG SERVICE commands to validate that all of the shards and services are online and running.

   See Verify Shard Status for details.