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

1. 规划

#上面所有主鍵的 hosts 文件 添加如下信息10.0.99.101 gsm0110.0.99.102 gsm0210.0.99.103 sc0110.0.99.104 sc0210.0.99.105 sd0110.0.99.106 sd0210.0.99.107 sd0310.0.99.108 sd0410.0.99.109 sd0510.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]$ lsgsm[gds@gsm01 setup]$ cd gsm[gds@gsm01 gsm]$ lsinstall  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 Settingsexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_HOSTNAME=sc01export ORACLE_UNQNAME=catadbexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1export ORACLE_SID=catadbexport PATH=/usr/sbin:/usr/local/bin:$PATHexport PATH=$ORACLE_HOME/bin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibalias 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 sysdbaSQL> show parameter db_create_file_dest;NAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------db_create_file_dest             string     /u01/app/oracle/oradataSQL> show parameter open_links;NAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------open_links                 integer     4open_links_per_instance          integer     4alter 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 immediateSQL> startup#在目錄數據庫上授予角色和特權a 解鎖並設置GSMCATUSER模式的密碼[oracle@sc01 ~]$ sql / as sysdbaSQL> SET SQLFORMAT ansiconsoleSQL>  SET SQLFORMAT ansiconsoleSQL>  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 ~]$ gdsctlGDSCTL: Version 19.0.0.0.0 - Production on Thu Mar 19 17:11:07 CST 2020Copyright (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 region1GDSCTL> start gsm -gsm sharddirector1#使用GDSCTL設置操作系統憑據（僅gsm01上）GDSCTL> add credential -credential cre_reg1 -osaccount oracle -ospassword 123456#gsm02上[gds@gsm02 ~]$ gdsctlGDSCTL> add gsm -gsm sharddirector2 -listener 1572 -pwd oracle -catalog sc01:1521:catadb -region region2GDSCTL> 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 Settingsexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_HOSTNAME=sd01export ORACLE_UNQNAME=sh2export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1export ORACLE_SID=sh2export PATH=/usr/sbin:/usr/local/bin:$PATHexport PATH=$ORACLE_HOME/bin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibalias 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 ~]$ gdsctlGDSCTL> set gsm -gsm sharddirector1GDSCTL> 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) 列表，并且创建shardGDSCTL> add invitednode sd01GDSCTL> create shard -shardgroup primary_shgrp -destination sd01 -credential cre_reg1 -sys_password 123456GDSCTL> add invitednode sd02GDSCTL> create shard -shardgroup standby_shgrp -destination sd02 -credential cre_reg1 -sys_password 123456GDSCTL> add invitednode sd03GDSCTL> create shard -shardgroup primary_shgrp -destination sd03 -credential cre_reg1 -sys_password 123456GDSCTL> add invitednode sd04GDSCTL> create shard -shardgroup standby_shgrp -destination sd04 -credential cre_reg1 -sys_password 1234565. 检查配置GDSCTL> configRegions------------------------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: oradbcloudMaster GSM: sharddirector1DDL sequence #: 0GDSCTL> config shardspaceShard space                   Chunks                        -----------                   ------                        shardspaceora                 12                            GDSCTL> GDSCTL> GDSCTL> config shardgroupShard Group         Chunks Region              Shard space         -----------         ------ ------              -----------         primary_shgrp       12     region1             shardspaceora       standby_shgrp       12     region2             shardspaceora       GDSCTL> config vncrName                          Group ID                      ----                          --------                      10.0.99.103                                                 sd01                                                        sd02                                                        sd03                                                        sd04                                                        GDSCTL> config shardName                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> deploydeploy: 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 GSMdeploy: shards configured successfullyThe operation completed successfully

10 . 验证(gsm01)

#验证是否已部署所有分片GDSCTL> config shardName                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> databasesDatabase: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1   Registered instances:     cust_sdb%1Database: "sh3" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2   Registered instances:     cust_sdbDatabase: "sh4" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1   Registered instances:     cust_sdb%21Database: "sh5" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2   Registered instances:     cust_sdb%31#检查分片的配置GDSCTL> config shard -shard sh2Name: sh2Shard Group: primary_shgrpStatus: OkState: DeployedRegion: region1Connection string: sd01:1521/sh2:dedicatedSCAN address: ONS remote port: 0Disk Threshold, ms: 20CPU Threshold, %: 75Version: 19.0.0.0Failed DDL: DDL Error: ---Failed DDL id: Availability: ONLINERack: 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_srvcGDSCTL> status serviceService "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_standbyGDSCTL> 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_srvcGDSCTL> status serviceService "oltp_ro_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE   Instance "cust_sdb", 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 ~]$ gdsctlGDSCTL> set gsm -gsm sharddirector2     GDSCTL> connect mysdbadmin/oracleGDSCTL> config shardName                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> databasesDatabase: "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%1Database: "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_sdbDatabase: "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%21Database: "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