399      阿裏雲  技術社區[雲棲]

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;

最後更新：2017-07-25 16:02:44

  上一篇：  最完整的網狐棋牌-新手零基礎教程 第五節 大廳源碼的編譯和修改教程

  下一篇：  區塊鏈給物聯網帶來的變化