Oracle Sharding

一直以来在关系型数据库当中，MySQL的分库分表被所有企业认可，并广泛的应用于互联网行业及各大电商平台，数据库的中间件产品也是满目狼藉，如代表，官方的MySQL Proxy、商用的有阿里DRDS、开源的有MyCAT、Altas等等。
自从Oracle 12.2发布之后，Oracle数据库也可以实现分库分表，袋鼠云RDS基于Oracle12.2分库分表的功能，在产品上实现一键部署、智能运维、监控告警、备份恢复、性能管控等，下面我们来看看如何部署Oracle Sharding，本次部署不涉及到DG。

1、部署环境，配置好安装Oracle软件所需条件，关闭防火墙

2、在三台主机上安装Oracle Database 12.2.0.1数据库软件，不创建数据库实例（过程略）

3、在master主机上安装Oracle GSM软件，并创建数据库实例scat（过程略）

4、在master节点上准备数据库环境，并创建路由
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;
alter user gsmcatuser identified by gsmcatuser account unlock;
create user mygds identified by mygds;
grant connect, create session, gsmadmin_role to mygds;
grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
alter system set events 'immediate trace name GWM_TRACE level 7';
alter system set event='10798 trace name context forever, level 7' scope=spfile;
startup force

set echo on
set termout on
spool config_remote_scheduler.lst
@?/rdbms/admin/prvtrsch.plb

exec DBMS_XDB.sethttpport(8080);
commit;
exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('welcome');
alter system register;

5、在所有shard节点上启动shardcat agent，并且连接master
schagent -start
schagent -status
echo welcome | schagent -registerdatabase master 8080

6、使用mygds用户创建GSM
gdsctl connect mygds/mygds@master:1521:scat

create shardcatalog -database master:1521:scat -user mygds/mygds -chunks 12 -user mygds/mygds -region dc1, dc2 -sdb scat
add gsm -gsm gsmha -listener 12106 -pwd gsmcatuser -catalog master:1521:scat -region dc1 -trace_level 16

7、继续创建shard
add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region dc1
create shard -shardgroup primary_shardgroup -destination slave1 -osaccount oracle -ospassword oracle
create shard -shardgroup primary_shardgroup -destination slave2 -osaccount oracle -ospassword oracle

8、开始部署sharding数据库实例，并添加服务
deploy
add service -service pri_srv -role primary
start service -service pri_srv

GDSCTL>databases
Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: dc1
Service: "pri_srv" Globally started: Y Started: Y

        Scan: N Enabled: Y Preferred: Y

Registered instances:

 scat%1

Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: dc1
Service: "pri_srv" Globally started: Y Started: Y

        Scan: N Enabled: Y Preferred: Y

Registered instances:

 scat%11

9、创建应用用户、表空间、表
alter session enable shard ddl;
create user app identified by oracle;
grant all privileges to app;
grant connect,resource,dba,select_catalog_role,gsmadmin_role to app;
grant execute on dbms_crypto to app;

CREATE TABLESPACE SET TSP_SET_2 using template (datafile size 100m extent management local segment space management auto );
CREATE TABLESPACE products_tsp_1 datafile size 100m extent management local uniform size 1m;

conn app/oracle
CREATE SHARDED TABLE cust
(
CustId VARCHAR2(60) NOT NULL,
FirstName VARCHAR2(60),
LastName VARCHAR2(60),
CONSTRAINT pk_cust PRIMARY KEY (CustId)
) TABLESPACE SET TSP_SET_2 PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

CREATE SHARDED TABLE Orders
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
constraint pk_orders primary key (CustId, OrderId),
constraint fk_orders_parent foreign key (CustId)
references cust on delete cascade
) partition by reference (fk_orders_parent);

CREATE SHARDED TABLE LineItems
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
ProductId INTEGER NOT NULL,
constraint pk_items primary key (CustId, OrderId, ProductId),
constraint fk_items_parent foreign key (CustId, OrderId) references Orders on delete cascade
) partition by reference (fk_items_parent);

CREATE DUPLICATED TABLE Products
(
ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Name VARCHAR2(128),
DescrUri VARCHAR2(128),
LastPrice NUMBER(19,4)
) TABLESPACE products_tsp_1;

10、验证sharding表和表空间
select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;

set linesize 140
column table_name format a20
column tablespace_name format a20
column partition_name format a20
show parameter db_unique_name
select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_2' order by tablespace_name;