Oracle DBA Lesson 15 — Choose Between Shared and Distributed Data

A company that serves several regions still has to decide what stays shared and what gets distributed. RAC, PDB consolidation, and sharding solve different parts of that problem — compute, containers, or the rows themselves.

Oracle DBA Lesson 15 — Choose Between Shared and Distributed Data

Decide the boundary first

The question is which boundary stays shared, and which must be independent:

• Compute — more instances, same files.
• Containers — more applications, same CDB infrastructure.
• Rows — different data, different databases.

RAC, PDBs, and sharding each own one of those. You can combine them. You cannot substitute one for another.

RAC shares the database

Oracle RAC runs multiple instances against one physical database. Each instance has its own SGA and background processes. The datafiles, control files, and redo threads still describe a single database. Sessions can fail over or balance across compute paths. They do not get a private copy of the rows.

V$ views describe the instance you are connected to. GV$ views add INST_ID and return the same view from every instance in the cluster.

SELECT inst_id, instance_name, host_name, status, parallel
FROM   gv$instance
ORDER  BY inst_id;

SELECT dbid, name, open_mode, database_role
FROM   v$database;

Several rows in gv$instance, one DBID in v$database, and PARALLEL = YES is RAC. One row and PARALLEL = NO is a single-instance database — one compute path, still one database.

PDBs share infrastructure

A pluggable database is a container inside a CDB. It is not a second independent database. PDBs have their own users, data, and application identity. They share the CDB’s instance, background processes, undo/redo, and operator surface. Many application boundaries. One infrastructure boundary.

SHOW PDBS

SELECT con_id, name, open_mode, restricted
FROM   v$pdbs
ORDER  BY con_id;

SELECT pdb_id, pdb_name, status
FROM   dba_pdbs
ORDER  BY pdb_id;

CON_ID identifies the container on this CDB. It is not a shard key. It is not a second DBID.

RAC and PDBs can stack

A CDB can run on RAC: multiple instances in front of one CDB, several PDBs inside that CDB. That still does not put customer 10 and customer 20 in independent databases. RAC does not distribute rows. PDBs do not distribute files.

SELECT inst_id, con_id, name, open_mode
FROM   gv$pdbs
ORDER  BY inst_id, con_id;

If the same PDB name appears on more than one INST_ID, that container is open on more than one RAC instance. Extra compute on a shared CDB. Not sharding.

Sharding distributes the rows

Oracle Sharding (Oracle Globally Distributed Database) partitions one logical database across independent physical databases — the shards. Hardware, software, and failure domains are not shared the way RAC shares datafiles. A shard key (customer id, account, region) decides which shard owns the row.

A sharded table names that key. This DDL requires a sharded database and a tablespace set.

CREATE SHARDED TABLE customers (
  cust_id  NUMBER NOT NULL,
  name     VARCHAR2(50),
  CONSTRAINT customers_pk PRIMARY KEY (cust_id)
)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
TABLESPACE SET tsp_set_1;

Customer 10 and its orders stay on shard A. Customer 20 stays on shard B. A request that carries the key goes straight to that database.

Catalog, director, and the hot path

Three names. Three jobs:

• The shard catalog stores topology. Configuration and SDB DDL start here. It is also the query coordinator for work that is not keyed.
• Shard directors (Global Data Services / GSM) are regional listeners. Given a sharding key, they look up the chunk and send the client to the shard that holds it.
• The routing cache on the client (UCP and similar) remembers recent key-to-shard mappings so later keyed calls skip the director.

Two routing methods. Direct routing sends a keyed request to one shard. The request and the result do not pass through a coordinator. Proxy routing handles queries with no key, or queries that need more than one shard. Those go through the catalog’s query coordinator.

The catalog is topology and coordination. It is not on every keyed request. Catalog downtime still hits config, DDL, and multi-shard reports. It does not take down every single-customer call that already knows its shard.

gdsctl config sdb
gdsctl databases
gdsctl config shard

Local work vs cross-shard work

A request for one customer stays on one shard. A company-wide total cannot. Oracle coordinates across shards. That is the cost of splitting the rows.

Sharding buys locality, regional placement, and failure isolation. Cross-shard reports and cross-shard transactions pay in coordination, latency, and operational surface. If most of the workload is unkeyed aggregates, do not shard.

Start with the requirement

• Use RAC when one database needs multiple instances for availability or scale.
• Use PDBs to consolidate application containers on shared infrastructure.
• Use sharding when different rows need independent placement across databases.

Base the choice on access patterns, data locality, failure boundaries, and operational complexity. What must remain shared, and what must be distributed?

Gotchas

• RAC distributes compute around one database. It does not place rows in independent databases.
• PDBs are containers inside a CDB — not separate databases.
• A RAC CDB with several PDBs is still one shared database. That stack is not sharding.
• The shard catalog is topology and coordination, not the hop every keyed request must take.
• Direct routing needs a shard key. No key, or a cross-shard total, is proxy routing through the coordinator.
• Empty sharding config on a single-instance lab means you are not on a sharded database.

Quick quiz

1. What does RAC share, and what does it multiply?

It shares nothing; each instance has its own independent database It shares one database and multiplies instances (compute paths) It shares a shard key and multiplies catalogs It shares PDBs and multiplies CDBs

2. What is a PDB?

An independent database that owns its own RAC cluster A shard that holds one customer’s rows A container inside a CDB that shares infrastructure, not a separate database A Global Service Manager that routes by shard key

3. A request carries customer_id and goes to one shard. Does it have to pass through the shard catalog?

No — keyed (direct) routing goes to the shard; the catalog is not on every request Yes — every SQL statement must open a session on the catalog first Yes — RAC instances only see data the catalog ships them No, because PDBs already isolated the customer

4. When is sharding the architecture to consider?

Whenever you need a second RAC instance for availability Whenever you want to consolidate many applications into one CDB Whenever every report is a company-wide total with no shard key When different rows need independent placement across databases

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro