签到成功

知道了

CNDBA社区CNDBA社区

Oracle 18c 必须掌握的新特性 Sharding系列(6) -- Sharding相关操作

2019-10-30 16:55 2433 0 原创 Oracle 18c
作者: dave

在上篇我们了解了在SDB中如何执行DDL,如下:

https://www.cndba.cn/cndba/dave/article/3763

Oracle 18c 必须掌握的新特性 Sharding系列(5) — SDB中执行DDL命令
https://www.cndba.cn/dave/article/3762

本篇我们了解一下Sharding 相关的其他操作。

Sharding中的操作都是在目录数据库上进行,而不是分片数据库。主要操作有创建用户,表空间集,表等。

https://www.cndba.cn/cndba/dave/article/3763
https://www.cndba.cn/cndba/dave/article/3763

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创建表空间

为分片表创建表空间集:https://www.cndba.cn/cndba/dave/article/3763https://www.cndba.cn/cndba/dave/article/3763https://www.cndba.cn/cndba/dave/article/3763

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命令可以看到执行的操作没有问题。https://www.cndba.cn/cndba/dave/article/3763https://www.cndba.cn/cndba/dave/article/3763

[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的名称,否则查询的就是目录数据库监听的信息:

https://www.cndba.cn/cndba/dave/article/3763

[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数据库:

https://www.cndba.cn/cndba/dave/article/3763

[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.

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2239
    原创
  • 3
    翻译
  • 547
    转载
  • 186
    评论
  • 访问:6666476次
  • 积分:4249
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ