The Oracle Sharding feature for Oracle Database 18c supported only one table family (a set of related tables sharing the same sharding key) for each sharded database. In Oracle Database 19c, Oracle Sharding includes support for multiple table families where all data from different table families reside in the same chunks. This feature applies to system-managed sharded databases only. Different applications accessing different table families can now be hosted on one sharded database.

There is one new GDSCTL command, CONFIG TABLE FAMILY, and several other commands are extended to support this feature: ADD SERVICE, MODIFY SERVICE, CONFIG SERVICE, CONFIG CHUNKS, STATUS ROUTING, and VALIDATE CATALOG.

There are no new SQL keywords or statements introduced with this feature; however, some restrictions are changed with the use of CREATE SHARDED TABLE and TABLESPACE SET.

See

• Sharded Table Family

• Oracle Database Global Data Services Concepts and Administration Guide

• Oracle Database SQL Language Reference

In Oracle Database 18c, Oracle Sharding introduced the capability for using a single PDB in a CDB as a shard or a shard catalog database. In Oracle Database 19c, Oracle Sharding enables you to use more than one PDB in a CDB for shards or shard catalog databases, with certain restrictions. For example, this feature allows a CDB to contain shard PDBs from different sharded databases (SDBs), each with their own separate catalog databases.

See

• Using Oracle Multitenant with Oracle Sharding for information about how to use PDBs as shards

Before Oracle Database 19c, if you needed a unique number across shards you had to manage it yourself. In Oracle Database 19c, Oracle Sharding allows you to independently generate sequence numbers on each shard which are unique across all shards.

To support this feature, new SEQUENCE object clauses, SHARD and NOSHARD, are included in the SEQUENCE object DDL syntax.

See

• Generating Unique Sequence Numbers Across Shards

• Oracle Database SQL Language Reference

Before Oracle Database 19c, only the primary shard catalog database could be used as the multi-shard query coordinator. In Oracle Database 19c you can also enable the multi-shard query coordinator on Oracle Active Data Guard standbys of the shard catalog database. This improves the scalability and availability of multi-shard query workload.

See

• Oracle Sharding Multi-Shard Query Coordinator

Before Oracle Database 19c, database administrators had to configure ALTER SYSTEM parameter settings on each shard in a sharded database. This feature provides ease of manageability by allowing administrators to centrally manage and propagate parameter settings from the shard catalog to all of the database shards. Once settings are configured at the shard catalog, they are automatically propagated to all shards of the sharded database.

See Propagation of Parameter Settings Across Shards

To enhance security, starting with Oracle Database 19c, the ability to specify passwords from the Global Data Services Control Utility (GDSCTL) command-line when called from the operating system prompt is no longer supported.

This desupport applies only to password changes where GDSCTL is called from a user command-line prompt. For example, the following command is desupported:

$ gdsctl add database -connect inst1 -pwd gsm_password

Specifying the password from the GDSCTL utility itself is still valid. For example, the following command is valid:

GDSCTL> add database -connect inst1 -pwd gsm_password

This deprecation addresses the security vulnerability when specifying passwords in GDSCTL commands called from the operating system prompt.

User-defined sharding allows you to explicitly specify mapping of data to individual shards. It is used when, because of performance, regulatory, or other reasons, certain data needs to be stored on a particular shard and you must have full control moving data between shards. This method allows you to define LIST or RANGE based sharding.

See

• User-Defined Sharding for a conceptual overview of user-defined sharding

• Using Oracle Data Guard with a Sharded Database for information about replicating a user-defined sharded database Oracle Data Guard

• Creating and Deploying a User-Defined SDB for tasks related to configuring, creating and deploying a user-defined sharded database

Use a PDB in a CDB for shards or a shard catalog database. In this release Oracle Sharding supports a shard or shard catalog as a single PDB in a CDB. The GDSCTL command ADD SHARD is extended and new commands ADD CDB, MODIFY CDB, CONFIG CDB, and REMOVE CDB are implemented so that Oracle Sharding can support a multitenant architecture.

See

• Using Oracle Multitenant with Oracle Sharding for information about how to use PDBs as shards

• Oracle Database Global Data Services Concepts and Administration Guide for information about the new commands

Oracle GoldenGate is used for fine-grained active-active replication where all shards are writable, and each shard can be partially replicated to other shards within a shardgroup. The Automatic Conflict Detection and Resolution feature added in Oracle 12.2 is used to handle any conflicts due to the active-active replication

See Shard-Level High Availability

The SQL SHARDS() clause lets you query Oracle supplied objects, such as V$, DBA/USER/ALL views, dictionary tables, and so on, across all shards.

See Querying System Objects Across Shards

You can use the initialization parameter MULTISHARD_QUERY_DATA_CONSISTENCY to set different consistency levels when executing multi-shard queries.

See Specifying Consistency Levels in a Multi-Shard Query

This release enables JSON operators that generate temporary LOBs, large JSON documents (those that require LOB Storage), Spatial Objects, Index and Operators and Persistent LOBs to be used in a sharded environment.

The following interfaces are new or changed as part of this feature.

• Query and DML statements

  Cross shard queries involving LOBs are supported.

  DMLs involving more than one shard are not supported. This behavior is similar to scalar columns.

  DMLs involving a single shard are supported from coordinator.

  Locator selected from a shard can be passed as bind value to the same shard.

• OCILob

  All non-BFILE related OCILob APIs in a sharding environment are supported. with some restrictions.

  On the coordinator, the OCI_ATTR_LOB_REMOTE attribute of a LOB descriptor returns TRUE if the LOB was obtained from a sharded table.

  Restrictions: For APIs that take two locators as input, OCILobAppend, OCILobCompare for example, both of the locators should be obtained from the same shard. If locators are from different shards an error is given.

• DBMS_LOB

  All non-BFILE related DBMS_LOB APIs in a sharding environment are supported, with some restrictions. On the coordinator, DBMS_LOB.isremote returns TRUE if the LOB was obtained from a sharded table.

  Restrictions: For APIs that take two locators as input, DBMS_LOB.append and DBMS_LOB.compare for example, both of the locators should be obtained from the same shard. If the locators are from different shards an error given.

See Creating a Schema for a System-Managed Sharded Database, Creating a Schema for a User-Defined SDB, and Creating a Schema for a Composite SDB for examples of using LOBs in sharded database deployment.

Various enhancements were made to improve the robustness and fault tolerance of shard queries. The query explain plan is enhanced to display information for all shards participating in the query.

See Supported Query Shapes in Proxy Routing and Execution Plans for Proxy Routing for updated information about these topics.

If a shard fails and is unrecoverable, you can replace it using the ADD SHARD -REPLACE command in GDSCTL. You can also use the -replace command option to move a shard to new equipment for any reason.

See Replacing a Shard

Oracle RAC Sharding creates an affinity for table partitions to particular Oracle RAC instances, and routes database requests that specify a partitioning key to the instance that logically holds the corresponding partition. This provides better cache utilization and dramatically reduces block pings across instances. The partitioning key can only be added to the most performance critical requests. Requests that don’t specify the key still work transparently and can be routed to any instance. No changes to the database schema are required to enable this feature.

See Oracle Real Application Clusters Administration and Deployment Guide