Oracle 19.3 Sharding 安裝配置之02 (安裝Sharding-系統管理分片)

1. 规划

序号	主机名	組件	sid	oracle_Home	IP	内存大小
1	gsm01	shard Director		/u05/../gsm_1	10.0.99.101	4GB
2	gsm02	shard Director		/u05/../gsm_1	10.0.99.102	4GB
3	sc01	Shard catalog	catadb	/u01/../db_1	10.0.99.103	4GB
4	sc02	Shard catalog	catadb	/u01/../db_1	10.0.99.104	4GB
5	sd01	shard服务器1	sh2	/u01/../db_1	10.0.99.105	4GB
6	sd02	shard服务器2	sh3	/u01/../db_1	10.0.99.106	4GB
7	sd03	shard服务器3	sh4	/u01/../db_1	10.0.99.107	4GB
8	sd04	shard服务器4	sh5	/u01/../db_1	10.0.99.108	4GB
9	sd05	shard服务器5	sh6	/u01/../db_1	10.0.99.109	4GB
10	sd06	shard服务器6	sh7	/u01/../db_1	10.0.99.110	4GB

#上面所有主鍵的 hosts 文件 添加如下信息
10.0.99.101 gsm01
10.0.99.102 gsm02
10.0.99.103 sc01
10.0.99.104 sc02
10.0.99.105 sd01
10.0.99.106 sd02
10.0.99.107 sd03
10.0.99.108 sd04
10.0.99.109 sd05
10.0.99.110 sd06

1. 安裝(在sc01、sc02、sd01 … sd06 上安裝software only, sd0x 系列不要创建监听)

[oracle@sc01 db_1]$ cd $ORACLE_HOME
[oracle@sc01 db_1]$ pwd
/u01/app/oracle/product/19.3.0/db_1
[oracle@sc01 db_1]$ unzip linux.X64_193000_db_home.zip
[oracle@sc01 db_1]$ rm LINUX.X64_193000_db_home.zip
[oracle@sc01 db_1]$ export DISPLAY=10.3.20.85:0.0
[oracle@sc01 db_1]$ ./runInstaller

1. Install GSM software on gsm01 and gsm02

[gds@gsm01 setup]$ unzip LINUX.X64_193000_gsm.zip
[gds@gsm01 setup]$ rm LINUX.X64_193000_gsm.zip 
[gds@gsm01 setup]$ ls
gsm
[gds@gsm01 setup]$ cd gsm
[gds@gsm01 gsm]$ ls
install  response  runInstaller  stage  welcome.html
[gds@gsm01 gsm]$  export DISPLAY=10.3.20.85:0.0
[gds@gsm01 gsm]$ source /home/gds/.bash_profile
[gds@gsm01 gsm]$ ./runInstaller

1. 创建Shard Catalog database (即catadb 實例) 在 sc01 上（sc02 備用）

[oracle@sc01 ~]$ cat /home/oracle/scripts/setEnv.sh
-----------------------------------------------------------------------------------
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=sc01
export ORACLE_UNQNAME=catadb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export ORACLE_SID=catadb
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
-----------------------------------------------------------------------------------
[oracle@sc01 ~]$ export DISPLAY=10.3.20.85:0.0
[oracle@sc01 ~]$ source /home/oracle/.bash_profile 
#創建監聽
[oracle@sc01 ~]$ netca
#創建目錄數據庫（資料庫）
#創建oradata和fast_recovery_area目錄
 mkdir /u01/app/oracle/oradata
 mkdir /u01/app/oracle/fast_recovery_area
#運行DBCA創建分片目錄數據庫（创建 non-cdb ：即不要勾選 create as container database）
[oracle@sc01 ~]$ dbca
將打開“數據庫配置助手”。
在“數據庫操作”頁面上，選擇“ 創建數據庫”，然後單擊“ 下一步”。
在“創建模式”頁面上，選擇“ 高級配置”，然後單擊“ 下一步”。
在“部署類型”頁面上，選擇Oracle單一實例數據庫數據庫類型，選擇“ 通用”或“事務處理”模板，然後單擊“ 下一步”。
在“數據庫標識”頁面上，輸入全局數據庫名稱和您在分片目錄主機環境腳本中配置的分片目錄SID，然後單擊下一步。
在“存儲選項”頁面上，選擇“ 對數據庫存儲屬性使用以下內容”選項，選擇“ 文件系統”，選擇“ 使用Oracle管理的文件（OMF）”選項，然後單擊“ 下一步”。
在“選擇快速恢復選項”頁面上，選擇“ Specify Fast Recovery Area ”，選擇“ Enable archiving”，然後單擊“ 下一步”。
(如沒有監聽選項）在“指定網絡配置詳細信息”頁面上，選擇“ 創建新的偵聽器”，設置偵聽器名稱和端口號，然後單擊“ 下一步”。
記下偵聽器名稱，以便以後可以連接到數據庫。
跳過“數據保管庫選項”頁面。
在“配置選項”頁面的“ 內存”選項卡上，選擇“ 使用自動共享內存管理”。
在“配置選項”頁面上的“ 字符集”選項卡上，選擇“ 使用Unicode（AL32UTF8）”,National character set 选择 AL16UTF,然後單擊“ 下一步”。
在“管理選項”頁面上，取消選中“ 配置企業管理器（EM）數據庫表達”選項，然後單擊“ 下一步”。
在“用戶憑據”頁面上，選擇適合您業務需求的選項，輸入密碼，然後單擊“ 下一步”。
記下您輸入的密碼，因為以後需要它們。
在“創建選項”頁面上，選擇“ 創建數據庫”，然後單擊“ 下一步”。
在摘要頁面上，單擊完成。
創建數據庫後，記下全局數據庫名稱，SID和spfile值。
如果計劃使用Oracle Data Guard保護分片目錄數據庫，請單擊“ 密碼管理”，解鎖SYSDG帳戶，並記下為此帳戶輸入的密碼。
單擊“ 關閉”退出DBCA。
#编辑 "/etc/oratab" file setting the restart flag for each instance to 'Y'.
catadb:/u01/app/oracle/product/19.3.0/db_1:Y
#通过如下脚本启动或停止数据库
/home/oracle/scripts/start_all.sh
/home/oracle/scripts/stop_all.sh

1. 設置Oracle分片管理和路由層

#目錄db上
[oracle@sc01 ~]$ sqlplus / as sysdba
SQL> show parameter db_create_file_dest;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest             string     /u01/app/oracle/oradata
SQL> show parameter open_links;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_links                 integer     4
open_links_per_instance          integer     4
alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;
alter system set open_links=16 scope=spfile;
alter system set open_links_per_instance=16 scope=spfile;
#關閉並重新啟動目錄數據庫
SQL> shutdown immediate
SQL> startup
#在目錄數據庫上授予角色和特權
a 解鎖並設置GSMCATUSER模式的密碼
[oracle@sc01 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
SQL>  SET SQLFORMAT ansiconsole
SQL>  select username,account_status from dba_users where username like '%GSM%';
USERNAME            ACCOUNT_STATUS   
GSMADMIN_INTERNAL   LOCKED           
GSMCATUSER          LOCKED           
GSMUSER             LOCKED           
GSMROOTUSER         LOCKED  
SQL> alter user gsmcatuser identified by oracle account unlock;
b. 創建管理員架構並為其授予特權
# mysdbadmin帳戶是分片目錄數據庫中的一個帳戶，用於存儲有關分片環境的信息。
# mysdbadmin帳戶是用於對分片數據庫環境進行管理更改的數據庫管理員架構。
# 運行GDSCTL命令時，GDSCTL通過該用戶連接到數據庫，並且mysdbadmin用戶在數據庫中進行必要的更改。
SQL> create user mysdbadmin identified by oracle;
SQL> grant connect, create session, gsmadmin_role to mysdbadmin;
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;

1. 連接到分片導向器主機(gsm01,gsm02)，然後啟動GDSCT

[gds@gsm01 ~]$ gdsctl
GDSCTL: Version 19.0.0.0.0 - Production on Thu Mar 19 17:11:07 CST 2020
Copyright (c) 2011, 2019, Oracle.  All rights reserved.
Welcome to GDSCTL, type "help" for information.
Warning:  GSM  is not set automatically because gsm.ora does not contain GSM entries. Use "set  gsm" command to set GSM for the session.
Current GSM is set to GSMORA

1. 使用Data Guard複製為系統管理的分片創建分片目錄
   ( 還有 複合分片數據庫、用戶定義的分片數據庫 後續再介紹）

#gsm01 上
GDSCTL>
create shardcatalog -database sc01:1521:catadb -chunks 12 -user mysdbadmin/oracle -sdb cust_sdb -region region1, region2 -agent_port 8080 -agent_passWord oracle
#創建並啟動分片導向器
GDSCTL> add gsm -gsm sharddirector1 -listener 1571 -pwd oracle -catalog sc01:1521:catadb -region region1
GDSCTL> start gsm -gsm sharddirector1
#使用GDSCTL設置操作系統憑據（僅gsm01上）
GDSCTL> add credential -credential cre_reg1 -osaccount oracle -ospassword 123456
#gsm02上
[gds@gsm02 ~]$ gdsctl
GDSCTL> add gsm -gsm sharddirector2 -listener 1572 -pwd oracle -catalog sc01:1521:catadb -region region2
GDSCTL> start gsm -gsm sharddirector2

1. 連接到每個分片主機，在其上註冊遠程調度程序代理，並在其上為oradata和fast_recovery_area創建目錄（未完成）

#sd01、sd02、sd03、sd04 上
#如下配置文件，不同机器 HOSTNAME、UNQNAME、SID 不同
[oracle@sc01 ~]$ cat /home/oracle/scripts/setEnv.sh
-----------------------------------------------------------------------------------
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=sd01
export ORACLE_UNQNAME=sh2
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export ORACLE_SID=sh2
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
-----------------------------------------------------------------------------------
$ schagent -start
$ schagent -status
--密码oracle和端口8080是在第7步创建shardcatalog时设置的（oracle 是指 agent_password）:
$ echo oracle | schagent -reGISterdatabase sc01 8080
$ mkdir /u01/app/oracle/oradata
$ mkdir /u01/app/oracle/fast_recovery_area

1. 创建系统管理的SDB

[gds@gsm01 ~]$ gdsctl
GDSCTL> set gsm -gsm sharddirector1
GDSCTL> connect mysdbadmin/oracle
#为主分片添加一个分片组
GDSCTL> add shardgroup -shardgroup primary_shgrp -deploy_as primary -region region1
#为活动Data Guard备用分片添加一个分片组
GDSCTL> add shardgroup -shardgroup standby_shgrp -deploy_as active_standby -region region2
#将每个分片的主机地址添加到有效节点，以检查目录中的注册（VNCR）列表，然后在主或备用分片组中创建分片
4. 将每个shard 地址添加到catalog 的 (VNCR) 列表，并且创建shard
GDSCTL> add invitednode sd01
GDSCTL> create shard -shardgroup primary_shgrp -destination sd01 -credential cre_reg1 -sys_password 123456
GDSCTL> add invitednode sd02
GDSCTL> create shard -shardgroup standby_shgrp -destination sd02 -credential cre_reg1 -sys_password 123456
GDSCTL> add invitednode sd03
GDSCTL> create shard -shardgroup primary_shgrp -destination sd03 -credential cre_reg1 -sys_password 123456
GDSCTL> add invitednode sd04
GDSCTL> create shard -shardgroup standby_shgrp -destination sd04 -credential cre_reg1 -sys_password 123456
5. 检查配置
GDSCTL> config
Regions
------------------------
region1                       
region2                       
GSMs
------------------------
sharddirector1                
sharddirector2                
Sharded Database
------------------------
cust_sdb                      
Databases
------------------------
sh2                           
sh3                           
sh4                           
sh5                           
Shard Groups
------------------------
primary_shgrp                 
standby_shgrp                 
Shard spaces
------------------------
shardspaceora                 
Services
------------------------
GDSCTL pending requests
------------------------
Command                       Object                        Status                        
-------                       ------                        ------                        
Global properties
------------------------
Name: oradbcloud
Master GSM: sharddirector1
DDL sequence #: 0
GDSCTL> config shardspace
Shard space                   Chunks                        
-----------                   ------                        
shardspaceora                 12                            
GDSCTL> 
GDSCTL> 
GDSCTL> config shardgroup
Shard Group         Chunks Region              Shard space         
-----------         ------ ------              -----------         
primary_shgrp       12     region1             shardspaceora       
standby_shgrp       12     region2             shardspaceora       
GDSCTL> config vncr
Name                          Group ID                      
----                          --------                      
10.0.99.103                                                 
sd01                                                        
sd02                                                        
sd03                                                        
sd04                                                        
GDSCTL> config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh2                 primary_shgrp       U         none        region1   -            
sh3                 standby_shgrp       U         none        region2   -            
sh4                 primary_shgrp       U         none        region1   -            
sh5                 standby_shgrp       U         none        region2   -            
#运行DEPLOY命令以创建分片和副本。
#该DEPLOY命令需要一些时间才能运行，大约需要15到30分钟
GDSCTL> deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh2' ...
deploy: network listener configuration successful at destination 'sd01'
deploy: starting DBCA at destination 'sd01' to create primary shard 'sh2' ...
deploy: deploying primary shard 'sh4' ...
deploy: network listener configuration successful at destination 'sd03'
deploy: starting DBCA at destination 'sd03' to create primary shard 'sh4' ...
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 'sd03' for shard 'sh4'
deploy: deploying standby shard 'sh5' ...
deploy: network listener configuration successful at destination 'sd04'
deploy: starting DBCA at destination 'sd04' to create standby shard 'sh5' ...
deploy: DBCA primary creation job succeeded at destination 'sd01' for shard 'sh2'
deploy: deploying standby shard 'sh3' ...
deploy: network listener configuration successful at destination 'sd02'
deploy: starting DBCA at destination 'sd02' to create standby shard 'sh3' ...
deploy: waiting for 2 DBCA standby creation job(s) to complete...
.
.
.
deploy: waiting for 2 DBCA standby creation job(s) to complete...
deploy: DBCA standby creation job succeeded at destination 'sd02' for shard 'sh3'
deploy: DBCA standby creation job succeeded at destination 'sd04' for shard 'sh5'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully

10 . 验证(gsm01)

#验证是否已部署所有分片
GDSCTL> config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh2                 primary_shgrp       Ok        Deployed    region1   ONLINE       
sh3                 standby_shgrp       Ok        Deployed    region2   READ ONLY    
sh4                 primary_shgrp       Ok        Deployed    region1   ONLINE       
sh5                 standby_shgrp       Ok        Deployed    region2   READ ONLY 
#验证所有分片均已注册
GDSCTL> databases
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     cust_sdb%1
Database: "sh3" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2
   Registered instances:
     cust_sdb%11
Database: "sh4" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     cust_sdb%21
Database: "sh5" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2
   Registered instances:
     cust_sdb%31
#检查分片的配置
GDSCTL> config shard -shard sh2
Name: sh2
Shard Group: primary_shgrp
Status: Ok
State: Deployed
Region: region1
Connection string: sd01:1521/sh2:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 19.0.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 
Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------

1. 添加一个在所有主分片上运行的全局服务

#oltp_rw_srvc全局服务是客户端可以用来连接到分片数据库的全局数据服务
#oltp_rw_srvc服务在主分片上运行OLTP事务
GDSCTL> add service -service oltp_rw_srvc -role primary 
GDSCTL> config service 
Name           Network name                      Pool           Started Preferred all 
----           ------------                      ----           ------- ------------- 
oltp_rw_srvc   oltp_rw_srvc.cust_sdb.oradbcloud cust_sdb       No      Yes           
#启动oltp_rw_srvc全局服务
GDSCTL> start service -service oltp_rw_srvc
GDSCTL> status service
Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "cust_sdb%1", name: "sh2", db: "sh2", region: "region1", status: ready.
   Instance "cust_sdb%21", name: "sh4", db: "sh4", region: "region1", status: ready.

1. 添加一个在所有备分片上运行的全局服务

#oltp_ro_srvc全局服务以在备用分片上运行只读工作负载
GDSCTL> add service -service oltp_ro_srvc -role physical_standby
GDSCTL> config service 
Name           Network name                      Pool           Started Preferred all 
----           ------------                      ----           ------- ------------- 
oltp_ro_srvc   oltp_ro_srvc.cust_sdb.oradbcloud cus_sdb        No      Yes                                                                                          
oltp_rw_srvc   oltp_rw_srvc.cust_sdb.oradbcloud    cust_sdb       Yes     Yes           
#启动只读服务
GDSCTL> start service -service oltp_ro_srvc
GDSCTL> status service
Service "oltp_ro_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "cust_sdb%11", name: "sh3", db: "sh3", region: "region2", status: ready.
   Instance "cust_sdb%31", name: "sh5", db: "sh5", region: "region2", status: ready.
Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "cust_sdb%1", name: "sh2", db: "sh2", region: "region1", status: ready.
   Instance "cust_sdb%21", name: "sh4", db: "sh4", region: "region1", status: ready.

1. 验证(gsm02)

[gds@gsm02 ~]$ gdsctl
GDSCTL> set gsm -gsm sharddirector2     
GDSCTL> connect mysdbadmin/oracle
GDSCTL> config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh2                 primary_shgrp       Ok        Deployed    region1   ONLINE       
sh3                 standby_shgrp       Ok        Deployed    region2   READ ONLY    
sh4                 primary_shgrp       Ok        Deployed    region1   ONLINE       
sh5                 standby_shgrp       Ok        Deployed    region2   READ ONLY 
GDSCTL> databases
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Service: "oltp_ro_srvc" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Service: "oltp_rw_srvc" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     cust_sdb%1
Database: "sh3" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2
   Service: "oltp_ro_srvc" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Service: "oltp_rw_srvc" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     cust_sdb%11
Database: "sh4" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Service: "oltp_ro_srvc" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Service: "oltp_rw_srvc" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     cust_sdb%21
Database: "sh5" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2
   Service: "oltp_ro_srvc" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Service: "oltp_rw_srvc" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     cust_sdb%31