CNDBA社区
在上篇我们了解了在SDB中如何执行DDL，如下：
Oracle 18c 必须掌握的新特性 Sharding系列（5） — SDB中执行DDL命令
https://www.cndba.cn/dave/article/3762
本篇我们了解一下Sharding 相关的其他操作。
Sharding中的操作都是在目录数据库上进行，而不是分片数据库。主要操作有创建用户，表空间集，表等。
1 创建用户并赋权
这里创建测试的用户shard，相关代码如下：
SQL>alter session enable shard ddl;
SQL>create user shard identified by oracle;
SQL>grant all privileges to shard;
SQL>grant gsmadmin_role to shard;
SQL>grant select_catalog_role to shard;
SQL>grant connect, resource, dba to shard;
SQL>grant execute on dbms_crypto to shard;
2创建表空间
为分片表创建表空间集：
SQL> create tablespace set shard_tbs_set using template (datafile size 100m autoextend on next 10m maxsize unlimited);
Tablespace created.
如果有LOB类型的字段，可以为LOB字段创建单独的表空间：
SQL> create tablespace set lobts;
Tablespace created.
为复制表创建表空间：
SQL> create tablespace duplicat_tbs_set datafile size 100m autoextend on next 10m maxsize unlimited;
Tablespace created.
3 创建分片表
用shard用户连接数据库，然后创建，否则会报如下错误：
ORA-02572: A non all-shard user cannot operate on schema objects that are sharded or duplicated on all shards.
SQL> conn shard/oracle
Connected.
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 shard_tbs_set
partition by consistent hash (custid) partitions auto;
如果表中有LOB字段，可以把LOB字段存在单独表空间中，如：
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),
image BLOB,
CONSTRAINT pk_customers PRIMARY KEY (CustId),
CONSTRAINT json_customers CHECK (CustProfile IS JSON)
) TABLESPACE SET SHARD_TBS_SET
LOB(image) store as (TABLESPACE SET LOBTS1)
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
再创建几个相关联的分片表，这里创建的表都是以Customers表为父表，所以相关属性也会继承父表。如：存储的表空间，HASH分区等。
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);
创建一个序列:
SQL> create sequence orders_seq;
创建表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);
4 创建复制表
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 duplicat_tbs_set;
5 检查SQL语句执行情况
通过show ddl命令可以看到执行的操作没有问题。
[oracle@shardcatalog ~]$ gdsctl
GDSCTL: Version 18.0.0.0.0 - Production on Sat Oct 13 11:08:22 CST 2018
Copyright (c) 2011, 2018, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to SHARDDIRECTOR
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------
4 grant select_catalog_role to shard
5 grant connect, resource, dba to shard
6 grant execute on dbms_crypto to shard
7 create tablespace set shard_tbs_set u...
8 create tablespace duplicat_tbs_set da...
9 create sharded table customers ( ...
10 create sharded table orders ( ...
11 create sequence orders_seq
12 create sharded table lineitems ( ...
13 CREATE MATERIALIZED VIEW "SHARD"."PRO...
这里的SQL语句是不完整的，如果想要看完成的SQL语句可以通过下面SQL语句查看：
select ddl_text from gsmadmin_internal.ddl_requests
6 验证表空间是否在所有分片创建了
GDSCTL> config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 4
sh2 7 10
sh21 5 6
sh21 11 12
在分片上查看，每个分片上有4个chunk，表空间集中的表空间数是有chunk数量决定的。所以可以看到每个表空间由4个chunk组成。
#在catalog 上查询：
SQL> set lines 120
SQL> set pages 200
SQL> col tablespace_name for a20
SQL> select tablespace_name, bytes/1024/1024 mb from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
-------------------- ----------
DUPLICAT_TBS_SET 100
SHARD_TBS_SET 100
SYSAUX 530
SYSTEM 840
UNDOTBS1 305
USERS 5
6 rows selected.
在分片上查询：
SQL> select tablespace_name, bytes/1024/1024 mb from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
-------------------- ----------
C001SHARD_TBS_SET 100
C002SHARD_TBS_SET 100
C003SHARD_TBS_SET 100
C004SHARD_TBS_SET 100
DUPLICAT_TBS_SET 100
SHARD_TBS_SET 100
SYSAUX 530
SYSTEM 840
UNDOTBS1 300
USERS 5
10 rows selected.
#分片2：
SQL> select tablespace_name, bytes/1024/1024 mb from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
----------------------------------------------------------------------- ----------
C007SHARD_TBS_SET 100
C008SHARD_TBS_SET 100
C009SHARD_TBS_SET 100
C00ASHARD_TBS_SET 100
DUPLICAT_TBS_SET 100
SHARD_TBS_SET 100
SYSAUX 530
SYSTEM 840
UNDOTBS1 295
USERS 5
10 rows selected.
分片3：
SQL> select tablespace_name, bytes/1024/1024 mb from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------------------------------------ ----------
C005SHARD_TBS_SET 100
C006SHARD_TBS_SET 100
C00BSHARD_TBS_SET 100
C00CSHARD_TBS_SET 100
DUPLICAT_TBS_SET 100
SHARD_TBS_SET 100
SYSAUX 530
SYSTEM 840
UNDOTBS1 305
USERS 5
10 rows selected.
7 验证chunk和chunk表空间是否在所有分片上创建了
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
------------------------------------ ----------- ------------------------------
_gwm_db_unique_name string sh164
db_unique_name string sh164
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%SHARD_TBS_SET%' order by tablespace_name;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
ORDERS CUSTOMERS_P1 C001SHARD_TBS_SET
CUSTOMERS CUSTOMERS_P1 C001SHARD_TBS_SET
LINEITEMS CUSTOMERS_P1 C001SHARD_TBS_SET
CUSTOMERS CUSTOMERS_P2 C002SHARD_TBS_SET
ORDERS CUSTOMERS_P2 C002SHARD_TBS_SET
LINEITEMS CUSTOMERS_P2 C002SHARD_TBS_SET
ORDERS CUSTOMERS_P3 C003SHARD_TBS_SET
CUSTOMERS CUSTOMERS_P3 C003SHARD_TBS_SET
LINEITEMS CUSTOMERS_P3 C003SHARD_TBS_SET
ORDERS CUSTOMERS_P4 C004SHARD_TBS_SET
LINEITEMS CUSTOMERS_P4 C004SHARD_TBS_SET
CUSTOMERS CUSTOMERS_P4 C004SHARD_TBS_SET
12 rows selected.
在所有分片节点上执行查询。
8 在catalog数据库上验证chunk分布是否相同
在catalog数据库上执行：
SQL> set lines 120
SQL> col shard for a15
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 4
sh2 4
sh21 4
9 验证分片表和复制表是否创建了
在每个分片上查询表是否已成功创建。
SQL> conn shard/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
---------------------------------------------------------------------------------
PRODUCTS
MLOG$_PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS
RUPD$_PRODUCTS
6 rows selected.
10 创建数据测试
在SDB中插入数据：
SQL>insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd) values ('david.dai@www.cndba.cn','david','dai','gold','west',null,null);
SQL>insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd) values ('zhixin.dai@www.cndba.cn','zhixin','dai','gold','west',null,null);
SQL>insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd) values ('ahdba.dai@www.ahdba.cn','ahdba','dai','silver','east',null,null);
SQL>insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd) values ('cndba.dai@www.cndba.cn','cndba','dai','silver','east',null,null);
SQL>insert into orders(orderid, custid, orderdate,sumtotal,status) values (10,'david.dai@www.cndba.cn','1-oct-2018',null,null);
SQL>insert into orders(orderid, custid, orderdate,sumtotal,status) values (20,'zhixin.dai@www.cndba.cn','2-oct-2018',null,null);
SQL>insert into orders(orderid, custid, orderdate,sumtotal,status) values (30,'ahdba.dai@www.ahdba.cn','3-oct-2018',null,null);
SQL>insert into orders(orderid, custid, orderdate,sumtotal,status) values (40,'cndba.dai@www.cndba.cn','4-oct-2018',null,null);
SQL> commit;
Commit complete.
对duplicated table，这不存在上述2种的限制:
SQL> insert into products select rownum,dbms_random.STRING('U',8),dbms_random.STRING('A',64),round(dbms_random.value(1,1000),2) from dual connect by level<=20;
20 rows created.
SQL> commit;
Commit complete.
注意sharded table不允许跨shard做delete：
SQL> delete from customers;
delete from customers
*
ERROR at line 1:
ORA-02671: DML are not allowed on more than one shard
可以到每个shard node上去删除。
11 访问Sharding
注意这里查询监听状态的时候，要加上gsm的名称，否则查询的就是目录数据库监听的信息：
[dave@www.cndba.cn ~]$ lsnrctl status sharddirector
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2018 13:12:30
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(HOST=sdb)(PORT=1571)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=GDS$CATALOG.oradbcloud)))
STATUS of the LISTENER
------------------------
Alias SHARDDIRECTOR
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 21-OCT-2018 10:59:19
Uptime 0 days 2 hr. 13 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/18.3.0/db_1/network/admin/gsm.ora
Listener Log File /u01/app/oracle/diag/gsm/sdb/sharddirector/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb)(PORT=1571)))
Services Summary...
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
Instance "shardcat", status READY, has 1 handler(s) for this service...
Service "_MONITOR" has 1 instance(s).
Instance "SHARDDIRECTOR", status READY, has 1 handler(s) for this service...
Service "_PINGER" has 1 instance(s).
Instance "SHARDDIRECTOR", status READY, has 1 handler(s) for this service...
Service "oltp_rw_servcie.cust_sdb.oradbcloud" has 3 instance(s).
Instance "cust_sdb%1", status READY, has 1 handler(s) for this service...
Instance "cust_sdb%11", status READY, has 1 handler(s) for this service...
Instance "cust_sdb%21", status READY, has 1 handler(s) for this service...
The command completed successfully
如果Sharding环境中还没有service，可以在gdsctl中使用add service目录来添加。
在客户端tnsnames.ora中添加以下内容：
[dave@www.cndba.cn admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/18.3.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SH1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shard1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sh1)
)
)
LISTENER_SH1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = shard1)(PORT = 1521))
shardcat =
(DESCRIPTION =
(ADDRESS = (HOST = 192.168.1.200)(PORT = 1521)(PROTOCOL = tcp))
(CONNECT_DATA =
(SERVICE_NAME = shardcat)
)
)
[dave@www.cndba.cn admin]$
连接测试，这里我们连接的sharding catalog数据库：
[dave@www.cndba.cn admin]$ sqlplus shard/oracle@shardcat
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 21 14:44:03 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Oct 21 2018 13:55:39 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------------------------------------
shardcat
这里我们直接连接的是分片1，此时只能查询到分片1上的数据：
[dave@www.cndba.cn admin]$ sqlplus shard/oracle@sh1
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 21 14:44:20 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Oct 21 2018 13:36:26 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------------------------------------
sh1
SQL> col custid for a30
SQL> select orderid,custid from orders;
ORDERID CUSTID
---------- ------------------------------
10 david.dai@www.cndba.cn
通过sevice连接到sharding，这个是我们应该使用的方式，可以通过分片键自动连接到对应的分片上：
[dave@www.cndba.cn admin]$ cat tnsnames.ora
sdb =
(DESCRIPTION =
(ADDRESS = (HOST = 192.168.1.200)(PORT = 1522)(PROTOCOL = tcp))
(CONNECT_DATA =
(SERVICE_NAME = oltp_rw_servcie.cust_sdb.oradbcloud)
(region=region1)
(SHARDING_KEY=david.dai@www.cndba.cn)
)
)
[dave@www.cndba.cn admin]$
测试可以可以看到，会根据分区键自动连接到分片1上：
[dave@www.cndba.cn admin]$ sqlplus shard/dave@www.cndba.cn
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 21 15:08:38 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Oct 21 2018 14:49:34 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------------------------------------
sh1
SQL> col custid for a30
SQL> select orderid,custid from orders;
ORDERID CUSTID
---------- ------------------------------
10 david.dai@www.cndba.cn
如果没有指定sharding key，sharding 会随机的选择一个分片进行连接。在Tnsnames.ora 文件中添加以下内容：
sdb2 =
(DESCRIPTION =
(ADDRESS = (HOST = 192.168.1.200)(PORT = 1522)(PROTOCOL = tcp))
(CONNECT_DATA =
(SERVICE_NAME = oltp_rw_servcie.cust_sdb.oradbcloud)
)
)
[dave@www.cndba.cn admin]$ sqlplus shard/dave@www.cndba.cn2
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 21 15:18:17 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Oct 21 2018 15:10:35 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------------------------------------
sh1
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[dave@www.cndba.cn admin]$ sqlplus shard/dave@www.cndba.cn2
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 21 15:19:09 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Oct 21 2018 15:14:08 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------------------------------------
sh21
所以要查询单个分片的数据，可以直接连分片，也可以通过GSM的分片键自动来连接，如果要查询所有数据，那么直接连接catalog数据库。
shardcat=
(DESCRIPTION =
(ADDRESS = (HOST = 192.168.1.200)(PORT = 1522)(PROTOCOL = tcp))
(CONNECT_DATA =
(SERVICE_NAME = GDS$CATALOG.oradbcloud)
)
)
[dave@www.cndba.cn admin]$ sqlplus shard/oracle@shardcat
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 21 15:27:42 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Oct 21 2018 14:44:04 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> col custid for a30
SQL> select orderid,custid from orders;
ORDERID CUSTID
---------- ------------------------------
10 david.dai@www.cndba.cn
30 ahdba.dai@www.ahdba.cn
40 cndba.dai@www.cndba.cn
20 zhixin.dai@www.cndba.cn
在分片目录数据库上，还有类似于多租户过来分片查询的功能，在查询时加上ora_shard_id即可，如下：
SQL>select ora_shard_id, instance_name from shards(sys.v_$instance);
ORA_SHARD_ID INSTANCE_NAME
------------ --------------------------------
1 sh1
11 sh2
21 sh21
SQL> select orderid,custid from orders where ora_shard_id=1;
ORDERID CUSTID
---------- ------------------------------
10 david.dai@www.cndba.cn
SQL> select orderid,custid from orders where ora_shard_id=21;
ORDERID CUSTID
---------- ------------------------------
40 cndba.dai@www.cndba.cn
20 zhixin.dai@www.cndba.cn
12 删除表和表空间集
在Sharding 环境中，可能存在删除表和表空间的需求，所以这里演示相关操作如下：
删除分片表和复制表：
SQL> drop table lineitems;
Table dropped.
SQL> drop table orders;
Table dropped.
SQL> drop table customers;
Table dropped.
SQL> drop table products;
Table dropped.
SQL> drop tablespace duplicat_tbs_set including contents;
Tablespace dropped.
SQL> drop tablespace shard_tbs_set including contents;
drop tablespace shard_tbs_set including contents
*
ERROR at line 1:
ORA-02496: cannot alter or drop an individual chunk tablespace
这里报错，不能删除chunk tablespace，需要使用drop tablespace set命令：
SQL> drop tablespace set shard_tbs_set including contents;
drop tablespace set shard_tbs_set including contents
*
ERROR at line 1:
ORA-02557: cannot operate on sharded objects when shard DDL is disabled
这里这里继续报错，提示没有启用shard dll：
SQL> alter session enable shard ddl;
Session altered.
SQL> drop tablespace set shard_tbs_set including contents;
Tablespace dropped.
版权声明：本文为博主原创文章，未经博主允许不得转载。
