创建Oracle sharding database的例子
一、Oracle sharding database的一些概念
（1）Table family：
有相关关联关系的一组表，如客户表（customers），订单表（order），订单明细表（LineItems）。这些表之间往往有外键约束关系，可以通过如下2中方式建立table family：
（1.1）通过CONSTRAINT [FK_name] FOREIGN KEY (FK_column) REFERENCES [R_table_name]([R_table_column]) ————这种关系可以有级联关系。
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>
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.
SQL>
SQL> CREATE SEQUENCE Orders_Seq;
Sequence created.
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.
SQL>
可以看到上面根表（root table）是customer表，主键是CustId，partition是根据CONSISTENT HASH，对CustId进行分区；
下一级的表是order表，主键是CustId+OrderId，外键是CustId且references Customers表，partition是参考外键；
再下一级表是LineItems表，主键是CustId+OrderId+ProductId，外键是CustId+OrderId，即上一层表达主键，partition是参考外键
（1.2）同关键字PARENT来显式的说明父子关系。————这种关系只有父子一层关系，不能级联。
SQL> CREATE SHARDED TABLE Customers
2 ( CustNo NUMBER NOT NULL
3 , Name VARCHAR2(50)
4 , Address VARCHAR2(250)
5 , region VARCHAR2(20)
6 , class VARCHAR2(3)
7 , signup DATE
8 )
9 PARTITION BY CONSISTENT HASH (CustNo)
10 TABLESPACE SET ts1
11 PARTITIONS AUTO
12 ;
SQL> CREATE SHARDED TABLE Orders
2 ( OrderNo NUMBER
3 , CustNo NUMBER
4 , OrderDate DATE
5 )
6 PARENT Customers
7 PARTITION BY CONSISTENT HASH (CustNo)
8 TABLESPACE SET ts1
9 PARTITIONS AUTO
10 ;
SQL> CREATE SHARDED TABLE LineItems
2 ( LineNo NUMBER
3 , OrderNo NUMBER
4 , CustNo NUMBER
5 , StockNo NUMBER
6 , Quantity NUMBER
7 )
8 PARENT Customers
9 PARTITION BY CONSISTENT HASH (CustNo)
10 TABLESPACE SET ts1
11 PARTITIONS AUTO
12 ;
注意上面的order表和LineItems表，都是属于同一个父表，即parent customers表。
另外，也注意上面的CustNo字段，在每个表中都是有的。而上面说的第一种的级联关系的table family，可以不在每个表中都存在CustNo字段。
（2）Sharded Table和Duplicated table：
上面创建在表，都是sharded table，即表的各个分区，可以分布在不同的shard node上。各个shard node上的分区，是不同的。即整个表的内容，是被切割成片，分配在不同的机器上的。
而duplicated table，是整个表达同样内容，在各个机器上是一样的。duplicate table在各个shard node上，是以read only mv的方式呈现：在shardcat中，存在mast table；在各个shard中，存在read only mv。duplicated table的同步：以物化视图的方式同步。
（3）chunk：
A chunk contains a single partition from each table of a table family. This guarantees that related data from different sharded tables can be moved together.
chunk的概念和table family密不可分。因为family之间的各个表都是有关系的，我们把某个table family的一组分区称作一个chunk。如
customers表中的1号～100万号客户信息在一个分区中；在order表中，也有1号～100万号的客户的order信息，也在一个分区中；另外LineItems表中的1号～100万号客户的明细信息，也在一个分区中，我们希望这些相关的分区，都是在一个shard node中，避免cross shard join。所以，我们把这些在同一个table family内，相关的分区叫做chunk。在进行re-sharding的时候，是以chunk为单位进行移动。因此可以避免cross shard join。
另外，虽然我们设计了chunk来避免cross shard join，但是在做查询的时候，还是有可能会查到非table family进行cross shard join，这在设计之初，就应该避免的。如果有cross shard，还不如用duplicated table。
注：chunk的数量在CREATE SHARDCATALOG的指定，如果不指定，默认值是每个shard 120个chunk
（4）chunk move：
chunk move的条件：
（1）re-sharding发生，即当shard的数量发生改变的时候，会发生chunk move。
注，re-sharding之后，chunk的数虽然平均，但并不连续。如：
原来是2个shard，1～6号chunk在shard 1，7～12号chunk在shard2。加多一个shard后，1～4号chunk在shard 1，7～10号chunk在shard 2，那么5～6，11～12号chunk在shard 3上。即：总是挪已经存在的shard node上的后面部分chunk。
（2）DBA手工发起：move chunk -chunk 7 -source sh2 -target sh1。将chunk 7从shard node sh2上，挪到shard node sh1上。
chunk move的过程：
在chunk migration的时候，chunk大部分时间是online的，但是期间会有几秒钟的时间chunk中的data处于read-only状态。
chunk migration的过程就是综合利用rman增量备份和TTS的过程：
level 0备份源chunk相关的TS，还原到新shard->开始FAN（等待几秒）->将源chunk相关的TS置于read-only->level 1备份还原->chunk up(更新routing table连新shard)->chunk down(更新routing table断开源shard)->结束FAN（等待几秒）->删除原shard上的老chunk
（5）shardspace：
create tablespace set的时候，指定shardspace。主要是在Composite sharding架构中使用多个shardspace。
ADD SHARDSPACE –SHARDSPACE shspace1, shspace2;
ADD SHARD –CONNECT shard1 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard2 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard3 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard4 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard5 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard6 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard7 –SHARDSPACE shspace2;
CREATE TABLESPACE SET tbs1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;
（6）sharding 方式：
在gdsctl create shardcatalog时指定，
System-Managed Sharding：partitioning by consistent hash.主要作用是打散数据。
Composite Sharding：create multiple shardgroups for different subsets of data in a table partitioned by consistent hash. 分层，可以用不同的shardspace，不同的tablespace set，使用不同的硬件。高级的customer用更好的硬件资源。
Using Subpartitions with Sharding：all of the subpartitioning methods provided by Oracle Database are also supported for sharding. 更细粒度的分区。
（7）如何部署sharding：
简单来说，就是在gdsctl中运行如下命令。关于详细内容，我会另外再写一篇，讲讲sharding的部署和架构中的注意点。
CREATE SHARDCATALOG
ADD GSM; START GSM (create and start shard directors)
CREATE SHARD (for each shard)
DEPLOY
二、创建Oracle sharding database
你在本文中可以看到：
（一）安装介质需求。
（二）HIGH LEVEL安装步骤。
（三）详细安装步骤。
（四）建立应用用户，利用应用用户建立sharded table和duplicated table：
（五）安装过程known issue。
（六）sharded table的一些测试，以及发现其对dml的一些限制。
关于sharding在架构上的一些想法和注意点，我准备下一篇文章再谈。
（一）安装介质：
1. 你需要12.2的database的安装介质（两个zip压缩包）来安装db软件，用于shardcat数据库，和shard node主机上的数据库。
2. 你还需要12.2的gsm安装介质（一个压缩包）来安装GDS框架和gsm服务。这是安装在shardcat主机上的。
3. 你还需要12.2.的client安装介质（一个压缩包）来装scheagent，这是安装在shard node主机上的。安装schagent是为了在shardcat主机上发起命令，在远程的shard node上，通过agent调起来netca和dbca来安装监听和数据库。另外，如果shard node有active dataguard，agent也会自动帮你配好dataguard，配好broker和FSFO。
（二）HIGH LEVEL安装步骤:
1.Oracle Sharding Prerequisites
2.Setting Up the Oracle Sharding Host Environment Scripts
3.Installing Oracle Database
4.Installing the Shard Director Software
5.Creating the Shard Catalog Database
6.Setting Up the Oracle Sharding Management and Routing Tier
7.Deploying and Managing a System-Managed SDB
（三）详细安装步骤：
1.Oracle Sharding Prerequisites
12.2企业版
non-cdb
使用文件系统而非ASM （12.2 Beta要求，正式发行后，可能会改）
主机hosts文件写上本机和各个shard node的IP解析
机器必须全新，不能残留之前有安装过oracle的信息。
2.Setting Up the Oracle Sharding Host Environment Scripts
目的是因为shardcat和gds都安装在一个主机上，同一个oracle用户，不同ORACLE_HOME，所以建立环境变量的脚本，会比较容易在database环境和gsm环境之间切换。
admin guide上是用shardcat.sh，shard-director1.sh脚本，但是我的可能更简单实用，直接定义成alias。（这种方法其实是跟ORACLE BASE学的。老DBA应该都听说过这个网站。）
##修改环境变量，在环境变量中设置2个alias别名
[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
if [ $USER = "oracle12c" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
alias gsm_env='. /home/oracle12c/gsm_env'
alias db_env='. /home/oracle12c/db_env'
##创建2个脚本，gsm_env和db_env
[oracle12c@sdb1 ~]$ cat /home/oracle12c/gsm_env
ORACLE_HOME=$GSM_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
[oracle12c@sdb1 ~]$
[oracle12c@sdb1 ~]$ cat /home/oracle12c/db_env
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
[oracle12c@sdb1 ~]$
[oracle12c@sdb1 ~]$
3.Installing Oracle Database
安装db软件，解开2个压缩包，加载一下上面建好的db_env环境变量，开始跑runInstaller，选择software only，没啥好说的。注意ORACLE_HOME的路径和环境变量中定义的DB的ORACLE_HOME一致。
在shardcat主机和shard node主机，都需要安装好db软件。
4.Installing the Shard Director Software
安装gds框架和gsm服务，解开gsm的压缩包，加载一下上面建好的gsm_env环境变量开始跑runInstaller，注意选择不同与DB的ORACLE_HOME，注意ORACLE_HOME的路径和环境变量中定义的gsm的ORACLE_HOME一致。
本文中gds安装在和shardcat同一个主机上。即shardcat和shard Director在同一主机。（其实，如果有需要，也可以不同主机的）
4.b. Installer schagent in all shard node（admin guide文档没写这步骤，本人免费赠送）
选择client安装包，解压缩后，运行runInstaller，在每个shard node上建立agent
5.Creating the Shard Catalog Database
运行dbca开始建立数据库实例，这个实例是放分片数据的元数据的。我们把这个实例名叫shardcat。
安装好后，再建立listener。以便可以连接这个数据库。
6.Setting Up the Oracle Sharding Management and Routing Tier
登录shardcat主机，登录shardcat数据库：
--建立tablespace set需要使用omf，所以需要指定db_create_file_dest参数。
SQL> alter system set db_create_file_dest='/u01/ora12c/app/oracle/oradata' scope=both;
SQL> alter system set open_links=16 scope=spfile;
SQL> alter system set open_links_per_instance=16 scope=spfile;
SQL> startup force
SQL> alter user gsmcatuser account unlock;
SQL> alter user gsmcatuser identified by oracle;
SQL> CREATE USER mygdsadmin IDENTIFIED BY oracle;
SQL> GRANT connect, create session, gsmadmin_role to mygdsadmin;
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
SQL> alter system set events 'immediate trace name GWM_TRACE level 7';
SQL> alter system set event='10798 trace name context forever, level 7' scope=spfile;
SQL> execute dbms_xdb.sethttpport(8080);
SQL> commit;
SQL> @?/rdbms/admin/prvtrsch.plb
SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracleagent');
登录shard node主机：
[oracle12c@sdb2 ~]$ schagent -start
Scheduler agent started using port 1025
[oracle12c@sdb2 ~]$
[oracle12c@sdb2 ~]$
[oracle12c@sdb2 ~]$ schagent -status
Agent running with PID 2084
Agent_version:12.2.0.1.2
Running_time:00:00:17
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/ora12c/app/oracle/product/12.2.0/db_1
ORACLE_BASE:/u01/ora12c/app/oracle
Port:1025
Host:sdb2
[oracle12c@sdb2 ~]$
[oracle12c@sdb2 ~]$ echo oracleagent|schagent -registerdatabase sdb1 8080
Agent Registration Password ?
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
[oracle12c@sdb2 ~]$
[oracle12c@sdb2 oracle]$ mkdir -p /u01/ora12c/app/oracle/oradata
[oracle12c@sdb2 oracle]$ mkdir -p /u01/ora12c/app/oracle/fast_recovery_area
[oracle12c@sdb2 oracle]$
各个shard node主机都进行上述操作。
7.Deploying and Managing a System-Managed SDB
我们开始部署，以最简单的System-Managed SDB为例。
另外，admin guide中介绍的是4台主机做shard node，其中每2台互为dataguard主备。我们这边为了节约空间和资源，不搞dataguard了，只建立primary库。因此只要2台主机做shard node。
[oracle12c@sdb1 ~]$ gsm_env
[oracle12c@sdb1 ~]$ gdsctl
GDSCTL: Version 12.2.0.0.0 - Beta on Mon May 09 23:11:05 CST 2016
Copyright (c) 2011, 2015, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to SHARDDIRECTOR1
GDSCTL>set gsm -gsm sharddirector1
GDSCTL>
GDSCTL>
GDSCTL>connect mygdsadmin/oracle
Catalog connection is established
GDSCTL>GDSCTL>
GDSCTL>
GDSCTL>
GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
GDSCTL>create shard -shardgroup primary_shardgroup -destination sdb2 -credential oracle_cred
DB Unique Name: sh1
GDSCTL>
GDSCTL>add invitednode sdb3
GDSCTL>create shard -shardgroup primary_shardgroup -destination sdb3 -credential oracle_cred
DB Unique Name: sh2
GDSCTL>
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>
GDSCTL>
GDSCTL>
GDSCTL>config shardspace
SHARDSPACE Chunks
---------- ------
shardspaceora 12
GDSCTL>config shardgroup
Shard Group Chunks Region SHARDSPACE
----------- ------ ------ ----------
primary_shardgroup 12 region1 shardspaceora
GDSCTL>config vncr
Name Group ID
---- --------
sdb2
sdb3
192.168.56.21
GDSCTL>config shard
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_shardgroup U none region1 -
sh2 primary_shardgroup U none region1 -
GDSCTL>deploy
GDSCTL>
此时，就开始部署shard了。在shard node上的agent会自动的调用netca和dbca，创建listener和database，2个shard node的操作是并行进行的。（如果是有datauard，那么是先建立一对主备，再建立另一对主备。）你可以在分别是两个shard node上ps -ef|grep ora_ 看到已经有sh1和sh2的实例了。
等deploy完，我们可以检查一下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>
GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: sdb2:1521/sh1:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Last Failed DDL:
DDL Error: ---
Failed DDL id:
Availability: ONLINE
Supported services
------------------------
Name Preferred Status
---- --------- ------
建立service：
GDSCTL>add service -service oltp_rw_srvc -role primary
GDSCTL>
GDSCTL>config service
Name Network name Pool Started Preferred all
---- ------------ ---- ------- -------------
oltp_rw_srvc oltp_rw_srvc.shardcat.oradbcl shardcat No Yes
oud
GDSCTL>
GDSCTL>start service -service oltp_rw_srvc
GDSCTL>
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.
GDSCTL>
（2016-05-14更新：其实这个service，用于adg的主备切换后，这个service漂移到备库上。）
（四）建立应用用户，利用应用用户建立sharded table和duplicated table：
[oracle12c@sdb1 ~]$ db_env
[oracle12c@sdb1 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.2.0.0.2 Beta on Mon May 9 23:37:34 2016
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
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.
SQL>
利用应用用户登录，创建sharded table和duplicated table
SQL> conn app_schema/oracle
Connected.
SQL>
SQL> alter session enable shard ddl;
Session altered.
SQL> CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m extent
2 management local segment space management auto );
Tablespace created.
SQL>
SQL> CREATE TABLESPACE products_tsp datafile size 100m extent management local uniform
2 size 1m;
Tablespace created.
SQL>
SQL>-- Create sharded table family
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>
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.
SQL> CREATE SEQUENCE Orders_Seq;
Sequence created.
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.
SQL>
SQL> -- duplicated table
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.
SQL>
在shardcat检查:
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by
2 tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
PRODUCTS_TSP 100
SYSAUX 690
SYSTEM 880
TSP_SET_1 100
UNDOTBS1 410
USERS 5
6 rows selected.
SQL>
SQL>
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions
2 where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;
no rows selected
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like '%SET%';
SQL> col TABLE_NAME for a20
SQL> col PARTITION_NAME for a20
SQL> col TABLESPACE_NAME for a20
SQL> /
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 880
SYSAUX 690
UNDOTBS1 410
USERS 5
TSP_SET_1 100
PRODUCTS_TSP 100
6 rows selected.
SQL> l
1* select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files
SQL> /
TABLESPACE_NAME MB
-------------------- ----------
SYSTEM 880
SYSAUX 690
UNDOTBS1 410
USERS 5
TSP_SET_1 100
PRODUCTS_TSP 100
6 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select a.name Shard, count( b.chunk_number) Number_of_Chunks from
2 gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b where
3 a.database_num=b.database_num group by a.name;
SHARD NUMBER_OF_CHUNKS
------------------------------ ----------------
sh1 6
sh2 6
SQL>
在on shard node 1上可以检查:
[oracle12c@sdb2 trace]$ export ORACLE_SID=sh1
[oracle12c@sdb2 trace]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.2.0.0.2 Beta on Mon May 9 23:51:44 2016
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set pages 1000
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by
2 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 650
SYSTEM 890
SYS_SHARD_TS 100
TSP_SET_1 100
UNDOTBS1 110
USERS 5
13 rows selected.
SQL>
SQL> col TABLE_NAME for a30
SQL> col PARTITION_NAME for a30
SQL> col TABLESPACE_NAME for a30
SQL>
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions
2 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.
###########################################
在on shard node 2上可以检查:
[oracle12c@sdb3 trace]$ export ORACLE_SID=sh2
[oracle12c@sdb3 trace]$
[oracle12c@sdb3 trace]$
[oracle12c@sdb3 trace]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.2.0.0.2 Beta on Mon May 9 23:52:06 2016
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set pages 1000
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by
2 tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
C007TSP_SET_1 100
C008TSP_SET_1 100
C009TSP_SET_1 100
C00ATSP_SET_1 100
C00BTSP_SET_1 100
C00CTSP_SET_1 100
PRODUCTS_TSP 100
SYSAUX 650
SYSTEM 890
SYS_SHARD_TS 100
TSP_SET_1 100
UNDOTBS1 115
USERS 5
13 rows selected.
SQL>
SQL>
SQL> l
1 select table_name, partition_name, tablespace_name from dba_tab_partitions
2* where tablespace_name like 'C%TSP_SET_1' order by tablespace_name
SQL> /
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
ORDERS CUSTOMERS_P7 C007TSP_SET_1
LINEITEMS CUSTOMERS_P7 C007TSP_SET_1
CUSTOMERS CUSTOMERS_P7 C007TSP_SET_1
ORDERS CUSTOMERS_P8 C008TSP_SET_1
CUSTOMERS CUSTOMERS_P8 C008TSP_SET_1
LINEITEMS CUSTOMERS_P8 C008TSP_SET_1
LINEITEMS CUSTOMERS_P9 C009TSP_SET_1
ORDERS CUSTOMERS_P9 C009TSP_SET_1
CUSTOMERS CUSTOMERS_P9 C009TSP_SET_1
LINEITEMS CUSTOMERS_P10 C00ATSP_SET_1
ORDERS CUSTOMERS_P10 C00ATSP_SET_1
CUSTOMERS CUSTOMERS_P10 C00ATSP_SET_1
ORDERS CUSTOMERS_P11 C00BTSP_SET_1
LINEITEMS CUSTOMERS_P11 C00BTSP_SET_1
CUSTOMERS CUSTOMERS_P11 C00BTSP_SET_1
LINEITEMS CUSTOMERS_P12 C00CTSP_SET_1
CUSTOMERS CUSTOMERS_P12 C00CTSP_SET_1
ORDERS CUSTOMERS_P12 C00CTSP_SET_1
18 rows selected.
SQL>
（五）安装过程known issue：
Known Issue（1）STANDARD_ERROR=”Launching external job failed: Invalid username or password”
现象：
GDSCTL>create shard -shardgroup shgrp1 -destination sdb2 -credential oracle_cred
GSM-45029: SQL error
ORA-02610: Remote job failed with error:
EXTERNAL_LOG_ID="job_23872_1",
USERNAME="oracle",
STANDARD_ERROR="Launching external job failed: Invalid username or password"
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 6920
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4596
ORA-06512: at line 1
解决方法：
GDSCTL>connect sdb1:1521:shardcat
username:sdb_admin
password:
Catalog connection is established
GDSCTL>
GDSCTL>remove credential -CREDENTIAL oracle_cred
GDSCTL>add credential -credential oracle_cred -osaccount oracle12c -ospassword oracle12c
GDSCTL>
Known Issue（2）ORA-06512: at “GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN”, line 14499
现象：
GDSCTL>deploy
GSM-45029: SQL error
ORA-02610: Remote job failed with error:
EXTERNAL_LOG_ID="job_23892_7",
USERNAME="oracle
