Oracle Sharding简介
================
Oracle Sharding是Oracle 12.2版本推出的新功能,也称为数据分片,适用于online transaction processing (OLTP). Oracle Sharding基于表分区技术，是一种在数据层将数据水平分区存储到不同的数据库的技术. Sharding可以实现将一个分区表的不同分区存储在不同的数据库中,每个数据库位于不同的服务器，每一个数据库都称为shard, 这些shard组成一个逻辑数据库,称为sharded database (SDB). 这个table也称为sharded table, 每个shard数据库中保存该表的不同数据集(按照sharding key分区), 但是他们有相同的列(columns)。
Shard是一种shared-nothing技术,每个shard数据库使用独立的服务器硬件(CPU,内存等)。Shard可以运行在单机数据库或者DATAGUARD/ADG数据库。
Oracle Sharding优势
================
Oracle Sharding技术提供线性扩展和失败隔离的优点：
线性扩展: 因为每个shard是一个独立的数据库，通过增加新的Shard节点，来线性扩展性能。自动rebalance数据。
失败隔离: 由于Shard是一种shared-nothing技术，每个shard使用独立的硬件，因此一个shard节点出现故障，只会影响到这个shard存放的数据，而不会影响到其他shard。
按照地理位置分布数据：可以选择根据地理位置不同，将数据存储在不同的shard。
滚动升级：选择不同时间升级不同的shard。比如同一时间只升级一个或一部分shard，那么只有这些升级的shard中存储的数据受到影响，其他的shard不受到影响，可以继续提供服务。
云部署：Shard非常适合部署在cloud。
Oracle Sharding组成
=================
Oracle Sharding 主要包括下面组件：
Sharded database (SDB): 逻辑上SDB是一个数据库，但是物理上SDB包括多个物理独立的数据库，SDB类似一个数据库池(pool)，数据库池(pool)中包括多个数据库(Shard). 目前版本最大支持1000个shard。
Shards: SDB包括多个物理独立的数据库，每一个数据库都称为shard, 每个shard数据库位于不同的服务器,他们不共享CPU，内存，存储等资源。每个shard数据库中保存表的不同数据集, 但是每个shard中都有相同的列(columns). Shard数据库可以是Dataguard/ADG，提供高可用性, Shard数据库(单机或者ADG)可以通过GSM deploy来自动创建，也可以将一个已经通过dbca创建好的数据库add到SDB。
Shard catalog:是一个Oracle数据库，用于集中存储管理SDB配置信息，是SDB的核心。SDB配置变化，比如添加/删除shard，Global service等等，都记录在Shard catalog。如果应用查询多个shard中的数据，那么由Shard catalog统一协调分配。我们推荐将Shard catalog配置为dataguard环境，这样可以提供HA高可用。如果Shard catalog无法访问，那么只会影响一些维护操作和跨shard访问，而不会影响单独的shard操作(通过sharding key的查询/DML)。
Shard directors: Global Data Service (GDS)实现对Sharding的集中部署和管理。GSM是GDS的核心组件。GSM作为Shard director. GSM类似于监听器，将客户端对SDB的请求路由到对应的shard。负载均衡客户端的访问。
Global service: 数据库的服务(service), 用于访问SDB中的数据
管理接口：通过GDSCTL (command-line utility) 接口部署管理监控Sharding。
Oracle Sharding方法
================
Oracle Sharding支持3种方法shard/分片方法：
System-Managed Sharding:这种方法用户不用指定数据存放在哪个shard中。Sharding通过一致性哈希(CONSISTENT HASH)方法将数据分区(partitioning)，并自动分布在不同的Shard。System-managed sharding只能有一个shardspace.
Composite Sharding: 这种方法用户创建多个shardspaces ，每个shardspaces 中存放一定范围(range)或者列表(list)的数据。一般情况下，Shardspace按照区域来划分，比如美国区域的shard属于shardspace cust_america，欧洲的shard属于shardspace cust_europe。
Subpartitions with Sharding: Sharding基于表分区，因此子分区(Subpartitions)技术同样适用于Sharding
Oracle Sharding 对象
=================
被Shard/分片的表我们成为sharded table，这些sharded table的集合称为表家族(Table Family)。所谓表家族(Table Family)就是指sharded table之间是父-子关系，一个表家族(Table Family)中没有任何父表的表叫做根表(root table)，每个表家族中只能有一个根表。在12.2，在一个SDB中只支持一个表家族。在表家族(Table Family)中的所有sharded table都按照相同的sharding key(主键)来分片，主要是由root table的sharding key决定的。表家族(Table Family)中有相同sharding key的数据存储在同一个Chunk中，这样方便以后的数据移动。
比如： 用户表 – 订单表 – 订单明细表 就是一个表家族，其中用户表是root table，订单表 和 订单明细表分别是子表，他们都按照sharding key (CustNo )分区。
具体请参考后面的测试-安装配置system managed sharding
用户表:
CustNo Name Address Location Class
--------- ---------- -------------- --------- ------
123 Brown 100 Main St us3 Gold
456 Jones 300 Pine Ave us1 Silver
999 Smith 453 Cherry St us2 Bronze
订单表:
OrderNo CustNo OrderDate
--------- -------- -----------
4001 123 14-FEB-2013
4002 456 09-MAR-2013
4003 456 05-APR-2013
4004 123 27-MAY-2013
4005 999 01-SEP-2013
订单明细表
LineNo OrderNo CustNo StockNo Quantity
------ ------- ------ ------- --------
40011 4001 123 05683022 1
40012 4001 123 45423609 4
40013 4001 123 68584904 1
40021 4002 456 05683022 1
40022 4002 456 45423509 3
40022 4003 456 80345330 16
40041 4004 123 45423509 1
40042 4004 123 68584904 2
40051 4005 999 80345330 12
Oracle Sharding 路由选择(Routing)
==========================
--直接路由应用程序初始化时，在应用层/中间件层建立连接池，连接池获取所有shard节点的sharding key范围，并且保存在连接池中，形成shard topology cache(拓扑缓存)，Cache提供了一个快速的方法直接将请求路由到具体的shard。客户端请求时指定shard key，直接从连接池获取连接，这种情况下不经过shard director/catalog数据库，直接连接到对应的shard。--代理路由如果客户端执行select或者DML时不指定shard key或者执行聚合操作(比如group by)，那么请求发送到Catalog数据库，根据matadata信息，SQL编译器决定访问哪些shards。
为了实现sharding，oracle在连接池和驱动方面都做了增强，提供了新的API(UCP, JDBC, OCI等等)在连接创建时来传递sharding keys.
比如: Sharding APIs for Oracle UCP
PoolDataSource pds =
PoolDataSourceFactory.getPoolDataSource();
// Set Connection Pool properties
pds.setURL(DB_URL);
pds.setUser("hr" );
pds.setPassword("****" );
pds.setInitialPoolSize(10);
pds.setMinPoolSize(20);
pds.setMaxPoolSize(30);
// build the sharding key object
OracleShardingKey shardingKey =
pds.createShardingKeyBuilder()
.subkey("mary.smith@example.com", OracleType.VARCHAR2)
.build();
// Get an UCP connection for a shard
Connection conn =
pds.createConnectionBuilder()
.shardingKey(shardingKey)
.build();
比如：SQLPLUS连接串中指定sharding key：
$ sqlplus app_schema/oracle @ ' (description=(address=(protocol=tcp)(host=gsm1)(port=1522)) (connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=james.parker@x-DOT-bogus)))'
安装配置system managed sharding(shard节点为单机)
==================================================
系统环境概述
--------------
这是一个测试环境,因此shard均为单机数据库,没有配置ADG. 生产环境下，建议配置为ADG，提供高可用性。
主机名角色系统配置
gsm1 - GSM/shard Director和Shard catalog 都安装在gsm1服务器。4GB内存，1颗CPU，30GB ssd磁盘
sd1 - shard服务器1。 4GB内存，1颗CPU，30GB ssd磁盘
sd2 - shard服务器2。 4GB内存，1颗CPU，30GB ssd磁盘
安装12.2 RDBMS软件
-------------------
在所有服务器安装12.2 ORACLE RDBMS软件，包括Shard catalog 服务器和所有shard服务器。只安装软件，不用dbca创建数据库。
安装GDS/GSM软件
-------------------
在shard Director服务器gsm1安装12.2 GDS/GSM软件。GSM安装过程比较简单，按照默认配置安装即可。
创建Shard Catalog database
----------------------------
在Shard catalog 服务器gsm1 创建 non-cdb数据库。创建过程与普通数据库相同。
配置GSM/Shard director
------------------------
1.在gsm1服务器(catalog 数据库/shard director)，连接到Sharding catalog数据库, 解锁 GSMCATUSER 用户，shard director 通过GSMCATUSER 用户连接到shard catalog database。
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/products/12.2.0.1
$ export ORACLE_SID=catadb
$ sqlplus / as sysdba
SQL> alter user gsmcatuser identified by oracle account unlock;
2.在 catalog数据库，创建管理用户mygds，用户mygds用于存储Sharding管理信息，GDSCTL接口通过用户mygds连接到catalog数据库。
SQL> create user mygds identified by oracle;
SQL> grant connect, create session, gsmadmin_role to mygds;
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
3.在gsm1服务器(catalog 数据库/shard director)，启动listener
4.在gsm1服务器(catalog 数据库/shard director)，创建shard catalog，在shard catalog中配置remote scheduler agent.
参数含义：
-user : 指定管理用户，在前面步骤中创建的catalog database管理用户mygds
-database : 指定catalog database 信息，catalog 数据库的主机名:监听器port: catalog 数据库db_name
-sdb : 指定sharded database name
-agent_port: 设置端口，用于shard节点agent连接到GSM
-agent_password: 设置密码，用于shard节点agent连接到GSM
如果没有指定- sharding参数，默认是创建system-managed (default)类型
$ su – oracle
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/products/12.2.0/gsmhome_1
$ export PATH=/u01/app/oracle/products/12.2.0/gsmhome_11/bin:$PATH:$HOME/bin
$ gdsctl
GDSCTL>create shardcatalog -database gsm1:1521:catadb -chunks 12 -user mygds/oracle -sdb shdb -region region1, region2 -agent_port 8080 -agent_password oracle
Catalog is created
5.创建和启动shard director.
参数含义：
-gsm: 指定shard director名称
-listener: 指定shard director的监听端口，注意不能与数据库的listener端口冲突
-catalog: 指定catalog database 信息，catalog数据库的主机名:监听器port: catalog 数据库db_name
GDSCTL>add gsm -gsm sharddirector3 -listener 1522 -pwd oracle -catalog gsm1:1521:catadb -region region1
GSM successfully added
GDSCTL>start gsm -gsm sharddirector3
GSM is started successfully
6.添加操作系统认证.
GDSCTL> add credential -credential cre_reg1 -osaccount oracle -ospassword oracle
The operation completed successfully
7.在所有的shard节点分别执行Agent���册
--在sd1节点执行
[oracle@sd1 ~]$ schagent -start
Scheduler agent started using port 21620
[oracle@sd1 ~]$ schagent -status
Agent running with PID 1814
Agent_version:12.2.0.1.2
Running_time:00:00:08
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/products/12.2.0.1
ORACLE_BASE:/u01/app/oracle
Port:21620
Host:sd1
--密码oracle和端口8080是在第4步创建shardcatalog时设置的:
$ echo oracle | schagent -registerdatabase 192.168.56.230 8080
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
--创建shard 数据库的数据文件存储路径
$ mkdir /u01/app/oracle/oradata
--在sd2节点执行
[oracle@sd2 oradata]$ schagent -start
Scheduler agent started using port 24240
[oracle@sd2 oradata]$ schagent -status
Agent running with PID 1887
Agent_version:12.2.0.1.2
Running_time:00:01:10
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/products/12.2.0.1
ORACLE_BASE:/u01/app/oracle
Port:24240
Host:sd2
$ echo oracle | schagent -registerdatabase 192.168.56.230 8080
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
$ mkdir /u01/app/oracle/oradata
创建System-Managed SDB
-------------------------
部署system-managed SDB
1.在Shard服务器 sd1 连接到shard director/GSM服务器(gsm1)
$ ssh oracle@gsm1
2.设置当前session为sharddirector3 shard director.
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/products/gsm_home1
$ export PATH=/u01/app/oracle/products/gsm_home1/bin:$PATH:$HOME/bin
$ gdsctl
GDSCTL>set gsm -gsm sharddirector3
GDSCTL>connect mygds/oracle
Catalog connection is established
3.添加shardgroup， shardgroup是一组shard的集合，shardgroup名称为primary_shardgroup，-deploy_as primary表示这个group中的shard都是主库。
GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
The operation completed successfully
4.将每个shard地址添加到catalog的valid node checking for registration (VNCR)列表，并且创建shard
GDSCTL> add invitednode sd1
GDSCTL>create shard -shardgroup primary_shardgroup -destination sd1 -credential cre_reg1 -sys_password oracle
The operation completed successfully
DB Unique Name: sh1
GDSCTL> add invitednode sd2
GDSCTL>create shard -shardgroup primary_shardgroup -destination sd2 -credential cre_reg1 -sys_password oracle
The operation completed successfully
DB Unique Name: sh21
5.检查配置
GDSCTL>config
Regions
------------------------
region1
region2
GSMs
------------------------
sharddirector3
Sharded Database
------------------------
shdb
Databases
------------------------
sh1
sh21
Shard Groups
------------------------
primary_shardgroup
Shard spaces
------------------------
shardspaceora
Services
------------------------
GDSCTL pending requests
------------------------
Command Object Status
------- ------ ------
Global properties
------------------------
Name: oradbcloud
Master GSM: sharddirector3
DDL sequence #: 0
GDSCTL>config shardspace
Shard space Chunks
----------- ------
shardspaceora 12
GDSCTL>
GDSCTL>config shardgroup
Shard Group Chunks Region Shard space
----------- ------ ------ -----------
primary_shardgroup 12 region1 shardspaceora
GDSCTL>
GDSCTL>config vncr
Name Group ID
---- --------
192.168.56.230
sd1
sd2
GDSCTL>
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup U none region1 -
sh21 primary_shardgroup U none region1 -
6.部署/deploy
Shard数据库部署过程采用静默安装方式。
GDSCTL>deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh1' ...
deploy: network listener configuration successful at destination 'sd1'
deploy: starting DBCA at destination 'sd1' to create primary shard 'sh1' ...
deploy: deploying primary shard 'sh21' ...
deploy: network listener configuration successful at destination 'sd2'
deploy: starting DBCA at destination 'sd2' 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: 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 'sd1' for shard 'sh1'
deploy: DBCA primary creation job succeeded at destination 'sd2' for shard 'sh21'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully
7.检查配置信息
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup Ok Deployed region1 ONLINE
sh21 primary_shardgroup Ok Deployed region1 ONLINE
GDSCTL>databases
Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Registered instances:
shdb%1
Database: "sh21" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Registered instances:
shdb%11
GDSCTL>config shard -shard sh1
Conversion = ':'Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd1:1521/sh1:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Last Failed DDL:
DDL Error: ---
Failed DDL id:
Availability: ONLINE
Supported services
------------------------
Name Preferred Status
---- --------- ------
GDSCTL>config shard -shard sh21
Conversion = ':'Name: sh21
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd2:1521/sh21:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Last Failed DDL:
DDL Error: ---
Failed DDL id:
Availability: ONLINE
Supported services
------------------------
Name Preferred Status
---- --------- ------
8.创建service
GDSCTL>add service -service oltp_rw_srvc -role primary
The operation completed successfully
GDSCTL>start service -service oltp_rw_srvc
The operation completed successfully
GDSCTL>status service
Service "oltp_rw_srvc.shdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
Instance "shdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
Instance "shdb%11", name: "sh21", db: "sh21", region: "region1", status: ready.
创建用户和对象
1.在catalog数据库中创建业务用户
SQL> alter session enable shard ddl;
SQL> create user app_schema identified by oracle;
SQL> grant all privileges to app_schema;
SQL> grant gsmadmin_role to app_schema;
SQL> grant select_catalog_role to app_schema;
SQL> grant connect, resource to app_schema;
SQL> grant dba to app_schema;
SQL> grant execute on dbms_crypto to app_schema;
2.创建表空间集合
SQL> CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto);
3.为duplicated tables创建表空间，这个测试中duplicated table是Products table.
SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
4.创建root 表Customers
SQL> CONNECT app_schema/oracle
SQL> ALTER SESSION ENABLE SHARD DDL;
SQL> 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;
5.创建其他sharded table Orders.
SQL> 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);
6.为OrderId�������创建序列
SQL> CREATE SEQUENCE Orders_Seq;
7.创建SHARDED TABLE LineItems
SQL> 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);
8.创建duplicated tables.
In this example, the Products table is a duplicated object.
SQL> 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;
9.创建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;
/
10.检查是否有错误：
GDSCTL>connect mygds/oracle
GDSCTL>show ddl
id DDL Text Failed shards
-- -------- -------------
5 grant connect, resource to appuser1
6 grant dba to appuser1
7 grant execute on dbms_crypto to appuser1
8 CREATE TABLESPACE SET TSP_SET_1 using...
9 CREATE TABLESPACE products_tsp datafi...
10 CREATE SHARDED TABLE Customers ( ...
11 CREATE SHARDED TABLE Orders ( O...
12 CREATE SEQUENCE Orders_Seq
13 CREATE SHARDED TABLE LineItems ( ...
14 CREATE MATERIALIZED VIEW "APPUSER1"."...
11.检查每个shard是否有DDL错误
GDSCTL>config shard -shard sh1
Conversion = ':'Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd1:1521/sh1:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Last Failed DDL:
DDL Error: --- <<<<<<<<<<<
Failed DDL id:
Availability: -
Supported services
------------------------
Name Preferred Status
---- --------- ------
验证环境-表空间/chunks
1.在gsm节点，检查chunks信息
前面创建shardcatalog时指定chunks为12，因此后续创建shard table分配12个chunks
GDSCTL>config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 6
sh21 7 12
2.在sd1节点检查表空间和chunks信息
--表空间
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 460
SYSTEM 800
TSP_SET_1 100
UNDOTBS1 70
USERS 5
创建了6个表空间，分别是C001TSP_SET_1 ~ 表空间C006TSP_SET_1，因为设置chunks=12，每个shard有6个chunks。
每个表空间有一个datafile，大小是100M，这个是在创建tablespace set时设置的datafile 100M。
--检查chunks
SQL> set linesize 140
SQL> column table_name format a20
SQL> column tablespace_name format a20
SQL> column partition_name format a20
SQL> show parameter db_unique_name
SQL> 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
-------------------- -------------------- --------------------
ORDERS CUSTOMERS_P1 C001TSP_SET_1
CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1
LINEITEMS CUSTOMERS_P1 C001TSP_SET_1
CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1
LINEITEMS CUSTOMERS_P2 C002TSP_SET_1
ORDERS CUSTOMERS_P2 C002TSP_SET_1
CUSTOMERS CUSTOMERS_P3 C003TSP_SET_1
ORDERS CUSTOMERS_P3 C003TSP_SET_1
LINEITEMS CUSTOMERS_P3 C003TSP_SET_1
ORDERS CUSTOMERS_P4 C004TSP_SET_1
CUSTOMERS CUSTOMERS_P4 C004TSP_SET_1
LINEITEMS CUSTOMERS_P4 C004TSP_SET_1
CUSTOMERS CUSTOMERS_P5 C005TSP_SET_1
LINEITEMS CUSTOMERS_P5 C005TSP_SET_1
ORDERS CUSTOMERS_P5 C005TSP_SET_1
CUSTOMERS CUSTOMERS_P6 C006TSP_SET_1
LINEITEMS CUSTOMERS_P6 C006TSP_SET_1
ORDERS CUSTOMERS_P6 C006TSP_SET_1
18 rows selected.
3.在sd2节点检查表空间和chunks信息
--表空间
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 470
SYSTEM 800
TSP_SET_1 100
UNDOTBS1 70
USERS 5
12 rows selected.
创建了6个表空间，分别是C007TSP_SET_1 ~ 表空间C00CTSP_SET_1，因为设置chunks=12，每个shard有6个chunks。
每个表空间有一个datafile，大小是100M，这个是在创建tablespace set时设置的datafile 100M。
--检查chunks
SQL> set linesize 140
SQL> column table_name format a20
SQL> column tablespace_name format a20
SQL> column partition_name format a20
SQL> show parameter db_unique_name
SQL> 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
-------------------- -------------------- --------------------
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
18 rows selected.
4.在catalog数据库检查chunks信息
SQL> set echo off
SQL> select a.name Shard, count( b.chunk_number) Number_of_Chunks from
2 gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b where
3 a.database_num=b.database_num group by a.name;
SHARD NUMBER_OF_CHUNKS
------------------------------ ----------------
sh1 6
sh21 6
6.6.4验证环境-tables
--catalog数据库
SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
PRODUCTS
MLOG$_PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS
RUPD$_PRODUCTS
6 rows selected.
--shard节点sd1和sd2
SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS
访问单独一个shard
---------------------------
在连接串中指定sharding key，那么GSM/shard director将请求连接到对应的一个shard
参数含义：
app_schema – 是业务用户，
(host=gsm1)(port=1522) – 是GSM/shard director 监听地址
service_name=oltp_rw_srvc.shdb.oradbcloud – 是前面创建的全局service
$sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=gsm1)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=james.parker@x.bogus)))'
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
sh1
--插入数据
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
Class, Geo, Passwd) VALUES ('james.parker@x.bogus', 'James', 'Parker',
NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@gsm1 ~]$ sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=gsm1)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=james.parker@x.bogus)))'
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
sh1
SQL> column custid format a20
SQL> column firstname format a15
SQL> column lastname format a15
SQL> select custid, FirstName, LastName, class, geo from customers
where custid = 'james.parker@x.bogus';
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- --------
james.parker@x.bogus James Parker Gold east
SQL> SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
shdb%1
--查询SHARDING_KEY=tom.david，连接到sd2：
sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=gsm1)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=tom.david)))'
访问多个shard
----------------------
如果在连接串中指定sharding key，那么GSM/shard director将请求连接到对应的一个shard。
如果没有指定sharding key，那么session和coordinator database (shard catalog)建立连接,然后再分别到需要(prund)的shard中查询，最后再整合。
优化器判断访问一个shard还是访问多个shard。
--链接到catalog数据库查询
$ sqlplus app_schema/oracle@gsm1:1521/GDS\$CATALOG.oradbcloud
set termout on
set linesize 120
set echo on
column firstname format a20
column lastname format a20
explain plan for SELECT FirstName,LastName, geo, class FROM Customers
WHERE CustId in ('Scott.Tiger@x.bogus', 'Mary.Parker@x.bogus') AND class != 'free' ORDER
BY geo, class;
select plan_table_output from table(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1622328711
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7700 | 1 (100)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 100 | 7700 | 1 (100)| 00:00:01 | | |
| 2 | VIEW | VW_SHARD_5B3ACD5D | 100 | 7700 | 5 (100)| 00:00:01 | | |
| 3 | SHARD ITERATOR | | | | | | | |
| 4 | REMOTE | | | | | | ORA_S~ | R->S |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
"A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS" FROM "CUSTOMERS" "A1" WHERE
("A1"."CUSTID"='Mary.Parker@x.bogus' OR "A1"."CUSTID"='Scott.Tiger@x.bogus') AND
"A1"."CLASS"<>'free' /* coord_sql_id=462qrk7rf02kq */ (accessing
'ORA_SHARD_POOL@ORA_MULTI_TARGET' )
21 rows selected.
DEMO
---------------
1.下载demo，Doc ID 2184500.1
2.在gsm节点解压缩
3.创建额外一些对象，运行下面脚本，可能需要手动修改demo_app_ext.sql中app_schema的密码
$ cd sdb_demo_app/sql
$ sqlplus / as sysdba
SQL>@demo_app_ext.sql
4.修改配置文件
name=demo
connect_string=(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS=(HOST=gsm1)(PORT=1522)(PROTOCOL=tcp)))
monitor.user=dbmonuser
monitor.pass=TEZiPP4MsLLL
app.service.write=oltp_rw_srvc.shdb.oradbcloud
#app.service.write=oltp_rw_srvc.orasdb.oradbcloud
app.service.readonly= oltp_rw_srvc.shdb.oradbcloud
#app.service.readonly=oltp_ro_srvc.orasdb.oradbcloud
app.user=app_schema
app.pass=oracle
app.threads=7
5.运行demo
./run.sh demo
6.运行monitor
./run.sh monitor
7.访问web，监控性能。性能与测试环境有关系，这篇文章只是提供一个实验环境，非生产环境。
参考文档
========
http://docs.oracle.com/database/122/ADMIN/sharding-overview.htm#ADMIN-GUID-0F39B1FB-DCF9-4C8A-A2EA-88705B90C5BF
http://www.oracle.com/technetwork/database/availability/sharding-adg-createshard-cookbook-3610619.pdf
173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
