How to Deploy and Test Oracle 12c R2 Sharding: A Step‑by‑Step Guide

1. Overview of Oracle 12c R2 Sharding

Oracle 12c R2 introduces a sharding technology that separates partitioned tables into independent databases called shard nodes, removing geographic constraints and providing horizontal scalability, fault isolation, and dedicated hardware resources per shard.

Supports horizontal data sharding for massive scale.

Deployable on on‑premise, private cloud, public cloud, or hybrid cloud.

Provides full fault isolation.

Each shard node has independent CPU, memory, and storage.

Auto‑rebalancing and elastic scaling.

Automatic deployment.

2. Sharding Architecture

The shared‑database architecture (sdb) consists of a shard director (GSM), a shard catalog, and one or more shard groups.

Shard catalog stores metadata, coordinates databases, and defines shard table metadata.

Shard director (GSM) routes application requests to the appropriate shard, handles cross‑database failover, and performs load balancing.

Oracle Routing offers two modes:

Session‑based routing – each transaction connects to a single shard.

Cross‑shard routing – used for reporting queries that span multiple shards.

Connection requests can be of two types:

UCP with a shard key – the request is sent directly to the target shard.

Without a shard key – the request is first handled by the catalog, which parses the SQL and routes it.

3. Test Environment Setup

Three virtual machines were used:

shard0 – hosts GSM and the shard catalog.

shard1 – first shard node.

shard2 – second shard node.

Software requirement: Oracle version 12.2.0.0.3 or later.

3.1 Deploy GSM

Set environment variables on shard0:

[oracle@shard0 ~]$ env |grep ORA
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsmhome_1

Unzip GSM.zip and run the graphical installer (screenshots omitted for brevity).

3.2 Create Users and Grant Permissions on the Catalog

ssh shard0
su - oracle
sqlplus / as sysdba
alter user gsmcatuser account unlock;
alter user gsmcatuser identified by passwd_gsmcatuser;
create user mygdsadmin identified by passwd_mygdsadmin;
grant connect, create session to mygdsadmin;
grant gsmadmin_role to mygdsadmin;
grant inherit privileges on user SYS to GSMADMIN_INTERNAL;

3.3 Configure Remote Scheduler

ssh shard0
su - oracle
sqlplus / as sysdba
set echo on
set termout on
set time on
spool /u01/stage/labs/config_remote_scheduler.lst
execute dbms_xdb.sethttpport(8080);
Commit;
@?/rdbms/admin/prvtrsch.plb
exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('welcome');
spool off

3.4 Register Shard Databases

# On shard1
ssh shard1
su - oracle
schagent -stop
schagent -start
schagent -status
echo welcome | schagent -registerdatabase shard0 8080
mkdir /data/oracle/oradata
mkdir /data/oracle/fast_recovery_area

# On shard2 (same steps)
ssh shard2
su - oracle
schagent -stop
schagent -start
schagent -status
echo welcome | schagent -registerdatabase shard0 8080
mkdir /data/oracle/oradata
mkdir /data/oracle/fast_recovery_area

3.5 Configure GSM

ssh shard0
su - oracle
gdsctl
create shardcatalog -database shard0:1521:orcl -chunks 12 -user mygdsadmin/passwd_mygdsadmin -sdb cust_sdb -region region1
add gsm -gsm sharddirector1 -listener 1571 -pwd passwd_gsmcatuser -catalog shard0:1521:orcl -region -trace_level 16
start gsm -gsm sharddirector1
set _event 17
modify catalog -agent_password welcome
add credential -credential oracle_cred -osaccount oracle -ospassword oracle
set gsm -gsm sharddirector1
connect mygdsadmin/passwd_mygdsadmin
add shardgroup -shardgroup shgrp1 -deploy_as primary -region region1
add invitednode shard1
create shard -shardgroup shgrp1 -destination shard1 -credential oracle_cred
add invitednode shard2
create shard -shardgroup shgrp2 -destination shard2 -credential oracle_cred
deploy

At this point a functional sdb test environment was built. No disaster‑recovery configuration was performed due to limited resources; Oracle offers OGG and ADG for shard node HA.

4. Sharding Use‑Case Limitations

Targeted at OLTP workloads.

Performance gains require a proper shard key.

~80% of transactions should operate on a single shard.

Cross‑shard operations are not fully supported in the current release.

5. Test Model and Results

The test model used the Customer Center business, which stores CRM three‑party information and subscription data. Core tables are accessed via a bbb_id → uuu_id mapping, then by ccc_id / aaa_id for customer and account details. Most user‑initiated operations stay within a single shard.

Single‑shard queries – speed improvement was marginal because the uuu_id column already had an index.

Duplicate‑table queries – no noticeable performance gain as they rely on materialized views in the catalog.

Cross‑shard queries – Oracle 12c R2 does not support IN or OR conditions across shards, limiting practical use.

6. Issues Encountered and Resolutions

Environment deployment requires Oracle 12.2.0.0.3+; earlier versions lack needed features.

GSM error messages are not user‑friendly, making troubleshooting difficult.

Data import: pre‑12.2 versions raise ORA‑600 errors when importing duplicate tables; no PSU fix available.

Using CREATE TABLE AS SELECT over dblink fails with unsupported operation errors.

Cross‑shard queries with IN / OR are unsupported; a feature request has been submitted to Oracle.

The catalog becomes a bottleneck under concurrent connections; multiple catalog instances are recommended.

All tables within the same schema must maintain primary‑foreign key relationships across shards.