Complete reference - Oracle Sharding 12.2 (Doc ID 2143551.1)

ument Goal Table Of Contents: Introduction What is Sharding? Sharding Vs Non Sharding Benefits of Sharding Types of Sharding System Managed Sharding User Defined Sharding Composite Sharding Complete Reference Document DEMO Solution APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Information in this document applies to any platform. GOAL NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s),Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner. For the purposes of this document, the following fictitious environment is used as an example to describe the procedure: DATABASE: PROD, SH1, SH2, SH3 This Article is intended to detail the features of Sharding which is available in Oracle 12.2. Oracle Sharding is a database scaling technology based on horizontal partitioning of data across multiple databases. Table Of Contents: Introduction What is Sharding? Sharding Vs Non Sharding Benefits of Sharding Types of Sharding System Managed Sharding User Defined Sharding Composite Sharding Complete Reference Document DEMO SOLUTION Introduction: Data is split into multiiple Databases called Shards. Sharding is a Sharded-nothing database architecture since Shards don't share physical resources such as CPU,memory or storage devices. From the DBA perspective, an SDB consists of multiple databases that can be managed either collectively or individually. However from the perspective of an application team an SDB looks like a single database. The number of shards and distribution of data across them are completely transparent to database applications. What is Sharding? Oracle Sharding is a Data Tier Architecture in which Data is horizontally partitioned across different databases. It is scalability and availability feature for custom designed OLTP applications that enable distribution and replication of data across a pool of discrete Oracle databases that share no hardware or software. The pool of database is presented to application as a single logical database. Applications elastically scale any level on any platform by adding database (shards) into pool. Oracle Sharding scaled upto 1000 Shards in current version. In regards to replication Sharding supports Oracle Data Guard and Oracle Golden Gate. Each database in a pool (Shards) runs in dedicated storage and different server. All Shards together makeup the single logical group called Sharded Databases called SDB. Each database in a pool (Shards) contains a table with same column but different subsets of rows called CHUNKS. Sharding based on Oracle Partitioning. Partitions decompose a large table in to small partitions. Sharding keeps each partition into different databases called Shards. Number of partitions decided number of Shards. Sharding uses Global Data Service (GDS) framework for automatic deployment and management. GDS uses GDSCTL utility. The Advantages of GDS is, it provides connection load balancing and role based routing in SDB (Shard Catalog). GSM Manager is a central component of GDS which provides direct routing of client connections to Shards. GSM is called as Shard Director. Shard catalog (SDB) stores the information about Shard table. Additionally it provides centralized schema maintenance and cross shard queries. Sharding Vs Non Sharding shard_vs_nonshard Non Sharding Vs Sharding The diagram outlines the difference between non Sharding with Sharding environment. Database PRODS with table CUSTOMER having six partitions on the non Shard environment. Unlikely in Sharding environment three different database with dedicated hardware sharing partitions of single table CUSTOMER. On Sharding environment shown in the diagram has the table CUSTOMER with 2 partitions referred as CHUNKS. Table load is shard across three different Shards. For application it is a single logical database (SDB). Application/Clients connect to GSM and it internally routes the connection to the respective Shard. Now Shard directly sends the requested data to clients directly. Benefits of Sharding: Extreme Scalability: Sharding eliminates performance bottlenecks and makes it possible to increase performance and capacity of the system by adding additional Shards. Fault Containment: Sharding also eliminates single points of failure (Sharded disks, SAN, clusterware, etc) and provides strong fault isolation: a failure of a shard does not affect availability of the other Shards. Global Data Distribution: Sharding makes it possible to store particular data close to its consumers and satisfy regulatory requirements when data has to be located in a certain jurisdiction. Rolling Upgrades: Applying configuration changes on one shard at a time does not affect other shards and allows administrators to first test the changes on a small subset of data. Simplicity of Cloud Deployment: Since the size of a shard can be arbitrarily small, it makes it easier to deploy an SDB in a cloud consisting of low-end commodity servers with local storage. TYPEs of Sharding: There are three type of Sharding. • System Managed Sharding • User Defined Sharding • Composite Sharding System Managed Sharding: System managed Sharding does not require the user to specify mapping of data to Shards. DATA is automatically distributed across the Shards using partitioning by consistent HASH. User has no control over data. The advantage of this method is, it will avoid HOT spots and provide uniform performance across Shards. User Defined Sharding : User has control over database means user specifies the mapping of data to individual Shards. It is useful in cases where application decides certain data need to be kept in a particular Shard and user have control on moving data between Shards. The advantage of this method is that in case of maintenance planned and unplanned outage of a Shard, the user knows exactly what data is not available. But the disadvantage of this model is user has to keep track of data to maintain the balanced distribution and workload across shards. User defined Sharding uses partitioning by Range or list. User defined Partitioning not available in 12.2.0.1 Beta. Composite Sharding : Composite Sharding is a combination of system managed and user defined Sharding. Data first partitioned by list or range and then further partitioned in to consistent hash. Consistent partition maintains balanced distribution of data across set of Shards. • Combination of system managed and user defined Sharding • Data is partitioned using LIST or RANGE. • Subset of partitions further partitioned using composite Partitioning Following Document contains details on what/why/how to configure Sharding : Prerequisites: 1. Shard Catalog Server, install GDS and Oracle 12.2 Enterprise edition software 2. On all SHARD servers, install Oracle 12.2 Enterprise edition software with ADG license on standby servers. Introduction to Oracle Sharding.pdf DEMO: Contains information about Prerequisites, Configure remote scheduler agent, Create GSM and create sharding and DEPLY with Data Guard replicated environment. DEMO - Deploy Sharding with Data Guard