首页
登录 | 注册

Oracle 12C Sharding部署和测试

Oracle 12C Sharding部署和测试

日期:2019-02-26

作者:陈举超


 

一:环境说明



OSCentOS Linux release 7.5

DBOracle 12.2.0.1.0

GSMOCI2.2.1

本次使用三台虚拟机进行安装:

其中:

(1) db01主机上安装Shard DirectorShard catalog,也就是分别安装GSMORACLE 12.2 RDBMSnon-cdb Database(GSMORACLE 12.2环境变量需要手动切换)

(2)db02主机上安装Shard1,也就是ORACLE 12.2 RDBMS,不需要创建数据库;

(3) db03主机上安装Shard2,也就是ORACLE 12.2 RDBMS,不需要创建数据库;

主机

组件

SID

Oracle_Home

db01

Shard Director

/u01/app/oracle/product/12.2.0/gsm_1

db01

Shard catalog

db01

/u01/app/oracle/product/12.2.0/db_1

db02

Shard1

sh1

/u01/app/oracle/product/12.2.0/db_1

db03

Shard2

sh2

/u01/app/oracle/product/12.2.0/db_1

安装文件下载链接如下:

https://www.oracle.com/technetwork/cn/database/options/partitioning/downloads/index.html

Oracle软件下载


GSM下载


二:安装GSM/Shard catalog/ORACLE 12.2 RDBMS

2.1 Gsm安装



2.2 ORACLE 12.2 RDBMS安装

2.3 创建并启动监听

Netca

2.4 创建Shard Catalog database

Shard catalog  服务器gsm1  创建 non-cdb数据库。创建过程与普通数据库相同。

三:配置GSM/Shard director

https://blogs.oracle.com/database4cn/12c-oracle-sharding

3.1 解锁用户

db01服务器(catalog 数据库/shard director),连接到Sharding catalog数据库, 解锁 GSMCATUSER 用户,shard director 通过GSMCATUSER 用户连接到shard catalog database

SQL> alter user gsmcatuser identified by oracle account unlock;

3.2创建管理用户mygds

catalog数据库,创建管理用户mygds,用户mygds用于存储Sharding管理信息,GDSCTL接口通过用户mygds连接到catalog数据库。

SQL> create user mygds identified by oracle;

SQL> grant connect, create session, gsmadmin_role to mygds;

SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;

3.3 启动listener

db01服务器(catalog 数据库/shard director),启动listener

3.4创建shard catalog配置remote scheduler agent

db01服务器(catalog 数据库/shard director),创建shard catalog,在shard catalog中配置remote scheduler agent.

参数含义:

-user : 指定管理用户,在前面步骤中创建的catalog database管理用户mygds

-database : 指定catalog database 信息,catalog 数据库的主机名:监听器port: catalog 数据库db_name

-sdb : 指定sharded database name

-agent_port: 设置端口,用于shard节点agent连接到GSM

-agent_password: 设置密码,用于shard节点agent连接到GSM

如果没有指定- sharding参数,默认是创建system-managed (default)类型

[oracle@db01 ~]$ export ORACLE_BASE=/u01/app/oracle

[oracle@db01 ~]$ export ORACLE_HOME=/u01/app/oracle/products/12.2.0/gsm_1

[oracle@db01 ~]$

export PATH=/u01/app/oracle/products/12.2.0/gsmh_11/bin:$PATH:$HOME/bin

[oracle@db01 ~]$ gdsctl

GDSCTL: Version 12.2.0.1.0 - Production on Mon Feb 25 10:38:54 CST 2019

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 db01:1521:db01 -chunks 12 -user mygds/oracle -sdb shdb -region region1, region2 -agent_port 8080 -agent_password oracle

Catalog is created

3.5 创建和启动shard director.

参数含义:

-gsm: 指定shard director名称

-listener: 指定shard director的监听端口,注意不能与数据库的listener端口冲突

-catalog: 指定catalog database 信息,catalog数据库的主机名:监听器port: catalog 数据库db_name

GDSCTL>add gsm -gsm sharddirector1 -listener 1522 -pwd oracle -catalog db01:1521:db01 -region region1

GSM successfully added

GDSCTL>start gsm -gsm sharddirector1

GSM 已成功启动

3.6 添加操作系统认证.

GDSCTL>add credential -credential cre_reg1 -osaccount oracle -ospassword oracle

操作已成功完成

3.7在所有的shard节点分别执行Agent

---db02节点执行

[oracle@db02 ~]$ schagent -start

Scheduler agent started using port 24409

[oracle@db02 ~]$ schagent -status

Agent running with PID 8537

Agent_version:12.2.0.1.2

Running_time:00:00:11

Total_jobs_run:0

Running_jobs:0

Platform:Linux

ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1

ORACLE_BASE:/u01/app/oracle

Port:24409

Host:db02

---db03节点执行

[oracle@db03 ~]$ schagent -start

Scheduler agent started using port 19249

[oracle@db03 ~]$ schagent -status

Agent running with PID 8344

Agent_version:12.2.0.1.2

Running_time:00:00:05

Total_jobs_run:0

Running_jobs:0

Platform:Linux

ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1

ORACLE_BASE:/u01/app/oracle

Port:19249

Host:db03

-----密码oracle和端口8080

[oracle@db02 ~]$ echo oracle | schagent -registerdatabase 192.168.2.226 8080

Agent Registration Password ? 

Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent

Agent Registration Successful!

[oracle@db03 ~]$ echo oracle | schagent -registerdatabase 192.168.2.226 8080

Agent Registration Password ? 

Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent

Agent Registration Successful!

3.8创建shard 数据库的数据文件存储路径

[oracle@db02 ~]$ mkdir /u01/app/oracle/oradata

[oracle@db02 ~]$ mkdir /u01/app/oracle/fast_recovery_area

[oracle@db03 ~]$ mkdir /u01/app/oracle/oradata

[oracle@db03 ~]$ mkdir /u01/app/oracle/fast_recovery_area

四:创建System-Managed SDB

4.1 连接gsm

Shard服务器 sd1 连接到shard director/GSM服务器(db01)

[oracle@db02 ~]$ ssh oracle@db01

……

oracle@db01's password:

Last login: Mon Feb 25 10:45:20 2019 from 192.168.70.249

[oracle@db01 ~]$ source .bash_profile

[oracle@db01 ~]$ gdsctl

GDSCTL: 版本 12.2.0.1.0 - Production, 时间: Mon Feb 25 11:16:04 CST 2019

版权所有 (c) 2011, 2016, Oracle。保留所有权利。

欢迎使用GDSCTL, 请键入 "help" 以获得信息。

当前 GSM 设置为 SHARDDIRECTOR1

GDSCTL>set gsm -gsm sharddirector1

GDSCTL>connect mygds/oracle

已建立目录连接

4.2添加shardgroup

shardgroup是一组shard的集合,shardgroup名称为primary_shardgroup-deploy_as primary表示这个group中的shard都是主库。

GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1

操作已成功完成

4. 3创建shard

将每个shard地址添加到catalogvalid node checking for registration (VNCR)列表,并且创建shard

GDSCTL> add invitednode db02 

GDSCTL>create shard -shardgroup primary_shardgroup -destination db02 -credential cre_reg1 -sys_password oracle

操作已成功完成

数据库的唯一名称: sh1

GDSCTL>add invitednode db03

GDSCTL>create shard -shardgroup primary_shardgroup -destination db03 -credential cre_reg1 -sys_password oracle

操作已成功完成

数据库的唯一名称: sh2

4.4 检查配置

GDSCTL>config

区域

------------------------

region1                      

region2                      

GSM

------------------------

sharddirector1               

sharddirector3               

分片数据库

------------------------

shdb                         

数据库

------------------------

sh1                          

sh2                          

分片组

------------------------

primary_shardgroup           

分片空间

------------------------

shardspaceora                

服务

------------------------

GDSCTL 暂挂请求

------------------------

命令                            对象                            状态                           

--                            --                            --                           

全局属性

------------------------

名称: oradbcloud

GSM: sharddirector1

DDL 序列号: 0

GDSCTL>config shardspace

分片空间                                                      

----                          -                            

shardspaceora                 12  

GDSCTL>config shardgroup

分片组                       区域                  分片空间               

---                 -      --                  ----                

primary_shardgroup  12     region1             shardspaceora 

GDSCTL>config vncr

名称                            ID                         

--                            ----                         

192.168.2.226                                               

db02                                                       

db03                              

GDSCTL>config shard

名称                  分片组                 状态        状态          区域        可用性      

--                  ---                 --        --          --        ---      

sh1                 primary_shardgroup  U         无           region1   -        

sh2                 primary_shardgroup  U         无           region1   -        

4.5 部署/deploy

执行deploy后,自动在db02db03服务器上采用静默安装方式部署shard数据库,速度较慢;

GDSCTL>deploy

deploy: examining configuration...

deploy: deploying primary shard 'sh1' ...

deploy: network listener configuration successful at destination 'db02'

deploy: starting DBCA at destination 'db02' to create primary shard 'sh1' ...

deploy: deploying primary shard 'sh2' ...

deploy: network listener configuration successful at destination 'db03'

deploy: starting DBCA at destination 'db03' 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: 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: 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: 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 'db02' for shard 'sh1'

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 'db03' for shard 'sh2'

deploy: requesting Data Guard configuration on shards via GSM

deploy: shards configured successfully

操作已成功完成

4.6 检查配置信息

GDSCTL>config shard

名称                  分片组                 状态        状态          区域        可用性      

--                  ---                 --        --          --        ---      

sh1                 primary_shardgroup  确定        已部署         region1   ONLINE   

sh2                 primary_shardgroup  确定        已部署         region1   ONLINE    

GDSCTL>databases

数据库: "sh1" 注册时间: Y 状态: 确定 ONS: N。角色: PRIMARY 实例: 1 区域: region1

   已注册的实例:

     shdb%1

数据库: "sh2" 注册时间: Y 状态: 确定 ONS: N。角色: PRIMARY 实例: 1 区域: region1

   已注册的实例:

     shdb%11

GDSCTL>config shard -shard sh1

名称: sh1

分片组: primary_shardgroup

状态: 确定

状态: 已部署

区域: region1

连接字符串: db02:1521/sh1:dedicated

SCAN 地址:

ONS 远程端口: 0

磁盘阈值, 毫秒: 20

CPU 阈值, %: 75

版本: 12.2.0.0

上次失败的 DDL:

DDL 错误: ---

失败的 DDL ID:

可用性: ONLINE

Rack:

支持的服务

------------------------

名称                                                              首选        状态       

--                                                              --        --       

GDSCTL>config shard -shard sh2  

名称: sh2

分片组: primary_shardgroup

状态: 确定

状态: 已部署

区域: region1

连接字符串: db03:1521/sh2:dedicated

SCAN 地址:

ONS 远程端口: 0

磁盘阈值, 毫秒: 20

CPU 阈值, %: 75

版本: 12.2.0.0

上次失败的 DDL:

DDL 错误: ---

失败的 DDL ID:

可用性: ONLINE

Rack:

支持的服务

------------------------

名称                                                              首选        状态       

--                                                              --        --    

4.7 创建service

GDSCTL>add service -service oltp_rw_srvc -role primary

操作已成功完成

GDSCTL>start service -service oltp_rw_srvc

操作已成功完成

GDSCTL>status service

服务 "oltp_rw_srvc.shdb.oradbcloud" 具有 2 个实例。关系: ANYWHERE

   实例 "shdb%1", 名称: "sh1", 数据库: "sh1", 区域: "region1", 状态: ready

   实例 "shdb%11", 名称: "sh2", 数据库: "sh2", 区域: "region1", 状态: ready

五:创建用户和对象

5.1 catalog数据库中创建业务用户

---db01

SQL> alter session enable shard ddl;

SQL> create user app_schema identified by oracle;

SQL> grant all privileges to app_schema;

SQL> grant gsmadmin_role to app_schema;

SQL> grant select_catalog_role to app_schema;

SQL> grant connect, resource to app_schema;

SQL> grant dba to app_schema;

SQL> grant execute on dbms_crypto to app_schema;

5.2 创建表空间集合

---db01

SQL> conn app_schema

Enter password:

Connected.

SQL> alter session enable shard ddl;

Session altered.

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.

GDSCTL>set gsm -gsm sharddirector1

GDSCTL>config shard

已建立目录连接

名称                  分片组                 状态        状态          区域        可用性      

--                  ---                 --        --          --        ---      

sh1                 primary_shardgroup  确定        DDL 错误      region1   ONLINE   

sh2                 primary_shardgroup  确定        DDL 错误      region1   ONLINE   

5.3duplicated tables创建表空间

---这个测试中duplicated tableProducts table.

SQL> CREATE TABLESPACE products_tsp datafile '/u01/app/oracle/oradata/db01/products_tsp01.dbf' size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;

Tablespace created.

5.4 创建root 表Customers

SQL> CONNECT app_schema/oracle

SQL> ALTER SESSION ENABLE SHARD DDL;

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;


5.5 创建其他sharded table Orders.

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); 


5.6 为OrderId列创建序列

SQL> CREATE SEQUENCE Orders_Seq; 

Sequence created.

5.7创建SHARDED TABLE 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);

5.8创建duplicated tables.

---In this example, the Products table is a duplicated object.

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; 

5.9 检查是否有错误

GDSCTL>set gsm -gsm sharddirector1

GDSCTL>show ddl

ID      DDL 文本                                   失败的分片    

--      ------                                   -----    

108     grant dba to app_schema                           

109     grant execute on dbms_crypto to app_s...          

110     CREATE TABLESPACE SET TSP_SET_1 using...          

111      CREATE TABLESPACE products_tsp dataf...          

112      CREATE SHARDED TABLE Customers   (  ...          

113     CREATE SHARDED TABLE Orders   (     O...          

114     CREATE SHARDED TABLE LineItems   (   ...          

115     CREATE MATERIALIZED VIEW "APP_SCHEMA"...          

116     CREATE OR REPLACE FUNCTION PasswCreat...          

117     CREATE OR REPLACE FUNCTION PasswCheck...         


GDSCTL>config shard -shard sh1

名称: sh1

分片组: primary_shardgroup

状态: 确定

状态: 已部署

区域: region1

连接字符串: db02:1521/sh1:dedicated

SCAN 地址:

ONS 远程端口: 0

磁盘阈值, 毫秒: 20

CPU 阈值, %: 75

版本: 12.2.0.0

上次失败的 DDL: CREATE MATERIALIZED VIEW "APP_...

DDL 错误: ORA-00942: table or view does not exist

ORA-06512: at "SYS.EXECASUSER", line 44

ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529

ORA-06512: at "SYS.EXECASUSER", line 31

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1 \(ngsmoci_execute\)

失败的 DDL ID: 57

可用性: ONLINE

Rack:

支持的服务

------------------------

名称                                                              首选        状态       

--                                                              --        --       

oltp_rw_srvc                                                    是         启用       

六:验证环境-表空间/chunks

6.1  db01检查chunks信息

前面创建shardcatalog时指定chunks12,因此后续创建shard table分配12chunks

GDSCTL>config chunks

------------------------

数据库                                            

---                           -         -        

sh1                           1         6        

sh2                           7         12   

6.2 在sd1,sd2节点检查表空间和chunks信息

---db02

--表空间

SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;


TABLESPACE_NAME               MB

------------------------------ ----------

C001TSP_SET_1                100

C002TSP_SET_1                100

C003TSP_SET_1                100

C004TSP_SET_1                100

C005TSP_SET_1                100

C006TSP_SET_1                100

PRODUCTS_TSP                 100

SYSAUX                       480

SYSTEM                       810

TSP_SET_1                100

UNDOTBS1                  70


TABLESPACE_NAME               MB

------------------------------ ----------

USERS                       5


12 rows selected.

创建了6个表空间,分别是C001TSP_SET_1 ~ 表空间C006TSP_SET_1,因为设置chunks=12,每个shard6chunks

每个表空间有一个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

where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;  2 

TABLE_NAME         PARTITION_NAME      TABLESPACE_NAME

-------------------- -------------------- --------------------

LINEITEMS      CUSTOMERS_P1    C001TSP_SET_1

CUSTOMERS      CUSTOMERS_P1    C001TSP_SET_1

ORDERS             CUSTOMERS_P1    C001TSP_SET_1

CUSTOMERS      CUSTOMERS_P2    C002TSP_SET_1

ORDERS             CUSTOMERS_P2    C002TSP_SET_1

LINEITEMS      CUSTOMERS_P2    C002TSP_SET_1

CUSTOMERS      CUSTOMERS_P3    C003TSP_SET_1

LINEITEMS      CUSTOMERS_P3    C003TSP_SET_1

ORDERS             CUSTOMERS_P3    C003TSP_SET_1

LINEITEMS      CUSTOMERS_P4    C004TSP_SET_1

CUSTOMERS      CUSTOMERS_P4    C004TSP_SET_1

ORDERS             CUSTOMERS_P4    C004TSP_SET_1

CUSTOMERS      CUSTOMERS_P5    C005TSP_SET_1

ORDERS             CUSTOMERS_P5    C005TSP_SET_1

LINEITEMS      CUSTOMERS_P5    C005TSP_SET_1

CUSTOMERS      CUSTOMERS_P6    C006TSP_SET_1

ORDERS             CUSTOMERS_P6    C006TSP_SET_1

LINEITEMS      CUSTOMERS_P6    C006TSP_SET_1


18 rows selected.


sd2节点检查表空间和chunks信息

--表空间

SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;

TABLESPACE_NAME               MB

------------------------------ ----------

C007TSP_SET_1                100

C008TSP_SET_1                100

C009TSP_SET_1                100

C00ATSP_SET_1                100

C00BTSP_SET_1                100

C00CTSP_SET_1                100

PRODUCTS_TSP                 100

SYSAUX                       480

SYSTEM                       810

TSP_SET_1                100

UNDOTBS1                  65

USERS                       5


12 rows selected.

创建了6个表空间,分别是C007TSP_SET_1 ~ 表空间C00CTSP_SET_1,因为设置chunks=12,每个shard6chunks

每个表空间有一个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> 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_P7    C007TSP_SET_1

LINEITEMS      CUSTOMERS_P7    C007TSP_SET_1

CUSTOMERS      CUSTOMERS_P7    C007TSP_SET_1

ORDERS             CUSTOMERS_P8    C008TSP_SET_1

CUSTOMERS      CUSTOMERS_P8    C008TSP_SET_1

LINEITEMS      CUSTOMERS_P8    C008TSP_SET_1

LINEITEMS      CUSTOMERS_P9    C009TSP_SET_1

ORDERS             CUSTOMERS_P9    C009TSP_SET_1

CUSTOMERS      CUSTOMERS_P9    C009TSP_SET_1

LINEITEMS      CUSTOMERS_P10   C00ATSP_SET_1

ORDERS             CUSTOMERS_P10   C00ATSP_SET_1

CUSTOMERS      CUSTOMERS_P10   C00ATSP_SET_1

ORDERS             CUSTOMERS_P11   C00BTSP_SET_1

LINEITEMS      CUSTOMERS_P11   C00BTSP_SET_1

CUSTOMERS      CUSTOMERS_P11   C00BTSP_SET_1

LINEITEMS      CUSTOMERS_P12   C00CTSP_SET_1

CUSTOMERS      CUSTOMERS_P12   C00CTSP_SET_1

ORDERS             CUSTOMERS_P12   C00CTSP_SET_1


18 rows selected.

6.3catalog数据库检查chunks信息

SQL>  set echo off

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; 

SHARD                     NUMBER_OF_CHUNKS

------------------------------ ----------------

sh1                          6

sh2                          6

6.4  验证环境

--catalog数据库

SQL> conn app_schema/oracle

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

--------------------------------------------------------------------------------

PRODUCTS

MLOG$_PRODUCTS

CUSTOMERS

ORDERS

LINEITEMS

RUPD$_PRODUCTS

6 rows selected.


--shard节点sd1sd2

SQL> conn app_schema/oracle

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

--------------------

CUSTOMERS

ORDERS

LINEITEMS

PRODUCTS

七:访问Shard

7.1 访问单独一个shard

在连接串中指定sharding key,那么GSM/shard director将请求连接到对应的一个shard

参数含义:

app_schema – 是业务用户,

(host=gsm1)(port=1522) – 是GSM/shard director 监听地址

service_name=oltp_rw_srvc.shdb.oradbcloud – 是前面创建的全局service

[oracle@db01 ~]$ sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=192.168.2.226)(port=1522)) (connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=chenjch)))'

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME

------------------------------

sh1

---插入数据

SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,

    Class, Geo, Passwd) VALUES ('chenjch', 'chen', 'jch',

    NULL, 'Gold', 'east', hextoraw('8d1c00e'));  2    3 

1 row created.


SQL> commit;

Commit complete.


SQL> select custid, FirstName, LastName, class, geo from customers where custid = 'chenjch';

CUSTID             FIRSTNAME          LASTNAME      CLASS    GEO

-------------------- --------------- --------------- ---------- --------

chenjch      chen      jch      Gold east


--查询SHARDING_KEY=tom.david,连接到sd2

[oracle@db01 ~]$ sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=db01)(port=1522))

(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=ch.jc)))'

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME

------------------------------

sh2


SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,

    Class, Geo, Passwd) VALUES ('chjc', 'ch', 'jc',

    NULL, 'Gold', 'east', hextoraw('8d1c00a'));

1 row created.

SQL> commit;

Commit complete.

---查询数据

---catalog本地查询

SQL> conn app_schema/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

-------------------- --------------- --------------- ---------- --------

chenjch      chen      jch      Gold east

chjc          ch      jc      Gold east

---sh1本地查询

SQL> conn app_schema/oracle

Connected.

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

-------------------- --------------- --------------- ---------- --------

chenjch      chen      jch      Gold east

---sh2本地查询

SQL> conn app_schema/oracle

Connected.

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

-------------------- --------------- --------------- ---------- --------

chjc          ch      jc      Gold east


总结:
1、每个shard节点只能查询到本地节点shard table的数据
2、在catalog数据库可以查询shard节点汇聚后的数据、
3、客户端可指定sharding key的方式,定向获取数据

7.2 访问多个shard

如果在连接串中指定sharding key,那么GSM/shard director将请求连接到对应的一个shard

如果没有指定sharding key,那么sessioncoordinator database (shard catalog)建立连接,然后再分别到需要(prund)shard中查询,最后再整合。

优化器判断访问一个shard还是访问多个shard

--链接到catalog数据库查询

[oracle@db01 ~]$ sqlplus app_schema/oracle@db01:1521/GDS\$CATALOG.oradbcloud

SQL> conn app_schema/oracle

Connected.

SQL> set termout on

SQL> set linesize 120

SQL> set echo on

SQL> column firstname format a20

SQL> column lastname format a20

SQL> explain plan for SELECT FirstName,LastName, geo, class FROM Customers

WHERE CustId in ('Scott.Tiger@x.bogus', 'Mary.Parker@x.bogus') AND class != 'free' ORDER

BY geo, class;


八:shard数据库启动和停止

---参考

https://blog.csdn.net/kiral07/article/details/86923508#Shard_Catalog_Database_63

8.1 关闭

(1)catalog数据库端,关闭director

GDSCTL>stop gsm -gsm SHARDDIRECTOR1

GSM 已成功停止

(2)关闭catalog监听

[oracle@db01 ~]$ lsnrctl stop

(3)关闭catalog数据库

SQL> shutdown immediate

(4) shard节点1和节点2关闭agent

[oracle@db02 trace]$ schagent -stop

[oracle@db03 trace]$ schagent -stop

(5) shard节点1和节点2关闭监控

[oracle@db02 trace]$ lsnrctl stop

[oracle@db03 trace]$ lsnrctl stop

(6) shard节点1和节点2关闭数据库

SQL> shutdown immediate

8.2 启动

(1)catalog端启动数据库和监听

SQL> startup

[oracle@db01 ~]$ lsnrctl start

(2) 所有shard节点启动数据库,监听,代理

SQL> startup

[oracle@db02 ~]$ lsnrctl start

[oracle@db03 ~]$ lsnrctl start

(3) catalog端启动director

GDSCTL>start gsm -gsm SHARDDIRECTOR1

GSM 已成功启动

(4)检查

GDSCTL>connect mygds/oracle

已建立目录连接


GDSCTL>config shard

名称                  分片组                 状态        状态          区域        可用性      

--                  ---                 --        --          --        ---      

sh1                 primary_shardgroup  确定        DDL 错误      region1   ONLINE   

sh2                 primary_shardgroup  确定        DDL 错误      region1   ONLINE


GDSCTL>config service

名称             网络名称                                        已启动     全部首选        

--             ----                          -              ---     ----        

oltp_rw_srvc   oltp_rw_srvc.shdb.oradbcloud  shdb           是             


GDSCTL>databases

数据库: "sh1" 注册时间: Y 状态: 确定 ONS: N。角色: PRIMARY 实例: 1 区域: region1

   服务: "oltp_rw_srvc" 全局启动: Y 启动时间: Y

            扫描: Y 启用: Y 首选: Y

   已注册的实例:

     shdb%1

数据库: "sh2" 注册时间: Y 状态: 确定 ONS: N。角色: PRIMARY 实例: 1 区域: region1

   服务: "oltp_rw_srvc" 全局启动: Y 启动时间: Y

            扫描: Y 启用: Y 首选: Y

   已注册的实例:

     shdb%11


九:常见问题

9.1 create shard –shardgroup错误

GDSCTL>create shard -shardgroup primary_shardgroup -destination db02 -credential cre_reg1 -sys_password oracle

GSM-45029: SQL 错误

ORA-03710: 目标中不存在目录或者无法写入目录: $ORACLE_BASE/fast_recovery_area

ORA-06512: "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 7469

ORA-06512: "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4978

ORA-27436: 调度程序代理操作失败, 显示消息: Agent Error: /u01/app/oracle/fast_recovery_area/shard_check.txt (No such file or directory)

ORA-06512: "SYS.DBMS_ISCHED", line 3638

ORA-06512: "SYS.DBMS_ISCHED", line 10444

ORA-06512: "SYS.DBMS_ISCHED", line 5344

ORA-06512: "SYS.DBMS_ISCHED", line 10439

ORA-06512: "SYS.DBMS_ISCHED", line 3631

ORA-06512: "SYS.DBMS_SCHEDULER", line 2414

ORA-06512: "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 164

ORA-27436: 调度程序代理操作失败, 显示消息: Agent Error: /u01/app/oracle/fast_recovery_area/shard_check.txt (No such file or directory)

ORA-06512: "SYS.DBMS_ISCHED", line 3638

ORA-06512: "SYS.DBMS_ISCHED", line 10444

ORA-06512: "SYS.DBMS_ISCHED", line 5344

ORA-06512: "SYS.DBMS_ISCHED", line 10439

ORA-06512: "SYS.DBMS_ISCHED", line 3631

ORA-06512: "SYS.DBMS_SCHEDULER", line 2414

ORA-06512: "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 157

ORA-06512: "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4965

ORA-06512: line 1

---错误提示很明显,没有fast_recovery_area目录,需要提前在db02db03服务器上创建fast_recovery_area目录

[oracle@db02 ~]$ mkdir $ORACLE_BASE/fast_recovery_area

9.2 catalog创建用户和表,不能同步到shard db库

错误原因很多,需要具体问题具体分析,一般需要用到如下命令和日志进行分析问题:

(1)recover shard -shard sh1

(2)config shard -shard sh1

(3)alert_sh1.log

[oracle@db02 trace]$ pwd

/u01/app/oracle/diag/rdbms/sh1/sh1/trace

[oracle@db02 trace]$ tail -f alert_sh1.log

例如:

GDSCTL>recover shard -shard sh1

GSM 错误:

primary_shardgroup sh1:ORA-01119: error in creating database file \'/u01/app/oracle/oradata/db01/products_tsp01.dbf\'

ORA-06512: at "SYS.EXECASUSER", line 44

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529

ORA-06512: at "SYS.EXECASUSER", line 31

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1 (ngsmoci_execute)


GDSCTL>recover shard -shard sh1

GSM 错误:

primary_shardgroup sh1:ORA-03715: invalid user APP_SCHEMA

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.EXECASUSER", line 22

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1 (ngsmoci_execute)


GDSCTL>config shard -shard sh2

名称: sh2

分片组: primary_shardgroup

状态: 确定

状态: 已部署

区域: region1

连接字符串: db03:1521/sh2:dedicated

SCAN 地址:

ONS 远程端口: 0

磁盘阈值, 毫秒: 20

CPU 阈值, %: 75

版本: 12.2.0.0

上次失败的 DDL: drop tablespace TSP_SET_1 incl...

DDL 错误: ORA-03715: invalid user APP_SCHEMA

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.EXECASUSER", line 22

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1 \(ngsmoci_execute\)


十:参考文献

12c新特性-Oracle Sharding简介》

https://blogs.oracle.com/database4cn/12c-oracle-sharding

Sharded Database Deployment

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/sharding-deployment.html#GUID-61B495D3-4482-47E2-937E-B7E03286F565

Oracle 12C Sharding分片数据库》

https://blog.csdn.net/kiral07/article/details/86923508#OracleSharding_Manage_80

Oracle Sharding - Troubleshooting Tips and Techniques (文档 ID 2180259.1)

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=151coor2ou_9&_afrLoop=249031377428729



相关文章


2020 unjeep.com webmaster#unjeep.com
12 q. 0.013 s.
京ICP备10005923号