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:
• 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 22.214.171.124 release, all shards and shard catalog must be Non-CDB databases.
b) Specify the topology layout using:
• CREATE SHARDCATALOG
• ADD GSM
• START GSM
• ADD CREDENTIAL (IF USING ‘CREATE SHARD’)
• ADD SHARDGROUP
• ADD INVITEDNOTE
• CREATE SHARD (OR ADD SHARD) (for each shard)
c) Run the “Deploy” command and add global service to access any shard in the SDB:
• ADD SERVICE
Oracle Sharding supports two deployment methods.
- 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.
- 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.