Oracle 12C Sharding
部署和测试
成都创新互联长期为数千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为梅河口企业提供专业的成都网站设计、做网站，梅河口网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。
日期：
2019-02-26
作者：陈举超
目录
一：环境说明
...
4
二：安装GSM/Shard catalog/ORACLE 12.2 RDBMS
.
5
2.1 Gsm
安装
...
5
2.2 ORACLE 12.2 RDBMS
安装
...
8
2.3
创建并启动监听
...
8
2.4
创建Shard Catalog database
.
8
三：配置GSM/Shard director
.
8
3.1
解锁用户
...
8
3.2
创建管理用户mygds
.
8
3.3
启动listener
.
8
3.4
创建shard catalog
配置remote scheduler agent
.
8
3.5
创建和启动shard director.
9
3.6
添加操作系统认证.
9
3.7
在所有的shard
节点分别执行Agent
.
10
3.8
创建shard
数据库的数据文件存储路径
...
11
四：创建System-Managed SDB
.
11
4.1
连接gsm
..
11
4.2
添加shardgroup
.
11
4. 3
创建shard
.
11
4.4
检查配置
...
12
4.5
部署/deploy
.
13
4.6
检查配置信息
...
14
4.7
创建service
.
16
五：创建用户和对象
...
16
5.1
在catalog
数据库中创建业务用户
...
16
5.2
创建表空间集合
...
16
5.3
为duplicated tables
创建表空间
...
17
5.4
创建
root
表
Customers
.
17
5.5
创建其他
sharded table Orders.
18
5.6
为
OrderId
列创建序列
...
18
5.7
创建
SHARDED TABLE LineItems
.
18
5.8
创建
duplicated tables.
18
5.9
检查是否有错误
...
19
六：验证环境-
表空间/chunks
.
20
6.1
在db01
检查chunks
信息
...
20
6.2
在
sd1,sd2
节点检查表空间和
chunks
信息
...
20
6.3
在catalog
数据库检查chunks
信息
...
23
6.4
验证环境
...
23
七：访问Shard
.
24
7.1
访问单独一个shard
.
24
7.2
访问多个shard
.
26
八：shard
数据库启动和停止
...
27
8.1
关闭
...
27
8.2
启动
九：常见问题
...
29
9.1 create shard –shardgroup
错误
...
29
9.2 catalog
创建用户和表，不能同步到
shard db
库
...
29
9.3 GSM
启动失败，显示异常
十：参考文献
一：环境说明
OS
：CentOS Linux release 7.5
DB
：Oracle 12.2.0.1.0
GSMOCI
：2.2.1
本次使用三台虚拟机进行安装：
其中：
(1) db01
主机上安装Shard Director
和Shard catalog
，也就是分别安装GSM
、ORACLE 12.2 RDBMS
、non-cdb Database(GSM
和ORACLE 12.2
环境变量需要手动切换)
；
(2)db02
主机上安装Shard1
，也就是ORACLE 12.2 RDBMS
，不需要创建数据库；
(3) db03
主机上安装Shard2
，也就是ORACLE 12.2 RDBMS
，不需要创建数据库；
主机 |
组件 |
SID |
Oracle_Home |
db01 |
Shard Director |
|
/u01/app/oracle/product/12.2.0/gsm_1 |
db01 |
Shard catalog |
db01 |
/u01/app/oracle/product/12.2.0/db_1 |
db02 |
Shard1 |
sh2 |
/u01/app/oracle/product/12.2.0/db_1 |
db03 |
Shard2 |
sh3 |
/u01/app/oracle/product/12.2.0/db_1 |
安装文件下载链接如下：
https://www.oracle.com/technetwork/cn/database/options/partitioning/downloads/index.html
Oracle
软件下载
GSM
下载
二：安装GSM/Shard catalog/ORACLE 12.2 RDBMS
2.1 Gsm
安装
2.2 ORACLE 12.2 RDBMS
安装
略
2.3
创建并启动监听
Netca
2.4
创建Shard Catalog database
在Shard catalog
服务器gsm1
创建
non-cdb
数据库。创建过程与普通数据库相同。
三：配置GSM/Shard director
https://blogs.oracle.com/database4cn/12c-oracle-sharding
3.1
解锁用户
在db01
服务器(catalog
数据库/shard director)
，连接到Sharding catalog
数据库,
解锁
GSMCATUSER
用户，shard director
通过GSMCATUSER
用户连接到shard catalog database
SQL> alter user gsmcatuser identified by oracle account unlock;
3.2
创建管理用户mygds
在
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.3
启动listener
在db01
服务器(catalog
数据库/shard director)
，启动listener
3.4
创建shard catalog
配置remote scheduler agent
在db01
服务器(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)
类型
[oracle@db01 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@db01 ~]$ export ORACLE_HOME=/u01/app/oracle/products/12.2.0/gsm_1
[oracle@db01 ~]$
export PATH=/u01/app/oracle/products/12.2.0/gsmh_11/bin:$PATH:$HOME/bin
[oracle@db01 ~]$ gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Mon Feb 25 10:38:54 CST 2019
Copyright (c) 2011, 2016, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Warning: current GSM name is not set automatically because gsm.ora contains zero or several GSM entries. Use "set gsm" command to set GSM for the session.
Current GSM is set to GSMORA
GDSCTL>
create shardcatalog -database db01:1521:db01 -chunks 12 -user mygds/oracle -sdb shdb -region region1, region2 -agent_port 8080 -agent_password oracle
Catalog is created
3.5
创建和启动shard director
参数含义：
-gsm:
指定shard director
名称
-listener:
指定shard director
的监听端口，注意不能与数据库的listener
端口冲突
-catalog:
指定catalog database
信息，catalog
数据库的主机名:
监听器port: catalog
数据库db_name
GDSCTL>
add gsm -gsm sharddirector1 -listener
1522
-pwd oracle -catalog db01:1521:db01 -region region1
GSM successfully added
GDSCTL>
start gsm -gsm sharddirector1
GSM
已成功启动
3.6
添加操作系统认证
GDSCTL>
add credential -credential cre_reg1 -osaccount oracle -ospassword oracle
操作已成功完成
3.7
在所有的shard
节点分别执行Agent
---
在db02
节点执行
[oracle@db02 ~]$
schagent -start
Scheduler agent started using port 24409
[oracle@db02 ~]$ schagent -status
Agent running with PID 8537
Agent_version:12.2.0.1.2
Running_time:00:00:11
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1
ORACLE_BASE:/u01/app/oracle
Port:24409
Host:db02
---
在db03
节点执行
[oracle@db03 ~]$
schagent -start
Scheduler agent started using port 19249
[oracle@db03 ~]$ schagent -status
Agent running with PID 8344
Agent_version:12.2.0.1.2
Running_time:00:00:05
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1
ORACLE_BASE:/u01/app/oracle
Port:19249
Host:db03
-----
密码oracle
和端口8080
[oracle@db02 ~]$ echo oracle | schagent -registerdatabase 192.168.2.226 8080
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
[oracle@db03 ~]$ echo oracle | schagent -registerdatabase 192.168.2.226 8080
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
3.8
创建shard
数据库的数据文件存储路径
[oracle@db02 ~]$ mkdir /u01/app/oracle/oradata
[oracle@db02 ~]$ mkdir /u01/app/oracle/fast_recovery_area
[oracle@db03 ~]$ mkdir /u01/app/oracle/oradata
[oracle@db03 ~]$ mkdir /u01/app/oracle/fast_recovery_area
四：创建System-Managed SDB
4.1
连接gsm
在Shard
服务器
sd1
连接到shard director/GSM
服务器(db01)
[oracle@db02 ~]$ ssh oracle@db01
……
oracle@db01's password:
Last login: Mon Feb 25 10:45:20 2019 from 192.168.70.249
[oracle@db01 ~]$ source .bash_profile
[oracle@db01 ~]$ gdsctl
GDSCTL:
版本
12.2.0.1.0 - Production,
时间: Mon Feb 25 11:16:04 CST 2019
版权所有
(c) 2011, 2016, Oracle
。保留所有权利。
欢迎使用GDSCTL,
请键入
"help"
以获得信息。
当前
GSM
设置为
SHARDDIRECTOR1
GDSCTL>
set gsm -gsm sharddirector1
GDSCTL>connect mygds/oracle
已建立目录连接
4.2
添加shardgroup
shardgroup
是一组shard
的集合，shardgroup
名称为primary_shardgroup
，-deploy_as primary
表示这个group
中的shard
都是主库。
GDSCTL>
add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
操作已成功完成
4. 3
创建shard
将每个shard
地址添加到catalog
的valid node checking for registration (VNCR)
列表，并且创建shard
GDSCTL>
add invitednode db02
GDSCTL>
create shard -shardgroup primary_shardgroup -destination db02 -credential cre_reg1 -sys_password oracle
操作已成功完成
数据库的唯一名称: sh2
GDSCTL>
add invitednode db03
GDSCTL>
create shard -shardgroup primary_shardgroup -destination db03 -credential cre_reg1 -sys_password oracle
操作已成功完成
数据库的唯一名称: sh3
4.4
检查配置
GDSCTL>config
区域
------------------------
region1
region2
GSM
------------------------
sharddirector1
sharddirector3
分片数据库
------------------------
shdb
数据库
------------------------
sh2
sh3
分片组
------------------------
primary_shardgroup
分片空间
------------------------
shardspaceora
服务
------------------------
GDSCTL
暂挂请求
------------------------
命令
对象
状态
-- -- --
全局属性
------------------------
名称: oradbcloud
主
GSM: sharddirector1
DDL
序列号: 0
GDSCTL>
config shardspace
分片空间
块
---- -
shardspaceora 12
GDSCTL>
config shardgroup
分片组
块
区域
分片空间
--- - -- ----
primary_shardgroup 12 region1 shardspaceora
GDSCTL>
config vncr
名称
组
ID
-- ----
192.168.2.226
db02
db03
GDSCTL>
config shard
名称
分片组
状态
状态
区域
可用性
-- --- -- -- -- ---
sh2 primary_shardgroup U
无
region1 -
sh3 primary_shardgroup U
无
region1 -
4.5
部署/deploy
执行deploy
后，自动在db02
和db03
服务器上采用静默安装方式部署shard
数据库，速度较慢；
GDSCTL>
deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh2' ...
deploy: network listener configuration successful at destination 'db02'
deploy: starting DBCA at destination 'db02' to create primary shard 'sh2' ...
deploy: deploying primary shard 'sh3' ...
deploy: network listener configuration successful at destination 'db03'
deploy: starting DBCA at destination 'db03' to create primary shard 'sh3' ...
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: 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: 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 'db02' for shard 'sh2'
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'db03' for shard 'sh3'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
操作已成功完成
4.6
检查配置信息
GDSCTL>
config shard
名称
分片组
状态
状态
区域
可用性
-- --- -- -- -- ---
sh2 primary_shardgroup
确定
已部署
region1 ONLINE
sh3 primary_shardgroup
确定
已部署
region1 ONLINE
GDSCTL>
databases
数据库: "sh2"
注册时间: Y
状态:
确定
ONS: N
。角色: PRIMARY
实例: 1
区域: region1
已注册的实例:
shdb%1
数据库: "sh3"
注册时间: Y
状态:
确定
ONS: N
。角色: PRIMARY
实例: 1
区域: region1
已注册的实例:
shdb%11
GDSCTL>
config shard -shard sh2
名称: sh2
分片组: primary_shardgroup
状态:
确定
状态:
已部署
区域: region1
连接字符串: db02:1521/sh2:dedicated
SCAN
地址:
ONS
远程端口: 0
磁盘阈值,
毫秒: 20
CPU
阈值, %: 75
版本: 12.2.0.0
上次失败的
DDL:
DDL
错误: ---
失败的
DDL ID:
可用性: ONLINE
Rack:
支持的服务
------------------------
名称
首选
状态
-- -- --
GDSCTL>
config shard -shard sh3
名称: sh3
分片组: primary_shardgroup
状态:
确定
状态:
已部署
区域: region1
连接字符串: db03:1521/sh3:dedicated
SCAN
地址:
ONS
远程端口: 0
磁盘阈值,
毫秒: 20
CPU
阈值, %: 75
版本: 12.2.0.0
上次失败的
DDL:
DDL
错误: ---
失败的
DDL ID:
可用性: ONLINE
Rack:
支持的服务
------------------------
名称
首选
状态
-- -- --
4.7
创建service
GDSCTL>
add service -service oltp_rw_srvc -role primary
操作已成功完成
GDSCTL>
start service -service oltp_rw_srvc
操作已成功完成
GDSCTL>
status service
服务
"oltp_rw_srvc.shdb.oradbcloud"
具有
2
个实例。关系: ANYWHERE
实例
"shdb%1",
名称: "sh2",
数据库: "sh2",
区域: "region1",
状态: ready
。
实例
"shdb%11",
名称: "sh3",
数据库: "sh3",
区域: "region1",
状态: ready
。
五：创建用户和对象
5.1
在catalog
数据库中创建业务用户
---db01
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;
5.2
创建表空间集合
---db01
SQL> conn app_schema
Enter password:
Connected.
SQL> alter session enable shard ddl;
Session altered.
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);
Tablespace created.
GDSCTL>
set gsm -gsm sharddirector1
GDSCTL>
config shard
已建立目录连接
名称
分片组
状态
状态
区域
可用性
-- --- -- -- -- ---
sh2 primary_shardgroup
确定
DDL
错误
region1 ONLINE
sh3 primary_shardgroup
确定
DDL
错误
region1 ONLINE
5.3
为duplicated tables
创建表空间
---
这个测试中duplicated table
是Products table.
SQL> CREATE TABLESPACE products_tsp datafile '/u01/app/oracle/oradata/db01/products_tsp01.dbf' size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
Tablespace created.
5.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
.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
);
5.6
为
OrderId
列创建序列
SQL
>
CREATE
SEQUENCE
Orders_Seq
;
Sequence
created.
5.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
);
5.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
;
5.9
检查是否有错误
GDSCTL>
set gsm -gsm sharddirector1
GDSCTL>
show ddl
ID DDL
文本 失败的分片
-- ------ -----
108 grant dba to app_schema
109 grant execute on dbms_crypto to app_s...
110 CREATE TABLESPACE SET TSP_SET_1 using...
111 CREATE TABLESPACE products_tsp dataf...
112 CREATE SHARDED TABLE Customers ( ...
113 CREATE SHARDED TABLE Orders ( O...
114 CREATE SHARDED TABLE LineItems ( ...
115 CREATE MATERIALIZED VIEW "APP_SCHEMA"...
116 CREATE OR REPLACE FUNCTION PasswCreat...
117 CREATE OR REPLACE FUNCTION PasswCheck...
GDSCTL>
config shard -shard sh2
名称: sh2
分片组: primary_shardgroup
状态: 确定
状态: 已部署
区域: region1
连接字符串: db02:1521/sh2:dedicated
SCAN
地址:
ONS
远程端口: 0
磁盘阈值, 毫秒: 20
CPU
阈值, %: 75
版本: 12.2.0.0
上次失败的 DDL: CREATE MATERIALIZED VIEW "APP_...
DDL
错误:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.EXECASUSER", line 44
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529
ORA-06512: at "SYS.EXECASUSER", line 31
ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70
ORA-06512: at line 1 \(ngsmoci_execute\)
失败的 DDL ID: 57
可用性: ONLINE
Rack:
支持的服务
------------------------
名称 首选 状态
-- -- --
oltp_rw_srvc
是 启用
六：验证环境-表空间/chunks
6.1
在db01
检查chunks
信息
前面创建shardcatalog时指定chunks为12，因此后续创建shard table分配12个chunks
GDSCTL>config chunks
块
------------------------
数据库 自 至
--- - -
sh2 1 6
sh3 7 12
6.2
在
sd1,sd2
节点检查表空间和
chunks
信息
---db02
--表空间
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 480
SYSTEM 810
TSP_SET_1 100
UNDOTBS1 70
TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
12 rows selected.
创建了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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string sh2
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions
where tablespace_name like 'C%TSP_SET_1' order by tablespace_name; 2
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
18 rows selected.
在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 480
SYSTEM 810
TSP_SET_1 100
UNDOTBS1 65
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> 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
LINEITEMS CUSTOMERS_P7 C007TSP_SET_1
CUSTOMERS CUSTOMERS_P7 C007TSP_SET_1
ORDERS CUSTOMERS_P8 C008TSP_SET_1
CUSTOMERS CUSTOMERS_P8 C008TSP_SET_1
LINEITEMS CUSTOMERS_P8 C008TSP_SET_1
LINEITEMS CUSTOMERS_P9 C009TSP_SET_1
ORDERS CUSTOMERS_P9 C009TSP_SET_1
CUSTOMERS CUSTOMERS_P9 C009TSP_SET_1
LINEITEMS CUSTOMERS_P10 C00ATSP_SET_1
ORDERS CUSTOMERS_P10 C00ATSP_SET_1
CUSTOMERS CUSTOMERS_P10 C00ATSP_SET_1
ORDERS CUSTOMERS_P11 C00BTSP_SET_1
LINEITEMS CUSTOMERS_P11 C00BTSP_SET_1
CUSTOMERS CUSTOMERS_P11 C00BTSP_SET_1
LINEITEMS CUSTOMERS_P12 C00CTSP_SET_1
CUSTOMERS CUSTOMERS_P12 C00CTSP_SET_1
ORDERS CUSTOMERS_P12 C00CTSP_SET_1
18 rows selected.
6.3
在catalog
数据库检查chunks
信息
SQL> set echo off
SQL> 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
------------------------------ ----------------
网站题目：Oracle12CSharding部署和测试
网站网址：
http://cdkjz.cn/article/gjddpp.html
