Category Archives: Sharding

AskTOM Office Hours: Oracle Sharding (Mar 26th, 2018 : 8 AM PST)

Exploring Oracle Sharding
AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Join Srinagesh Battula and other members of the Oracle Sharding team to get your questions about sharding in Oracle Database answered.
March 26, 2018 15:00 – 16:00 UTC
UTC 15:00 March 26 2018
US/Pacific 08:00 March 26 2018
US/Eastern 11:00 March 26 2018
Europe/London 16:00 March 26 2018
Asia/Calcutta 20:30 March 26 2018
Asia/Hong_Kong 23:00 March 26 2018
Australia/Sydney 02:00 March 27 2018
It’s the only one you’ll ever need for this Office Hours, taking you back to this page to join the next Q&A session, review upcoming sessions, and check out past sessions.

Visit the Zoom in advance of the session to make sure your browser is properly configured. But note that we will not be sending you the Zoom URL. Instead, you will come back to the AskTOM Office Hours page (URL above) to join the session.

We will take questions via chat (all audio lines will be muted). We will record sessions for later study.

For an audio-only connection, follow these instructions:

Dial (for higher quality, dial a number based on your current location):
US: +1 669 900 6833 or +1 646 558 8656
Meeting ID: 175942660
International numbers available:
Click here for more information on joining a Zoom session by phone.

Oracle Sharding Product Overview

Oracle Sharding is a scale-out relational database architecture where data is horizontally partitioned across multiple discrete databases that share no hardware or software. It provides linear scalability, fault isolation and geographic data distribution for applications designed for a sharded architecture. Checkout this video to learn how Oracle Sharding automates the deployment of sharded databases, supports elastic scaling and automatic rebalancing, direct routing, proxy routing for multi-shard queries. It does all this while rendering strict consistency, full power of SQL, and the proven enterprise qualities of Oracle Database.

AskTOM Office Hours: Oracle Sharding

AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Join me, and other members of the Oracle Sharding team to get your questions about sharding in Oracle Database answered.


When: 2018-02-26   22:00 UTC  – Oracle Sharding Office Hours

Start Times around the world:

UTC 10:00 PM February 26 2018
US/Pacific 02:00 PM February 26 2018
US/Eastern 05:00 PM February 26 2018
Europe/London 10:00 PM February 26 2018
Asia/Calcutta 03:30 AM February 27 2018
Asia/Hong_Kong 06:00 AM February 27 2018
Australia/Sydney 09:00 AM February 27 2018

And here are some general resources for the Office Hours program:

Landing page
Promotional Video

Hope you can join us.

My takeaways from – “High Availability and Sharding with Next Generation Oracle Database” OOW-2017 conference session

In his High Availability (HA)  conference session, Wei Hu, VP of Product Development covered the new Oracle Sharding and HA features of 18c, and how the Oracle Maximum Availability Architecture (MAA) powers the Oracle Autonomous Database Cloud.

Here are my key takeaways from the session:

  • Oracle Autonomous Database Cloud Service relies completely on Oracle Maximum Availability Architecture (MAA) for automated protection and repair for both planned and unplanned downtime:
    • System Failure   – Exadata, RAC, ASM
    • Regional Outage   – Active Data Guard
    • Patches   – RAC Rolling Updates
    • Major Upgrades   – Transient Logical Standby
    • Table Changes   – Online Redefinition
    • User Error   – Flashback
  • Sharding 1.0 (Oracle 12.2) is great for internet-style applications
  • Sharding 2.0 (Oracle 18c) expands the range of use cases. Following are some of the key Sharding features in 18c.
    • User-defined sharding – Partition data across shards by RANGE or LIST. By explicitly mapping the data to the shards, you achieve better control, compliance & application performance. This sharding method supports the geo-distributed, hybrid cloud and cloud bursting use cases.
    • Swim-lanes – Establish mid-tier affinity with shards to improve mid-tier cache locality, effective connection management and for geo- distributed shards – eliminate the chatty midtier-to-shard  connections across regions.
    • Multishard queries – Proxy routing now supports all sharding methods and all query shapes.
    • RAC Sharding – Affinitizes table partitions to  RAC instances. Requests that specify sharding key will be routed to the RAC instance that logically holds the partition. Affinity gives better cache utilization and reduced block pings across instances. Oracle RAC on steroids, I say.
  • Wei showcased the first customer implementation of Oracle Sharding. China Telecom went from setup to go-live in only 3 months.  China Telecom’s rational – “Migration cost is too high, if we go to other data stores…DBAs and Developers are familiar with Oracle Database. Since Oracle has sharding, why don’t use Oracle Sharding?”  Details of China Telecom’s implementation will be covered in my Oracle Sharding conference session.
  • Some of the Active Data Guard 18c Enhancements:
    • Automated Nologging support in Active Data Guard
    • Update-like capabilities on Active Data Guard for  “Mostly Read, Occasional Updates” applications
    • Automatically update password file on Active Data Guard standbys – changing Admin password on primary automatically updates standbys’
    • Standby-first encryption – Encrypt tablespaces on standby first, switchover, then encrypt on the old primary. How cool this is!
    • New Broker “Validate” command – For validation and proactive health checks to automatically detect and fix issues before they happen
  • He also covered Automated backups with Zero Data Loss Recovery Appliance, Online operations, Online patching enhancements and the Zero Downtime upgrade utility and many other capabilities.

Oracle Database 18c HA features are the core building blocks for the reliability of Oracle Autonomous Database cloud. On-premise deployments must deliver the same availability as the Oracle Cloud. Attain this by adopting the same MAA architecture used by the Oracle Autonomous Database Cloud Service.

Are your on-premise database deployments as resilient and reliable as Autonomous Database cloud? Something to ponder.


Oracle Sharding – Licensing

This blog post covers how Oracle Sharding is licensed for On-premises and Oracle Cloud deployments.

Q: How is Oracle Sharding Licensed for On-premises?

A: For a sharded database (SDB) with 3 or fewer primary shards, Oracle Sharding is included with EE (includes Data Guard). No limit on number of standby shards.

For an SDB with more than 3 primary shards, in addition to EE, all shards must be licensed either for Active Data Guard, Oracle GoldenGate or RAC.

So if your licensing agreement covers EE and one of the HA options – Active Data Guard or Oracle GoldenGate or RAC, you can use Oracle Sharding at no additional cost.

Q: How is Oracle Sharding Licensed for Oracle Cloud (PaaS)?

A: With DBCS EE and DBCS EE-High Performance (HP), use is limited to three primary shards. (No limit on number of standby shards)

With DBCS EE-Extreme Performance (EP) and Exadata Cloud Service (ECS), there is no limit on the number of primary shards or standby shards.

Q: What is the Sharding BYOL model for Oracle Cloud (IaaS)?

A: For a sharded database (SDB) with 3 or fewer primary shards, Oracle Sharding is included with EE (includes Data Guard). No limit on number of standby shards. For the 3 primary shards, if Active Data Guard, Oracle GoldenGate or RAC are required for these shards, they ought to be licensed accordingly.

For an SDB with more than 3 primary shards, in addition to EE, all shards must be licensed either for Active Data Guard, Oracle GoldenGate or RAC.

Ref: Oracle Database Licensing Information User Manual, 12c Release 2 (12.2)

Can I build a scalable document store application with an Oracle Sharded Database?

Yes, indeed! Starting with Oracle Database 12c Release, Oracle Database provides support for JSON. It can store, manage, and index JSON documents, and it enables rapid schemaless development. Oracle delivers the schemaless development paradigm that many developers want today, but without compromising on core enterprise capabilities. While many NoSQL systems are now recognizing the need for a tabular, structured format for accessing data, and some are even introducing SQL-like languages, Oracle Database delivers the full power of SQL to JSON document stores today, with its advanced SQL analytic capabilities and scalable parallel SQL infrastructure.

Unlike NoSQL databases, Oracle Database provides sophisticated SQL querying and reporting over JSON documents. This lets you integrate JSON and relational data, joining them in the same query. And because JSON features are integrated into Oracle Database 12c, all of its enterprise features for availability, security, scalability, and manageability are fully supported for JSON data. And now, with the advent of Oracle Sharding which naturally supports Oracle JSON, document-oriented databases can be built very easily. The following table illustrates how simple it is to build a system managed sharded table for a document store application.

( CustId   VARCHAR2(60) NOT NULL,
CustProfile CLOB,
CONSTRAINT pk_customers PRIMARY KEY(CustId),
CONSTRAINT cons_json CHECK (CustProfile IS JSON)

The combination of Oracle JSON and Oracle Sharding renders data model flexibility, developer productivity plus the linear scalability of a sharded database.

I plan to do a detailed blog on using Oracle JSON with Oracle Sharding.

Cookbooks on Oracle Sharding

In this blog, I will give you pointers on how to get started in deploying a sharded database (SDB), create a sample sharded schema and access the SDB with direct and proxy routing. I will also cover how to elastically scale an SDB.
Oracle Sharding provides the capability to automatically deploy the sharded database , which includes both the shards and the replicas.  For the replication, the SDB administrator can select either Data Guard or Active Data Guard or Oracle GoldenGate 12.3 while specifying the topology.
The SDB administrator defines the topology (regions, shard hosts, replication technology etc.) and invokes the DEPLOY command with declarative specification using the GDSCTL command-line interface.
The high-level steps of the deployment of a sharded database include the following:
a) Prerequisites:
• Create a Non-CDB database that hosts the shard catalog
• Install Oracle Database software on shard nodes
• Install shard director (GSM) software on shard director nodes
Note:  For production deployments, it is highly recommended to configure Data Guard for the shard catalog database. In Oracle Database release, all shards and shard catalog must be Non-CDB databases.
b) Specify the topology layout using:
• CREATE SHARD (OR ADD SHARD) (for each shard)
c) Run the “Deploy” command and add global service to access any shard in the SDB:
Oracle Sharding supports two deployment methods.
  1. The first method is with the “CREATE SHARD” command, where the creation of shards and the configuration of the replication setup are automatically done by the Oracle Sharding management tier. The “DEPLOY” command creates the shards. This is done via the DBMS_SCHEDULER package (executed on the shard catalog), which communicates with the scheduler agents on the remote shard hosts. Agents then invoke DBCA and NETCA to create the shards and the local listeners. Once the primary shards are created, the corresponding standby shards are built using the RMAN ‘duplicate’ command. After the primary and standby shards are built, the “DEPLOY” command configures the Data Guard Broker with Fast-Start Failover (FSFO) enabled. The FSFO observers are automatically started on the regional shard director.  If you plan to use CREATE SHARD method, use this cookbook.
  2. The second method is with the “ADD SHARD” command. Many customers have their own database creation standards and they may opt to deploy the SDB using their own pre-created databases. The ADD SHARD based deployment method supports this requirement by simply adding the shards, which are pre-built by the user. If the “ADD SHARD” command is used for deployment, the “DEPLOY” command handles the configuration of the Data Guard, Broker and Fast-start Failover. It also handles the scenario where the user has pre-configured Data Guard for the shard that is being added. You may also want to consider the “ADD SHARD” method, if you are using ASM, RAC or Oracle Restart at the shard-level. If you do plan to use “ADD SHARD” method, use this specific cookbook.
These cookbooks walk you through the entire lifecycle of a sharded database which include the following steps:
  • Creation of shard catalog and shard directors for system managed sharding
  • Specify the metadata and deploy the sharded database
  • Creation of table family using system managed sharding
  • Specify Sharding_Key for session based routing (using SQL*Plus)
  • Observe uniform data distribution by executing Read Write and Read Only workloads on the sharded database using a demo application (using UCP)
  • Execute cross-shard queries
  • Elastically scale the sharded database
Hope this helps in allowing you to get started in playing with Oracle Sharding.