溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
Oracle 12C Sharding 部署和測試
日期： 2019-02-26
作者：陳舉超
目錄
一：環(huán)境說明 ... 4
二：安裝GSM/Shard catalog/ORACLE 12.2 RDBMS . 5
2.1 Gsm 安裝 ... 5
2.2 ORACLE 12.2 RDBMS 安裝 ... 8
2.3 創(chuàng)建并啟動監(jiān)聽 ... 8
2.4 創(chuàng)建Shard Catalog database . 8
三：配置GSM/Shard director . 8
3.1 解鎖用戶 ... 8
3.2 創(chuàng)建管理用戶mygds . 8
3.3 啟動listener . 8
3.4 創(chuàng)建shard catalog 配置remote scheduler agent . 8
3.5 創(chuàng)建和啟動shard director. 9
3.6 添加操作系統(tǒng)認(rèn)證. 9
3.7 在所有的shard 節(jié)點(diǎn)分別執(zhí)行Agent . 10
3.8 創(chuàng)建shard 數(shù)據(jù)庫的數(shù)據(jù)文件存儲路徑 ... 11
四：創(chuàng)建System-Managed SDB . 11
4.1 連接gsm .. 11
4.2 添加shardgroup . 11
4. 3 創(chuàng)建shard . 11
4.4 檢查配置 ... 12
4.5 部署/deploy . 13
4.6 檢查配置信息 ... 14
4.7 創(chuàng)建service . 16
五：創(chuàng)建用戶和對象 ... 16
5.1 在catalog 數(shù)據(jù)庫中創(chuàng)建業(yè)務(wù)用戶 ... 16
5.2 創(chuàng)建表空間集合 ... 16
5.3 為duplicated tables 創(chuàng)建表空間 ... 17
5.4 創(chuàng)建 root 表 Customers . 17
5.5 創(chuàng)建其他 sharded table Orders. 18
5.6 為 OrderId 列創(chuàng)建序列 ... 18
5.7 創(chuàng)建 SHARDED TABLE LineItems . 18
5.8 創(chuàng)建 duplicated tables. 18
5.9 檢查是否有錯誤 ... 19
六：驗(yàn)證環(huán)境- 表空間/chunks . 20
6.1 在db01 檢查chunks 信息 ... 20
6.2 在 sd1,sd2 節(jié)點(diǎn)檢查表空間和 chunks 信息 ... 20
6.3 在catalog 數(shù)據(jù)庫檢查chunks 信息 ... 23
6.4 驗(yàn)證環(huán)境 ... 23
七：訪問Shard . 24
7.1 訪問單獨(dú)一個shard . 24
7.2 訪問多個shard . 26
八：shard 數(shù)據(jù)庫啟動和停止 ... 27
8.1 關(guān)閉 ... 27
8.2 啟動
九：常見問題 ... 29
9.1 create shard –shardgroup 錯誤 ... 29
9.2 catalog 創(chuàng)建用戶和表，不能同步到 shard db 庫 ... 29
9.3 GSM 啟動失敗，顯示異常
十：參考文獻(xiàn)
一：環(huán)境說明
OS ：CentOS Linux release 7.5
DB ：Oracle 12.2.0.1.0
GSMOCI ：2.2.1
本次使用三臺虛擬機(jī)進(jìn)行安裝：
其中：
(1) db01 主機(jī)上安裝Shard Director 和Shard catalog ，也就是分別安裝GSM 、ORACLE 12.2 RDBMS 、non-cdb Database(GSM 和ORACLE 12.2 環(huán)境變量需要手動切換) ；
(2)db02 主機(jī)上安裝Shard1 ，也就是ORACLE 12.2 RDBMS ，不需要創(chuàng)建數(shù)據(jù)庫；
(3) db03 主機(jī)上安裝Shard2 ，也就是ORACLE 12.2 RDBMS ，不需要創(chuàng)建數(shù)據(jù)庫；
|
主機(jī) |
組件 |
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 |
sh2 |
/u01/app/oracle/product/12.2.0/db_1 |
|
db03 |
Shard2 |
sh3 |
/u01/app/oracle/product/12.2.0/db_1 |
安裝文件下載鏈接如下：
https://www.oracle.com/technetwork/cn/database/options/partitioning/downloads/index.html
Oracle 軟件下載
GSM 下載
略
Netca
在Shard catalog 服務(wù)器gsm1 創(chuàng)建 non-cdb 數(shù)據(jù)庫。創(chuàng)建過程與普通數(shù)據(jù)庫相同。
三：配置GSM/Shard director
https://blogs.oracle.com/database4cn/12c-oracle-sharding
在db01 服務(wù)器(catalog 數(shù)據(jù)庫/shard director) ，連接到Sharding catalog 數(shù)據(jù)庫, 解鎖 GSMCATUSER 用戶，shard director 通過GSMCATUSER 用戶連接到shard catalog database
SQL> alter user gsmcatuser identified by oracle account unlock;
在 catalog 數(shù)據(jù)庫，創(chuàng)建管理用戶mygds ，用戶mygds 用于存儲Sharding 管理信息，GDSCTL 接口通過用戶mygds 連接到catalog 數(shù)據(jù)庫。
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;
在db01 服務(wù)器(catalog 數(shù)據(jù)庫/shard director) ，啟動listener
在db01 服務(wù)器(catalog 數(shù)據(jù)庫/shard director) ，創(chuàng)建shard catalog ，在shard catalog 中配置remote scheduler agent.
參數(shù)含義：
-user : 指定管理用戶，在前面步驟中創(chuàng)建的catalog database 管理用戶mygds
-database : 指定catalog database 信息，catalog 數(shù)據(jù)庫的主機(jī)名: 監(jiān)聽器port: catalog 數(shù)據(jù)庫db_name
-sdb : 指定sharded database name
-agent_port: 設(shè)置端口，用于shard 節(jié)點(diǎn)agent 連接到GSM
-agent_password: 設(shè)置密碼，用于shard 節(jié)點(diǎn)agent 連接到GSM
如果沒有指定- sharding 參數(shù)，默認(rèn)是創(chuàng)建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
參數(shù)含義：
-gsm: 指定shard director 名稱
-listener: 指定shard director 的監(jiān)聽端口，注意不能與數(shù)據(jù)庫的listener 端口沖突
-catalog: 指定catalog database 信息，catalog 數(shù)據(jù)庫的主機(jī)名: 監(jiān)聽器port: catalog 數(shù)據(jù)庫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
操作已成功完成
--- 在db02 節(jié)點(diǎn)執(zhí)行
[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 節(jié)點(diǎn)執(zhí)行
[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!
[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
在Shard 服務(wù)器 sd1 連接到shard director/GSM 服務(wù)器(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
版權(quán)所有 (c) 2011, 2016, Oracle 。保留所有權(quán)利。
歡迎使用GDSCTL, 請鍵入 "help" 以獲得信息。
當(dāng)前 GSM 設(shè)置為 SHARDDIRECTOR1
GDSCTL> set gsm -gsm sharddirector1
GDSCTL>connect mygds/oracle
已建立目錄連接
shardgroup 是一組shard 的集合，shardgroup 名稱為primary_shardgroup ，-deploy_as primary 表示這個group 中的shard 都是主庫。
GDSCTL> add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
操作已成功完成
將每個shard 地址添加到catalog 的valid node checking for registration (VNCR) 列表，并且創(chuàng)建shard
GDSCTL> add invitednode db02
GDSCTL> create shard -shardgroup primary_shardgroup -destination db02 -credential cre_reg1 -sys_password oracle
操作已成功完成
數(shù)據(jù)庫的唯一名稱: sh2
GDSCTL> add invitednode db03
GDSCTL> create shard -shardgroup primary_shardgroup -destination db03 -credential cre_reg1 -sys_password oracle
操作已成功完成
數(shù)據(jù)庫的唯一名稱: sh3
GDSCTL>config
區(qū)域
------------------------
region1
region2
GSM
------------------------
sharddirector1
sharddirector3
分片數(shù)據(jù)庫
------------------------
shdb
數(shù)據(jù)庫
------------------------
sh2
sh3
分片組
------------------------
primary_shardgroup
分片空間
------------------------
shardspaceora
服務(wù)
------------------------
GDSCTL 暫掛請求
------------------------
命令 對象 狀態(tài)
-- -- --
全局屬性
------------------------
名稱: oradbcloud
主 GSM: sharddirector1
DDL 序列號: 0
GDSCTL> config shardspace
分片空間 塊
---- -
shardspaceora 12
GDSCTL> config shardgroup
分片組 塊 區(qū)域 分片空間
--- - -- ----
primary_shardgroup 12 region1 shardspaceora
GDSCTL> config vncr
名稱 組 ID
-- ----
192.168.2.226
db02
db03
GDSCTL> config shard
名稱 分片組 狀態(tài) 狀態(tài) 區(qū)域 可用性
-- --- -- -- -- ---
sh2 primary_shardgroup U 無 region1 -
sh3 primary_shardgroup U 無 region1 -
執(zhí)行deploy 后，自動在db02 和db03 服務(wù)器上采用靜默安裝方式部署shard 數(shù)據(jù)庫，速度較慢；
GDSCTL> deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh2' ...
deploy: network listener configuration successful at destination 'db02'
deploy: starting DBCA at destination 'db02' to create primary shard 'sh2' ...
deploy: deploying primary shard 'sh3' ...
deploy: network listener configuration successful at destination 'db03'
deploy: starting DBCA at destination 'db03' to create primary shard 'sh3' ...
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 'sh2'
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 'sh3'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
操作已成功完成
GDSCTL> config shard
名稱 分片組 狀態(tài) 狀態(tài) 區(qū)域 可用性
-- --- -- -- -- ---
sh2 primary_shardgroup 確定 已部署 region1 ONLINE
sh3 primary_shardgroup 確定 已部署 region1 ONLINE
GDSCTL> databases
數(shù)據(jù)庫: "sh2" 注冊時間: Y 狀態(tài): 確定 ONS: N 。角色: PRIMARY 實(shí)例: 1 區(qū)域: region1
已注冊的實(shí)例:
shdb%1
數(shù)據(jù)庫: "sh3" 注冊時間: Y 狀態(tài): 確定 ONS: N 。角色: PRIMARY 實(shí)例: 1 區(qū)域: region1
已注冊的實(shí)例:
shdb%11
GDSCTL> config shard -shard sh2
名稱: sh2
分片組: primary_shardgroup
狀態(tài): 確定
狀態(tài): 已部署
區(qū)域: region1
連接字符串: db02:1521/sh2:dedicated
SCAN 地址:
ONS 遠(yuǎn)程端口: 0
磁盤閾值, 毫秒: 20
CPU 閾值, %: 75
版本: 12.2.0.0
上次失敗的 DDL:
DDL 錯誤: ---
失敗的 DDL ID:
可用性: ONLINE
Rack:
支持的服務(wù)
------------------------
名稱 首選 狀態(tài)
-- -- --
GDSCTL> config shard -shard sh3
名稱: sh3
分片組: primary_shardgroup
狀態(tài): 確定
狀態(tài): 已部署
區(qū)域: region1
連接字符串: db03:1521/sh3:dedicated
SCAN 地址:
ONS 遠(yuǎn)程端口: 0
磁盤閾值, 毫秒: 20
CPU 閾值, %: 75
版本: 12.2.0.0
上次失敗的 DDL:
DDL 錯誤: ---
失敗的 DDL ID:
可用性: ONLINE
Rack:
支持的服務(wù)
------------------------
名稱 首選 狀態(tài)
-- -- --
GDSCTL> add service -service oltp_rw_srvc -role primary
操作已成功完成
GDSCTL> start service -service oltp_rw_srvc
操作已成功完成
GDSCTL> status service
服務(wù) "oltp_rw_srvc.shdb.oradbcloud" 具有 2 個實(shí)例。關(guān)系: ANYWHERE
實(shí)例 "shdb%1", 名稱: "sh2", 數(shù)據(jù)庫: "sh2", 區(qū)域: "region1", 狀態(tài): ready 。
實(shí)例 "shdb%11", 名稱: "sh3", 數(shù)據(jù)庫: "sh3", 區(qū)域: "region1", 狀態(tài): ready 。
---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
已建立目錄連接
名稱 分片組 狀態(tài) 狀態(tài) 區(qū)域 可用性
-- --- -- -- -- ---
sh2 primary_shardgroup 確定 DDL 錯誤 region1 ONLINE
sh3 primary_shardgroup 確定 DDL 錯誤 region1 ONLINE
--- 這個測試中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.
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 ;
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 ;
Sequence created.
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 );
---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 sh2
名稱: sh2
分片組: primary_shardgroup
狀態(tài): 確定
狀態(tài): 已部署
區(qū)域: region1
連接字符串: db02:1521/sh2:dedicated
SCAN 地址:
ONS 遠(yuǎn)程端口: 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:
支持的服務(wù)
------------------------
名稱 首選 狀態(tài)
-- -- --
oltp_rw_srvc 是 啟用
前面創(chuàng)建shardcatalog時指定chunks為12，因此后續(xù)創(chuàng)建shard table分配12個chunks
GDSCTL>config chunks
塊
------------------------
數(shù)據(jù)庫 自 至
--- - -
sh2 1 6
sh3 7 12
---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.
創(chuàng)建了6個表空間，分別是C001TSP_SET_1 ~ 表空間C006TSP_SET_1，因?yàn)樵O(shè)置chunks=12，每個shard有6個chunks。
每個表空間有一個datafile，大小是100M，這個是在創(chuàng)建tablespace set時設(shè)置的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 sh2
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節(jié)點(diǎn)檢查表空間和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.
創(chuàng)建了6個表空間，分別是C007TSP_SET_1 ~ 表空間C00CTSP_SET_1，因?yàn)樵O(shè)置chunks=12，每個shard有6個chunks。
每個表空間有一個datafile，大小是100M，這個是在創(chuàng)建tablespace set時設(shè)置的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.
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
------------------------------ ----------------
sh2 6
sh3 6
--catalog數(shù)據(jù)庫
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節(jié)點(diǎn)sd1和sd2
SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS
在連接串中指定sharding key，那么GSM/shard director將請求連接到對應(yīng)的一個shard
參數(shù)含義：
app_schema – 是業(yè)務(wù)用戶，
(host=gsm1)(port=1522) – 是GSM/shard director 監(jiān)聽地址
service_name=oltp_rw_srvc.shdb.oradbcloud – 是前面創(chuàng)建的全局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
------------------------------
sh2
--- 插入數(shù)據(jù)
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
------------------------------
sh3
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.
--- 查詢數(shù)據(jù)
---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
---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
-------------------- --------------- --------------- ---------- --------
chenjch chen jch Gold east
---sh3本地查詢
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
總結(jié)：
1
、每個shard節(jié)點(diǎn)只能查詢到本地節(jié)點(diǎn)shard table的數(shù)據(jù)
2
、在catalog數(shù)據(jù)庫可以查詢shard節(jié)點(diǎn)匯聚后的數(shù)據(jù)、
3
、客戶端可指定sharding key的方式，定向獲取數(shù)據(jù)
如果在連接串中指定sharding key，那么GSM/shard director將請求連接到對應(yīng)的一個shard。
如果沒有指定sharding key，那么session和coordinator database (shard catalog)建立連接,然后再分別到需要(prund)的shard中查詢，最后再整合。
優(yōu)化器判斷訪問一個shard還是訪問多個shard。
--鏈接到catalog數(shù)據(jù)庫查詢
[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;
--- 參考
https://blog.csdn.net/kiral07/article/details/86923508#Shard_Catalog_Database_63
(1)catalog數(shù)據(jù)庫端,關(guān)閉director
GDSCTL>stop gsm -gsm SHARDDIRECTOR1
GSM 已成功停止
(2)關(guān)閉catalog監(jiān)聽
[oracle@db01 ~]$ lsnrctl stop
(3)關(guān)閉catalog數(shù)據(jù)庫
SQL> shutdown immediate
(4) shard節(jié)點(diǎn)1和節(jié)點(diǎn)2關(guān)閉agent
[oracle@db02 trace]$ schagent -stop
[oracle@db03 trace]$ schagent -stop
(5) shard節(jié)點(diǎn)1和節(jié)點(diǎn)2關(guān)閉監(jiān)控
[oracle@db02 trace]$ lsnrctl stop
[oracle@db03 trace]$ lsnrctl stop
(6) shard節(jié)點(diǎn)1和節(jié)點(diǎn)2關(guān)閉數(shù)據(jù)庫
SQL> shutdown immediate
(1)catalog端啟動數(shù)據(jù)庫和監(jiān)聽
SQL> startup
[oracle@db01 ~]$ lsnrctl start
(2) 所有shard節(jié)點(diǎn)啟動數(shù)據(jù)庫，監(jiān)聽，代理
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
名稱 分片組 狀態(tài) 狀態(tài) 區(qū)域 可用性
-- --- -- -- -- ---
sh2 primary_shardgroup 確定 DDL 錯誤 region1 ONLINE
sh3 primary_shardgroup 確定 DDL 錯誤 region1 ONLINE
GDSCTL>config service
名稱 網(wǎng)絡(luò)名稱 池 已啟動 全部首選
-- ---- - --- ----
oltp_rw_srvc oltp_rw_srvc.shdb.oradbcloud shdb 是 是
GDSCTL>databases
數(shù)據(jù)庫: "sh2" 注冊時間: Y 狀態(tài): 確定 ONS: N。角色: PRIMARY 實(shí)例: 1 區(qū)域: region1
服務(wù): "oltp_rw_srvc" 全局啟動: Y 啟動時間: Y
掃描: Y 啟用: Y 首選: Y
已注冊的實(shí)例:
shdb%1
數(shù)據(jù)庫: "sh3" 注冊時間: Y 狀態(tài): 確定 ONS: N。角色: PRIMARY 實(shí)例: 1 區(qū)域: region1
服務(wù): "oltp_rw_srvc" 全局啟動: Y 啟動時間: Y
掃描: Y 啟用: Y 首選: Y
已注冊的實(shí)例:
shdb%11
GDSCTL>create shard -shardgroup primary_shardgroup -destination db02 -credential cre_reg1 -sys_password oracle
GSM-45029: SQL 錯誤
ORA-03710: 目標(biāo)中不存在目錄或者無法寫入目錄: $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: 調(diào)度程序代理操作失敗, 顯示消息: 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: 調(diào)度程序代理操作失敗, 顯示消息: 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 服務(wù)器上創(chuàng)建fast_recovery_area 目錄
[oracle@db02 ~]$ mkdir $ORACLE_BASE/fast_recovery_area
錯誤原因很多，需要具體問題具體分析，一般需要用到如下命令和日志進(jìn)行分析問題：
(1)recover shard -shard sh2
(2)config shard -shard sh2
(3)alert_sh2.log
[oracle@db02 trace]$ pwd
/u01/app/oracle/diag/rdbms/sh2/sh2/trace
[oracle@db02 trace]$ tail -f alert_sh2.log
例如：
GDSCTL>recover shard -shard sh2
GSM 錯誤:
primary_shardgroup sh2: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 sh2
GSM 錯誤:
primary_shardgroup sh2: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 sh3
名稱: sh3
分片組: primary_shardgroup
狀態(tài): 確定
狀態(tài): 已部署
區(qū)域: region1
連接字符串: db03:1521/sh3:dedicated
SCAN 地址:
ONS 遠(yuǎn)程端口: 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\)
問題現(xiàn)象：
啟動GSM顯示實(shí)例已運(yùn)行，停止GSM顯示異常或未運(yùn)行；
排查原因：
查看告警日志
[oracle@db01 trace]$ pwd
/u01/app/oracle/diag/gsm/db01/sharddirector1/trace
[oracle@db01 trace]$ vi alert_sharddirector.log
問題原因：
創(chuàng)建sharddirector時指定的端口已經(jīng)被其他應(yīng)用占用，但是可以創(chuàng)建成功，后續(xù)啟動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》
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/sharding-deployment.html#GUID-61B495D3-4482-47E2-937E-B7E03286F565
《Oracle 12C Sharding分片數(shù)據(jù)庫》
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
歡迎關(guān)注我的微信公眾號"IT小Chen"，共同學(xué)習(xí)，共同成長?。?！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
