Monthly Archives: August 2018

Oracle Sharding 18c Blog Post Series – #3: Oracle RAC Sharding

Leave a reply

In the first post of the Oracle Database 18c Sharding features blog post series, I covered User-defined sharding method and discussed in detail on how on it can be leveraged for global data distribution and hybrid cloud deployment. In the second post, I  discussed the PDB Sharding feature with which we can distribute data with Sharding and yet consolidate databases with Multitenant – a best of both worlds. In this post, I will discuss the Oracle RAC Sharding feature that allows you to virtually shard data across instances of an Oracle RAC database.

#3:  Oracle RAC Sharding

Oracle RAC Sharding is a feature that logically establishes affinity for table partitions to  RAC instances. This reduces block pings across instances while yielding better cache affinity.

RACsharding

Figure 1.  High-performance for shard-aware RAC applications with RAC Sharding

This feature which is applicable to non-sharded RAC databases, takes advantage of direct routing API of Oracle Sharding – to specify the partitioning key as part of the check-out of a given connection from the connection pool. The application must use integrated Oracle clients such as Oracle Universal Connection Pool (UCP), Oracle Call Interface (OCI) Session Pool, Oracle Data Provider for .NET (ODP.NET) Connection Pool etc. Apache Tomcat, JBoss, IBM WebSphere and Oracle WebLogic can use UCP support for sharding. PHP, Python, Perl, and Node.js can use OCI support.

Requests that specify partitioning key are routed to the instance that logically holds the corresponding partition while the requests that don’t specify partitioning key still work transparently.

This capability empowers Oracle RAC with the performance and linear scalability of an Oracle Sharded Database with minimal application changes. The application just specifies the sharding key (as part of the connection check-out) for the most performance critical operations.  Here is an example on how to use Oracle Universal Connection Pooling (UCP) Sharding API for RAC Sharding feature.

OracleShardingKey keyMaryEmail =
pds.createShardingKeyBuilder()
.subkey(“mary.smith@xyz.com”, OracleType.VARCHAR2)
.build();

Connection connection =
pds.createConnectionBuilder()
.shardingKey(keyMaryEmail)
.build();

The RAC Sharding feature is enabled on the RAC database by executing the following:

alter system enable affinity <TableName>;

No changes to the database schema are required. There is no requirement to deploy Sharding infrastructure as well.

Note: Application changes which are required to supply the partition key, don’t have to affect all modules of the application – can be done only for the most performance intensive operations. Access via partitioning  key will go to the RAC instance that holds the logical subset of the data. All instances can still access all the data. Requests that don’t provide the partitioning key will be routed based on the default load balancing policy. 

For more information on RAC Sharding, take a look at the following documentation Links:

Oracle RAC Sharding 

https://docs.oracle.com/en/database/oracle/oracle-database/18/racad/workload-management-with-dynamic-database-services.html#GUID-1100990E-18BD-4C92-A0C7-3C08C40AAD14

UCP APIs for Database Sharding Support

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjucp/ucp-sharding-api.html#GUID-3B91E8F7-AE45-4CC8-B87F-6F4FC9EEA001

JDBC Support for Database Sharding

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjdbc/database-sharding.html#GUID-1D7795CA-79DC-452B-9FCC-0EF430F87461

For in-depth collateral on Sharding, visit the Oracle Sharding OTN portal – https://www.oracle.com/goto/sharding and do follow me on Twitter  – https://twitter.com/nageshbattula

In the next blog post, I will discuss the Multi-shard query enhancements in Oracle Sharding 18c.

Oracle Sharding 18c Blog Post Series – #2: PDB Sharding

1 Reply

In the first of the Oracle Database 18c Sharding features blog post series, I covered User-defined sharding method and discussed in detail on how on it can be leveraged for global data distribution and hybrid cloud deployment. In this post, I will discuss the PDB Sharding feature.

#2 PDB Sharding

The multitenant architecture allows an Oracle database to function as a multitenant container database (CDB). A CDB includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.  Read this doc for the background on CDBs.

From Oracle Database 18c onwards, all Oracle databases will be container (CDB) or pluggable (PDB) databases by default. This PDB Sharding feature adds support for the ability to use PDBs as shards or shard catalog databases within the sharding architecture. PDB Sharding capability renders good manageability benefits for a sharded database. For example – consolidation of shards, manage many as one, database upgrades etc.

In Oracle Database 18c, we support shard as a single PDB in a given CDB. In addition to a single shard PDB, a CDB can contain other non-shard PDBs.

 

pdbsharding

Figure 1.  Oracle Sharded Database with pluggable databases as shards

Use ‘add cdb’ and ‘add shard’ GDSCTL commands to add pre-created containers and PDBs to the sharded database.

add cdb -connect <CDB$ROOT_connect_string> -pwd <gsmuser_pwd>

add shard -connect <PDB_connect_string> -shardgroup <sg_name> -pwd <gsmuser_pwd> -cdb <db_unique_name_of_CDB_as_displayed_during_add_cdb>

…

deploy

Note: The ‘add cdb’ command just adds metadata to the catalog for the CDB so that we know the connect string to CDB$ROOT and the GSMUSER password.  The new “-cdb” option on ‘add shard’ tells the shard catalog that the shard is a PDB and that it lives in the CDB specified by -cdb.

For more information on PDB sharding, please peruse the following documentation links:

Using Oracle Multitenant with Oracle Sharding

https://docs.oracle.com/en/database/oracle/oracle-database/18/shard/sharding-deployment.html#GUID-4FFB5665-B925-4DB6-ACDD-807D6E699365

For in-depth collateral on Sharding, visit the Oracle Sharding OTN portal – https://www.oracle.com/goto/sharding and do follow me on Twitter  – https://twitter.com/nageshbattula

In my next blog post, I will discuss RAC Sharding feature – a capability which empowers Oracle RAC with the performance and scalability of a Sharded database, with minimal application changes.