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.

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.

When the Oracle Sharding feature was introduced in Oracle Database 12c Release 2, the focus was on internet scale applications that require linear scalability and fault isolation. These applications deal with 100s of millions or billions of users, massive concurrent user base and extremely large databases. With Oracle 18c, Oracle Sharding has been made more inclusive to applications and various other use cases by increasing its flexibility and strategies.

Here is the list of the salient Oracle Database 18c Sharding features:

1. User-defined Sharding
2. PDB Sharding
3. RAC Sharding
4. JSON & Spatial capabilities and Multi-shard query enhancements
5. Midtier Sharding

I plan to cover the details of these Sharding features in this blog post series. In the first post, we will discuss the new user-defined sharding method that aids in global data distribution and hybrid cloud deployment.

#1 User-defined Sharding:

In Oracle Database 12c, we introduced two sharding methods – system-managed sharding and composite sharding. 

System-managed sharding is based on partitioning by consistent hash. This sharding method randomly and evenly distributes data across shards and automatically redistributes it when shards are added to or removed from the sharded database. Consistent Hash is good for application where there are millions and even billions of values of the sharding key and it is not practical to manage them individually. This method enables linear scalability of transactions, concurrent users and the database capacity.

With composite sharding method, data is first partitioned by list or range (super_sharding_key) and then further partitioned by consistent hash (sharding_key). The two levels of sharding make it possible to map data to a set of shards, and then automatically maintain balanced distribution of data across that set of shards. Composite sharding is ideal for global data distribution where shards are placed in each geography and within a given geography data is uniformly distributed. Another use case for this method is – a set of powerful shards dedicated to Gold class users and set of low powered shards for Silver class users. Within each class of users, data is uniformly distributed.

But what if there are much less distinct values of the sharding key, maybe thousands, and the customer wants tighter control in mapping data to shards. For such cases, in Oracle Database 18c, we introduced user-defined sharding – a sharding method which is based on partitioning by RANGE or LIST and allows the user to explicitly specify mapping of data to shards. User-defined sharding is ideal for performance, regulatory, or other reasons when the user needs to store related data in the same shard and have full control on moving data between shards.

User-controlled data distribution provides:

• Regulatory compliance – data remains in country of origin
• Hybrid clouds – some shards are on-premises and other shards in the cloud
• Cloud bursting – ability to move data from on-premises to the cloud during peak seasons
• Increased visibility into planned maintenance – when a shard needs to be brought down for maintenance, the administrator knows exactly which data will not be available
• Each shard can have different hardware and HA configuration
• More efficient range queries

With user-defined sharding, the user owns the control to maintain balanced data distribution.

The term shardspace is used to describe a shard or a set of fully replicated shards that is used to store a part of an SDB partitioned by user-defined subset of key values. The shardspaces comprising an SDB may be in the same data center or in different data centers. With user-defined sharding, you can scale by adding additional shardspaces – extend the range or list of sharding_keys. Each shardspace can have different location, platform and replication topology.

Figure 1.  Oracle Sharded Database with User-defined sharding

High-Level Steps for Creating a User-defined Sharded Schema

• Create Shardspaces for each shard and all of its replicas
• Manually create a tablespace for each partition in the primary shard of a given shardspace
• Specify partitioning scheme and column that is the sharding key for the root table
  • The DBA explicitly maps partitions to the tablespaces
  • Child and grandchild tables all inherit the same partitioning scheme using the partition by reference clause
• Specify which tables are duplicated on all shards (common reference data)

Here are the GDSCTL commands to create a sharded table for data sovereignty using the user-defined sharding method. In this example there are three shardspaces: NA_space, EU_space and ASIA_space.

ADD SHARDSPACE –SHARDSPACE NA_space

ADD SHARDSPACE –SHARDSPACE EU_space

ADD SHARDSPACE –SHARDSPACE ASIA_space

CREATE SHARD -SHARDSPACE NA_space -DEPLOY_AS primary  -DESTINATION host01 -CREDENTIAL oracle_cred -REGION NA

CREATE SHARD -SHARDSPACE EU_space -DEPLOY_AS primary -DESTINATION host02 -CREDENTIAL oracle_cred -REGION EU

CREATE SHARD -SHARDSPACE ASIA_space -DEPLOY_AS primary -DESTINATION host03 -CREDENTIAL oracle_cred -REGION ASIA

DEPLOY

Figure 2.  Dedicated partitions for different user-defined shardspaces

Now create the shardspace specific tablespaces using SQL.

CREATE TABLESPACE NA_tbs IN SHARDSPACE NA_space;

CREATE TABLESPACE EU_tbs IN SHARDSPACE EU_space;

CREATE TABLESPACE ASIA_tbs IN SHARDSPACE ASIA_space;

Once the shards and the tablespaces are created, you can go ahead and create the sharded tables using SQL as shown below.

CREATE SHARDED TABLE accounts

( id NUMBER, account_number NUMBER , customer_id NUMBER

, branch_id NUMBER , geo VARCHAR2(20), status VARCHAR2(1)
)
PARTITION BY LIST (geo)
( PARTITION p_NA VALUES (‘NA’) TABLESPACE NA_tbs

, PARTITION p_EU VALUES (‘EU’) TABLESPACE EU_tbs

, PARTITION p_ASIA VALUES (‘ASIA’) TABLESPACE ASIA_tbs
);

It is as simple as that. This blog covered how user-defined sharding helps prepare organizations to honor data sovereignty requirements by preventing access to customer data outside of their home legal jurisdiction.

For more information on user-defined sharding, please peruse the following documentation links:

Sharding Methods:

https://docs.oracle.com/en/database/oracle/oracle-database/18/shard/sharding-methods.html#GUID-3B07D91C-CEAA-4170-A94B-ACF47BEE617B

SDB Deployment:

https://docs.oracle.com/en/database/oracle/oracle-database/18/shard/sharding-deployment.html#GUID-F99B8742-4089-4E77-87D4-4691EA932207

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

Data Pump Import (impdp)  has a new capability starting from Oracle Database 12.2 to filter appropriate records for loading into individual shards of an SDB. It supports all methods of Sharding – namely System Managed, Composite and User-defined Sharding.

If you have a stand-alone database, then you need to export the table data using the data_options=group_partition_table_data. This unloads all table data at once instead of unloading each table partition independently.

Here is an example that walks you through the steps:

On the Source Database:

SQL>grant DATAPUMP_EXP_FULL_DATABASE to  testuser1;

SQL>create or replace directory def_dir1 as ‘/tmp’

SQL>grant read, write on directory def_dir1 to testuser1;

On the non-sharded database, export the table data using “data_options=group_partition_table_data” as shown below. This unloads all table data at once instead of doing each table partition independently. Data Pump exports all data into the dump file.

$ expdp testuser1/testuser1@src_db                   \

directory=def_dir1                                         \

schemas=testuser1                                         \

dumpfile=no_part%u                                     \

logfile=tkdpshrd_exp.log                              \

data_options=group_partition_table_data \

parallel=4

Pre-reqs (on the Target Sharded Database)

On the Shard Catalog

Create the Data Pump directory objects (on shardcat and all shards) and grant appropriate role to the user as shown below.

SQL>alter session enable shard ddl

SQL>create or replace directory def_dir1 as ‘/tmp’

SQL>grant read, write on directory def_dir1 TO <user>;

SQL>grant DATAPUMP_IMP_FULL_DATABASE  to  <user>;

<create the sharded tables that match the structure of the source tables>

<Create other schema objects>

On each of the shards:

Execute the import specifying CONTENT=DATA_ONLY.  Because the tables already exist in the shard, attempts to recreate them will fail. The Data Pump Import detects that you are importing into a shard and only load rows that belong to that shard.  You need to run the import command on each shard.

impdp testuser1/testuser1@shard1          \

directory=def_dir1                                 \

dumpfile=no_part%u                             \

logfile=tkdpshrd_imp_01.log                \

table_exists_action=append                 \

content=data_only                                \

parallel=4

At the time of import, the Data Pump filters out rows that do not belong in the given shard. Data Pump passes key values for each row to a internal GSM function that tells  whether the row belongs in that shard.

Highlights:

@1.52 – Introduction to Oracle Sharding

@4:08 – Benefits of Sharding

@6:28 – Architecture and Key Features

@21:02 – Data Modeling and Application Considerations for a Sharded Database (SDB)

@29.51 –  Sharded Schema Creation

@36:14 – Direct Routing against an SDB

@41:23 – Proxy Routing for reporting workload

@48:35 – Results of Oracle Sharding Scalability Characterization study