Oracle Sharding Architecture, Shard Catalog, Shard Directors, and Sharded Tables

Learning outcomes

ServiceHub grows from one country to many regions. Keeping every tenant in one database means one storage/CPU/maintenance failure domain, while manually creating unrelated regional databases makes schema drift, routing and global reporting somebody else's custom problem. Oracle's globally distributed database architecture deliberately places different row sets in different independent Oracle databases—shards—while a shard catalog and shard directors maintain topology/routing metadata so applications can still experience one logical database.

1. A shard is a database, not a partition

A shard is an independent Oracle database that stores a subset of the distributed dataset. A sharded table has the same logical columns across shards but different row partitions/chunks on different shard databases. Ordinary table partitioning keeps every partition inside one database and therefore one database failure/administration domain; sharding spreads data across independent databases that share no CPU, memory or storage.

2. The shard catalog is control plane plus query coordinator

The shard catalog stores distributed topology metadata and a gold copy of propagated schema. It also holds the primary copy of duplicated tables and can coordinate queries that touch multiple shards or arrive without a routable sharding key. Key-routed single-shard transactions can continue during a catalog outage because clients/directors cache routing metadata, while topology changes, duplicated-table updates and multi-shard queries depend on catalog availability.

3. Shard directors are routing listeners, not data stores

A shard director is the Oracle Global Service Manager (GSM) role used by distributed databases. It maintains a current topology map and routes global-service connections based on data location, region, role, availability and load. Production designs use multiple shard directors per region for availability rather than making one listener/router a new single point of failure.

GDSCTL> add gsm -gsm gsm_baku_1   -listener 1522   -catalog catalog-host:1521/shardcatpdb   -region bakuGDSCTL> start gsm -gsm gsm_baku_1GDSCTL> config gsm -gsm gsm_baku_1

The example is topology-dependent; it is not executed in the mandatory one-Free-instance lab.

4. Sharded and duplicated tables solve different locality problems

A sharded table horizontally distributes its rows. A duplicated table is copied to all shards so small read-mostly reference data can join locally with sharded rows. The catalog contains the primary copy of duplicated data and replicates it to shards. 26ai also adds synchronous duplicated-table capability for workloads that need on-commit synchronization rather than refresh-lag semantics.

ALTER SESSION ENABLE SHARD DDL;CREATE TABLESPACE SET servicehub_tsUSING TEMPLATE (  DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M);CREATE SHARDED TABLE servicehub_orders (  tenant_id      NUMBER NOT NULL,  work_order_id  NUMBER NOT NULL,  status_code    VARCHAR2(12) NOT NULL,  CONSTRAINT servicehub_orders_pk    PRIMARY KEY(tenant_id,work_order_id))PARTITION BY CONSISTENT HASH (tenant_id)PARTITIONS AUTOTABLESPACE SET servicehub_ts;CREATE DUPLICATED TABLE servicehub_status_codes (  status_code VARCHAR2(12) PRIMARY KEY,  display_name VARCHAR2(60) NOT NULL);

The sharding key appears in the root-table key so a tenant's table family can remain colocated.

5. DDL is catalog-coordinated and observable

GDSCTL> configGDSCTL> config sdbGDSCTL> config shardGDSCTL> config shardgroupGDSCTL> show ddl

A healthy config shard should show deployed/online shards. show ddl reveals propagated schema changes and failed shards. SQL*Plus successfully returning from a catalog DDL is not sufficient proof that every shard applied it; GDSCTL propagation state is the distributed evidence.

6. Deliberate failure on ordinary Free: shard-specific storage DDL

CREATE TABLESPACE SET sh26_bad_tsUSING TEMPLATE (  DATAFILE SIZE 10M);-- Expected in a non-shard database:-- ORA-02508: attempted to create a shard tablespace in a non-shard database

The database rejects sharding storage metadata because no sharding environment was deployed. The safe repair is not to fake SHARD DDL state; either deploy a correctly supported topology or use the local simulation below.

7. Free-safe topology simulation

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh26_sim_orders PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh26_sim_shards PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh26_sim_shards (  shard_name VARCHAR2(30) PRIMARY KEY,  region_name VARCHAR2(30) NOT NULL,  role_name VARCHAR2(20) NOT NULL,  availability VARCHAR2(12) NOT NULL,  CONSTRAINT sh26_sim_role_ck    CHECK (role_name IN ('PRIMARY','STANDBY')),  CONSTRAINT sh26_sim_avail_ck    CHECK (availability IN ('ONLINE','OFFLINE')));INSERT INTO sh26_sim_shards VALUES('SHARD_1','BAKU','PRIMARY','ONLINE');INSERT INTO sh26_sim_shards VALUES('SHARD_2','EU','PRIMARY','ONLINE');INSERT INTO sh26_sim_shards VALUES('SHARD_3','ME','PRIMARY','ONLINE');CREATE TABLE sh26_sim_orders (  tenant_id NUMBER NOT NULL,  work_order_id NUMBER NOT NULL,  assigned_shard VARCHAR2(30) NOT NULL    REFERENCES sh26_sim_shards(shard_name),  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL,  PRIMARY KEY(tenant_id,work_order_id));COMMIT;

This is a teaching model only. The three rows represent three failure domains but remain physically inside one Free database; the simulation must never be described as actual sharding.

8. Populate a deterministic placement map

INSERT INTO sh26_sim_ordersSELECT  tenant_id,  100000 + tenant_id AS work_order_id,  CASE MOD(tenant_id-1,3)    WHEN 0 THEN 'SHARD_1'    WHEN 1 THEN 'SHARD_2'    ELSE 'SHARD_3'  END AS assigned_shard,  'OPEN',  100 + tenant_idFROM (  SELECT LEVEL tenant_id  FROM dual  CONNECT BY LEVEL <= 12);COMMIT;SELECT assigned_shard,COUNT(*) AS rows_per_simulated_shardFROM sh26_sim_ordersGROUP BY assigned_shardORDER BY assigned_shard;

Expected: four rows in each simulated shard. This teaches visible row placement, not Oracle's real consistent-hash chunk algorithm.

9. Simulate one shard failure

UPDATE sh26_sim_shardsSET availability='OFFLINE'WHERE shard_name='SHARD_2';COMMIT;SELECT  o.assigned_shard,  COUNT(*) AS unavailable_rowsFROM sh26_sim_orders oJOIN sh26_sim_shards s  ON s.shard_name=o.assigned_shardWHERE s.availability='OFFLINE'GROUP BY o.assigned_shard;

The simulation shows the intended mental model: failure affects the rows owned by that shard, rather than stopping unrelated shard data. A real replicated topology may fail over the affected shard to a Data Guard/Raft replica; this table does not simulate failover mechanics.

10. Production deployment prerequisites

• Plan host/network reachability among catalog, shard directors and shards; director/listener/ONS ports must be reachable as documented.
• Shard PDBs must use supported character sets and COMPATIBLE >= 12.2.0.
• System-managed chunk movement relies on Oracle Managed Files; configure DB_CREATE_FILE_DEST appropriately.
• Use dedicated server connections where the current deployment guide recommends them.
• Validate the topology before deployment because much catalog metadata becomes hard to change afterward.

11. Cleanup

DROP TABLE sh26_sim_orders PURGE;DROP TABLE sh26_sim_shards PURGE;

12. Production judgment

Choose sharding because the data/workload needs independent database failure domains, geographic placement or scale—not because a table is large. Keep a catalog HA plan, multiple shard directors, routable services and distributed DDL monitoring. Treat duplicated tables as a locality optimization with consistency/refresh semantics, not a substitute for sharding-key design.

Current 26ai licensing marks Globally Distributed Database available in Free up to three shards, but the traditional shard-catalog guide still specifies an EE PDB; production/offering design must reconcile the exact deployment path with the current license contract. No mandatory lab creates a real shard topology. Lesson 2 now chooses the key that decides whether most ServiceHub requests stay on one shard or become expensive fan-out work.

Authoritative references

• Architecture and Components — catalog/director/shard/global-service model
• Create the Shard Catalog Database — catalog PDB/edition boundary
• Configure the Distributed Database Topology — GDSCTL deployment/verification
• DDL Processing in a Distributed Database — show ddl/config shard evidence
• Licensing Information — Free/shard/Raft limits and offering matrix