Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database is hosted on dedicated server with its own local resources - CPU, memory, flash, or disk. Each database in such configuration is called a shard. Those collection of shards make up a single logical database called as Sharding Database. All of the shards together make up a single logical database, which is referred to as a sharded database (SDB). Horizontal partitioning involves splitting a database table across shards so that each shard contains the table with the same columns but a different subset of rows. A table split up in this manner is also known as a sharded table. These independent database is hosted on dedicated servers with its own resources as CPU, Memory and disk.
Database Sharding has been introduced with the Oracle Database 12c R2.
Note: In Oracle Database 12c Release 1 (12.2.0.1) all shards and shard catalog must be non-CDB databases.
In Oracle Database 12c Release 2, a single table can be partitioned up to 1000 shards you can deploy up to 5 shard directors in a given region with following benefits:
- Linear scalability
- Workload distribution
- Users with isolation
Environment:
3 Virtual machines with OS Oracle Enterprise Linux 6.9 installed.
SHARD-DIRECTOR
Host: sharddirector
IP: 192.168.100.20
Role: Shard Director
SID: SHARDDIR
Software: Oracle database 12.2.0.1, Oracle Database 12c Release 2 Global Service Manager
SHARD-NODES:
| HOST | IP | ROLE | SID |
|---|---|---|---|
| shard1 | 192.168.100.21 | 1st Shard | sh1 |
| shard2 | 192.168.100.22 | 2nd Shard | sh2 |
Software: Oracle database 12.2.0.1
Download Oracle database 12.2.0.1 and Oracle Database 12.2.0.1 GSM software from URL below:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html
Configure host configuration /etc/hosts on all the machines(director and 2 shard nodes)
192.168.100.20 sharddirector.localdomain sharddirector
192.168.100.21 shard1.localdomain shard1
192.168.100.22 shard2.localdomain shard2
Installing Oracle database software, software only option has been chosen on all the hosts. Where as the gms software is required on the shard director only.
Install Oracle Database on shard director.
Execute dbca.
Select Create a database, and click Next.
Select Advanced configuration, and click Next.
Select the Oracle Single Instance database, the General Purpose or Transaction Processing template, and click Next.
Enter the Global Database name and SID for the shard catalog. Uncheck the Create as Container Database option, and click Next.
On the Storage Option page, select the Use following for the database storage attributes option, select File System, select the Use Oracle-Managed Files (OMF) option, and click Next.
Select Specify Fast Recovery Area, select Enable archiving, and click Next.
Select Create a new listener, set the listener name and port number, and click Next.
Simply click on Next on the Data Vault Config Option page.
On Memory tab, select Use Automatic Shared Memory Management.
On Character sets tab, select Use Unicode (AL32UTF8), and click Next
Uncheck the Configure Enterprise Manager (EM) database express option, and click Next.
Enter the passwords, and click Next.
Select Create database, and click Next.
Click Finish.
Database Creation on progress ##########.
Click on Close once database creation completes.
Login to shard director server and execute following SQL commands.
sqlplus /nolog
conn / as sysdba
Modify following initialization parameters.
alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;
alter system set open_links=16 scope=spfile;
alter system set open_links_per_instance=16 scope=spfile;
Unlock gsmcatuser, create gsm_admin user and grant required privileges.
alter user gsmcatuser identified by oracle_4U account unlock;
create user gsm_admin identified by oracle_4U;
grant connect,create session,gsmadmin_role to gsm_admin;
grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
GSM software is already installed on shard director, Create gsm environment to access gsm.
cat > gsm.env <
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/12.2.0/gsm
export PATH=${ORACLE_BASE}/product/12.2.0/gsm/bin:$PATH
EOF
chmod 755 gsm.env
Execute the gsm.env file to set the gsm environments
. gsm.env
Execute gdsctl to get the gsm command line tool
gdsctl
Create shard catalog database on Oracle Shard Director.
create shardcatalog -database sharddirector:1521/sharddir -region dc1 -user gsm_admin/oracle_4U -agent_port 7777 -agent_password oracle_4U -chunks 120 -force
Create and start Oracle Shard Director
add gsm -gsm shdd -catalog sharddirector:1521/sharddir -region dc1 -pwd oracle_4U
add gsm -gsm shdd -catalog sharddirector:1521/sharddir -region dc1 -pwd oracle_4U
start gsm -gsm shdd
Create credential for the OS username on all the shard hosts.
add credential -credential mycred -osaccount oracle -ospassword oracle
Create oradata and fast_recovery_area on all the shard nodes
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fast_recovery_area
Add following lines of configuration on glogin file
cd $ORACLE_HOME/sqlplus/admin
cat >> glogin.sql
set sqlprompt "_user '@' _connect_identifier > "
define _editor=vi
Register scheduler agent on both shard hosts. Make sure that shard director is reachable before configuring scheduler agent.
schagent -registerdatabase sharddirector 7777
Verify the scheduler agent is started. If not start scheduler agent.
schagent -status
schagent -start
Create Shard group on shard director
add shardgroup -shardgroup sgroup1 -region dc1 -deploy_as primary
add invitednode sharddirector
add invitednode shard1
add invitednode shard2
Create shard metadata.
create shard -shardgroup sgroup1 -destination shard1 -credential mycred -dbtemplatefile /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
create shard -shardgroup sgroup1 -destination shard2 -credential mycred -dbtemplatefile /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
Database name will be sh1, sh2 and shn for n number of nodes.
Execute deploy to create database via scheduler agents.
deploy;
Once deploy is completed it looks as below:
Execute command below on the gds control to view the configurations.
config
Create and start services.
add service -service srvshard -role primary;
start service -service srvshard
Verify the service is started on shard nodes.
lsnrctl status
Now create shard and normal tablespace using the oracle shard catalog.
sqlplus /nolog
conn / as sysdba
Enable shard ddl for the session.
alter session enable shard ddl;
Creating Shard tablespace.
create tablespace set tbs_shard in shardspace shardspaceora using template
(datafile size 128m extent management local segment space management auto);
Create non shard normal tablespace.
create tablespace tbs_nshard
datafile
size 128m
autoextend on
next 100m
maxsize 512M;
Verify the tablespace has been created on all the hosts with SQL command below.
select tablespace_name from dba_tablespaces where tablespace_name in ('TBS_SHARD','TBS_NSHARD');
Create user.
create user usr_shard
identified by oracle_4U;
Grant required privileges.
grant connect, resource to usr_shard;
grant all privileges to usr_shard;
grant gsmadmin_role to usr_shard;
grant select_catalog_role to usr_shard;
grant connect, resource to usr_shard;
grant dba to usr_shard;
grant execute on dbms_crypto to usr_shard;
Connect to the shard user.
connect usr_shard/oracle_4U
Enable shard ddl for the session.
alter session enable shard ddl;
Create shared table. Shared table will distribute table data among the shard databases.
CREATE SHARDED TABLE Customers
(
CustId VARCHAR2(60) NOT NULL,
FirstName VARCHAR2(60),
LastName VARCHAR2(60),
Class VARCHAR2(10),
Geo VARCHAR2(8),
CustProfile VARCHAR2(4000),
CONSTRAINT pk_customers PRIMARY KEY (CustId),
CONSTRAINT json_customers CHECK (CustProfile IS JSON)
) TABLESPACE SET tbs_shard
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
Create duplicate table. Duplicate table will be duplicate on all the shard database.
CREATE DUPLICATED TABLE Products
(
ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Name VARCHAR2(128),
DescrUri VARCHAR2(128),
LastPrice NUMBER(19,4)
) TABLESPACE TBS_NSHARD;
Login to the shard director database and insert data.
sqlplus usr_shard
insert into customers values(1,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(2,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(3,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(4,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(5,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(6,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(7,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(8,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(9,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(10,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into customers values(11,'6sh60jxm','6sh60jxm','6sh60jxm','6sh60j','{ "name":"6sh60jxm" }');
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
insert into products(name, DescrUri,lastPrice) values('A','B',1);
Verify the number of rows on the sharddirector and the shard1 and shard2 respectively. We can notice that Shared table have partitioned the rows among the shard nodes where as the duplicate table is copied among the shard nodes.
Execute following SQL on all the hosts.
select count(*) from customers;
select count(*) from products;
Additional Command for administration
To list all the DDLs executed
show ddl;
To view the shard configuration
show ddl;
To view the shard configuration
config shard
config shard -shard sh1
config shard -shard sh1
config shard -shard sh2
List all the objects on user schema
col object_name format a30
select object_name, object_type from user_objects;
Connect to the shard catalog and verify the chunk has been equally distributed
set echo off
SELECT a.name Shard, COUNT(b.chunk_number) Number_of_Chunks
FROM gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b
WHERE a.database_num=b.database_num
GROUP BY a.name
ORDER BY a.name;
List all the destination objects in the database pointing to remote agents
select DESTINATION_NAME from ALL_SCHEDULER_EXTERNAL_DESTS;
Recovery of shard node once the shard get issue
recover shard -shard sh1
recover shard -shard sh2
Recovery of shard node once the shard get issue
recover shard -shard sh1
recover shard -shard sh2