CNDBA社区
ORACLE 12C Sharding 数据库
实验环境参考官方链接：https://blogs.oracle.com/database4cn/12c-oracle-sharding
详解sharing方法参考官方链接：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/sharding-methods.html#GUID-1FCC7F96-59BF-4C8D-B71A-6A307D7283EA
Sharding 环境备份恢复注意事项：https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=176802338917416&id=2189659.1&_afrWindowMode=0&_adf.ctrl-state=cy0qvij4a_58
主要ORACLE12c MOS文档文章：Master Note for Handling Oracle Sharding - Oracle Database 12.2 Technology (文档 ID 2226341.1)
本次实验采用system-managed (default)方法：
正常大部分业务，我个人觉得system-managed足够了，hash分片分区自动分布某个shard，而且管理相对方便…
另外当前环境没有实现ADG，也就是说没有实现高可用架构，即Catalog数据库 Shard所有节点都做ADG(配置好环境后支持deploy一键部署环境），这里注意当前说的高可用并非是RAC 而是ADG，需要创建全局service name以及划分region数据中心作为条件
Oracle Sharding方法
Oracle Sharding支持3种方法shard/分片方法：
1、System-Managed Sharding:这种方法用户不用指定数据存放在哪个shard中。Sharding通过一致性哈希(CONSISTENT HASH)方法将数据分区(partitioning)，并自动分布在不同的Shard。System-managed sharding只能有一个shardspace.
2、Composite Sharding: 这种方法用户创建多个shardspaces ，每个shardspaces 中存放一定范围(range)或者列表(list)的数据。一般情况下， Shardspace按照区域来划分，比如美国区域的shard属于shardspace cust_america，欧洲的shard属于shardspace cust_europe
该sharing方法首先根据list或者range，分成若干个shardspace，然后再根据一致性hash进行分片
优点：
1、按照服务级别来划分shardspace，如硬件好的，作为shardspace_gold,硬件差一些的，划做shardspace_silver
2、根据业务需要，按照地域来分不同的shardgroup，如这里的shardgroup cust_america和shardgroup cust_euro
3、Subpartitions with Sharding: Sharding基于表分区，因此子分区(Subpartitions)技术同样适用于Sharding
本次实验环境:
· 一共3台Host，即sdb，shared1、shared2，系统CENTOS 7（当前环境为单机环境）
· 在Host sdb上安装Shard Director（对Sharding的集中部署和管理）和 Shard Catalog（是一个Oracle数据库，用于集中存储管理SDB配置信息，是SDB的核心）以及安装GSM软件（即SDB服务器上需要安装数据库软件以及GSM软件，其他Shared节点只需要安装数据库软件）
· 在Host shared1和Host shared2上各安装一个Shard（即物理数据库）
一、下载安装软件：参考网址链接：http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html
软件下载：
OracleDatabase 12c Release 2
OracleDatabase 12c Release 2 Global Service Manager (GSM/GDS)
1、在所有节点上安装Oracle Database 12c Release 2(注：只安装软件，不创建DB)
当前环境为单机环境，具体安装数据库软件过程省略.....
2、在Shard Director 所在节点安装Oracle Database 12c Release 2 Global Service Manager (GSM/GDS)软件，其他节点无需安装GSM软件，本例中Shard Director 所在节点即HOST sdb，GSM安装过程比较简单，按照默认配置安装即可，但是不能和DB软件安装目录存放同一目录，否则报错:[INS-32025] The chosen installation conflicts with software alreaddy installed in the given Oracle home
ORACLE用户环境变量中
增加一行参数：GSM_HOME=/u01/oracle/gsm
修改PATH变量：PATH=$GSM_HOME/bin:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/oracm/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/jdbc
另外，安装GSM软件时，指定安装位置为/u01/oracle/gsm即可....
3、各个服务器主机hosts文件写上本机和各个shard node的IP解析
#cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.10.130 sdb
172.16.10.131 shared1
172.16.10.132 shared2
二、创建Shard Catalog database(SDB服务器上)
注意：DBCA建库，不要创建容器数据库CDB，只需要创建NON-CDB数据库(换一句话说，该特性针对于non-cdb数据库)，创建过程与普通数据库相同
dbca建库过程省略，建库过程中，不要勾选Create as Container database选项即可...其他建库过程忽略
三、配置GSM/Shard director(SDB服务器上)
1、配置监听
oracle@sdb:/u01/oracle/12.2.0/network/admin>pwd
/u01/oracle/12.2.0/network/admin
oracle@sdb:/u01/oracle/12.2.0/network/admin>vi listener.ora
添加如下：
ADR_BASE_LISTENER1 = /u01/oracle
LISTENER = (
DESCRIPTION_LIST = (
DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.130)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER = (
SID_LIST = (
SID_DESC =
(GLOBAL_DBNAME = sdb)
(ORACLE_HOME = /u01/oracle/12.2.0)
(SID_NAME=sdb)
)
)
oracle@sdb:/u01/oracle/12.2.0/network/admin>vi tnsnames.ora
SDB = (
DESCRIPTION = (
ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.130)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sdb)
)
)
重启监听
oracle@sdb:/u01/oracle/12.2.0/network/admin>lsnrctl stop
oracle@sdb:/u01/oracle/12.2.0/network/admin>lsnrctl start
2、解锁 GSMCATUSER 用户，shard director 通过该GSMCATUSER 用户连接到shard catalog database
oracle@sdb:/u01/oracle/12.2.0/network/admin>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 7 15:56:43 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter user gsmcatuser identified by yunq111 account unlock;
User altered.
3、创建管理用户mygds，用户mygds用于存储Sharding管理信息，GDSCTL接口通过用户mygds连接到catalog数据库
SQL> create user mygds identified by yunq111;
User created.
SQL> grant connect, create session, gsmadmin_role to mygds;
Grant succeeded.
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
Grant succeeded.
4、在SDB服务器（catalog 数据库/shard director所在节点)，创建shard catalog，在shard catalog中配置remote scheduler agent.
注：GDSCTL是一个命令行工具，用于管理和配置Global Data Services framework
参数含义：
-user : 指定管理用户，在前面步骤中创建的catalog database管理用户mygds
-database : 指定catalog database 信息，catalog 数据库(SDB服务器)的主机名或者IP:监听器port: catalog 数据库db_name（即sdb服务器上物理数据库名，dbca建库名）
-sdb : 指定sharded database name（即sdb逻辑数据库名，可任意取）
-agent_port: 设置端口，用于shard节点agent连接到GSM
-agent_password: 设置密码，用于shard节点agent连接到GSM
-region:地区的概念。可以指一个数据中心，也可以指一个地域（–region region1主端，region2备端）
注：每个region需要至少一个shard director，可以最多有五个shard director
oracle@sdb:/u01>gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Thu Jun 07 16:03:27 CST 2018
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 172.16.10.130:1521:sdb -chunks 12 -user mygds/yunq111 -sdb shsdb -region region1, region2 -agent_port 8888 -agent_password yunq111
Catalog is created
5、创建和启动shard director.
参数含义：
-gsm: 指定shard director名称
-listener: 指定shard director的监听端口，注意不能与数据库的listener端口冲突
-catalog: 指定catalog database 信息，catalog数据库的主机名:监听器port: catalog 数据库db_name
-pwd: mygds用户密码(gdsctl接口所属用户密码，即当前用户名为mygds)
添加sharddirectory1(用于主)
GDSCTL>add gsm -gsm sharddirector1 -listener 1522 -pwd yunq111 -catalog 172.16.10.130:1521:sdb -region region1
GSM successfully added
GDSCTL>start gsm -gsm sharddirector1
GSM is started successfully
GDSCTL>
6、 添加操作系统认证
sdb节点：
GDSCTL>add credential -credential cre_oracle -osaccount oracle -ospassword yunq111
GSM-45034: Connection to GDS catalog is not established
GDSCTL>start gsm -gsm sharddirector1 --启动shared director,并连接Catalog
GSM is started successfully
GDSCTL>add credential -credential cre_oracle -osaccount oracle -ospassword yunq111
Catalog connection is established
The operation completed successfully
7、在所有的shard节点分别执行Agent注册
7.1、节点互信
三台机器（sdb、shared1、shared2）ORACLE用户配置互信(使用ORACLE自带互信脚本，Oracle 11G及以上才有，10G没，但是可以尝试拷贝该脚本使用)
oracle@sdb:/home/oracle>cd /u01/database/sshsetup/
oracle@sdb:/u01/database/sshsetup>./sshUserSetup.sh -user oracle -hosts "sdb sdbstandby shared1 shared2" -advanced -noPromptPassphrase
互信验证：
ssh shared1 date
ssh shared2 date
ssh sdb date
7.2、shared节点Agentz注册
Shared1节点：
oracle@sdb:/u01>ssh shared1
oracle@shared1:/home/oracle>schagent -start
Scheduler agent started using port 21137
oracle@shared1:/home/oracle>schagent -status
Agent running with PID 28500
Agent_version:12.2.0.1.2
Running_time:00:00:36
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/oracle/12.2.0/
ORACLE_BASE:/u01/oracle
Port:21137
Host:shared1
--密码yunq111和端口8888是在第4步创建shardcatalog时设置的:
参数：-registerdatabase SDB数据库服务器IP
oracle@shared1:/home/oracle>echo yunq111 | schagent -registerdatabase 172.16.10.130 8888
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
--创建shard1 数据库的数据文件存储路径
oracle@shared1:/home/oracle>mkdir -p /u01/oradata/sdb/shsdb
Shared2节点：
oracle@sdb:/u01>ssh shared2
oracle@shared2:/home/oracle>schagent -start
Scheduler agent started using port 26909
oracle@shared2:/home/oracle>schagent -status
Agent running with PID 19218
Agent_version:12.2.0.1.2
Running_time:00:00:11
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/oracle/12.2.0/
ORACLE_BASE:/u01/oracle
Port:26909
Host:shared2
--密码yunq111和端口8888是在第4步创建shardcatalog时设置的:
oracle@shared2:/home/oracle>echo yunq111 | schagent -registerdatabase 172.16.10.130 8888
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
--创建shard1 数据库的数据文件存储路径
oracle@shared2:/home/oracle>mkdir -p /u01/oradata/sdb/shsdb
四、部署system-managed SDB（本实例部署系统管理的SDB）操作都在sdb服务器gdsctl命令窗口内
1、在SDB服务器上sdb连接到shard director/GSM服务器(sdb)，设置当前session为sharddirector1 分片目录(shard director)
oracle@sdb:/home/oracle>gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Fri Jun 08 09:43:19 CST 2018
Copyright (c) 2011, 2016, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to SHARDDIRECTOR1
GDSCTL>set gsm -gsm sharddirector1
GDSCTL>connect mygds/yunq111
Catalog connection is established
GDSCTL>
2、添加shardgroup， shardgroup是一组shard的集合，shardgroup名称为primary_shardgroup（可随意命名），
-deploy_as primary表示这个group中的shard都是主库
-deploy_as active_standby表示这个group中的shard都是备库
GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
The operation completed successfully
GDSCTL>
3、将每个shard地址添加到catalog的valid node checking for registration (VNCR)列表，并且创建shard
添加shared1节点：
GDSCTL>add invitednode shared1 --shared节点主机名
参数解析：
-shardgroup：指定分片组
-destination：shared服务器主机名（当前为shared1、shared2）
-credential：sdb服务器系统验证身份标识，参考第三步骤的第6小步
GDSCTL>create shard -shardgroup primary_shardgroup -destination shared1 -credential cre_oracle -sys_password yunq111
GSM-45029: SQL error
ORA-03710: directory does not exist or is not writeable at destination: $ORACLE_BASE/oradata
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 7469
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4953
ORA-27436: Scheduler agent operation failed with message: Agent Error: /u01/oracle/oradata/shard_check.txt (No such file or directory)
ORA-06512: at "SYS.DBMS_ISCHED", line 3638
ORA-06512: at "SYS.DBMS_ISCHED", line 10444
ORA-06512: at "SYS.DBMS_ISCHED", line 5344
ORA-06512: at "SYS.DBMS_ISCHED", line 10439
ORA-06512: at "SYS.DBMS_ISCHED", line 3631
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2414
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 164
ORA-27436: Scheduler agent operation failed with message: Agent Error: /u01/oracle/oradata/shard_check.txt (No such file or directory)
ORA-06512: at "SYS.DBMS_ISCHED", line 3638
ORA-06512: at "SYS.DBMS_ISCHED", line 10444
ORA-06512: at "SYS.DBMS_ISCHED", line 5344
ORA-06512: at "SYS.DBMS_ISCHED", line 10439
ORA-06512: at "SYS.DBMS_ISCHED", line 3631
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2414
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 157
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4940
ORA-06512: at line 1
创建shared 服务器增加到分片组报错，是因为shared服务器无法找到数据文件存放路径，创建数据文件存放路径为$ORACLE_BASE/oradata，但是我们创建在/u01/oradata/sdb/shsdb，所以所有shared服务器上删除之前的，重新创建数据文件存放路径
删除：
oracle@shared1:/home/oracle>rm -rf /u01/oradata
oracle@shared2:/home/oracle>rm -rf /u01/oradata
创建：
oracle@shared1:/u01>mkdir -p $ORACLE_BASE/oradata
oracle@shared2:/home/oracle>mkdir -p $ORACLE_BASE/oradata
再次SDB服务器上运行语句
GDSCTL>create shard -shardgroup primary_shardgroup -destination shared1 -credential cre_oracle -sys_password yunq111
GSM-45029: SQL error
ORA-03710: directory does not exist or is not writeable at destination: $ORACLE_BASE/fast_recovery_area
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 7469
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4978
ORA-27436: Scheduler agent operation failed with message: Agent Error: /u01/oracle/fast_recovery_area/shard_check.txt (No such file or directory)
ORA-06512: at "SYS.DBMS_ISCHED", line 3638
ORA-06512: at "SYS.DBMS_ISCHED", line 10444
ORA-06512: at "SYS.DBMS_ISCHED", line 5344
ORA-06512: at "SYS.DBMS_ISCHED", line 10439
ORA-06512: at "SYS.DBMS_ISCHED", line 3631
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2414
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 164
ORA-27436: Scheduler agent operation failed with message: Agent Error: /u01/oracle/fast_recovery_area/shard_check.txt (No such file or directory)
ORA-06512: at "SYS.DBMS_ISCHED", line 3638
ORA-06512: at "SYS.DBMS_ISCHED", line 10444
ORA-06512: at "SYS.DBMS_ISCHED", line 5344
ORA-06512: at "SYS.DBMS_ISCHED", line 10439
ORA-06512: at "SYS.DBMS_ISCHED", line 3631
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2414
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 157
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4965
ORA-06512: at line 1
报错变更，目标端不能找到快速闪回区域，再次shared节点创建
oracle@shared1:/home/oracle>mkdir -p $ORACLE_BASE/fast_recovery_area
oracle@shared2:/home/oracle>mkdir -p $ORACLE_BASE/fast_recovery_area
再次运行SDB服务器上语句，成功
GDSCTL>create shard -shardgroup primary_shardgroup -destination shared1 -credential cre_oracle -sys_password yunq111
The operation completed successfully
DB Unique Name: sh1
添加shared2节点：
GDSCTL>add invitednode shared2
GDSCTL>create shard -shardgroup primary_shardgroup -destination shared2 -credential cre_oracle -sys_password yunq111
The operation completed successfully
DB Unique Name: sh2
GDSCTL>
4、检查配置
所有配置：
GDSCTL>config
Regions
------------------------
region1
region2
GSMs
------------------------
sharddirector1
Sharded Database
------------------------
shsdb
Databases
------------------------
sh1
sh2
Shard Groups
------------------------
primary_shardgroup
Shard spaces
------------------------
shardspaceora
Services
------------------------
GDSCTL pending requests
------------------------
Command Object Status
------- ------ ------
Global properties
------------------------
Name: oradbcloud
Master GSM: sharddirector1
DDL sequence #: 0
查看sharedspace
GDSCTL>config shardspace
Shard space Chunks
----------- ------
shardspaceora 12
查看分片组
GDSCTL>config shardgroup
Shard Group Chunks Region Shard space
----------- ------ ------ -----------
primary_shardgroup 12 region1 shardspaceora
查看shared节点主机或IP地址
GDSCTL>config vncr
Name Group ID
---- --------
shared1
shared2
172.16.10.130
查看shard
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup U none region1 -
sh2 primary_shardgroup U none region1 -
5. 部署/deploy
Shard数据库部署过程采用静默安装方式
GDSCTL>deploy
TIPs:
deploy命令会调用远程每一个节点上的dbca去静默安装sharded database。我们可以通过dbca的日志文件去监控安装进度。
deploy 还会在catalogdatabase 上提交一些job来完成相关事务。我们可以查询dba_scheduler_jobs 来监控进度。
另外，GSM日志可以用于deploy过程的监控和诊断。GSM日志的位置可以通过如下命令查到
6.再次检查shard配置信息
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup Ok Deployed region1 ONLINE
sh2 primary_shardgroup Ok Deployed region1 ONLINE
可以看到当前状态是OK...也是online...
7.查看shared节点database
GDSCTL>databases
Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Registered instances:
shsdb%1
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
Registered instances:
shsdb%11
查看某个shard配置信息
GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shared1: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: ---
Failed DDL id:
Availability: ONLINE
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
8.查看gsm状态，监听日志位置以及trc文件
GDSCTL>status gsm
Alias SHARDDIRECTOR1
Version 12.2.0.1.0
Start Date 08-JUN-2018 09:23:15
Trace Level off
Listener Log File /u01/oracle/diag/gsm/sdb/sharddirector1/alert/log.xml
Listener Trace File /u01/oracle/diag/gsm/sdb/sharddirector1/trace/ora_3230_140229409452416.trc
Endpoint summary (ADDRESS=(HOST=sdb)(PORT=1522)(PROTOCOL=tcp))
GSMOCI Version 2.2.1
Mastership Y
Connected to GDS catalog Y
Process Id 3233
Number of reconnections 0
Pending tasks. Total 0
Tasks in process. Total 0
Regional Mastership TRUE
Total messages published 2
Time Zone +08:00
Orphaned Buddy Regions:
None
GDS region region1
至此，Sharded Database (SDB) 安装布署到此完成。SDB的安装布署非常容易，几乎所有的管理配置都是通过GDSCTL的几条简单命令完成的。另外，Oracle Sharding还高度整合了Oracle Data Guard：如果你想布署standby database，可以通过GDSCTL的一两条命令来定义，Oracle Sharding 会自动帮你布署好standbys
五、创建全局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.shsdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
Instance "shsdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
Instance "shsdb%11", name: "sh2", db: "sh2", region: "region1", status: ready.
六、创建用户和对象(SDB服务器上)
1. 在catalog数据库中创建业务用户
oracle@sdb:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 8 11:03:21 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>alter session enable shard ddl; --获取追踪当前会话数据库DDL语句相关信息
create user marvin identified by oracle;
grant all privileges to marvin ;
grant gsmadmin_role to marvin ;
grant select_catalog_role to marvin ;
grant connect, resource to marvin ;
grant dba to marvin ;
grant execute on dbms_crypto to marvin ;
2. 创建shard表空间
SQL> CREATE TABLESPACE SET MARVIN using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto);
Tablespace created.
3. 为duplicated tables创建表空间
SQL> CREATE TABLESPACE product_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
Tablespace created.
4. 创建创建root 表Customers（表家族中的根基表)
SQL> conn marvin/oracle; --连接业务用户
Connected.
SQL> ALTER SESSION ENABLE SHARD DDL;
Session altered.
SQL> CREATE SHARDED TABLE Customers
2 (
3 CustId VARCHAR2(60) NOT NULL,
4 FirstName VARCHAR2(60),
5 LastName VARCHAR2(60),
6 Class VARCHAR2(10),
7 Geo VARCHAR2(8),
8 CustProfile VARCHAR2(4000),
9 Passwd RAW(60),
10 CONSTRAINT pk_customers PRIMARY KEY (CustId),
11 CONSTRAINT json_customers CHECK (CustProfile IS JSON)
12 ) TABLESPACE SET MARVIN
13 PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
Table created.
5. 创建其他sharded table（表家族的被切割分片的表）
创建sharded table Orders：
SQL> CREATE SHARDED TABLE Orders
2 (
3 OrderId INTEGER NOT NULL,
4 CustId VARCHAR2(60) NOT NULL,
5 OrderDate TIMESTAMP NOT NULL,
6 SumTotal NUMBER(19,4),
7 Status CHAR(4),
8 CONSTRAINT pk_orders PRIMARY KEY (CustId, OrderId),
9 CONSTRAINT fk_orders_parent FOREIGN KEY (CustId)
10 REFERENCES Customers ON DELETE CASCADE
11 ) PARTITION BY REFERENCE (fk_orders_parent);
Table created.
SQL> CREATE SEQUENCE Orders_Seq; --为表字段OrderId创建序列
Sequence created.
创建sharded table LineItems：
SQL> CREATE SHARDED TABLE LineItems
2 (
3 OrderId INTEGER NOT NULL,
4 CustId VARCHAR2(60) NOT NULL,
5 ProductId INTEGER NOT NULL,
6 Price NUMBER(19,4),
7 Qty NUMBER,
8 CONSTRAINT pk_items PRIMARY KEY (CustId, OrderId, ProductId),
9 CONSTRAINT fk_items_parent FOREIGN KEY (CustId, OrderId)
10 REFERENCES Orders ON DELETE CASCADE
11 ) PARTITION BY REFERENCE (fk_items_parent);
Table created.
6. 创建duplicated tables.
In this example, the Products table is a duplicated object.
SQL> CREATE DUPLICATED TABLE Products
(
2 3 ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4 Name VARCHAR2(128),
5 DescrUri VARCHAR2(128),
6 LastPrice NUMBER(19,4)
7 ) TABLESPACE product_tsp;
Table created.
7. 检查是否有错误
GDSCTL>connect mygds/yunq111
Catalog connection is established
GDSCTL>show ddl --之所以可以show ddl，是因为12C新特性可以在会话级别或者系统级别捕获DDL记录
id DDL Text Failed shards
-- -------- -------------
7 grant execute on dbms_crypto to marvin
8 CREATE TABLESPACE SET MARVIN using te...
9 CREATE TABLESPACE product_tsp datafil...
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 "MARVIN"."PR...
15 CREATE OR REPLACE FUNCTION PasswCreat...
16 CREATE OR REPLACE FUNCTION PasswCheck...
8.检查每个shard是否有错误
GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shared1:1521/sh1:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: <<<<<<<<<<<<没有DDL错误
DDL Error: ---
Failed DDL id:
Availability: ONLINE
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_rw_srvc Yes Enabled
忽略shard节点sh2输出.....
七、验证环境-表空间/chunks
1、检查chunks信息(SDB服务器)
前面创建shardcatalog时指定chunks为12，因此后续创建shard table分配12个chunks
GDSCTL>config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 6
sh2 7 12
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
MARVIN 100
PRODUCT_TSP 100
SYSAUX 520
SYSTEM 810
UNDOTBS1 70
USERS 5
6 rows selected.
2、在shard节点检查表空间和chunks信息
修改shard1服务器节点环境变量ORACLE_SID=sh1
修改shard2服务器节点环境变量ORACLE_SID=sh2
否则，无法进入数据库....
shard1节点：
--表空间
oracle@shared1:/u01>sqlplus / as sysdba
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
C001MARVIN 100
C002MARVIN 100
C003MARVIN 100
C004MARVIN 100
C005MARVIN 100
C006MARVIN 100
MARVIN 100
PRODUCT_TSP 100
SYSAUX 470
SYSTEM 800
UNDOTBS1 70
TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
12 rows selected.
创建了6个表空间，分别是C001MARVIN~ 表空间C006MARVIN，因为设置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 sh1
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions
2 where tablespace_name like 'C%MARVIN' order by tablespace_name;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
ORDERS CUSTOMERS_P1 C001MARVIN
CUSTOMERS CUSTOMERS_P1 C001MARVIN
LINEITEMS CUSTOMERS_P1 C001MARVIN
CUSTOMERS CUSTOMERS_P2 C002MARVIN
LINEITEMS CUSTOMERS_P2 C002MARVIN
ORDERS CUSTOMERS_P2 C002MARVIN
CUSTOMERS CUSTOMERS_P3 C003MARVIN
ORDERS CUSTOMERS_P3 C003MARVIN
LINEITEMS CUSTOMERS_P3 C003MARVIN
ORDERS CUSTOMERS_P4 C004MARVIN
CUSTOMERS CUSTOMERS_P4 C004MARVIN
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS CUSTOMERS_P4 C004MARVIN
CUSTOMERS CUSTOMERS_P5 C005MARVIN
LINEITEMS CUSTOMERS_P5 C005MARVIN
ORDERS CUSTOMERS_P5 C005MARVIN
CUSTOMERS CUSTOMERS_P6 C006MARVIN
LINEITEMS CUSTOMERS_P6 C006MARVIN
ORDERS CUSTOMERS_P6 C006MARVIN
18 rows selected.
shard2节点：
--表空间
oracle@shared2:/u01>sqlplus / as sysdba
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
C007MARVIN 100
C008MARVIN 100
C009MARVIN 100
C00AMARVIN 100
C00BMARVIN 100
C00CMARVIN 100
MARVIN 100
PRODUCT_TSP 100
SYSAUX 470
SYSTEM 800
UNDOTBS1 70
TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
12 rows selected.
创建了6个表空间，分别是C007MARVIN~ 表空间C00CMARVIN，因为设置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 sh1
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions
2 where tablespace_name like 'C%MARVIN' order by tablespace_name;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
ORDERS CUSTOMERS_P1 C001MARVIN
CUSTOMERS CUSTOMERS_P1 C001MARVIN
LINEITEMS CUSTOMERS_P1 C001MARVIN
CUSTOMERS CUSTOMERS_P2 C002MARVIN
LINEITEMS CUSTOMERS_P2 C002MARVIN
ORDERS CUSTOMERS_P2 C002MARVIN
CUSTOMERS CUSTOMERS_P3 C003MARVIN
ORDERS CUSTOMERS_P3 C003MARVIN
LINEITEMS CUSTOMERS_P3 C003MARVIN
ORDERS CUSTOMERS_P4 C004MARVIN
CUSTOMERS CUSTOMERS_P4 C004MARVIN
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS CUSTOMERS_P4 C004MARVIN
CUSTOMERS CUSTOMERS_P5 C005MARVIN
LINEITEMS CUSTOMERS_P5 C005MARVIN
ORDERS CUSTOMERS_P5 C005MARVIN
CUSTOMERS CUSTOMERS_P6 C006MARVIN
LINEITEMS CUSTOMERS_P6 C006MARVIN
ORDERS CUSTOMERS_P6 C006MARVIN
18 rows selected.
3. 在catalog数据库上检查ckunks（sdb服务器)
oracle@sdb:/u01>sqlplus / as sysdba
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
sh2 6
4. 验证环境-tables
Catalog数据库:sdb服务器
SQL> conn marvin/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS
MLOG$_PRODUCTS
RUPD$_PRODUCTS
6 rows selected.
--shard节点shard1和shard2
SQL> conn marvin/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS
八、访问单独一个shard
在连接串中指定sharding key，那么GSM/shard director将请求连接到对应的一个shard
参数含义：
marvin – 是业务用户，
(host=sdb)(port=1522) – 是GSM/shard director 监听地址
service_name=oltp_rw_srvc.shsdb.oradbcloud – 是前面创建的全局service名 + create shardcatalog 中指定的sdb逻辑数据库名shsdb，从status service中可以看到service_name
GDSCTL>status service
Service "oltp_rw_srvc.shsdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
Instance "shsdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
Instance "shsdb%11", name: "sh2", db: "sh2", region: "region1", status: ready.
oracle@sdb:/home/oracle>sqlplus marvin/oracle@'(description=(address=(protocol=tcp)(host=172.16.10.130)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shsdb.oradbcloud)(region=region1)(SHARDING_KEY=Marvin)))'
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 11 10:16:05 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 11 2018 10:07:58 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
sh1
可以看到指定Sharding_key=Marvin，当前路由到的shard服务器是sh1(即shared1服务器)
--插入数据
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('james.parker@x.bogus', 'James', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
可以看到,当前指定了Sharding_key不是Marvin，则DML插入语句报错无法插入，但带有Sharing_key为Marvin则可以插入,见如下效果...
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('Marvin', 'James', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> commit;
Commit complete.
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('Marvin', 'James1', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
*
ERROR at line 1:
ORA-00001: unique constraint (MARVIN.PK_CUSTOMERS) violated
也就是说，当前分片键值，要是通过单独访问某个shard进行插入，得累死，每次都得重新指定sharding-key插入，无法插入相同的(唯一键约束）或者其他的指定的sharding-key
SQL> column custid format a20
SQL> column firstname format a15
SQL> column lastname format a15
SQL> select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- --------
Marvin James Parker Gold east
SQL> UPDATE CUSTOMERS set firstname='King' where CUSTID='Marvin';
1 row updated.
SQL> commit;
Commit complete.
SQL> DELETE FROM CUSTOMERS WHERE CUSTID='Marvin';
1 row deleted.
SQL>rolllback;
Rollback complete.
DML事务语句都是可以的，但是仅限于操作当前指定的Sharding-key键值....
--重新指定sharding-key插入
oracle@sdb:/home/oracle>sqlplus marvin/oracle@'(description=(address=(protocol=tcp)(host=172.16.10.130)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shsdb.oradbcloud)(region=region1)(SHARDING_KEY=Marvinn)))'
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 11 10:26:57 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 11 2018 10:12:14 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
sh2
插入语句限制跟上述一样...
九、访问多个Shared
如果在连接串中指定sharding key，那么GSM/shard director将请求连接到对应的一个shard。
如果没有指定sharding key，那么session和coordinator database (shard catalog)建立连接,然后再分别到需要(prund)的shard中查询，最后再整合。
链接到catalog数据库查询
--查看service服务名
oracle@sdb:/home/oracle>lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-JUN-2018 10:36:04
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.10.130)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 07-JUN-2018 18:42:56
Uptime 3 days 15 hr. 53 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/12.2.0/network/admin/listener.ora
Listener Log File /u01/oracle/diag/tnslsnr/sdb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.10.130)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb)(PORT=8888))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
Instance "sdb", status READY, has 1 handler(s) for this service...
Service "sdb" has 2 instance(s).
Instance "sdb", status UNKNOWN, has 1 handler(s) for this service...
Instance "sdb", status READY, has 1 handler(s) for this service...
Service "sdbXDB" has 1 instance(s).
Instance "sdb", status READY, has 1 handler(s) for this service...
The command completed successfully
注意：当前端口是1521端口（Catalog数据库服务端口），而1522端口是指定Sharding_key连接某个Shard服务器所用端口...
oracle@sdb:/home/oracle>sqlplus marvin/oracle@172.16.10.130:1521/GDS/$CATALOG.oradbcloud
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 11 10:38:34 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 11 2018 10:37:58 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('Marvinn', 'James1', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('Bob', 'James1', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('David', 'James1', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('Sidney', 'James1', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> commit;
Commit complete.
可以看到，当前是可以进行任何的DML语句，它自动路由按照分片键值HASH规则分片到对应的Shard服务器....但是就是感觉DML语句有点慢，不知道是不是我服务器和网络差的原因....
数据验证...
shared1服务器节点:
oracle@shared1:/home/oracle>sqlplus marvin/oracle
SQL> column custid format a20
SQL> column firstname format a15
SQL> column lastname format a15
SQL> select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- --------
David James1 Parker Gold east
Sidney James1 Parker Gold east
shared2服务器节点:
oracle@shared2:/home/oracle>sqlplus marvin/oracle
SQL> column custid format a20
SQL> column firstname format a15
SQL> column lastname format a15
SQL> select custid, FirstName, LastName, class, geo from customers;
CUSTID FIRSTNAME LASTNAME CLASS GEO
-------------------- --------------- --------------- ---------- --------
Marvinn James1 Parker Gold east
Bob James1 Parker Gold east
Marvin King Parker Gold east
可以看到数据确实已经被分片存放...而且由于之前我们更改了CUSTID=Marvin得字段Firstname数据为King,则它从原来的sh1，自动路由分片到了sh2服务器，这可能是由于hash分片规则引起的...
--那么优化器判断访问一个shard还是访问多个shard
SQL> set timing on;
SQL> set termout on
SQL> set linesize 120
SQL> set echo on
SQL> column firstname format a20
SQL> column lastname format a20
SQL> SELECT FirstName,LastName, geo, class FROM Customers
2 WHERE CustId in ('Marvin', 'David') AND class != 'free' ORDER
3 BY geo, class;
FIRSTNAME LASTNAME GEO CLASS
-------------------- -------------------- -------- ----------
King Parker east Gold
James1 Parker east Gold
Elapsed: 00:00:10.62
查看两条在不通shard数据竟然要10s....看下执行计划
SQL> SELECT FirstName,LastName, geo, class FROM Customers
2 WHERE CustId in ('Marvin', 'David') AND class != 'free' ORDER
3 BY geo, class;
Execution Plan
----------------------------------------------------------
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 |
-------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS173891' INTO PLAN_TABLE@! FOR SELECT
"A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS" FROM "CUSTOMERS" "A1" WHERE
("A1"."CUSTID"='David' OR "A1"."CUSTID"='Marvin') AND "A1"."CLASS"<>'free' /*
coord_sql_id=cya0frzt1c9yq */ (accessing 'ORA_SHARD_POOL@ORA_MULTI_TARGET' )
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
839 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
可以看到，两个不同shard访问数据最终合并成视图 VW_SHARD_5B3ACD5D，排序返回数据...消耗以及时间并不大...所以可以推测花费10s的时间主要在remote步骤，即网络访问和接受部分...跟网络有关...
总结：
1、单个访问Shard,通过全局服务名 + sharding-key访问，并且只能操作当前sharding-key数据
2、多个访问shard，即连接catalog数据库访问，自动分发路由到具体的shard，并且没有操作限制...
3、操作修改某个sharding-key数据，其根绝hash分片规则，可能由原shard节点分配到另外一台shard节点，但这个并不影响应用使用，透明...上面示例就有这个佐证
至此….关于ORACLE 12C Shard 数据库得环境搭建完成….
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：RMAN Catalog备份脚本
- 下一篇：ORACLE 删除软件安装目录恢复处理
