本文详细介绍Oracle12C Sharding分片数据库的部署步骤，从环境搭建到服务启动，再到数据分片测试，全面覆盖了Sharding数据库的实施流程。通过实际案例演示如何在多节点环境中实现数据的高效分片存储与查询。
Oracle 12C Sharding分片数据库
环境简介
操作系统：CentOS 7.5
主机：sdb1、sdb2、sdb3
软件：Oracle Database 12c Release 2+OracleDatabase 12c Release 2 Global Service Manager (GSM/GDS)
sdb1为catalog数据库存储shard database的元数据、通过gds软件实现shard节点数据库的自动部署
sdb2、sdb3用于存储各个节点分片数据
部署过程
一、部署前准备
1、在所有节点编辑hosts文件用于ip解析
vi /etc/hosts
172.16.4.21 sdb1
172.16.4.22 sdb2
172.16.4.23 sdb3
2、在所有节点安装12C数据库软件（不创建DB）
备注：在所有节点确保执行root.sh
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/12.2.0/db_1/root.sh
二、安装Oracle Database 12c Release 2 Global Service Manager (GSM/GDS)
在主节点sdb1上进行
2.1 解压GSM软件
unzip linuxx64_12201_gsm.zip
2.2 oracle用户下进行安装
./runInstaller
2.3 GDS安装过程与oracle software类似
三、创建Shard Catalog Database
在主节点dbca创建shard数据库实例,这里需要注意的是选择非CDB选项，字符集使用UTF8（zhs16gbk不支持），启用OMF,归档以及快闪区
注意这里不要勾选cdb
创建数据库
最后通过netca创建监听
四、设置OracleSharding Manage和路由层
4.1-4.1都是在主节点操作
4.1 解锁用户GSMCATUSER
SQL> alter user gsmcatuser identified by oracle account unlock;
备注：创建gsmcatuser用户目的是可以让shard director通过此用户连接到shard catalog数据库
4.2 创建管理用户gsmadmin
SQL> create user gsmadmin identified by oracle;
SQL> grant connect, create session, gsmadmin_role to gsmadmin;
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracle');
--->设置scheduler agent连接到主数据库的口令
备注：
1、在catalog数据库创建管理用户gsmadmin用于存储Sharding节点管理信息，以及GDSCTL接口可以通过gsmadmin用户连接到catalog数据库
4.3 进入到GDSCTL命令行，创建shard catalog
环境变量增加gds bin路径
PATH=$PATH:$HOME/.local/bin:/u01/app/oracle/product/12.2.0/gsmhome_1/bin:$HOME/bin
[oracle@sdb1 admin]$ gdsctl
GDSCTL>create shardcatalog -database 172.16.4.21:1521:shard -chunks 12 -user gsmadmin/oracle -sdb shard -region region1,region2
Catalog is created
备注：在创建catalog之前先查看下监听是否正常
4.4 创建并启动shard director。并设置操作系统安全认证
GDSCTL>add gsm -gsm sharddirector1 -listener 1522 -pwd oracle -catalog 172.16.4.21:1521:shard -region region1
GSM successfully added
参数含义：
-gsm: 指定shard director名称
-listener: 指定shard director的监听端口，注意不能与数据库的listener端口冲突
-catalog: 指定catalog database 信息，catalog数据库的主机名:监听器port: catalog 数据库db_name
启动director
GDSCTL>start gsm -gsm sharddirector1
GSM is started successfully
添加操作系统认证
GDSCTL>add credential -credential region1_cred -osaccount oracle -ospassword oracle
The operation completed successfully
GDSCTL>exit
4.5 在其余节点注册Scheduler agents, 并创建好oradata和fast_recovery_area文件夹。
sdb2:
schagent -start
schagent -status
echo oracle | schagent -registerdatabase 172.16.4.21 8080
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fast_recovery_area
sdb3:
schagent -start
schagent -status
echo oracle | schagent -registerdatabase 172.16.4.21 8080
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fast_recovery_area
五、开始布署SharedDatabase
本例将布署System-ManagedSDB。
主节点进行
5.1 添加shardgroup
[oracle@sdb1 ~]$ gdsctl
GDSCTL>set gsm -gsm sharddirector1
GDSCTL>connect gsmadmin/oracle
GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
备注：
shardgroup是一组shard的集合，shardgroup名称为primary_shardgroup，-deploy_as primary表示这个group中的shard都是主库。
5.2 创建shard
sdb2：
GDSCTL>create shard -shardgroup primary_shardgroup -destination sdb2 -credential region1_cred -sys_password oracle
The operation completed successfully
DB Unique Name: sh1
sdb3：
GDSCTL>create shard -shardgroup primary_shardgroup -destination sdb3 -credential region1_cred -sys_password oracle
The operation completed successfully
DB Unique Name: sh21
附删除shard方式
-REMOVE SHARD -SHARD{shard_name_list | ALL} | -SHARDSPACE shardspace_list | -SHARDGROUP shardgroup_list} [-FORCE]
-remove shard -shard sh21 -force
-remove shard -shard sh1 -force
--primary_shardgroup
--shardspaceora
5.3 查看shard节点配置信息
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup U none region1 -
sh21 primary_shardgroup U none region1 -
由于还未部署，state为none
5.4 查看gsm状态信息
GDSCTL>status gsm
Alias SHARDDIRECTOR1
Version 12.2.0.1.0
Start Date 16-OCT-2018 17:34:06
Trace Level off
Listener Log File /u01/app/oracle/diag/gsm/sdb1/sharddirector1/alert/log.xml
Listener Trace File /u01/app/oracle/diag/gsm/sdb1/sharddirector1/trace/ora_13073_140473160778112.trc
Endpoint summary (ADDRESS=(HOST=sdb1)(PORT=1522)(PROTOCOL=tcp))
GSMOCI Version 2.2.1
Mastership Y
Connected to GDS catalog Y
Process Id 13076
Number of reconnections 0
Pending tasks. Total 0
Tasks in process. Total 0
Regional Mastership TRUE
Total messages published 0
Time Zone +08:00
Orphaned Buddy Regions:
None
GDS region region1
5.5 利用GDS自动部署shard节点数据库
GDSCTL>deploy
备注：
1、deploy命令会调用远程每一个节点上的dbca去静默安装sharded database
2、可以通过查看dbca日志跟踪每个节点的安装进度
3、deploy过程会持续一段时间，中间没有输出过程
dbca日志：$ORACLE_BASE/cfgtoollogs/dbca/实例名/trace.log
[oracle@sdb2 sh1]$ tail -100f /u01/app/oracle/cfgtoollogs/dbca/sh1/trace.log_2018-10-16_03-00-25-PM
deploy: examining configuration...
deploy: deploying primary shard 'sh1' ...
deploy: network listener configuration successful at destination 'sdb2'
deploy: starting DBCA at destination 'sdb2' to create primary shard 'sh1' ...
deploy: deploying primary shard 'sh21' ...
deploy: network listener configuration successful at destination 'sdb3'
deploy: starting DBCA at destination 'sdb3' to create primary shard 'sh21' ...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'sdb3' for shard 'sh21'
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'sdb2' for shard 'sh1'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully
部署完毕
5.6 再次查看shard数据库状态
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup Ok Deployed region1 ONLINE
sh21 primary_shardgroup Ok Deployed region1 ONLINE
status状态ok已部署完毕
六、建立service
6.1 创建服务
GDSCTL>add service -service oltp_rw_srvc -role primary
The operation completed successfully
6.2 启动服务
GDSCTL>start service -service oltp_rw_srvc
The operation completed successfully
6.3 查看服务状态
GDSCTL>status service
Service "oltp_rw_srvc.shard.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
Instance "shard%1", name: "sh1", db: "sh1", region: "region1", status: ready.
Instance "shard%11", name: "sh21", db: "sh21", region: "region1", status: ready.
服务已启动完毕
七、修改sqlnet文件
所有节点修改sqlnet.ora
七、创建用户并授权
SQL> alter session enable shard ddl;
Session altered.
--创建应用用户app_schema
SQL> create user app_schema identified by oracle;
User created.
--对用户授权
SQL> grant all privileges to app_schema;
Grant succeeded.
SQL> grant gsmadmin_role to app_schema;
Grant succeeded.
SQL> grant select_catalog_role to app_schema;
Grant succeeded.
SQL> grant connect, resource to app_schema;
Grant succeeded.
SQL> grant dba to app_schema;
Grant succeeded.
SQL> grant execute on dbms_crypto to app_schema;
Grant succeeded.
八、利用应用用户登录，创建sharded table和duplicated table
创建表空间集合
conn app_schema/oracle
alter session enable shard ddl;
CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m extent management local segment space management auto );
CREATE TABLESPACE products_tsp datafile size 100m extent management local uniform size 1m;
备注：
1、创建TSP_SET_1表空间集是提供给以下customers，orders，lineitems，这3个sharded table使用。products_tsp表空间是用于duplicate表products使用。
2、TABLESPACE SET只有sharding环境才能创建，并且需要在catalog数据库以sdb用户创建
3、TABLESPACE SET中的表空间是bigfile，每一个表空间会自动创建，其总数量与chunks相同（所有节点chunks之和）
创建表家族
-- Create sharded table family
CREATE SHARDED TABLE Customers
(
CustId VARCHAR2(60) NOT NULL,
FirstName VARCHAR2(60),
LastName VARCHAR2(60),
Class VARCHAR2(10),
Geo VARCHAR2(8),
CustProfile VARCHAR2(4000),
Passwd RAW(60),
CONSTRAINT pk_customers PRIMARY KEY (CustId),
CONSTRAINT json_customers CHECK (CustProfile IS JSON)
) TABLESPACE SET TSP_SET_1
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
CREATE SHARDED TABLE Orders
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
OrderDate TIMESTAMP NOT NULL,
SumTotal NUMBER(19,4),
Status CHAR(4),
constraint pk_orders primary key (CustId, OrderId),
constraint fk_orders_parent foreign key (CustId)
references Customers on delete cascade
) partition by reference (fk_orders_parent);
CREATE SEQUENCE Orders_Seq;
CREATE SHARDED TABLE LineItems
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
ProductId INTEGER NOT NULL,
Price NUMBER(19,4),
Qty NUMBER,
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);
-- duplicated table
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、如上创建了customers、orders、lineitems3张表，均为shared table，三张表组成了表家族，其中customers是根表，orders以及lineitems表为子表，他们按照sharding key （custid）根表的主键进行分区
2、customers表partitioning by consistent hash.主要作用是打散数据
创建function，目的是为了后面的DEMO：
CREATE OR REPLACE FUNCTION PasswCreate(PASSW IN RAW)
RETURN RAW
IS
Salt RAW(8);
BEGIN
Salt := DBMS_CRYPTO.RANDOMBYTES(8);
RETURN UTL_RAW.CONCAT(Salt, DBMS_CRYPTO.HASH(UTL_RAW.CONCAT(Salt,
PASSW), DBMS_CRYPTO.HASH_SH256));
END;
/
CREATE OR REPLACE FUNCTION PasswCheck(PASSW IN RAW, PHASH IN RAW)
RETURN INTEGER IS
BEGIN
RETURN UTL_RAW.COMPARE(
DBMS_CRYPTO.HASH(UTL_RAW.CONCAT(UTL_RAW.SUBSTR(PHASH, 1, 8),
PASSW), DBMS_CRYPTO.HASH_SH256),
UTL_RAW.SUBSTR(PHASH, 9));
END;
/
进入catalog数据库检查刚才执行的ddl操作是否有错误
GDSCTL>connect gsmadmin/oracle
Catalog connection is established
GDSCTL>show ddl
id DDL Text Failed shards
-- -------- -------------
7 grant execute on dbms_crypto to app_s...
8 CREATE TABLESPACE SET TSP_SET_1 using...
9 CREATE TABLESPACE products_tsp datafi...
10 CREATE SHARDED TABLE Customers ( ...
11 CREATE SHARDED TABLE Orders ( ...
12 CREATE SEQUENCE Orders_Seq
13 CREATE SHARDED TABLE LineItems ...
14 CREATE MATERIALIZED VIEW "APP_SCHEMA"...
15 CREATE OR REPLACE FUNCTION PasswCreat...
16 CREATE OR REPLACE FUNCTION PasswCheck...
检查每个shard是否有ddl错误
shard node1节点：
GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sdb2:1521/sh1:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL:
DDL Error: --- <<<<<<<<<<<<没有DDL错误
Failed DDL id:
Availability: ONLINE
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_rw_srvc Yes Enabled
shard node2节点：
GDSCTL>config shard -shard sh21
Name: sh21
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sdb3:1521/sh21:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL:
DDL Error: --- 没有错误
Failed DDL id:
Availability: ONLINE
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_rw_srvc Yes Enabled
九、验证环境-表空间/chunks
9.1 在gsm节点，检查chunks信息
前面创建shardcatalog时指定chunks为12，因此后续创建shard table分配12个chunks
每个shard节点均有6个chunk
GDSCTL>config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 6
sh21 7 12
9.2 在shard所有节点检查表空间和chunks信息
shard node1:
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
C001TSP_SET_1 100
C002TSP_SET_1 100
C003TSP_SET_1 100
C004TSP_SET_1 100
C005TSP_SET_1 100
C006TSP_SET_1 100
PRODUCTS_TSP 100
SYSAUX 470
SYSTEM 800
TSP_SET_1 100
UNDOTBS1 70
USERS 5
备注：
1、创建了6个表空间，分别是C001TSP_SET_1 ~ 表空间C006TSP_SET_1，因为设置chunks=12，每个shard有6个chunks。
2、每个表空间有一个datafile，大小是100M，这个是在创建tablespace set时设置的datafile 100M。
shard node2:
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
C007TSP_SET_1 100
C008TSP_SET_1 100
C009TSP_SET_1 100
C00ATSP_SET_1 100
C00BTSP_SET_1 100
C00CTSP_SET_1 100
PRODUCTS_TSP 100
SYSAUX 480
SYSTEM 800
TSP_SET_1 100
UNDOTBS1 70
USERS 5
检查chunks
shard node1:
set linesize 140
set pagesize 200
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_1' order by tablespace_name;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS CUSTOMERS_P1 C001TSP_SET_1
CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1
ORDERS CUSTOMERS_P1 C001TSP_SET_1
CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1
ORDERS CUSTOMERS_P2 C002TSP_SET_1
LINEITEMS CUSTOMERS_P2 C002TSP_SET_1
CUSTOMERS CUSTOMERS_P3 C003TSP_SET_1
LINEITEMS CUSTOMERS_P3 C003TSP_SET_1
ORDERS CUSTOMERS_P3 C003TSP_SET_1
LINEITEMS CUSTOMERS_P4 C004TSP_SET_1
CUSTOMERS CUSTOMERS_P4 C004TSP_SET_1
ORDERS CUSTOMERS_P4 C004TSP_SET_1
CUSTOMERS CUSTOMERS_P5 C005TSP_SET_1
ORDERS CUSTOMERS_P5 C005TSP_SET_1
LINEITEMS CUSTOMERS_P5 C005TSP_SET_1
CUSTOMERS CUSTOMERS_P6 C006TSP_SET_1
ORDERS CUSTOMERS_P6 C006TSP_SET_1
LINEITEMS CUSTOMERS_P6 C006TSP_SET_1
shard node2:
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
ORDERS CUSTOMERS_P7 C007TSP_SET_1
CUSTOMERS CUSTOMERS_P7 C007TSP_SET_1
LINEITEMS CUSTOMERS_P7 C007TSP_SET_1
CUSTOMERS CUSTOMERS_P8 C008TSP_SET_1
LINEITEMS CUSTOMERS_P8 C008TSP_SET_1
ORDERS CUSTOMERS_P8 C008TSP_SET_1
CUSTOMERS CUSTOMERS_P9 C009TSP_SET_1
ORDERS CUSTOMERS_P9 C009TSP_SET_1
LINEITEMS CUSTOMERS_P9 C009TSP_SET_1
ORDERS CUSTOMERS_P10 C00ATSP_SET_1
CUSTOMERS CUSTOMERS_P10 C00ATSP_SET_1
LINEITEMS CUSTOMERS_P10 C00ATSP_SET_1
CUSTOMERS CUSTOMERS_P11 C00BTSP_SET_1
LINEITEMS CUSTOMERS_P11 C00BTSP_SET_1
ORDERS CUSTOMERS_P11 C00BTSP_SET_1
CUSTOMERS CUSTOMERS_P12 C00CTSP_SET_1
LINEITEMS CUSTOMERS_P12 C00CTSP_SET_1
ORDERS CUSTOMERS_P12 C00CTSP_SET_1
9.3 在catalog数据库检查chunks信息
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;
SHARD NUMBER_OF_CHUNKS
------------------------------ ----------------
sh1 6
sh21 6
与gdsctl中config chunks命令一样，在catalog数据库查询的数量一致
9.4 检查catalog以及shard节点数据库中表信息是否正确
–catalog数据库
SQL> select table_name from user_tables;
TABLE_NAME
-----------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS
MLOG$_PRODUCTS
RUPD$_PRODUCTS
–shard节点node1、node2
SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS
十、客户端连接shard分片节点测试
在连接字符串中指定sharding key连接shard数据库
sqlplus app_schema/oracle@’(description=(address=(protocol=tcp)(host=sdb1)(port=1522))(connect_data=(service_name=oltp_rw_srvc.shard.oradbcloud)(region=region1)(SHARDING_KEY=james.parker@x.bogus)))’
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
sh1
已连接到shard node1数据库
#业务场景测试
一、shard table（分片表）测试
分别在shard的两个节点node1、node2做插入以及查询操作
目的：验证在shard 节点数据库是否只能看到本地节点数据
在shard节点node1插入一条数据
[oracle@sdb2 ~]$ sqlplus app_schema/oracle
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('james.parker@x.bogus', 'James', 'Parker',NULL, 'Gold', 'east', hextoraw('8d1c00e'));
SQL> commit;
column custid format a20
column firstname format a15
column lastname format a15
select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
--------- ------- ---- ---- ----
james.parker@x.bogus James Parker Gold east
已成功插入
在shard节点node2插入一条数据
[oracle@sdb3 ~]$ sqlplus app_schema/oracle
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('tom.crus@qq.com', 'tom', 'crus',NULL, 'silver', 'west', hextoraw('9d1c00e'));
SQL> commit;
查询数据
SQL> select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- --------
tom.crus@qq.com tom crus silver west
结果：只有1条数据，无法查看shard node1的数据（james）
客户端连接catalog database sdb1
set pagesize 200 linesize 200
column custid format a20
column firstname format a15
column lastname format a15
col class for a10
col geo for a10
select custid, FirstName, LastName, class, geo from customers;
SQL> select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- ----------
james.parker@x.bogus James Parker Gold east
tom.crus@qq.com tom crus silver west
在catalog数据库可查看到所有数据
客户端通过指定key访问shard
sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=sdb1)(port=1522))(connect_data=(service_name=oltp_rw_srvc.shard.oradbcloud)(region=region1)(SHARDING_KEY=tom.crus@qq.com)))'
set pagesize 200 linesize 200
column custid format a20
column firstname format a15
column lastname format a15
col class for a10
col geo for a10
select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- ----------
tom.crus@qq.com tom crus silver west
结果：通过指定sharding key只能查到tom
总结：
1、每个shard节点只能查询到本地节点shard table的数据
2、在catalog数据库可以查询shard节点汇聚后的数据、
3、客户端可指定sharding key的方式，定向获取数据
二、dupliucate table测试
duplicated table是整个shard database每个成员均能访问相同表数据，下面场景测试插入catalog数据库以及node节点
目的：验证在每个shard包括catalog数据库查看duplicate表内容是否一致
catalog database：
SQL> insert into products(PRODUCTID,name,DESCRURI,LASTPRICE) values(3,'cts','car',1000);
SQL> commit;
col productid for 99999
col name for a10
col DESCRURI for a10
select * from products;
PRODUCTID NAME DESCRURI LASTPRICE
--------- ---------- ---------- ----------
2 ps4 sony 150
3 cts car 1000 --------<<<
1 xbox game 100
成功插入
shard node 1:
SQL> alter system switch logfile;
System altered.
SQL> select * from products;
PRODUCTID NAME DESCRURI LASTPRICE
--------- ---------- ---------- ----------
2 ps4 sony 150
3 cts car 1000
1 xbox game 100
结果：catalog新增的数据，在节点1上并没有实时查询到，手动切换日志后可查到数据库
shard node 2:
SQL> alter system switch logfile;
System altered.
SQL> select * from products;
PRODUCTID NAME DESCRURI LASTPRICE
--------- ---------- ---------- ----------
2 ps4 sony 150
3 cts car 1000
1 xbox game 100
结果：catalog新增的数据，在节点1上并没有实时查询到，手动切换日志后可查到数据库
总结：
1、在catalog数据库可以插入数据到duplicate表products，shard结果无法DML操作
2、每个shard节点查询duplicate表的内容均相同，在默认情况下，shard节点需要等待60s，数据才会同步。由初始化参数SHRD_DUPL_TABLE_REFRESH_RATE控制。
3、在每个shard节点查询duplicate表，实际上只会查本地节点MV视图，不会查询主表。即使主表所在的catalog数据库宕机也不会影响其他节点的查询。
参考资料：
50.3 Duplicated Tables
58.2 Read-Only Materialized Views
duplicate表过程
十一、数据分片测试
场景：分别在两个终端插入数据，custid不一致
目的：验证shard数据库在插入数据的时候是否会根据custid的一致性哈希算法进行随机数据分片
1、在客户端编辑tnsnames，用于连接shard数据库
sharddb =
(DESCRIPTION=
(FAILOVER=on)
(ADDRESS_LIST=
(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL = TCP)(host=sdb1)(port=1522)))
(CONNECT_DATA=
(SERVICE_NAME=oltp_rw_srvc.shard.oradbcloud)
(REGION=region1)
)
)
2、在客户端分别用两个终端连接shard数据库
终端1 连接shard node1：
sqlplus app_schema/oracle@sharddb
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ------ ------------------------------
db_unique_name string sh1
连接的是shard node1
插入数据
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('tom2.cru@qq.com', 'tom2', 'crus2',NULL, 'silver', 'west', hextoraw('9d1c00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('james.bond@qq.com', 'james', 'bond',NULL, 'silver', 'south', hextoraw('9d1d00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('clark.kent@qq.com', 'clark', 'kent',NULL, 'bronze', 'west', hextoraw('9d2c00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('brad2.pitt@qq.com', 'brad2', 'pitt2',NULL, 'silver', 'north', hextoraw('9d1c10e'));
commit;
结果：只有1条数据可以插入，其余三条数据有报错。ORA-14466: Data in a read-only partition or subpartition cannot be modified.
终端2 连接shard node2
sqlplus app_schema/oracle@sharddb
结果：只有3条数据插入，剩余一条数据报错ORA-14466: Data in a read-only partition or subpartition cannot be modified.
3、客户端直接连接catalog数据库
sqlplus app_schema/oracle@172.16.4.21/shard
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('tom2.cru@qq.com', 'tom2', 'crus2',NULL, 'silver', 'west', hextoraw('9d1c00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('james.bond@qq.com', 'james', 'bond',NULL, 'silver', 'south', hextoraw('9d1d00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('clark.kent@qq.com', 'clark', 'kent',NULL, 'bronze', 'west', hextoraw('9d2c00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) VALUES ('brad2.pitt@qq.com', 'brad2', 'pitt2',NULL, 'silver', 'north', hextoraw('9d1c10e'));
commit;
set pagesize 200 linesize 200
column custid format a20
column firstname format a15
column lastname format a15
col class for a10
col geo for a10
select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- ----------
brad2.pitt@qq.com brad2 pitt2 silver north
tom2.cru@qq.com tom2 crus2 silver west
james.bond@qq.com james bond silver south
结果：在catalog数据库可以成功插入所有数据，数据成功分片在两个节点，节点1一条数据库，节点2三条数据
总结：
1.在catalog无法直接通过truncate或者不加where条件的delete删除所有行，必须通过主键（此文档为custid）进行删除行。否则会有报错：不允许跨节点的dml操作
ORA-02671: DML are not allowed on more than one shard
2.通过连接catalog数据库，数据将以分片形式插入数据到两个节点
3.两个节点插入的数据可能是以某种算法进行分布，在某个节点单独执行插入语句会报错
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
4.当以gdsctl创建的服务oltp_rw_srvc.shard.oradbcloud，进行客户端连接时，类似rac环境，shard数据库也能实现负载均衡。
#Sharding数据库维护
1.关闭shard database
catalog数据库端：
先关闭director
[oracle@sdb1 ~]$ gdsctl
Current GSM is set to SHARDDIRECTOR1
GDSCTL>stop gsm -gsm SHARDDIRECTOR1
GSM is stopped successfully
shard节点1
关闭agent、监听、关闭数据库
[oracle@sdb2 ~]$ schagent -stop
Agent running with PID 2783
Done stopping all running jobs
Terminating agent gracefully
[oracle@sdb2 ~]$ lsnrctl stop
SQL> shutdown immediate
shard节点2
关闭agent、监听、关闭数据库
[oracle@sdb3 ~]$ schagent -stop
Agent running with PID 2824
Done stopping all running jobs
Terminating agent gracefully
[oracle@sdb3 ~]$ lsnrctl stop
SQL> shutdown immediate
catalog数据库关监听、停库
[oracle@sdb1 ~]$ lsnrctl stop
SQL> shutdown immediate
2.启动shard数据库
catalog数据库
启动数据库以及监听
SQL> startup
[oracle@sdb1 ~]$ lsnrctl start
所有shard节点
启动数据库
SQL> startup
启动监听
[oracle@sdb2 ~]$ lsnrctl start
启动代理
[oracle@sdb2 ~]$ schagent -start
catalog数据库
启动director
GDSCTL>start gsm -gsm SHARDDIRECTOR1
查看shard节点状态
GDSCTL>config shard
查看shard数据库服务状态
GDSCTL>config service
查看shard节点数据库状态
GDSCTL>databases
3.客户端测试
sqlplus app_schema/oracle@sharddb
#性能测试
1与单节点数据库相比，性能是否提升
插入1000W数据、并查询
2demo的fuction监控性能
#参考
1、https://blog.csdn.net/lzw5210/article/details/68065766
2、https://blogs.oracle.com/database4cn/12c-oracle-sharding
3、https://www.cnblogs.com/automng/p/8017801.html
4、oracle doc Oracle Sharding
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/sharding-overview.html#GUID-D90D0A33-1BCB-48D8-8193-8FD1C2959371
5、CREATE TABLESPACE SET
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLESPACE-SET.html#GUID-877951F1-B2A5-4907-9F0F-EF4F1884E8C4
扫一扫
5366
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
