CNDBA社区
Oracle 18c 必须掌握的新特性 Sharding系列（9） -- Sharding 块与分片 管理
作者：
dave
1 管理块
1.1 拆分块
拆分块就是将一个块拆分为两个更小的块。如果一个块过大，或者想把某个块的一部分迁移到另一个分片中就会用到拆分块。
Oracle Sharding支持在线拆分块。然而，尽管拆分块不会影响数据的使用，但是拆分块是一项非常消耗资源的操作，因为它扫描正在分割分区的所有行，然后将它们逐个插入到新分区中。对于复合分片法，拆分块非常耗时，可能需要停机来重新指定分片键或超级分片键的值。因此，在部署SDB时尽量提前在每个分片上创建多的块，除非块的数量已经不足以满足重新分片时数据的平衡要求，或某个块是热点块，否则尽量不要拆分某个块。
拆分块有时候是不可避免的，哪怕是系统管理的分片法也可能会导致某个分片上的块比其他块更大。这时候就需要将该块拆分，然后会进行自动移动块以达到数据的再平衡。
语法：
GDSCTL> split chunk -h
Syntax
SPLIT CHUNK -CHUNK chunk_id_list [-SHARDSPACE shard_space_list]
Purpose
Splits each of the specified chunks into two chunks with an equal hash range
size. After the split, the chunks remain in the same database.
Usage Notes
This command can only be used with automatic sharding. For user-defined
sharding, ALTER TABLE is used to split a partition of the root (parent) table.
Merging of chunks is not supported.
Keywords and Parameters
chunk: List of numeric chunk identifiers.
shardspace: a comma-delimited list of shardspaces.
Examples
SPLIT CHUNK -CHUNK 3, 4, 5
具体操作示例：
GDSCTL>config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh21 5 6
sh21 11 12
GDSCTL> split chunk -chunk 8
The operation completed successfully
查看移动信息：
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh2 13 13
sh21 5 6
sh21 11 12
Ongoing chunk movement
------------------------
Chunk Source Target status
----- ------ ------ ------
8 started
这里这里显示开始分裂，在分片2上多了一个chunk 13：
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh2 13 13
sh21 5 6
sh21 11 12
Ongoing chunk movement
------------------------
Chunk Source Target status
----- ------ ------ ------
GDSCTL>
1.2 块移动
移动快的最重要的一个目的是保证各个分片上的负载和数据尽可能的平均分布。一般情况下只有在添加或删除分片的时候才需要去移动块，如果是系统管理的分片，那么Oracle会自动进行块的移动，否则需要DBA手动移动。
尤其在采用复合分片的SDB中，随着数据的不断增大可能会导致各个分片上的热点数据分布不均匀，虽然可能数据量分布很平均。为了保证各个分片的负载均衡，所以需要将热点块移动到负载低的分片上。
以下几个场景可能需要移动块：
1) 当某个分片的热点数据比其他分片多时
2) 当使用的是范围，列表或复合分片时，并且添加了新分片
3) 当使用的是范围，列表或复合分片时，并且删除了旧分片
4) 拆分分片时
移动快的一个标准就是将热点数据迁移到其他不太活跃的分片上，以达到负载的均衡。可以通过OEM或AWR报告来查看各个分片之前的热点数据分布情况，然后决定如何移动块。在移动快后，建议对移出块和移入块的分片都进行备份。
语法：
GDSCTL> move chunk -h
Syntax
MOVE CHUNK -CHUNK {chunk_id_list|ALL} -SOURCE db_name [-TARGET db_name]
[-verbose] [-copy]
Purpose
Moves a listed set of chunks from one shard to another shard or multiple shards.
Usage Notes
Chunks cannot be moved between shards that belong to different shardgroups.If
-CHUNK ALL is specified without the -TARGET option, all chunks will be moved out
from the source shard to other shards in round-robin fashion.
In user-defined sharding Chunks can be moved only if source and target shards
are in differnet shardspaces. Chunk movement is not allowed within shardspace.
Keywords and Parameters
chunk: List of numeric chunk identifiers or ALL for all chunks.
copy: Copy the chunk instead of moving (OGG only).
source: a name of the source shard.
target: a name of the target shard.
timeout: Timeout of connection retention between FAN is sent to clients and chunk
going read-only/down.
verbose: Enable verbose mode.
Examples
MOVE CHUNK -CHUNK 3,4 -SOURCE sale1 -TARGET sale3
MOVE CHUNK -CHUNK ALL -SOURCE sale1
具体操作示例：
GDSCTL> config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh2 13 13
sh21 5 6
sh21 11 12
GDSCTL> config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_grp Ok Deployed region1 ONLINE
sh2 primary_grp Ok Deployed region1 ONLINE
sh21 primary_grp Ok Deployed region1 ONLINE
开始移动：
GDSCTL> move chunk -chunk all -source sh21
查看移动状态：
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh2 13 13
sh21 5 6
sh21 11 12
Ongoing chunk movement # 注意这里的状态，操作完成之后这里为空
------------------------
Chunk Source Target status
----- ------ ------ ------
5 sh21 sh1 Running
6 sh21 sh2 scheduled
11 sh21 sh1 scheduled
12 sh21 sh2 scheduled
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 5
sh2 7 10
sh2 13 13
sh21 6 6
sh21 11 12
Ongoing chunk movement
------------------------
Chunk Source Target status
----- ------ ------ ------
5 sh21 sh1 Moved
6 sh21 sh2 scheduled
11 sh21 sh1 scheduled
12 sh21 sh2 scheduled
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 5
sh1 11 11
sh2 6 10
sh2 12 13
Ongoing chunk movement
------------------------
Chunk Source Target status
----- ------ ------ ------
GDSCTL>
2 管理分片
2.1 添加分片
可以将新的分片添加到现有的Sharding环境中，以扩展和提高容错能力。如果向SDB环境中添加新分片，那么原来分片中的块将会自动移动到新的分片中，以达到块的平衡。添加分片步骤和搭建SDB的步骤基本一致，没有什么区别。
1.安装数据库软件
这里通过CREATE SHARD方式来添加新分片，所以在新的分片上只需要安装数据库软件即可。如果通过ADD SHARD方式来添加新分片，那么需要在新的分片上安装数据库并且要进行检查已确保满足部署要求。如果新分片是CDB中的PDB，那么只能通过ADD SHARD命令来添加。
2.查看当前环境
[dave@www.cndba.cn ~]$ gdsctl
GDSCTL: Version 18.0.0.0.0 - Production on Tue Oct 16 14:29:58 CST 2018
Copyright (c) 2011, 2018, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to SHARDDIRECTOR
GDSCTL> set gsm -gsm SHARDDIRECTOR
#如果有多个director可以通过set来指定，如果只有一个，不要指定
GDSCTL> connect sdbadmin/oracle
Catalog connection is established
GDSCTL> config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_grp Ok Deployed region1 ONLINE
sh2 primary_grp Ok Deployed region1 ONLINE
3.注册新分片
在新分片上执行以下操作：
[dave@www.cndba.cn ~]$ schagent -start
Scheduler agent started using port 15786
[dave@www.cndba.cn ~]$ schagent -status
Agent running with PID 32371
Agent_version:18.1.0.0
Running_time:00:00:06
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/18.3.0/dbhome_1
ORACLE_BASE:/u01/app/oracle
Port:15786
Host:shard3
[dave@www.cndba.cn ~]$ echo oracle | schagent -registerdatabase sdb 8080
Agent Registration Password ?
Oracle Scheduler Agent Registration for 18.1.0.0 Agent
Agent Registration Successful!
[dave@www.cndba.cn ~]$
将新分片添加到shardgroup中：
GDSCTL> add invitednode shard3
GDSCTL> create shard -shardgroup primary_grp -destination shard3 -credential oracle_cred
The operation completed successfully
DB Unique Name: sh21
部署
GDSCTL> config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_grp Ok Deployed region1 ONLINE
sh2 primary_grp Ok Deployed region1 ONLINE
sh21 primary_grp U none region1 -
GDSCTL> deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh21' ...
deploy: network listener configuration successful at destination 'shard3'
deploy: starting DBCA at destination 'shard3' to create primary shard 'sh21' ...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: waiting for 1 DBCA primary creation job(s) to complete...
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 'shard3' for shard 'sh21'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully
GDSCTL> config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_grp Ok Deployed region1 ONLINE
sh2 primary_grp Ok Deployed region1 ONLINE
sh21 primary_grp Ok Deployed region1 ONLINE
新分片自动被注册：
GDSCTL> databases;
Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Service: "oltp_rw_servcie" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
cust_sdb%1
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Service: "oltp_rw_servcie" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
cust_sdb%11
Database: "sh21" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Service: "oltp_rw_servcie" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
cust_sdb%21
新分片上的服务会自动启动：
GDSCTL> services
Service "oltp_rw_servcie.cust_sdb.oradbcloud" has 3 instance(s). Affinity: ANYWHERE
Instance "cust_sdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
Instance "cust_sdb%11", name: "sh2", db: "sh2", region: "region1", status: ready.
Instance "cust_sdb%21", name: "sh21", db: "sh21", region: "region1", status: ready.
查看chunk自动重平衡
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh21 5 6
sh21 11 12
Ongoing chunk movement
------------------------
Chunk Source Target status
----- ------ ------ ------
2.2 删除分片
如果某个分片出现故障，我们想删除时，可以通过以下命令进行：
GDSCTL> config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_grp Ok Deployed region1 ONLINE
sh2 primary_grp Ok Deployed region1 ONLINE
sh21 primary_grp Ok Deployed region1 ONLINE
GDSCTL> config chunks -show_reshard
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh2 13 13
sh21 5 6
sh21 11 12
GDSCTL> remove shard -shard sh21
GSM-45029: SQL error
ORA-02659: cannot remove a shard which contains chunks
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 13392
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 13174
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 12973
ORA-06512: at line 1
这里需要先将sh21上的chunks移动到其他分片上再删除，移动参考上节，这里不描述。
GDSCTL> remove shard -shard sh21
The operation completed successfully
GDSCTL> config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_grp Ok Deployed region1 ONLINE
sh2 primary_grp Ok Deployed region1 ONLINE
GDSCTL> config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 5
sh1 11 11
sh2 6 10
sh2 12 13
版权声明：本文为博主原创文章，未经博主允许不得转载。
