Oracle having，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

Oracle having？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

oracle using，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;

oracle drop table，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;