參考：
Oracle 12C Sharding部署和測(cè)試
12c新特性-Oracle Sharding簡(jiǎn)介

1.規(guī)劃

2.硬盤劃分

3. 禁用防火墻

#關(guān)閉firewall：停止firewall
systemctl stop firewalld.service

#禁止firewall開機(jī)啟動(dòng)
systemctl disable firewalld.service

4. 安裝基本工具

yum -y install nano vim wget curl net-tools lsof  zip unzip

yum -y install perl autoconf autoconf

cd /usr/local/src

wget http://www.rpmfind.net/linux/epel/6/x86_64/Packages/r/rlwrap-0.42-1.el6.x86_64.rpm

rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm

5. linux完整更新(可選項(xiàng))

yum update -y

6. 檢查并卸載OpenJDK & 安裝JDK 1.8

#卸載
rpm -qa | grep java

#安裝jdk
mkdir /u01/java -p
cd /u01/java/

#上傳jdk-8u181-linux-x64.tar.gz

tar -zxvf jdk-8u181-linux-x64.tar.gz
#設(shè)置環(huán)境變量
vim /etc/profile

在profile中添加如下內(nèi)容:
#set java environment
JAVA_HOME=/u01/java/jdk1.8.0_181
JRE_HOME=/u01/java/jdk1.8.0_181/jre
CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
export JAVA_HOME JRE_HOME CLASS_PATH PATH

#讓修改生效:
source /etc/profile
#輸入java -version查看一下jdk版本信息：
java -version

7. sysctl.conf 配置

grep MemTotal /proc/meminfo
MemTotal:        3861320 kB

#計(jì)算方法如下
shmall  = 3861320 * 1024 /4096 =965330
shmmax  =  3861320 * 1024 *0.9 =3558592512

[root@sd2 ~]# cat /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 965330
kernel.shmmax = 3558592512
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

#生效
sysctl -p

8. 配置主機(jī)名

[root@gsm1 ~]# cat /etc/sysconfig/network
# Created by anaconda
NETWORKING=yes
HOSTNAME=gsm1

[root@sd1 ~]# cat /etc/sysconfig/network
# Created by anaconda
NETWORKING=yes
HOSTNAME=sd1

[root@sd2 ~]# cat /etc/sysconfig/network
# Created by anaconda
NETWORKING=yes
HOSTNAME=sd2

vim /etc/hosts
#增加如下
192.168.40.135  gsm1
192.168.40.136  sd1
192.168.40.137  sd2

9. 配置limits.conf

vim /etc/security/limits.conf
#增加如下配置 
#最后兩行memlock =kernel.shmmax/1024 = 3558592512/1024=3475188
oracle  soft  nproc  16384
oracle  hard  nproc  16384
oracle  soft  nofile  16384
oracle  hard  nofile  65536
oracle  soft  stack  10240
oracle  hard  stack  32768
oracle  soft  memlock 3475188
oracle  hard  memlock 3475188

10. 編輯 /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
#SELINUX=enforcing
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
#SELINUXTYPE=targeted
SELINUX=permissive 

#生效 
setenforce Permissive

11. 創(chuàng)建用戶和組

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
#groupadd -g 54324 backupdba
#groupadd -g 54325 dgdba
#groupadd -g 54326 kmdba
#groupadd -g 54327 asmdba
#groupadd -g 54328 asmoper
#groupadd -g 54329 asmadmin
#groupadd -g 54330 racdba

useradd -u 54321 -g oinstall -G dba,oper oracle

#oracle用戶的登錄密碼，后續(xù)登錄要用漱牵，記著夺蛇。
echo "123456" | passwd --stdin oracle

#設(shè)置密碼永不過(guò)期
chage -M -1 oracle
chage -l oracle

id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper)

12. 配置/dev/shm大小

#驗(yàn)證共享內(nèi)存是否已正確安裝且大小足夠 
# > SGA+PGA 或 =物理內(nèi)存*50%

vim /etc/fstab

#修改/dev/shm的大小  
修改/etc/fstab的這行： 默認(rèn)的：
tmpfs /dev/shm tmpfs defaults 0 0
改成：
tmpfs /dev/shm tmpfs defaults,size=4G 0 0

#重新mount /dev/shm使之生效：
mount -o remount /dev/shm

df -h
...
tmpfs                        4.0G     0  4.0G   0% /dev/shm
...

13. 限制參數(shù)修改(可)

vim /etc/pam.d/login
#add
#這是對(duì)應(yīng)64位的
session    required    /lib64/security/pam_limits.so
#這是對(duì)應(yīng)32位的
#session  required    /lib/security/pam_limits.so

#修改ulimit：
 vim /etc/profile
#添加oracle 用戶 limit：
if [ $USER = "oracle" ]; then 
if [ $SHELL = "/bin/ksh" ]; then 
ulimit -p 16384 
ulimit -n 65536 
else 
ulimit -u 16384 -n 65536 
fi 
fi

#生效
source /etc/profile

14.創(chuàng)建目錄

mkdir -p /u01/app/oracle/
mkdir -p /u01/tmp/
#mkdir -p /u02/oradata/
#mkdir -p /u03/fra/

mkdir -p /u01/app/oracle/oradata/
mkdir -p /u01/app/oracle/fast_recovery_area

chown -R oracle:oinstall /u01/app/
chown -R oracle:oinstall /u01/tmp/
#chown -R oracle:oinstall /u02/oradata/
#chown -R oracle:oinstall /u03/fra/

chmod -R 775 /u01/app/
chmod -R 775 /u01/tmp/
#chmod -R 775 /u02/oradata/
#chmod -R 775 /u03/fra/

15.安裝oracle 12c 依賴包

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install make -y
yum install nfs-utils -y
yum install net-tools -y
yum install smartmontools -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

yum install gcc gcc-c++ xterm libXp e2fsprogs e2fsprogs-libs elfutils-libelf-devel libXrender -y
yum -y install bc

16. 創(chuàng)建scripts 目錄及setEnv.sh文件(從此處開始無(wú)特別說(shuō)明都是在oracle用戶下操作)

mkdir /home/oracle/scripts

#創(chuàng)建 setEnv.sh 文件 [不同機(jī)器上 ORACLE_ 的 HOSTNAME、UNQNAME酣胀、SID 不同]
##gsm1
----------------------------------------------------------------------
cat > /home/oracle/scripts/setEnv.sh <<EOF
# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP

export ORACLE_HOSTNAME=gsm1
export ORACLE_UNQNAME=catadb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/12.2.0.1/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'
EOF
=================================================================
##sd1
----------------------------------------------------------------------
cat > /home/oracle/scripts/setEnv.sh <<EOF
# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP

export ORACLE_HOSTNAME=sd1
export ORACLE_UNQNAME=sh1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/12.2.0.1/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'
EOF
=================================================================##sd2
----------------------------------------------------------------------
cat > /home/oracle/scripts/setEnv.sh <<EOF
# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP

export ORACLE_HOSTNAME=sd2
export ORACLE_UNQNAME=sh2
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/12.2.0.1/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'
EOF
=================================================================
#Add a reference to the "setEnv.sh" file at "/home/oracle/.bash_profile" file
echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile

#生效
source /home/oracle/.bash_profile

#Create a "start_all.sh" and "stop_all.sh" script 
---------------------------------------------------------------
cat > /home/oracle/scripts/start_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

dbstart \$ORACLE_HOME
EOF

---------------------------------------------------------------
cat > /home/oracle/scripts/stop_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

dbshut \$ORACLE_HOME
EOF
=====================================================
chown -R oracle.oinstall /home/oracle/scripts
chmod u+x /home/oracle/scripts/*.sh

17.安裝database 軟件

export DISPLAY=192.168.0.7:0.0

#安裝oracle 【說(shuō)明：本次僅安裝軟件】
unzip linuxx64_12201_database.zip
cd database/
./runInstaller

18. 創(chuàng)建并啟動(dòng)監(jiān)聽

netca

19. 啟停說(shuō)明 【附加】

#編輯 "/etc/oratab" file setting the restart flag for each instance to 'Y'.
catadb:/u01/app/oracle/product/12.2.0.1/db_1:Y

#通過(guò)如下腳本啟動(dòng)或停止數(shù)據(jù)庫(kù)
/home/oracle//scripts/start_all.sh
/home/oracle//scripts/stop_all.sh

20. 安裝GDS/GSM軟件【gsm1上安裝】

#創(chuàng)建 gds 用戶 【root 下操作】
useradd -g oinstall -G dba,oper gds

#gds用戶的登錄密碼刁赦，后續(xù)登錄要用，記著闻镶。
echo "123456" | passwd --stdin gds

#設(shè)置密碼永不過(guò)期
chage -M -1 gds
chage -l gds

#創(chuàng)建安裝目錄
mkdir -p /u05/app/oracle/
chown -R gds:oinstall /u05/app/
chmod -R 775 /u05/app/

mkdir -p /u05/tmp/
chown -R gds:oinstall /u05/tmp/
chmod -R 775 /u05/tmp/

# 查看環(huán)境變量
su - gds
vim /home/gds/.bash_profile
source /home/gds/.bash_profile
cat /home/gds/.bash_profile
-------------------------------------------------------------------------
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi
# User specific environment and startup programs
ORACLE_BASE=/u05/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.2.0/gsm_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

export PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TEMP=/u05/tmp
export TMPDIR=/u05/tmp
umask 022
=================================================================

#開始安裝gsm
export DISPLAY=192.168.0.7:0.0

unzip linuxx64_12201_gsm.zip

cd gsm

./runInstaller
# 安裝很簡(jiǎn)單甚脉，一直點(diǎn) next

21. 創(chuàng)建Shard Catalog database (即catadb 實(shí)例) -> gsm1 上 oracle 用戶

#在Shard catalog   服務(wù)器gsm1  上 創(chuàng)建 non-cdb (即不要勾選 create as container database) 
#字符集:AL32UTF8,National character set 選擇 AL16UTF。創(chuàng)建過(guò)程與普通數(shù)據(jù)庫(kù)相同
#選擇File System铆农，選擇Oracle-Managed Files(OMF)
#選擇Specify Fast Recovery Area 牺氨，Enable archiving
#不要選擇Configure EnterpriseManager(EM) database express
dbca

22. 配置GSM/Shard director -> gsm1 上 oracle 用戶

#檢查資料庫(kù)是否存在GSM用戶(必備條件)
#12c后新增了幾個(gè)用戶，下面這些用戶用于GDS
sql / as sysdba

SQL> SET SQLFORMAT ansiconsole

SQL> select username,account_status from dba_users where username like '%GSM%';
USERNAME           ACCOUNT_STATUS    
GSMCATUSER         EXPIRED & LOCKED  
GSMUSER            EXPIRED & LOCKED  
GSMADMIN_INTERNAL  EXPIRED & LOCKED

#解鎖用戶
sqlplus / as sysdba
SQL> alter user gsmcatuser identified by oracle account unlock;

#創(chuàng)建管理用戶mygds 【用于存儲(chǔ)Sharding 管理信息，GDSCTL 接口通過(guò)用戶mygds 連接到catalog 數(shù)據(jù)庫(kù)】
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;

#在gsm1服務(wù)器(catalog 數(shù)據(jù)庫(kù)/shard director)猴凹，啟動(dòng)listener

#步驟4 在gsm1服務(wù)器gds用戶下夷狰，創(chuàng)建shard catalog，在shard catalog中配置remote scheduler agent.
su - gds

參數(shù)含義：
-user : 指定管理用戶郊霎，在前面步驟中創(chuàng)建的catalog database管理用戶mygds
-database : 指定catalog database 信息沼头，catalog 數(shù)據(jù)庫(kù)的主機(jī)名:監(jiān)聽器port: catalog 數(shù)據(jù)庫(kù)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)類型

$ gdsctl
GDSCTL>
create shardcatalog -database gsm1:1521:catadb -chunks 12 -user mygds/oracle -sdb shdb -region region1, region2 -agent_port 8080 -agent_password oracle

#創(chuàng)建和啟動(dòng)shard director
參數(shù)含義：
-gsm: 指定shard director名稱
-listener: 指定shard director的監(jiān)聽端口，注意不能與數(shù)據(jù)庫(kù)的listener端口沖突
-catalog: 指定catalog database 信息庄撮，catalog數(shù)據(jù)庫(kù)的主機(jī)名:監(jiān)聽器port: catalog 數(shù)據(jù)庫(kù)db_name

GDSCTL>add gsm -gsm sharddirector3 -listener 1522 -pwd oracle -catalog gsm1:1521:catadb -region region1

GDSCTL>start gsm -gsm sharddirector3

#添加操作系統(tǒng)認(rèn)證.
GDSCTL> add credential -credential cre_reg1 -osaccount oracle -ospassword 123456

#步驟7 在所有的shard 節(jié)點(diǎn)（sd1背捌、sd2）分別執(zhí)行Agent
--在sd1節(jié)點(diǎn)執(zhí)行
[oracle@sd1 ~]$ schagent -start
[oracle@sd1 ~]$ schagent -status

--密碼oracle和端口8080是在第4步創(chuàng)建shardcatalog時(shí)設(shè)置的:
[oracle@sd1 ~] $ echo oracle | schagent -registerdatabase gsm1 8080
Agent Registration Password ?  
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!

--在sd2節(jié)點(diǎn)執(zhí)行
[oracle@sd2 ]$ schagent -start
[oracle@sd2 ]$ schagent -status

[oracle@sd2 ]$ echo oracle | schagent -registerdatabase gsm1 8080
Agent Registration Password ?  
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!

23. 創(chuàng)建System-Managed SDB

1.在Shard服務(wù)器 sd1 連接到shard director/GSM服務(wù)器(gsm1)
$ ssh gds@gsm1

2. 設(shè)置當(dāng)前session為sharddirector3 shard director.
$ gdsctl
GDSCTL>set gsm -gsm sharddirector3
GDSCTL>connect mygds/oracle
or
GDSCTL>connect mygds/oracle@gsm1:1521:catadb

3. 添加shardgroup， shardgroup是一組shard的集合洞斯，
# shardgroup名稱為primary_shardgroup毡庆，-deploy_as primary表示這個(gè)group中的shard都是主庫(kù)。
GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1

4. 將每個(gè)shard 地址添加到catalog 的 (VNCR) 列表烙如，并且創(chuàng)建shard
GDSCTL> add invitednode sd1 
GDSCTL>create shard -shardgroup primary_shardgroup -destination sd1 -credential cre_reg1 -sys_password 123456
------------------------------------------------------
The operation completed successfully
DB Unique Name: sh1

GDSCTL> add invitednode sd2
GDSCTL> create shard -shardgroup primary_shardgroup -destination sd2 -credential cre_reg1 -sys_password 123456
-------------------------------------------------------
The operation completed successfully
DB Unique Name: sh2

5. 檢查配置
GDSCTL>config

Regions
------------------------
region1                       
region2                       

GSMs
------------------------
sharddirector3                

Sharded Database
------------------------
shdb                          

Databases
------------------------
sh1                           
sh2                           

Shard Groups
------------------------
primary_shardgroup            

Shard spaces
------------------------
shardspaceora                 

Services
------------------------

GDSCTL pending requests
------------------------
Command                       Object                        Status                        
-------                       ------                        ------                        

Global properties
------------------------
Name: oradbcloud
Master GSM: sharddirector3
DDL sequence #: 0
----------------------------------------------------------------------------

GDSCTL>config shardspace
Shard space                   Chunks                        
-----------                   ------                        
shardspaceora                 12  
-------------------------------------------------------------------------------

GDSCTL>config shardgroup
Shard Group         Chunks Region              Shard space         
-----------         ------ ------              -----------         
primary_shardgroup  12     region1             shardspaceora 
----------------------------------------------------------------------------------

GDSCTL>config vncr
Name                          Group ID                      
----                          --------                      
sd1                                                         
sd2                                                         
192.168.40.135
-------------------------------------------------------------------------------------

GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh1                 primary_shardgroup  U         none        region1   -            
sh2                 primary_shardgroup  U         none        region1   -    
-------------------------------------------------------------------------------------------

6. 部署/deploy ( Shard數(shù)據(jù)庫(kù)部署過(guò)程采用靜默安裝方式 )
GDSCTL>deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh1' ...
deploy: network listener configuration successful at destination 'sd1'
deploy: starting DBCA at destination 'sd1' to create primary shard 'sh1' ...
deploy: deploying primary shard 'sh2' ...
deploy: network listener configuration successful at destination 'sd2'
deploy: starting DBCA at destination 'sd2' to create primary shard 'sh2' ...
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 'sd1' for shard 'sh1'
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'sd2' for shard 'sh2'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully

7. 檢查配置信息
GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh1                 primary_shardgroup  Ok        Deployed    region1   ONLINE       
sh2                 primary_shardgroup  Ok        Deployed    region1   ONLINE       
==================================================================
GDSCTL>databases
Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     shdb%1
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     shdb%11
===================================================================
GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd1:1521/sh1:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    
========================================================
GDSCTL>config shard -shard sh2
Name: sh2
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd2:1521/sh2:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    
====================================================================
8. 創(chuàng)建service
GDSCTL>add service -service oltp_rw_srvc -role primary

GDSCTL>start service -service oltp_rw_srvc

GDSCTL>status service
Service "oltp_rw_srvc.shdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "shdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
   Instance "shdb%11", name: "sh2", db: "sh2", region: "region1", status: ready.

24. 創(chuàng)建用戶和對(duì)象

1. 在catalog數(shù)據(jù)庫(kù) [gsm1 上的catadb 實(shí)例] 中創(chuàng)建業(yè)務(wù)用戶
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;

2. 創(chuàng)建表空間集合
SQL> CREATE TABLESPACE SET TSP_SET_1 using template 
(
 datafile size 64M autoextend on next 64M maxsize unlimited 
 extent management local segment space management auto
);

3. 為duplicated tables創(chuàng)建表空間么抗，這個(gè)測(cè)試中duplicated table是Products table.
SQL> 
CREATE TABLESPACE products_tsp datafile size 64m autoextend on next 64M maxsize unlimited extent management local uniform size 1m;

4. 創(chuàng)建root 表Customers
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;

5. 創(chuàng)建其他sharded table Orders.
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); 

6. 為 OrderId 列創(chuàng)建序列
SQL> CREATE SEQUENCE Orders_Seq;  

7. 創(chuàng)建SHARDED TABLE LineItems
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);

8. 創(chuàng)建duplicated tables. （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;  

9. 創(chuàng)建function，目的是為了后面的DEMO：
SQL> 
CREATE OR REPLACE FUNCTION PasswCreate(PASSW IN RAW)
RETURN RAW
IS
Salt RAW(8);
BEGIN
Salt := DBMS_CRYPTO.RANDOMBYTES(8);
RETURN UTL_RAW.CONCAT(Salt, DBMS_CRYPTO.HASH(UTL_RAW.CONCAT(Salt,
PASSW), DBMS_CRYPTO.HASH_SH256));
END;
/

CREATE OR REPLACE FUNCTION PasswCheck(PASSW IN RAW, PHASH IN RAW)
RETURN INTEGER IS
BEGIN
RETURN UTL_RAW.COMPARE(
DBMS_CRYPTO.HASH(UTL_RAW.CONCAT(UTL_RAW.SUBSTR(PHASH, 1, 8),
PASSW), DBMS_CRYPTO.HASH_SH256),
UTL_RAW.SUBSTR(PHASH, 9));
END;
/

10. 檢查是否有錯(cuò)誤：
GDSCTL>connect mygds/oracle
GDSCTL>show ddl
id      DDL Text                                 Failed shards 
--      --------                                 ------------- 
7        grant execute on dbms_crypto to app_...               
8       CREATE TABLESPACE SET TSP_SET_1 using...               
9       CREATE TABLESPACE products_tsp datafi...               
10      CREATE SHARDED TABLE Customers   (   ...               
11      CREATE SHARDED TABLE Orders   (     O...               
12      CREATE SEQUENCE Orders_Seq                             
13      CREATE SHARDED TABLE LineItems   (   ...               
14      CREATE MATERIALIZED VIEW "APP_SCHEMA"...               
15      CREATE OR REPLACE FUNCTION PasswCreat...               
16      CREATE OR REPLACE FUNCTION PasswCheck...   

11. 檢查每個(gè)shard是否有DDL錯(cuò)誤
GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd1:1521/sh1:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    
oltp_rw_srvc                                                    Yes       Enabled   


GDSCTL>config shard -shard sh2  
Name: sh2
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sd2:1521/sh2:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    
oltp_rw_srvc                                                    Yes       Enabled

25. 驗(yàn)證環(huán)境-表空間/chunks

1. 在gsm節(jié)點(diǎn)亚铁，檢查chunks信息
前面創(chuàng)建shardcatalog時(shí)指定chunks為12蝇刀，因此后續(xù)創(chuàng)建shard table分配12個(gè)chunks
GDSCTL>config chunks
Chunks
------------------------
Database                      From      To        
--------                      ----      --        
sh1                           1         6         
sh2                           7         12

2. 在sd1節(jié)點(diǎn)檢查表空間和chunks信息
--表空間
--創(chuàng)建了6個(gè)表空間，分別是C001TSP_SET_1 ~ 表空間C006TSP_SET_1徘溢，因?yàn)樵O(shè)置chunks=12吞琐，
--每個(gè)shard有6個(gè)chunks曾雕。每個(gè)表空間有一個(gè)datafile，大小是64M肯污，
--這個(gè)是在創(chuàng)建tablespace set時(shí)設(shè)置的datafile 64M。
[oracle@sd1 ~]$ sqlplus / as sysdba

SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;

TABLESPACE_NAME                MB
------------------------------ ----------
C001TSP_SET_1                  64
C002TSP_SET_1                  64
C003TSP_SET_1                  64
C004TSP_SET_1                  64
C005TSP_SET_1                  64
C006TSP_SET_1                  64
PRODUCTS_TSP                   64
SYSAUX                    470
SYSTEM                    800
TSP_SET_1                  64
UNDOTBS1                   70

TABLESPACE_NAME                MB
------------------------------ ----------
USERS                   5

12 rows selected.


--檢查chunks
SQL> 
set linesize 140
column table_name format a20
column tablespace_name format a20
column partition_name format a20

SQL> show parameter db_unique_name
NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_unique_name               string  sh1

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_P1     C001TSP_SET_1
CUSTOMERS        CUSTOMERS_P1     C001TSP_SET_1
LINEITEMS        CUSTOMERS_P1     C001TSP_SET_1
CUSTOMERS        CUSTOMERS_P2     C002TSP_SET_1
LINEITEMS        CUSTOMERS_P2     C002TSP_SET_1
ORDERS           CUSTOMERS_P2     C002TSP_SET_1
CUSTOMERS        CUSTOMERS_P3     C003TSP_SET_1
ORDERS           CUSTOMERS_P3     C003TSP_SET_1
LINEITEMS        CUSTOMERS_P3     C003TSP_SET_1
ORDERS           CUSTOMERS_P4     C004TSP_SET_1
CUSTOMERS        CUSTOMERS_P4     C004TSP_SET_1

TABLE_NAME       PARTITION_NAME   TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS        CUSTOMERS_P4     C004TSP_SET_1
CUSTOMERS        CUSTOMERS_P5     C005TSP_SET_1
LINEITEMS        CUSTOMERS_P5     C005TSP_SET_1
ORDERS           CUSTOMERS_P5     C005TSP_SET_1
CUSTOMERS        CUSTOMERS_P6     C006TSP_SET_1
LINEITEMS        CUSTOMERS_P6     C006TSP_SET_1
ORDERS           CUSTOMERS_P6     C006TSP_SET_1


3. 在sd2節(jié)點(diǎn)檢查表空間和chunks信息
--表空間
--創(chuàng)建了6個(gè)表空間，分別是C007TSP_SET_1 ~ 表空間C00CTSP_SET_1灰伟，因?yàn)樵O(shè)置chunks=12挡爵，
--每個(gè)shard有6個(gè)chunks。每個(gè)表空間有一個(gè)datafile男杈，大小是64M肤无，
--這個(gè)是在創(chuàng)建tablespace set時(shí)設(shè)置的datafile 64M。
[oracle@sd2 oradata]$ sqlplus / as sysdba
 
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;

TABLESPACE_NAME                MB
------------------------------ ----------
C007TSP_SET_1                  64
C008TSP_SET_1                  64
C009TSP_SET_1                  64
C00ATSP_SET_1                  64
C00BTSP_SET_1                  64
C00CTSP_SET_1                  64
PRODUCTS_TSP                   64
SYSAUX                    470
SYSTEM                    800
TSP_SET_1                  64
UNDOTBS1                   70

TABLESPACE_NAME                MB
------------------------------ ----------
USERS                   5

12 rows selected.


--檢查chunks
SQL> 
set linesize 140
column table_name format a20
 column tablespace_name format a20
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;

TABLE_NAME       PARTITION_NAME   TABLESPACE_NAME
-------------------- -------------------- --------------------
ORDERS           CUSTOMERS_P7     C007TSP_SET_1
CUSTOMERS        CUSTOMERS_P7     C007TSP_SET_1
LINEITEMS        CUSTOMERS_P7     C007TSP_SET_1
CUSTOMERS        CUSTOMERS_P8     C008TSP_SET_1
LINEITEMS        CUSTOMERS_P8     C008TSP_SET_1
ORDERS           CUSTOMERS_P8     C008TSP_SET_1
CUSTOMERS        CUSTOMERS_P9     C009TSP_SET_1
ORDERS           CUSTOMERS_P9     C009TSP_SET_1
LINEITEMS        CUSTOMERS_P9     C009TSP_SET_1
ORDERS           CUSTOMERS_P10    C00ATSP_SET_1
CUSTOMERS        CUSTOMERS_P10    C00ATSP_SET_1

TABLE_NAME       PARTITION_NAME   TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS        CUSTOMERS_P10    C00ATSP_SET_1
CUSTOMERS        CUSTOMERS_P11    C00BTSP_SET_1
LINEITEMS        CUSTOMERS_P11    C00BTSP_SET_1
ORDERS           CUSTOMERS_P11    C00BTSP_SET_1
CUSTOMERS        CUSTOMERS_P12    C00CTSP_SET_1
LINEITEMS        CUSTOMERS_P12    C00CTSP_SET_1
ORDERS           CUSTOMERS_P12    C00CTSP_SET_1

18 rows selected.


4. 在catalog數(shù)據(jù)庫(kù)檢查chunks信息（gsm1上）
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
------------------------------ ----------------
sh1                       6
sh2                       6


5.驗(yàn)證環(huán)境-tables
--catalog數(shù)據(jù)庫(kù)
SQL> conn app_schema/oracle

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
PRODUCTS
MLOG$_PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS
RUPD$_PRODUCTS

--shard節(jié)點(diǎn)sd1和sd2
SQL> conn app_schema/oracle

SQL> select table_name from user_tables;

TABLE_NAME
--------------------
PRODUCTS
CUSTOMERS
ORDERS
LINEITEMS

26. 訪問單獨(dú)一個(gè)shard

在連接串中指定sharding key较锡，那么GSM/shard director將請(qǐng)求連接到對(duì)應(yīng)的一個(gè)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

$ 
sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=gsm1)(port=1522))(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=james.parker@x.bogus)))'

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
sh1

--插入數(shù)據(jù)
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,Class, Geo, Passwd) 
VALUES ( 'james.parker@x.bogus', 'James', 'Parker',NULL, 'Gold', 'east', hextoraw('8d1c00e') );

SQL> commit;

SQL> exit

[oracle@gsm1 ~]$ 
sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=gsm1)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=james.parker@x.bogus)))'

SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
sh1

SQL> 
column custid format a20
column firstname format a15
column lastname format a15
SQL> select custid, FirstName, LastName, class, geo from customers where custid = 'james.parker@x.bogus';

CUSTID               FIRSTNAME       LASTNAME        CLASS      GEO
-------------------- --------------- --------------- ---------- --------
james.parker@x.bogus James           Parker          Gold       east

SQL> SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
shdb%1

--查詢SHARDING_KEY=tom.david亮瓷，連接到sd2：
SQL>
sqlplus app_schema/oracle@'(description=(address=(protocol=tcp)(host=gsm1)(port=1522))
(connect_data=(service_name=oltp_rw_srvc.shdb.oradbcloud)(region=region1)(SHARDING_KEY=tom.david)))'

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
sh2

SQL> SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
shdb%11

SQL> select custid, FirstName, LastName, class, geo from customers;

no rows selected

SQL> select custid, FirstName, LastName, class, geo from customers where custid = 'james.parker@x.bogus';

no rows selected

27. 訪問多個(gè)shard

如果在連接串中指定sharding key扔枫，那么GSM/shard director將請(qǐng)求連接到對(duì)應(yīng)的一個(gè)shard汛聚。
如果沒有指定sharding key，那么session和coordinator database (shard catalog)建立連接,然后再分別到需要(prund)的shard中查詢短荐，最后再整合倚舀。
優(yōu)化器判斷訪問一個(gè)shard還是訪問多個(gè)shard叹哭。

--鏈接到catalog數(shù)據(jù)庫(kù)查詢
$ sqlplus app_schema/oracle@gsm1:1521/GDS\$CATALOG.oradbcloud

SQL>
column custid format a20
column firstname format a15
column lastname format a15
SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
catadb

SQL> select custid, FirstName, LastName, class, geo from customers;

CUSTID           FIRSTNAME       LASTNAME        CLASS  GEO
-------------------- --------------- --------------- ---------- --------
james.parker@x.bogus James       Parker      Gold   east

SQL> select custid, FirstName, LastName, class, geo from customers where custid = 'james.parker@x.bogus';

CUSTID           FIRSTNAME       LASTNAME        CLASS  GEO
-------------------- --------------- --------------- ---------- --------
james.parker@x.bogus James       Parker      Gold   east


set termout on
set linesize 120
set echo on
column firstname format a20
column lastname format a20

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;

Explained.

SQL> select plan_table_output from table(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1622328711

-------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   100 |  7700 |     1 (100)| 00:00:01 |        |      |
|   1 |  SORT ORDER BY    |           |   100 |  7700 |     1 (100)| 00:00:01 |        |      |
|   2 |   VIEW        | VW_SHARD_5B3ACD5D |   100 |  7700 |     5 (100)| 00:00:01 |        |      |
|   3 |    SHARD ITERATOR |           |       |       |        |          |        |      |
|   4 |     REMOTE    |           |       |       |        |          | ORA_S~ | R->S |
-------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
       "A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS" FROM "CUSTOMERS" "A1" WHERE
       ("A1"."CUSTID"='Mary.Parker@x.bogus' OR "A1"."CUSTID"='Scott.Tiger@x.bogus') AND
       "A1"."CLASS"<>'free' /* coord_sql_id=462qrk7rf02kq */  (accessing
       'ORA_SHARD_POOL@ORA_MULTI_TARGET' )


21 rows selected.

[oracle@gsm1 ~]$ sqlplus app_schema/oracle

SQL>
column custid format a20
column firstname format a15
column lastname format a15

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
catadb

SQL> select custid, FirstName, LastName, class, geo from customers where custid = 'james.parker@x.bogus';

CUSTID           FIRSTNAME       LASTNAME        CLASS  GEO
-------------------- --------------- --------------- ---------- --------
james.parker@x.bogus James       Parker      Gold   east

28.DEMO 部署

1. 下載demo
2. [oracle@gsm1 ~]$ unzip sdb_demo_app.zip 
3. 創(chuàng)建額外對(duì)象，需要手動(dòng)修改demo_app_ext.sql中app_schema的密碼
[oracle@gsm1 ~]$ cd sdb_demo_app/sql

[oracle@gsm1 sql]$ sqlplus / as sysdba

SQL>@demo_app_ext.sql
4. 修改配置文件
[oracle@gsm1 sdb_demo_app]$ cat demo.properties 
name=demo
connect_string=(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS=(HOST=gsm1)(PORT=1522)(PROTOCOL=tcp)))
monitor.user=dbmonuser
monitor.pass=TEZiPP4MsLLL
app.service.write=oltp_rw_srvc.shdb.oradbcloud
app.service.readonly= oltp_rw_srvc.shdb.oradbcloud
app.user=app_schema
app.pass=oracle
app.threads=7

5. 運(yùn)行demo
./run.sh demo
6. 運(yùn)行monitor
./run.sh monitor
7. 訪問web瞄桨，監(jiān)控性能话速。性能與測(cè)試環(huán)境有關(guān)系，這篇文章只是提供一個(gè)實(shí)驗(yàn)環(huán)境芯侥，非生產(chǎn)環(huán)境。
訪問  http://192.168.40.135:8081

image.png