Oracle sharding database

eric0435發表於2017-10-17
OracleDatabase

Sharding架構是資料庫層面的一種分片技術，可以使分過區的資料分佈在各不相同的獨立資料庫裡。Sharding是Oracle Database 12c Release 2的新特性，它能為適合於 Sharding技術的OLTP應用提供線性擴充套件和完全錯誤隔離的能力，q 我們可以將 Sharding簡單地理解為Oracle 表分割槽技術的擴充套件，下面將詳細描述安裝操作。

1.Oracle Sharding 安裝條件
12.2企業版
non-cdb
使用檔案系統而非ASM （12.2 Beta要求，正式發行後，可能會改）
主機hosts檔案寫上本機和各個shard node的IP解析
機器必須全新，不能殘留之前有安裝過oracle的資訊。

2.設定環境變數，建立相關目錄與設定核心引數
shardcat和gds都安裝在一個主機上，同一個oracle使用者，不同ORACLE_HOME。 

[oracle12c@sdb1 ~]$ cat .bash_profile
# .bash_profile
 
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
 
# User specific environment and startup programs
 
PATH=$PATH:$HOME/bin
 
export PATH
 
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
 
ORACLE_BASE=/u01/ora12c/app/oracle; export ORACLE_BASE
DB_HOME=$ORACLE_BASE/product/12.2.0/db_1; export DB_HOME
GSM_HOME=$ORACLE_BASE/product/12.2.0/gsm; export GSM_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=shardcat; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch; export PATH
 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL


[root@shard1 ~]# groupadd -g 1009 dba
[root@shard1 ~]# groupadd -g 1010 oper
[root@shard1 ~]# groupadd -g 1011 oinstall
[root@shard1 ~]# useradd -u 1001 -g oinstall -G dba,oper oracle
[root@shard1 ~]# passwd oracle
Changing password for user oracle.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@shard1 ~]# mkdir -p /u01/app/oraInventory
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oraInventory
[root@shard1 ~]# chmod -R 775 /u01/app/oraInventory
[root@shard1 ~]# mkdir -p /u01/app/oracle
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle
[root@shard1 ~]# chmod -R 775 /u01/app/oracle
[root@shard1 ~]# mkdir -p /u01/app/oracle/product/12.2.0/db
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/db
[root@shard1 ~]# chmod -R 775 /u01/app/oracle/product/12.2.0/db
[root@shard1 ~]# mkdir /u01/tmp
[root@shard1 ~]# chmod a+wr /u01/tmp
[root@shard1 ~]# mkdir -p /u01/app/oracle/product/12.2.0/gsm
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/gsm
[root@shard1 ~]# chmod -R 775 /u01/app/oracle/product/12.2.0/gsm
[root@shard1 ~]# su - oracle


[oracle@shard1 ~]$ vi .bash_profile
export PATH
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin


export PATH
TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/oracle
DB_HOME=/u01/app/oracle/product/12.2.0/db
GSM_HOME=/u01/app/oracle/product/12.2.0/gsm
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=jytest2
export ORACLE_UNQNAME=jytest
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022

[oracle@shard1 ~]$ alias gsm_env='. /home/oracle/gsm_env'
[oracle@shard1 ~]$ alias db_env='. /home/oracle/db_env' 
[oracle@shard1 ~]$ vi gsm_env
ORACLE_HOME=$GSM_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

[oracle@shard1 ~]$ vi db_env
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

修改核心引數編輯/etc/sysctl.conf檔案 

[root@shard1 ~]# vi /etc/sysctl.conf
# System default settings live in /usr/lib/sysctl.d/00-system.conf.
# To override those settings, enter new settings here, or in an /etc/sysctl.d/.conf file
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

[root@shard1 ~]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

修改oarcle引數的shell限制,在所有節點的/etc/security/limits.conf檔案中新增以下引數 

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock 3145728
oracle hard memlock 3145728

修改shell的預設引數檔案，在所有節點的/etc/profile檔案中新增以下內容： 

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

3.安裝Oracle軟體 

[root@shard1 soft]# unzip linuxx64_12201_database.zip

[root@shard1 soft]# chown -R oracle:oinstall database

[root@shard1 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@shard1 Desktop]# su - oracle
Last login: Thu Oct 12 12:01:58 CST 2017 on pts/1
[oracle@shard1 ~]$ export DISPLAY=:1
[oracle@shard1 ~]$ cd /soft/database
[oracle@shard1 database]$ ls -lrt
total 24
-rwxr-xr-x.  1 oracle oinstall  500 Feb  7  2013 welcome.html
drwxr-xr-x.  4 oracle oinstall 4096 Jan 26  2017 install
-rwxr-xr-x.  1 oracle oinstall 8771 Jan 26  2017 runInstaller
drwxr-xr-x.  2 oracle oinstall   34 Jan 26  2017 rpm
drwxrwxr-x.  2 oracle oinstall   28 Jan 26  2017 sshsetup
drwxrwxr-x.  2 oracle oinstall   58 Jan 26  2017 response
drwxr-xr-x. 14 oracle oinstall 4096 Jan 26  2017 stage
[oracle@shard1 database]$ ./runInstaller


[root@shard1 soft]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@shard1 soft]# /u01/app/oracle/product/12.2.0/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/12.2.0/db

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : 
yes
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/app/oracle/product/12.2.0/db/install/root_shard1_2017-10-12_13-06-02-537061115.log
Finished installing Oracle Trace File Analyzer (TFA)

其它兩個shard主機shard2,shard3安裝Oracle軟體的操作不再描述同上。

shard2上的oracle環境變數設定如下,其中ORACLE_SID與
ORACLE_UNQNAME設定為sh1，在後面向shard catalog資料庫註冊shard時會讀取到 

[oracle@shard2 ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin


export PATH
TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/oracle
DB_HOME=/u01/app/oracle/product/12.2.0/db
GSM_HOME=/u01/app/oracle/product/12.2.0/gsm
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=sh1
export ORACLE_UNQNAME=sh1
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022

shard3上的oracle環境變數設定如下,其中ORACLE_SID與
ORACLE_UNQNAME設定為sh1，在後面向shard catalog資料庫註冊shard時會讀取到 

[oracle@shard3 ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin


export PATH
TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/oracle
DB_HOME=/u01/app/oracle/product/12.2.0/db
GSM_HOME=/u01/app/oracle/product/12.2.0/gsm
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=sh2
export ORACLE_UNQNAME=sh2
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022

4.安裝Shard Director Software 

[root@shard1 soft]# unzip linuxx64_12201_gsm.zip

[root@shard1 Desktop]# xhost + 
access control disabled, clients can connect from any host
[root@shard1 Desktop]# su - oracle
Last login: Thu Oct 12 18:05:56 CST 2017 on pts/0
[oracle@shard1 ~]$ export DISPLAY=:1
[oracle@shard1 ~]$ cd /soft/gsm
[oracle@shard1 gsm]$ ls -lrt
total 24
-rwxrwxr-x.  1 oracle oinstall  500 Feb  7  2013 welcome.html
-rwxr-xr-x.  1 oracle oinstall 8772 Jan 26  2017 runInstaller
drwxr-xr-x.  4 oracle oinstall 4096 Jan 26  2017 install
drwxrwxr-x.  2 oracle oinstall   28 Jan 26  2017 response
drwxr-xr-x. 14 oracle oinstall 4096 Jan 26  2017 stage
[oracle@shard1 gsm]$ ./runInstaller


[root@shard1 soft]# /u01/app/oracle/product/12.2.0/gsm/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/12.2.0/gsm

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

5.建立shard catalog資料庫
執行dbca開始建立資料庫例項，這個例項是放分片資料的後設資料的。我們把這個例項名叫shardcat。 

[root@shard1 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@shard1 Desktop]# su - oracle
Last login: Thu Oct 12 18:35:35 CST 2017 on pts/1
[oracle@shard1 ~]$ export DISPLAY=:1
[oracle@shard1 ~]$ dbca


[oracle@shard1 arch]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-OCT-2017 18:58:06

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shard1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                12-OCT-2017 18:26:22
Uptime                    0 days 0 hr. 31 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "shardcat" has 1 instance(s).
  Instance "shardcat", status READY, has 1 handler(s) for this service...
Service "shardcatXDB" has 1 instance(s).
  Instance "shardcat", status READY, has 1 handler(s) for this service...
The command completed successfully

6.設定Oracle Sharding Management and Routing Tier
登入shardcat主機，登入shardcat資料庫：--建立tablespace set需要使用omf，所以需要指定db_create_file_dest引數。 

[oracle@shard1 ~]$ echo $ORACLE_SID
shardcat
[oracle@shard1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 12 19:01:30 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;

System altered.

SQL> alter system set open_links=16 scope=spfile;

System altered.

SQL> alter system set open_links_per_instance=16 scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8622776 bytes
Variable Size             620760392 bytes
Database Buffers         1509949440 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.
SQL> alter user gsmcatuser account unlock;

User altered.

SQL> alter user gsmcatuser identified by oracle;

User altered.

SQL> create user mygdsadmin identified by oracle;

User created.

SQL> grant connect, create session, gsmadmin_role to mygdsadmin;

Grant succeeded.

SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;

Grant succeeded.

（update 2016-11-10 注：在12.2. beta 2後可以不做這步）
SQL> alter system set events 'immediate trace name GWM_TRACE level 7'; 

System altered.

（update 2016-11-10  注：在12.2. beta 2後可以不做這步）
SQL> alter system set event='10798 trace name context forever, level 7' scope=spfile;

System altered.

SQL> execute dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/prvtrsch.plb 

PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


Session altered.


Package created.

No errors.

Grant succeeded.


Session altered.


Session altered.


Package body created.

No errors.

Session altered.


Session altered.


Procedure created.

No errors.

Function created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Audit policy altered.


1 row updated.


Commit complete.


Session altered.


PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracle'); 

PL/SQL procedure successfully completed.

SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8622776 bytes
Variable Size             620760392 bytes
Database Buffers         1509949440 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.

在所有的shard節點分別執行Agent註冊
登入shard2主機： 

[oracle@shard2 ~]$ schagent -start

Scheduler agent started using port 65121
[oracle@shard2 ~]$ schagent status
Agent running with PID 12078

Agent_version:12.2.0.1.2
Running_time:00:00:10
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/12.2.0/db
ORACLE_BASE:/u01/app/oracle
Port:65121
Host:shard2

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

[oracle@shard2 ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@shard2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

登入shard3主機： 

[oracle@shard3 ~]$ source .bash_profile
[oracle@shard3 ~]$ schagent -start

Scheduler agent started using port 35374
[oracle@shard3 ~]$ schagent -status
Agent running with PID 13019

Agent_version:12.2.0.1.2
Running_time:00:00:07
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/12.2.0/db
ORACLE_BASE:/u01/app/oracle
Port:35374
Host:shard3

[oracle@shard3 ~]$ echo oracle|schagent -registerdatabase shard1 8080
Agent Registration Password ?  
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
[oracle@shard3 ~]$  mkdir -p /u01/app/oracle/oradata
[oracle@shard3 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

7.Deploying and Managing a System-Managed SDB
我們開始部署，以最簡單的System-Managed SDB為例。
另外，admin guide中介紹的是4臺主機做shard node，其中每2臺互為dataguard主備。我們這邊為了節約空間和資源，不搞dataguard了，只建立primary庫。因此只要2臺主機做shard node。先設定gsm的環境變數
進入到GDSCTL命令列，建立shard catalog。 

[oracle@shard1 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@shard1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsm
[oracle@shard1 ~]$ export PATH=/u01/app/oracle/product/12.2.0/gsm/bin:$PATH:$HOME/bin
[oracle@shard1 ~]$ gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Thu Oct 12 19:24:36 CST 2017

Copyright (c) 2011, 2016, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Warning: current GSM name is not set automatically because gsm.ora contains zero or several GSM entries. Use "set  gsm" command to set GSM for the session.
Current GSM is set to GSMORA
GDSCTL>

GDSCTL>create shardcatalog -database shard1:1521:shardcat -chunks 12 -user mygdsadmin/oracle -sdb shardcat -region region1 -agent_port 8080 -agent_password oracle
Catalog is created

建立和啟動shard director.
引數含義：
-gsm: 指定shard director名稱
-listener: 指定shard director的監聽埠，注意不能與資料庫的listener埠衝突
-catalog: 指定catalog database 資訊，catalog資料庫的主機名:監聽器port: catalog 資料庫db_name


GDSCTL>add gsm -gsm sharddirector1 -listener 1571 -pwd oracle -catalog shard1:1521:shardcat -region region1
GSM successfully added

GDSCTL>start gsm -gsm sharddirector1
GSM is started successfully

新增作業系統認證.
GDSCTL>add credential -credential oracle_cred -osaccount oracle -ospassword oracle
The operation completed successfully

開始佈署SharedDatabase。本例將佈署System-ManagedSDB。
部署system-managed SDB

1.連線到shard director/GSM伺服器(shard1) 

[oracle@shard1 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@shard1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsm
[oracle@shard1 ~]$ export PATH=/u01/app/oracle/product/12.2.0/gsm/bin:$PATH:$HOME/bin
[oracle@shard1 ~]$ gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Thu Oct 12 19:35:21 CST 2017

Copyright (c) 2011, 2016, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to SHARDDIRECTOR1

設定當前session為sharddirector1 shard director 

GDSCTL>set gsm -gsm sharddirector1 
GDSCTL>connect mygdsadmin/oracle
Catalog connection is established

新增shardgroup， shardgroup是一組shard的集合，shardgroup名稱為primary_shardgroup，-deploy_as primary表示這個group中的shard都是主庫。 

GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
The operation completed successfully

將每個shard地址新增到catalog的valid node checking for registration (VNCR)列表，並且建立shard
GDSCTL>add invitednode shard2
GDSCTL>create shard -shardgroup primary_shardgroup -destination shard2 -credential oracle_cred
The operation completed successfully
DB Unique Name: sh1
GDSCTL>add invitednode shard3
GDSCTL>create shard -shardgroup primary_shardgroup -destination shard3 -credential oracle_cred
The operation completed successfully
DB Unique Name: sh2

檢查配置 

GDSCTL>config

Regions
------------------------
region1                       

GSMs
------------------------
sharddirector1                

Sharded Database
------------------------
shardcat                      

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

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

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>config shardgroup
Shard Group         Chunks Region              Shard space         
-----------         ------ ------              -----------         
primary_shardgroup  12     region1             shardspaceora       

GDSCTL>config vncr
Name                          Group ID                      
----                          --------                      
shard2                                                      
shard3                                                      
10.138.130.180                                              

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

部署deploy
Shard資料庫部署過程採用靜默安裝方式。 

GDSCTL>deploy

此時，就開始部署shard了。在shard node上的agent會自動的呼叫netca和dbca，建立listener和database，2個shard node的操作是並行進行的。（如果是有datauard，那麼是先建立一對主備，再建立另一對主備。）你可以在分別是兩個shard node上ps -ef|grep ora_ 看到已經有sh1和sh2的例項了。我們可以執行在shard2與shard3上執行ps -ef | grep dbca與lsnrctl status,ps -ef | grep pmon來檢查。 

[root@shard2 ~]# ps -ef | grep dbca
oracle   20437 20429 99 19:49 pts/0    00:00:19 /u01/app/oracle/product/12.2.0/db/jdk/jre/bin/java -Doracle.installer.not_bootstrap=true -DCV_HOME=/u01/app/oracle/product/12.2.0/db -DORACLE_HOME=/u01/app/oracle/product/12.2.0/db -XX:-OmitStackTraceInFastThrow -XX:CompileCommand=quiet -XX:CompileCommand=exclude,javax/swing/text/GlyphView,getBreakSpot -DSET_LAF= -Dsun.java2d.font.DisableAlgorithmicStyles=true -Dice.pilots.html4.ignoreNonGenericFonts=true -DDISPLAY= -DJDBC_PROTOCOL=thin -mx512m -classpath /u01/app/oracle/product/12.2.0/db/assistants/dbca/jlib/dbca.jar:/u01/app/oracle/product/12.2.0/db/assistants/dbca/jlib/dbcaext.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/assistantsCommon.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/rconfig.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/asstcommonext.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraInstaller.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraPrereq.jar:/u01/app/oracle/product/12.2.0/db/inventory/prereqs/oui/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraCheckPoint.jar:/u01/app/oracle/product/12.2.0/db/jlib/cvu.jar:/u01/app/oracle/product/12.2.0/db/install/jlib/installcommons_1.0.0b.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/ssh.jar:/u01/app/oracle/product/12.2.0/db/jlib/ewt3.jar:/u01/app/oracle/product/12.2.0/db/jlib/ewtcompat-3_3_15.jar:/u01/app/oracle/product/12.2.0/db/jlib/share.jar:/u01/app/oracle/product/12.2.0/db/jlib/help4.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/db/jlib/oracle_ice.jar:/u01/app/oracle/product/12.2.0/db/jlib/kodiak.jar:/u01/app/oracle/product/12.2.0/db/lib/xmlparserv2.jar:/u01/app/oracle/product/12.2.0/db/jlib/orai18n.jar:/u01/app/oracle/product/12.2.0/db/jlib/ldapjclnt12.jar:/u01/app/oracle/product/12.2.0/db/jlib/netcfg.jar:/u01/app/oracle/product/12.2.0/db/jlib/ojmisc.jar:/u01/app/oracle/product/12.2.0/db/jlib/oraclepki.jar:/u01/app/oracle/product/12.2.0/db/jlib/opm.jar:/u01/app/oracle/product/12.2.0/db/jdbc/lib/ojdbc8.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvm.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvmhas.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvmasm.jar:/u01/app/oracle/product/12.2.0/db/dv/jlib/dvca.jar:/u01/app/oracle/product/12.2.0/db/jlib/gns.jar:/u01/app/oracle/product/12.2.0/db/jlib/commons-compress-1.8.jar oracle.assistants.dbca.driver.DBConfigurator -silent -responseFile /u01/app/oracle/product/12.2.0/db/shard_sh1_dbca.rsp -createDatabase -gdbName sh1 -sid sh1 -initparams db_unique_name=sh1,db_name=sh1,db_domain= -templateName /u01/app/oracle/product/12.2.0/db/shard_sh1_template.dbt -customscripts /u01/app/oracle/product/12.2.0/db/shard_sh1_postCR.sql -listeners LISTENER_sh1
root     20881 12186  0 19:49 pts/1    00:00:00 grep --color=auto dbca


[oracle@shard2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-OCT-2017 19:56:01

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_sh1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                12-OCT-2017 19:48:45
Uptime                    0 days 0 hr. 7 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shard2/listener_sh1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "sh1" has 1 instance(s).
  Instance "sh1", status RESTRICTED, has 1 handler(s) for this service...
Service "sh1_DGMGRL" has 1 instance(s).
  Instance "sh1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@shard2 ~]$ ps -ef | grep pmon
oracle   22086     1  0 19:54 ?        00:00:00 ora_pmon_sh1
oracle   22982  5293  0 19:56 pts/0    00:00:00 grep --color=auto pmon

GDSCTL>deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh1' ...
deploy: network listener configuration successful at destination 'shard2'
deploy: starting DBCA at destination 'shard2' to create primary shard 'sh1' ...
deploy: deploying primary shard 'sh2' ...
deploy: network listener configuration successful at destination 'shard3'
deploy: starting DBCA at destination 'shard3' 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: 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 'shard2' for shard 'sh1'
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'shard3' for shard 'sh2'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully

我們可以檢查一下shard的情況了： 

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:
     shardcat%1
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     shardcat%11

GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shard2: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: shard3: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    
----                                                            --------- ------

建立service 

GDSCTL>add service -service oltp_rw_srvc -role primary
The operation completed successfully
GDSCTL>start service -service oltp_rw_srvc
The operation completed successfully
GDSCTL>status service
Service "oltp_rw_srvc.shardcat.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "shardcat%1", name: "sh1", db: "sh1", region: "region1", status: ready.
   Instance "shardcat%11", name: "sh2", db: "sh2", region: "region1", status: ready.

(其實這個service，用於adg的主備切換後，這個service漂移到備庫上)

建立使用者和物件
1. 在catalog資料庫中建立業務使用者 

[oracle@shard2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 13:24:45 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session enable shard ddl;

Session altered.

SQL> create user app_schema identified by oracle;

User created.

SQL> grant all privileges to app_schema;

Grant succeeded.

SQL> grant gsmadmin_role to app_schema;

Grant succeeded.

SQL> grant select_catalog_role to app_schema;

Grant succeeded.

SQL> grant connect, resource to app_schema;

Grant succeeded.

SQL>  grant dba to app_schema;

Grant succeeded.

SQL> grant execute on dbms_crypto to app_schema;

Grant succeeded.

2. 利用應用使用者登入,建立表空間集合 

SQL>  conn app_schema/oracle
Connected.
SQL>  alter session enable shard ddl;

Session altered.

SQL> create tablespace set tsp_set_1 using template (datafile size 100m autoextend on next 10m maxsize unlimited extent management local segment space management auto);

Tablespace created.

3. 為duplicated tables建立表空間，這個測試中duplicated table是Products table. 

SQL> create tablespace products_tsp datafile size 100m autoextend on next 10m maxsize unlimited extent management local uniform size 1m;

Tablespace created.

4. 建立shard表 

SQL> create sharded table customers
  2    (
  3      custid      varchar2(60) not null,
  4      firstname   varchar2(60),
  5      lastname    varchar2(60),
  6      class       varchar2(10),
  7      geo         varchar2(8),
  8      custprofile varchar2(4000),
  9      passwd      raw(60),
 10      constraint pk_customers primary key (custid),
 11      constraint json_customers check (custprofile is json)
 12    ) tablespace set tsp_set_1
 13  partition by consistent hash (custid) partitions auto;

Table created.

SQL> create sharded table orders
  2  (
  3    orderid     integer not null,
  4    custid      varchar2(60) not null,
  5    orderdate   timestamp not null,
  6    sumtotal    number(19,4),
  7    status      char(4),
  8    constraint  pk_orders primary key (custid, orderid),
  9    constraint  fk_orders_parent foreign key (custid) 
 10    references customers on delete cascade
 11  ) partition by reference (fk_orders_parent);  

Table created.

5.為orders表的orderid列建立序列 

SQL> create sequence orders_seq; 

Sequence created.

6. 建立SHARDED TABLE LineItems 

SQL> create sharded table lineitems
  2  (
  3    orderid     integer not null,
  4    custid      varchar2(60) not null,
  5    productid   integer not null,
  6    price       number(19,4),
  7    qty         number,
  8    constraint  pk_items primary key (custid, orderid, productid),
  9    constraint  fk_items_parent foreign key (custid, orderid)
 10    references orders on delete cascade
 11  ) partition by reference (fk_items_parent);

Table created.

7. 建立duplicated tables. 

SQL> create duplicated table products
  2  (
  3    productid  integer generated by default as identity primary key,
  4    name       varchar2(128),
  5    descruri   varchar2(128),
  6    lastprice  number(19,4)
  7  ) tablespace products_tsp;  

Table created.

8. 建立function，目的是為了後面的DEMO： 

SQL> create or replace function passwcreate(passw in raw)
  2  return raw
  3  is
  4  salt raw(8);
  5  begin
  6  salt := dbms_crypto.randombytes(8);
  7  return utl_raw.concat(salt, dbms_crypto.hash(utl_raw.concat(salt,
  8  passw), dbms_crypto.hash_sh256));
  9  end;
 10  /

Function created.

SQL> create or replace function passwcheck(passw in raw, phash in raw)
  2  return integer is
  3  begin
  4  return utl_raw.compare(
  5  dbms_crypto.hash(utl_raw.concat(utl_raw.substr(phash, 1, 8),
  6  passw), dbms_crypto.hash_sh256),
  7  utl_raw.substr(phash, 9));
  8  end;
  9  /

Function created.

GDSCTL>connect mygdsadmin/oracle
Catalog connection is established

GDSCTL>show ddl
id DDL Text Failed shards
-- -------- -------------
7 grant execute on dbms_crypto to app_s...
8 create tablespace set tsp_set_1 using...
9 create tablespace products_tsp datafi...
10 create sharded table customers ( ...
11 create sharded table orders ( orde...
12 create sequence orders_seq
13 create sharded table lineitems ( o...
14 CREATE MATERIALIZED VIEW "APP_SCHEMA"...
15 create or replace function passwcreat...
16 create or replace function passwcheck...

10. 檢查每個shard是否有DDL錯誤 

GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shard2: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: ---   沒有DDL錯誤
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: shard3: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: ---   沒有DDL錯誤
Failed DDL id: 
Availability: ONLINE
Rack: 


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

驗證環境-表空間/chunks
1. 在gsm(shard1)節點，檢查chunks資訊
前面建立shardcatalog時指定chunks為12，因此後續建立shard table分配12個chunks 

GDSCTL>config chunks
Chunks
------------------------
Database                      From      To        
--------                      ----      --        
sh1                           1         6         
sh2                           7         12   


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

TABLESPACE_NAME                                                      MB
------------------------------------------------------------ ----------
PRODUCTS_TSP                                                        100
SYSAUX                                                              520
SYSTEM                                                              810
TSP_SET_1                                                           100
UNDOTBS1                                                             70
USERS                                                                 5

6 rows selected.


SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;

no rows selected


SQL> col TABLE_NAME for a20
SQL> col PARTITION_NAME for a20
SQL> col TABLESPACE_NAME for a20
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like '%SET%';

TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME
-------------------- -------------------- --------------------
CUSTOMERS            CUSTOMERS_P1         TSP_SET_1
ORDERS               CUSTOMERS_P1         TSP_SET_1
LINEITEMS            CUSTOMERS_P1         TSP_SET_1


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

TABLESPACE_NAME              MB
-------------------- ----------
SYSTEM                      810
SYSAUX                      520
UNDOTBS1                     70
USERS                         5
TSP_SET_1                   100
PRODUCTS_TSP                100

6 rows selected.

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

2. 在shard2節點檢查表空間和chunks資訊
--表空間 

[oracle@shard2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:25:30 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

TABLESPACE_NAME                                                      MB
------------------------------------------------------------ ----------
C001TSP_SET_1                                                       100
C002TSP_SET_1                                                       100
C003TSP_SET_1                                                       100
C004TSP_SET_1                                                       100
C005TSP_SET_1                                                       100
C006TSP_SET_1                                                       100
PRODUCTS_TSP                                                        100
SYSAUX                                                              520
SYSTEM                                                              810
TSP_SET_1                                                           100
UNDOTBS1                                                             70
USERS                                                                 5

12 rows selected.

建立了6個表空間，分別是C001TSP_SET_1 ~ 表空間C006TSP_SET_1，因為設定chunks=12，每個shard有6個chunks。每個表空間有一個datafile，大小是100M，這個是在建立tablespace set時設定的datafile 100M。

--檢查chunks 

SQL> set linesize 140
SQL> column table_name format a20
SQL> column tablespace_name format a20
SQL> 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
-------------------- -------------------- --------------------
LINEITEMS            CUSTOMERS_P1         C001TSP_SET_1
CUSTOMERS            CUSTOMERS_P1         C001TSP_SET_1
ORDERS               CUSTOMERS_P1         C001TSP_SET_1
CUSTOMERS            CUSTOMERS_P2         C002TSP_SET_1
ORDERS               CUSTOMERS_P2         C002TSP_SET_1
LINEITEMS            CUSTOMERS_P2         C002TSP_SET_1
CUSTOMERS            CUSTOMERS_P3         C003TSP_SET_1
LINEITEMS            CUSTOMERS_P3         C003TSP_SET_1
ORDERS               CUSTOMERS_P3         C003TSP_SET_1
LINEITEMS            CUSTOMERS_P4         C004TSP_SET_1
CUSTOMERS            CUSTOMERS_P4         C004TSP_SET_1
ORDERS               CUSTOMERS_P4         C004TSP_SET_1
CUSTOMERS            CUSTOMERS_P5         C005TSP_SET_1
ORDERS               CUSTOMERS_P5         C005TSP_SET_1
LINEITEMS            CUSTOMERS_P5         C005TSP_SET_1
CUSTOMERS            CUSTOMERS_P6         C006TSP_SET_1
ORDERS               CUSTOMERS_P6         C006TSP_SET_1
LINEITEMS            CUSTOMERS_P6         C006TSP_SET_1

18 rows selected.

4. 在catalog資料庫檢查chunks資訊 

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. 驗證環境-tables
--catalog資料庫 

SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;

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

6 rows selected.

--shard節點shard2和shard3 

[oracle@shard2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:36:12 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;

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


[oracle@shard3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:36:06 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;

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

--插入資料 

SQL>  INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
  2      Class, Geo, Passwd) VALUES ('james.parker@x.bogus', 'James', 'Parker',
  3      NULL, 'Gold', 'east', hextoraw('8d1c00e'));

1 row created.

SQL> commit;

Commit complete.


SQL> set termout on
SQL> set linesize 120
SQL> set echo on
SQL> column firstname format a20
SQL> column lastname format a20
SQL> explain plan for SELECT FirstName,LastName, geo, class FROM Customers;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2953441084

--------------------------------------------------------------
| Id  | Operation        | Name | Cost (%CPU)| Inst   |IN-OUT|
--------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     0   (0)|        |      |
|   1 |  SHARD ITERATOR  |      |            |        |      |
|   2 |   REMOTE         |      |            | ORA_S~ | R->S |
--------------------------------------------------------------

Remote SQL Information (identified by operation id):

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

   2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
       "A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS" FROM
       "CUSTOMERS" "A1" /* coord_sql_id=9j0dws979r7rr */  (accessing
       'ORA_SHARD_POOL@ORA_MULTI_TARGET' )


18 rows selected.

sharding在12.2中有太多的限制與坑，期待以後的改進。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2146070/，如需轉載，請註明出處，否則將追究法律責任。

相關文章