Oracle 12C Sharding部署和測試

chenoracle發表於2019-02-27

Oracle 12C Sharding 部署和測試  

日期: 2019-02-26  

作者:陳舉超


目錄

十:參考文獻

Oracle 12C Sharding部署和測試

OS CentOS Linux release 7.5

DB Oracle 12.2.0.1.0

GSMOCI 2.2.1

本次使用三臺虛擬機器進行安裝:

其中:

(1) db01 主機上安裝Shard Director Shard catalog ,也就是分別安裝GSM ORACLE 12.2 RDBMS non-cdb Database(GSM ORACLE 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

安裝檔案下載連結如下:

Oracle 軟體下載

Oracle 12C Sharding部署和測試

GSM 下載

Oracle 12C Sharding部署和測試

安裝

Oracle 12C Sharding部署和測試

Oracle 12C Sharding部署和測試

Oracle 12C Sharding部署和測試

Oracle 12C Sharding部署和測試

Oracle 12C Sharding部署和測試

安裝

建立並啟動監聽

Netca

建立Shard Catalog database

Shard catalog   伺服器gsm1   建立 non-cdb 資料庫。建立過程與普通資料庫相同。

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

解鎖使用者

db01 伺服器(catalog 資料庫/shard director) ,連線到Sharding catalog 資料庫, 解鎖 GSMCATUSER 使用者,shard director 透過GSMCATUSER 使用者連線到shard catalog database

SQL> alter user gsmcatuser identified by oracle account unlock;

建立管理使用者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;

啟動listener

db01 伺服器(catalog 資料庫/shard director) ,啟動listener

建立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

建立和啟動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 已成功啟動

新增作業系統認證

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

操作已成功完成

在所有的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!

建立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

連線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

已建立目錄連線

新增shardgroup

shardgroup 是一組shard 的集合,shardgroup 名稱為primary_shardgroup -deploy_as primary 表示這個group 中的shard 都是主庫。

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

操作已成功完成

建立shard

將每個shard 地址新增到catalog valid 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

檢查配置

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   -        

部署/deploy

執行deploy 後,自動在db02 db03 伺服器上採用靜默安裝方式部署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

操作已成功完成

檢查配置資訊

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:

 

支援的服務

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

名稱                                                              首選        狀態       

--                                                              --        --    

建立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

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;

建立表空間集合

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

duplicated tables 建立表空間

--- 這個測試中duplicated table Products 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.

建立 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 ;

建立其他 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 );  

OrderId 列建立序列

SQL > CREATE SEQUENCE Orders_Seq ;  

Sequence created.

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

建立 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 ;  

檢查是否有錯誤

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                                                    是         啟用       

db01 檢查chunks 資訊

前面建立shardcatalog時指定chunks為12,因此後續建立shard table分配12個chunks

GDSCTL>config chunks

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

資料庫                           自         至        

---                           -         -        

sh1                           1         6        

sh2                           7         12   

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,每個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

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,每個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> 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.

catalog 資料庫檢查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

驗證環境

--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節點sd1和sd2

SQL> conn app_schema/oracle

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

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

CUSTOMERS

ORDERS

LINEITEMS

PRODUCTS

訪問單獨一個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的方式,定向獲取資料

訪問多個shard

如果在連線串中指定sharding key,那麼GSM/shard director將請求連線到對應的一個shard。

如果沒有指定sharding key,那麼session和coordinator 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;

Oracle 12C Sharding部署和測試

Oracle 12C Sharding部署和測試

--- 參考

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

關閉

(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

啟動

(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

 

錯誤

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 目錄,需要提前在db02 db03 伺服器上建立fast_recovery_area 目錄

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

建立使用者和表,不能同步到 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\)

啟動失敗,顯示異常或未執行

問題現象:

啟動GSM顯示例項已執行,停止GSM顯示異常或未執行;

排查原因:

檢視告警日誌

[oracle@db01 trace]$ pwd

/u01/app/oracle/diag/gsm/db01/sharddirector1/trace

[oracle@db01 trace]$ vi alert_sharddirector.log

問題原因:

建立sharddirector時指定的埠已經被其他應用佔用,但是可以建立成功,後續啟動GSM就會失敗,刪除新建即可

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

《12c新特性-Oracle Sharding簡介》

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

《Sharded Database Deployment》

《Oracle 12C Sharding分片資料庫》

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

《Oracle Sharding - Troubleshooting Tips and Techniques (文件 ID 2180259.1)》

歡迎關注我的微信公眾號"IT小Chen",共同學習,共同成長!!!

Oracle 12C Sharding部署和測試

Oracle 12C Sharding部署和測試



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2637038/,如需轉載,請註明出處,否則將追究法律責任。

相關文章