CNDBA社区
1 说明
Sharding是Oracle Database 12c Release 2(12.2)的一个适合于在线事务处理(OLTP)应用程序的架构，在这些应用程序中，数据被水平划分到多个独立的Oracle数据库中，称为shards，它不共享任何硬件或软件。shards的集合被作为一个单一的逻辑Oracle数据库提供给应用程序使用。
Oracle sharding是为定制的OLTP应用程序而设计的，这些应用程序是为共享的数据库架构而设计的。与基于Oracle Real Application集群(Oracle RAC)的架构不同，使用分片的应用程序必须有一个定义良好的数据模型和数据分布策略(一致的散列、范围、列表或组合)，主要使用分片键访问数据。例如：分片键包括：customer_id, account_no, country_id等等。Oracle sharding还支持数据放置策略(rack和geo感知)和所有部署模型(例如，on - premises和公有云或混合云)。
Sharding对于绝大部分应用，可以提供线性扩展和绝对的故障隔离，下面是Sharding注意好处：
1. Linear Scalability. Sharding eliminates performance bottlenecks and makes it possible to linearly scale performance and capacity by adding shards.
2. Fault Containment. Sharding is a shared nothing hardware infrastructure that eliminates single points of failure, such as shared disk, SAN, and clusterware, and provides strong fault isolation—the failure or slow-down of one shard does not affect the performance or availability of other shards.
3. Geographical Distribution of Data. Sharding makes it possible to store particular data close to its consumers and satisfy regulatory requirements when data must be located in a particular jurisdiction.
4. Rolling Upgrades. Applying configuration changes on one shard at a time does not affect other shards, and allows administrators to first test the changes on a small subset of data.
5. Simplicity of Cloud Deployment. Sharding is well suited to deployment in the cloud. Shards may be sized as required to accommodate whatever cloud infrastructure is available and still achieve required service levels. Oracle Sharding supports on-premises, cloud, and hybrid deployment models.
Oracle Sharding主要组件：
l Sharded database (SDB) – a single logical Oracle Database that is horizontally partitioned across a pool of physical Oracle Databases (shards) that share no hardware or software
l Shards - independent physical Oracle databases that host a subset of the sharded database
l Global service - database services that provide access to data in an SDB
l Shard catalog – an Oracle Database that supports automated shard deployment, centralized management of a sharded database, and multi-shard queries
l Shard directors – network listeners that enable high performance connection routing based on a sharding key
l Connection pools - at runtime, act as shard directors by routing database requests across pooled connections
l Management interfaces - GDSCTL (command-line utility) and Oracle Enterprise Manager (GUI)
下图是Oracle Sharding架构
关于Sharding相信信息，查看官方文档：
2 实验
官方文档安装步骤：
2.1 环境
l 操作系统RedHat 7.4
l 数据库软件：12.2.0.1
l GSM：12.2.0.1
环境架构如下：
软件下载地址:
| 主机IP |
主机名 |
DB_UNIQUE_NAME |
用途 |
| 192.168.199.155 |
SDB1 |
Sdb1 |
安装Shard Director和Shard Catalog |
| 192.168.199.156 |
SDB2 |
Sh1 |
Shard数据库 |
| 192.168.199.157 |
SDB3 |
Sh2 |
Shard数据库 |
准备三台虚拟机：
l 一台主机安装Shard Director和Shard Catalog
l 另外两台各安装Shard
所有主机上都安装DB软件，注意：只安装软件。
2.2 安装DB
略。。。
虚拟机可以再一台上安装DB软件，然后直接克隆，修改IP和主机名即可。
2.3 安装GSM
我这里是安装在SDB1主机上(Shard Director所在节点)，一路下一步即可。
新建目录用于存放GSM
[[email protected] software]$ mkdir /u01/app/oracle/product/12.2.0.1/db_2
$ ./runInstaller
[[email protected] ~]# /u01/app/oracle/product/12.2.0.1/db_2/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.2.0.1/db_2
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.2.4 创建Shard Catalog Database
在主机SDB1上创建
运行DBCA
2.5 设置Oracle Sharding Manage和路由层
2.5.1 设置环境变量-shard catalog database
[[email protected] ~]$ env|grep ORA
ORACLE_UNQNAME=sdb1
ORACLE_SID=sdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_12.5.2 启动监听
$ lsnrctl start2.5.3 检查shard catalog database的DB_CREATE_FILE_DEST 并设置open_links 和open_links_per_instance
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata--如果没有配置则使用下面SQL配置一下
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;
SQL> alter system set open_links=16 scope=spfile;
SQL> alter system set open_links_per_instance=16 scope=spfile;2.5.4 重启shard catalog database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1191182336 bytes
Fixed Size 8620032 bytes
Variable Size 436209664 bytes
Database Buffers 738197504 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.2.5.5 shard catalog database上给用户赋权限
--解锁gsmcatuser 用户
SQL> alter user gsmcatuser account unlock;
User altered.
SQL> alter user gsmcatuser identified by oracle;
User altered.--创建管理员用户并赋权限
mysdbadmin帐户是shard目录数据库中的一个帐户，该帐户存储在共享环境中的信息。mysdbadmin帐户是数据库管理员模式，用于对sharded数据库环境进行管理更改。当运行GDSCTL命令时，GDSCTL通过该用户连接到数据库，mysdbadmin用户在数据库中进行必要的更改。
SQL> create user mysdbadmin identified by oracle;
SQL> grant connect, create session, gsmadmin_role to mysdbadmin;
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
SQL> execute dbms_xdb.sethttpport(8080);
SQL> commit;
SQL> @?/rdbms/admin/prvtrsch.plb
SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracleagent'); --密码下面有用2.5.6 使用GDSCTL-创建catalog
---在SDB1主机上设置环境变量
注意设置环境变量：ORACLE_HOME:/u01/app/oracle/product/12.2.0.1/db_2
[[email protected] software]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_2 --这GSM的ORACLE_HOME
[[email protected] software]$ export PATH=$PATH:$ORACLE_HOME/bin
[[email protected] ~]$ env|grep ORA
ORACLE_UNQNAME=sdb1
ORACLE_SID=sdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_[[email protected] ~]$ gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Tue Aug 22 14:18:13 CST 2017
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 sdb1:1521:sdb1 -chunks 12 -user mysdbadmin/oracle -sdb sdb1 -force如果重新创建catalog，需加force
Catalog is created
语法：
create shardcatalog -database shard_catalog_host:1521:shard_catalog_name -chunks 12 -user
mysdbadmin/mysdbadmin_password -sdb cust_sdb -region region1, region2 -agent_port port_num -agent_password rsa_password2.5.7 创建和启动shard director
GDSCTL>add gsm -gsm sharddirector1 -listener 1571 -pwd oracle -catalog sdb1:1521:sdb1 -端口号不能被其他应用占用
GSM successfully added
GDSCTL>start gsm -gsm sharddirector1
GSM is started successfully
语法：
add gsm -gsm sharddirector1 -listener listener_port -pwd gsmcatuser_password -catalog
shard_catalog_host:1521:shardcat -region region1如果有多个shard director主机，则重复上面两个步骤。
2.5.8 添加操作系统认证
用于执行dbca等操作的用户。
GDSCTL>add credential -credential cred_os -osaccount oracle -ospassword oracle
The operation completed successfully2.5.9 连接到各个shard数据库节点
---创建必要目录
[[email protected] ~]$ mkdir /u01/app/oracle/oradata
[[email protected] ~]$ mkdir /u01/app/oracle/fast_recovery_area---启动agent，并注册数据库
[[email protected] ~]$ schagent -start
Scheduler agent started using port 34141
[[email protected] ~]$ echo oracleagent | schagent -registerdatabase sdb1 8080 --shard catalog主机名
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent egistration Successful!注意：如果注册失败，并导致用户锁住，要重新执行
SQL> @?/rdbms/admin/prvtrsch.plb
SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracleagent');2.6 部署System-Managed SDB
在SDB1主机上执行，也就是shard catalog database所在的主机。
注意几点：
l They must not be container databases (CDBs)
l They must have an associated TNS Listener on port 1521 on each host
l The GSMUSER account must be unlocked with a known password
l The primary and standby databases must be configured as such
l Redo apply should be set up between the corresponding primary and standby databases
l Flashback and force logging should be enabled
l The compatible parameter must be set to at least 12.2.0
l A server parameter file (SPFILE) must be in use
l A DATA_PUMP_DIR directory object must be created in each database and must point to a valid directory
2.6.1 检查数据库是否配置正确
根据实际情况进行修改，只是Oracle建议的，只要满足上面的要求即可。
SQL> set serveroutput on
SQL> execute DBMS_GSM_FIX.validateShard
INFO: Data Guard shard validation requested.
INFO: Database role is PRIMARY.
INFO: Database name is SDB1.
INFO: Database unique name is sdb1.
INFO: Database ID is 1687959461.
INFO: Database open mode is READ WRITE.
INFO: Database in archivelog mode.
WARNING: Flashback is off.
ERROR: Force logging is off.
INFO: Database platform is Linux x86 64-bit.
INFO: Database character set is AL32UTF8. This value must match the character
set of the catalog database.
INFO: 'compatible' initialization parameter validated successfully.
INFO: Database is not a multitenant container database.
INFO: Database is using a server parameter file (spfile).
INFO: db_create_file_dest set to: '/u01/app/oracle/oradata'
INFO: db_recovery_file_dest set to: '/u01/app/oracle/fast_recovery_area/sdb1'
INFO: db_files=200. Must be greater than the number of chunks and/or tablespaces
to be created in the shard.
ERROR: dg_broker_start set to FALSE.
INFO: remote_login_passwordfile set to EXCLUSIVE.
WARNING: db_file_name_convert is not set.
ERROR: GSMUSER account status is invalid: EXPIRED & LOCKED
ERROR: GSMADMIN_INTERNAL does not have READ privilege on DATA_PUMP_DIR.
ERROR: GSMADMIN_INTERNAL does not have WRITE privilege on DATA_PUMP_DIR.
INFO: DATA_PUMP_DIR is '/u01/app/oracle/admin/sdb1/dpdump/'.
PL/SQL procedure successfully completed.2.6.2 指定service并连接
GDSCTL>set gsm -gsm sharddirector1
GDSCTL>connect mysdbadmin/oracle
Catalog connection is established2.6.3 添加shardgroup
GDSCTL>add shardgroup -shardgroup sg1 -deploy_as primary
The operation completed successfully
2.6.4 添加shard database节点并创建shards
我这边环境共两个节点sdb2,sdb3
GDSCTL>add invitednode sdb2
GDSCTL>create shard -shardgroup sg1 -destination sdb2 -credential cred_os
The operation completed successfully
DB Unique Name: sh1GDSCTL>add invitednode sdb3
GDSCTL>create shard -shardgroup sg1 -destination sdb3 -credential cred_os
The operation completed successfully
DB Unique Name: sh22.6.5 检查shard 配置
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 sg1 U none regionora -
sh2 sg1 U none regionora - 2.6.6 使用DEPLOY创建shards和复制数据
大约需要15-30分钟，需要在shards执行dbca。
GDSCTL>deploy
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 'sh2' ...
deploy: network listener configuration successful at destination 'sdb3'
deploy: starting DBCA at destination 'sdb3' to create primary shard 'sh2' ...
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 'sdb2' for shard 'sh1'
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'sdb3' for shard 'sh2'
deplzy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully2.6.7 验证所有已部署的shards
可以看到已经ONLINE了
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 sg1 Ok Deployed regionora ONLINE
sh2 sg1 Ok Deployed regionora ONLINESharded Database (SDB) 安装成功。SDB的安装布署非常容易，几乎所有的管理配置都是通过GDSCTL的几条简单命令完成的。另外，Oracle Sharding还高度整合了Oracle Data Guard：如果你想布署standby database，可以通过GDSCTL的一两条命令来定义，Oracle Sharding 会自动帮你布署好standbys。
2.7 为System-Managed SDB创建用户
SQL> alter session enable shard ddl;
SQL> create user apptest identified by oracle;
SQL> grant all privileges to apptest ;
SQL> grant gsmadmin_role to apptest ;
SQL> grant select_catalog_role to apptest ;
SQL> grant connect, resource to apptest ;
SQL> grant dba to apptest ;
SQL> grant execute on dbms_crypto to apptest ;2.7.1 创建表空间集用于存储shared表
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.2.7.2 为Duplicated表创建表空间
SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
Tablespace created.2.7.3 现在我们使用shard用户创建Sharded表和Duplicated表
SQL> conn apptest/oracle
Connected.
SQL> ALTER SESSION ENABLE SHARD DDL
Session altered.
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;
Table created.
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);
Table created.
SQL> CREATE SEQUENCE Orders_Seq;
Sequence created.
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);
Table created.
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;
Table created.2.7.4 验证上面的DDL操作是否有失败的
可以看到Failed shards是空的，表示所有操作均执行成功。
GDSCTL>show ddl
id DDL Text Failed shards
-- -------- -------------
5 grant connect, resource to apptest
6 grant dba to apptest
7 grant execute on dbms_crypto to apptest
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 "APPTEST"."P... 2.7.5 验证表空间集，表
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
PRODUCTS_TSP 100
SYSAUX 490
SYSTEM 800
TSP_SET_1 100
UNDOTBS1 70
USERS 5
6 rows selected.2.7.6 验证所有shards上的表
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 sh1
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
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
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.2.7.7 连接shard catalog数据库，验证chunk是否均匀分布
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
ORDER BY a.name;
SHARD NUMBER_OF_CHUNKS
------------------------------ ----------------
sh1 6
sh2 6
2 rows selected.2.7.8 验证 所有节点上的sharded 和duplicated表
--sdb1,catalog
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
PRODUCTS
MLOG$_PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS
RUPD$_PRODUCTS--sdb2,sh1
SQL> conn apptest/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS--sdb3,sh2
SQL> conn apptest/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS至此Oracle Sharding数据库已经安装完毕。由于第一次搞，还是很慢，特别是一些新错误。
版权声明：本文为博主原创文章，未经博主允许不得转载。
sharding
