Oracle19.3Sharding安裝配置之02(安裝Sharding-系統管理分片)
- 规划
| 序号 | 主机名 | 組件 | 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
- 安裝(在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
- 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
- 创建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
- 設置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;
- 連接到分片導向器主機(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
- 使用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
- 連接到每個分片主機，在其上註冊遠程調度程序代理，並在其上為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
- 创建系统管理的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)
创新互联建站是专业的曲阜网站建设公司，曲阜接单;提供成都做网站、网站建设,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行曲阜网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
#验证是否已部署所有分片
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
---- --------- ------
- 添加一个在所有主分片上运行的全局服务
#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.
- 添加一个在所有备分片上运行的全局服务
#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.
- 验证(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
网站栏目：Oracle19.3Sharding安裝配置之02(安裝Sharding-系統管理分片)
网页URL：http://pwwzsj.com/article/gejped.html
