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 | sh1 |/u01/../db_1 |10.0.99.105 |4GB 6 | sd02 | shard服务器2 | sh2 |/u01/../db_1 | 10.0.99.106 |4GB 7 | sd03 | shard服务器3 | sh3 |/u01/../db_1 |10.0.99.107 |4GB 8 | sd04 | shard服务器4 | sh4 |/u01/../db_1 | 10.0.99.108 |4GB 9 | sd05 | shard服务器5 | sh5 |/u01/../db_1 |10.0.99.109 |4GB 10 | sd06 | shard服务器6 | sh6 |/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 ``` 2. 安裝(在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 ``` 3. 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 ``` 4. 创建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 ``` 5. 設置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; ``` 6. 連接到分片導向器主機(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 ``` 7. 使用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 ``` 8. 連接到每個分片主機，在其上註冊遠程調度程序代理，並在其上為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=sh1 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1 export ORACLE_SID=sh1 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 ``` 9. 创建系统管理的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 ------------------------ sh1 sh2 sh3 sh4 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 ---- ----------- ------ ----- ------ ------------ sh1 primary_shgrp U none region1 - sh2 standby_shgrp U none region2 - sh3 primary_shgrp U none region1 - sh4 standby_shgrp U none region2 - #运行DEPLOY命令以创建分片和副本。 #该DEPLOY命令需要一些时间才能运行，大约需要15到30分钟 GDSCTL> deploy deploy: examining configuration... deploy: deploying primary shard 'sh1' ... deploy: network listener configuration successful at destination 'sd01' deploy: starting DBCA at destination 'sd01' to create primary shard 'sh1' ... deploy: deploying primary shard 'sh3' ... deploy: network listener configuration successful at destination 'sd03' deploy: starting DBCA at destination 'sd03' 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: DBCA primary creation job succeeded at destination 'sd03' for shard 'sh3' deploy: deploying standby shard 'sh4' ... deploy: network listener configuration successful at destination 'sd04' deploy: starting DBCA at destination 'sd04' to create standby shard 'sh4' ... deploy: DBCA primary creation job succeeded at destination 'sd01' for shard 'sh1' deploy: deploying standby shard 'sh2' ... deploy: network listener configuration successful at destination 'sd02' deploy: starting DBCA at destination 'sd02' to create standby shard 'sh2' ... 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 'sh2' deploy: DBCA standby creation job succeeded at destination 'sd04' for shard 'sh4' 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 ---- ----------- ------ ----- ------ ------------ sh1 primary_shgrp Ok Deployed region1 ONLINE sh2 standby_shgrp Ok Deployed region2 READ ONLY sh3 primary_shgrp Ok Deployed region1 ONLINE sh4 standby_shgrp Ok Deployed region2 READ ONLY #验证所有分片均已注册 GDSCTL> databases Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1 Registered instances: cust_sdb%1 Database: "sh2" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2 Registered instances: cust_sdb%11 Database: "sh3" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1 Registered instances: cust_sdb%21 Database: "sh4" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2 Registered instances: cust_sdb%31 #检查分片的配置 GDSCTL> config shard -shard sh1 Name: sh1 Shard Group: primary_shgrp Status: Ok State: Deployed Region: region1 Connection string: sd01:1521/sh1: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 ---- --------- ------ ``` 11. 添加一个在所有主分片上运行的全局服务 ``` #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: "sh1", db: "sh1", region: "region1", status: ready. Instance "cust_sdb%21", name: "sh3", db: "sh3", region: "region1", status: ready. ``` 12. 添加一个在所有备分片上运行的全局服务 ``` #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: "sh2", db: "sh2", region: "region2", status: ready. Instance "cust_sdb%31", name: "sh4", db: "sh4", region: "region2", status: ready. Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE Instance "cust_sdb%1", name: "sh1", db: "sh1", region: "region1", status: ready. Instance "cust_sdb%21", name: "sh3", db: "sh3", region: "region1", status: ready. ``` 13. 验证(gsm02) ``` [gds@gsm02 ~]$ gdsctl GDSCTL> set gsm -gsm sharddirector2 GDSCTL> connect mysdbadmin/oracle GDSCTL> config shard Name Shard Group Status State Region Availability ---- ----------- ------ ----- ------ ------------ sh1 primary_shgrp Ok Deployed region1 ONLINE sh2 standby_shgrp Ok Deployed region2 READ ONLY sh3 primary_shgrp Ok Deployed region1 ONLINE sh4 standby_shgrp Ok Deployed region2 READ ONLY GDSCTL> databases Database: "sh1" 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: "sh2" 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: "sh3" 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: "sh4" 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 ```