Oracle 12c 分片（Sharding）技术

Sharding特点

Oracle12c Sharding技术实现了跨数据库级别的数据分片，实现了分布式IO大数据扩展的云数据库架构体系，可满足大数据海量存储、分布式数据读写以及满足Scale Out/ScaleUp扩展性需求。Sharding的主要特点：

线性伸缩性（Scale out）：Sharding技术减少性能瓶颈，同时可以通过添加Shard节点的方式扩展容量和性能；

错误纠正机制：Sharding是基于非共享物理资源的架构设计，有效消除了单点故障，例如共享磁盘以及SAN存储扥。它的错误纠正机制保证了单个Shard故障不会影响整个集群的使用和性能

地域分布式数据：Sharding可以将数据就近存储在消费者的同时，可以有效的满足监管范围内的管控需要

滚动式更新：应用更新一次仅在一个Shard节点上进行，不会影响其他节点，这样可以保证在小部分数据更新时，可以及时进行数据验证。

简化云发布：Sharding可以简化云发布

Sharding体系结构

Oracle12c 应用程序通过数据库pool将数据库在逻辑层当做1个数据库：数据、事务、用户以及所有Level，通过将Shards添加到pool。Oracle12c支持1000个Shards。

Sharding技术是数据层架构体系，在各个数据库间进行垂直分片。Sharding主要组成结构如下:

其中：

SDB（Sharded database）：一个逻辑层的数据库，该数据库跨越多个物理分片数据库（Shard）组成的数据池进行水平分区。

Shards：独立的物理数据库集（SDB）组成节点。

ShardCatalog - 它也是一个Oracle Database，主要用于Shard的自动部署、集中管理以及跨Shard的查询。

ShardDirectors - 跟据Sharding Key来提供到Shard的路由。OracleSharding中可以设置多个位于不同Host的Shard Director。

Sharding环境部署

1.在各个节点/etc/hosts目录追加记录：

192.168.43.101 shard1(Oracle软件+Client[agent])

192.168.43.102 shard2(Oracle软件+Client[agent])

192.168.43.200 sdb1(Oracle软件+GSM[安装shard catlog和shard directory])

SDB1主机安装shardcat实例（db）和gsm服务软件

2.在SDB 节点安装Oracle软件12c,在shard节点安装schagent

2.1SDB安装Oracle软件参考12c安装Installing OracleDatabase Software

配置$ORACLE_HOME/schagent.conf

选择client安装包（linuxx64_12201_client.zip），解压缩后，运行runInstaller，在每个shard node上建立agent

3.安装共享目录软件【GDS/GSM】(gsm：global service manager)

DownloadOracle Global Service Manager

Installingthe Shard Director Software

-------GSM安装依赖包

At least 256MB of totalphysical memory

At least 20MB ofavailable physical memory

At least 6 GB of totalswap space

At least 1.5 GB of freedisk space

Certified architecture,for example x86_64

Linux system kernelversion is at least "2.6.18"

Package make-3.81 isavailable on the system

Packagebinutils-2.17.50.0.6 is available on the system

Package gcc-4.1.2(x86_64) is available on the system

Package libaio-0.3.106(x86_64) is available on the system

Packagelibaio-devel-0.3.106 (x86_64) is available on the system

Package libstdc++-4.1.2(x86_64) is available on the system

Package sysstat-7.0.2is available on the system

Packagecompat-libstdc++-33-3.2.3 (x86_64) is available on the system

Package libgcc-4.1.2(x86_64) is available on the system

Packagelibstdc++-devel-4.1.2 (x86_64) is available on the system

Package glibc-devel-2.5(x86_64) is available on the system

Package gcc-c++-4.1.2(x86_64) is available on the system

Package glibc-2.5-58(x86_64) is available on the system

Package ksh-... isavailable on the system

-----------

注意：GSM安装必须使用操作系统中单独的用户（Linux），不能使用Root安装，一般使用oracle用户

详细安装步骤：

3.1.安装GSM

3.1.1配置gsm和shardcat数据库的环境变量（都使用oracle安装）

exportORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1

exportGSM_HOME=/u01/app/oracle/product/12.2.0/gsm

exportORACLE_SID=shardcat

exportPATH=$ORACLE_HOME/bin:$GSM_HOME/bin:$PATH

exportORACLE_TERM=xterm

exportTNS_ADMIN=$ORACLE_HOME/network/admin

exportLD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib

exportCLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/jlib:$ORACLE_HOME/network/lib

export LANG=en_US.UTF8

exportNLS_LANG=american_america.UTF8

export EDITOR=vi

4 创建分片目录数据库

Creatingthe Shard Catalog Database

--指定非CDB模式

---选择OMF

--设置归档和闪回

--安装完数据库后查看(non-CDB模式)：

SQL> show con_name;

CON_NAME

------------------------------

shardcat

5.配置分片管理和路由层

Setting Upthe Oracle Sharding Management and Routing Tier

The shard catalog,shard directors and shards must be configured to communicate with each other

分片目录、分片文件夹以及shard节点必须能够保证互相通讯。通过以下步骤实现：

5.1确认分片数据库环境

[oracle@sdb1 ~]$ env|grep ORA

ORACLE_SID=shardcat

ORACLE_BASE=/u01/app/oracle

ORACLE_TERM=xterm

ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1

5.2.启动shard catalog监听（shardcat实例）

lsnrctl start

5.3验证/设置参数 DB_CREATE_FILE_DEST/open_links/open_links_per_instance

Verify thatDB_CREATE_FILE_DEST parameter is set on the shard catalog database.

If the parameter is notset, then set it as shown in the following example. Note that open_links andopen_links_per_instance are set to 16 for the purposes of the Oracle Shardingdemo application.

---

SQL> show parameterdb_create_file;

NAME TYPE VALUE

--------------------------------------------------------------------- ------------------------------

db_create_file_dest string /u01/app/oracle/oradata

SQL>

SQL> show parameteropen_links;

NAME TYPE VALUE

--------------------------------------------------------------------- ------------------------------

open_links integer 16

open_links_per_instance integer 16

$ sqlplus / as sysdba

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

SQL> alter systemset open_links=16 scope=spfile;

SQL> alter systemset open_links_per_instance=16 scope=spfile;

------------

5.4重启数据库

startup force;

5.5在分片目录数据库中授权（gsmcatuser/mysdbadmin）

Grant rolesand privileges on the shard catalog database

--------

SQL> set echo on

SQL> set termout on

SQL> spoolsetup_grants_privs.lst

5.5.1.Unlockand set the password for the GSMCATUSER schema.

解锁GSMCATUSER用户，该用户用于分片文件夹连接到分片目录数据库

This schema is used bythe shard director when connecting to the shard catalog database.

SQL> alter user gsmcatuseraccount unlock;

SQL> alter usergsmcatuser identified by oracle;

5.5.2.Createthe administrator schema and grant privileges to it.

创建mysdbadmin用户，该用户管理分片目录数据库环境。GDSCTL命令就是使用该用户来连接数据库的

The mysdbadmin accountis an account in the shard catalog database which stores information on thesharding environment. The mysdbadmin account is the database administratorschema for making administrative changes to the sharded database environment.GDSCTL connects through this user to the database when GDSCTL commands are run,and the mysdbadmin user makes the necessary changes in the database.

SQL> create usermysdbadmin identified by oracle;

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

SQL> grant inheritprivileges on user SYS to GSMADMIN_INTERNAL;

SQL> spool off

**************

SQL> alter systemset events 'immediate trace name GWM_TRACE level 7';

SQL> alter systemset event='10798 trace name context forever, level 7' scope=spfile;

**********************

--查看job主数据库服务器和安装有schedule agent的远程服务器间进行通信，设置sdb与agent通讯端口为8080

SQL> selectdbms_xdb.GETHTTPPORT() from dual;

DBMS_XDB.GETHTTPPORT()

----------------------

--设置通信端口

SQL> executedbms_xdb.sethttpport(8080);

SQL> commit;

SQL> selectdbms_xdb.GETHTTPPORT() from dual;

DBMS_XDB.GETHTTPPORT()

----------------------

8080

--------查看端口启用状况

oracle@sdb1 admin]$lsnrctl status

LSNRCTL for Linux:Version 12.2.0.1.0 - Production on 06-JUN-2017 18:32:40

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sdb1)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version12.2.0.1.0 - Production

Start Date 06-JUN-2017 18:27:56

Uptime 0 days 0 hr. 4 min. 43 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener ParameterFile /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/sdb1/listener/alert/log.xml

Listening EndpointsSummary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb1)(PORT=8080))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service"shardcat.com" has 1 instance(s).

Instance "shardcat", status READY,has 1 handler(s) for this service...

Service"shardcatXDB.com" has 1 instance(s).

Instance "shardcat", status READY,has 1 handler(s) for this service...

The command completedsuccessfully

alter system setshared_servers=1 scope=both;

SQL> show parametershared_server

NAME TYPE VALUE

--------------------------------------------------------------------- ------------------------------

shared_servers integer 1

6.在所有shard节点上启动schagent，同时注册到SDB1

shard1/shard2

schagent -start

schagent -status

--------------------------------

[oracle@shard1 client]$schagent -start

Scheduler agent startedusing port 1025

[oracle@shard1 client]$schagent -status

Agent running with PID8674

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/dbhome_1

ORACLE_BASE:/u01/app/oracle

Port:1025

Host:shard1

--注册agent到SDB（shard1,shard2）

--echo oracle |schagent-registerdatabase sdb1 8080

[oracle@shard1 ~]$ echooracle |schagent -registerdatabase sdb1 8080

Agent RegistrationPassword ?

oracle

Oracle Scheduler AgentRegistration for 12.2.0.1.2 Agent

Agent RegistrationSuccessful!

[oracle@shard1 ~]$oracle

bash: oracle: commandnot found...

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

mkdir -p/u01/app/oracle/fast_recovery_area

由于是安装Oracle管理客户端（可选择Client安装指定Schagent端口），因此需要调整schagent端口启动：

$ORACLE_HOME/scheduler/execution_agent/schagent.conf文件里加入

PORT=30406

shard1节点：

7. 连接到分片目录主机，启动GDSCTL工具

The commands in thefollowing steps are executed from a shard director host because the GDSCTLcommand line interface is installed there.

The following exampleincludes a sanity check that environment variables have been set properly.

$ env |grep ORA

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsmhome_1

$ gdsctl

7.1 创建分片目录，同事配置远程调度

GDSCTL> createshardcatalog -database shard_catalog_host:1521:shard_catalog_name -chunks12 -usermysdbadmin/mysdbadmin_password

-sdb cust_sdb -region region1, region2 -agent_port port_num -agent_passwordrsa_password

---------------

--create shardcatalog-database sdb1:1521:shardcat -chunks 12 -user mysdbadmin/oracle -sdb sdb1 -region region1, region2-agent_port 8080 -agent_password oracle

其中：

-database 创建catalog database信息，主机名：端口号：catalog数据库db_name

-user指定管理用户mysdbadmin

-sdb指定shard database name:sdb1

create shardcatalog-database sdb1:1521:shardcat -chunks 12 -user mysdbadmin/oracle -sdb shardcat-region region1,region2

----

GDSCTL>createshardcatalog -database sdb1:1521:shardcat -chunks 12 -user mysdbadmin/oracle-sdb shardcat -region region1,region2

Catalog is created

....

7.2.创建分片directory

GDSCTL> add gsm -gsmsharddirector1 -listener listener_port -pwd gsmcatuser_password

-catalog shard_catalog_host:1521:shardcat-region region1

GDSCTL> start gsm-gsm sharddirector1

------创建分片directory

--add gsm -gsmsharddirector1 -listener 1571 -pwd oracle -catalog sdb1:1521:shardcat -regionregion1

GDSCTL>add gsm -gsmsharddirector1 -listener 1571 -pwd oracle -catalog sdb1:1521:shardcat -regionregion1

GSM successfully added

GDSCTL>

GDSCTL>start gsm-gsm sharddirector1

GDSCTL>GSM isstarted successfully

Repeat steps 6 and 8 oneach shard director host. Replace the shard director name and region name withappropriate values for each host.

7.3.创建信用账户，该账户为操作系统账户，用于远程调用agent来进行后台job任务运行，例如dbca,netca等（默认使用oracle账户）

Using GDSCTL, set theoperating system credentials.

GDSCTL> addcredential -credential credential_name -osaccount os_account_name -ospasswordos_password

GDSCTL> exit

These credentials arethe operating system user name and password on the shard hosts (not the cataloghost), and the credentials are used by the remote scheduler agent to run jobson the hosts to set up the shards using DBCA, NETCA, and the like.

--add credential-credential shard_cred -osaccount oracle -ospassword oracle

GDSCTL>addcredential -credential shard_cred -osaccount oracle -ospassword oracle

The operation completedsuccessfully

GDSCTL>exit

8.发布系统管理的SDB

发布系统管理的SDB需要创建shardgroup和shard、创建配置用于shard分片的数据库、执行deloy、创建基于角色的全局服务等操作

系统管理的shard不需要用户映射数据到shard节点，数据使用分区技术自动hash分布到各个shard节点

使用以下命令验证是否可以可以shard数据库

SQL> setserveroutput on

SQL> executeDBMS_GSM_FIX.validateShard

8.1创建shardgroup

$ gdsctl

GDSCTL> set gsm -gsmsharddirector1

GDSCTL> connectmysdbadmin/oracle

i).创建主库的shardgroup

GDSCTL> add shardgroup-shardgroup primary_shardgroup -deploy_as primary -region region1

ii)创建ADG的shardgroup

GDSCTL> addshardgroup -shardgroup standby_shardgroup -deploy_as active_standby -regionregion2

*************************

Current GSM is set toSHARDDIRECTOR1

GDSCTL>set gsm -gsmsharddirector1

GDSCTL>

GDSCTL>connectmygdsadmin/oracle

Catalog connection isestablished

GDSCTL>

GDSCTL>-- add shardgroup

GDSCTL>addshardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1

The operation completedsuccessfully

GDSCTL>

GDSCTL>-- add shard

GDSCTL>addinvitednode sdb2

GDSCTL>create shard-shardgroup primary_shardgroup -destination sdb2 -credential oracle_cred

DB Unique Name: sh1

GDSCTL>

GDSCTL>addinvitednode sdb3

GDSCTL>create shard-shardgroup primary_shardgroup -destination sdb3 -credential oracle_cred

DB Unique Name: sh2

*************************

------------创建单节点的shardgroup

Current GSM is set toSHARDDIRECTOR1

GDSCTL>set gsm -gsmsharddirector1

GDSCTL>connectmysdbadmin/oracle

Catalog connection isestablished

--创建shardgroup

GDSCTL>addshardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1

The operation completedsuccessfully

--添加shard1到shardgroup组

--删除shardgroup

8.2创建shard(将shard添加到shardgroup

可以使用-sys_password选项指定sys的密码（使用deloy指令后）

GDSCTL> addinvitednode shard_host_1

GDSCTL> create shard-shardgroup primary_shardgroup -destination shard_host_1 -credentialcredential_name -sys_password sys_password

GDSCTL> addinvitednode shard_host_2

GDSCTL> create shard-shardgroup standby_shardgroup -destination shard_host_2 -credentialcredential_name -sys_password sys_password

GDSCTL> addinvitednode shard_host_3

GDSCTL> create shard-shardgroup primary_shardgroup -destination shard_host_3 -credential credential_name-sys_password sys_password

GDSCTL> addinvitednode shard_host_4

GDSCTL> create shard-shardgroup standby_shardgroup -destination shard_host_4 -credentialcredential_name -sys_password sys_password

添加shard

GDSCTL> add shard–shardgroup shgrp1 –connect shard_host:1521/shard_database_name –pwdGSMUSER_password

---

删除shard

-REMOVE SHARD -SHARD{shard_name_list | ALL} | -SHARDSPACE shardspace_list | -SHARDGROUP shardgroup_list} [-FORCE]

-remove shard -shard sh21 -force

-remove shard -shardsh1 -force

--primary_shardgroup

--shardspaceora

-----------------------

GDSCTL>add invitednode shard1

GDSCTL>create shard-shardgroup primary_shardgroup -destination shard1 -credential shard_cred-sys_password sys

The operation completedsuccessfully

DB Unique Name: sh1

--添加shard2到shardgroup组

--create shard-shardgroup primary_shardgroup -destination shard2 -credential shard_cred

GDSCTL>add invitednode shard2

DSCTL>create shard-shardgroup primary_shardgroup -destination shard2 -credential shard_cred-sys_password sys

The operation completedsuccessfully

DB Unique Name: sh21

*********************

GDSCTL>create shard-shardgroup primary_shardgroup -destination shard1 -credential shard_cred

GSM-45029: SQL error

ORA-02610: Remote job failedwith error:

EXTERNAL_LOG_ID="job_73730_3",

USERNAME="oracle",

STANDARD_ERROR="Launchingexternal job failed: Login executable not setuid-root"

---解决办法，以root用户在各个$ORACLE_HOME执行root.sh

*********************

----------

8.3验证shard和shardgroup状态

GDSCTL>config

Regions

------------------------

region1

region2

GSMs

------------------------

sharddirector1

Sharded Database

------------------------

shardcat

Databases

------------------------

sh1

sh21

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 shard

Name Shard Group Status State Region Availability

---- ----------- ------ ----- ------ ------------

sh1 primary_shardgroup U none region1 -

sh21 primary_shardgroup U none region1 -

GDSCTL>config vncr

Name Group ID

---- --------

shard1

shard2

192.168.43.100

GDSCTL>databases;

Database:"sh1" Registered: N State: null ONS: N. Role: N/A Instances: 0Region: region1

Database:"sh21" Registered: N State: null ONS: N. Role: N/A Instances: 0Region: region1

8.4Deploy部署shard

此时，就开始部署shard了。在shard node上的agent会自动的调用netca和dbca，创建listener和database，2个shard node的操作是并行进行的。

（如果是有datauard，那么是先建立一对主备，再建立另一对主备。）你可以在分别是两个shard node上ps -ef|grep ora_ 看到已经有sh1和sh2的实例了。

---

delploy

GDSCTL>deploy

deploy: examiningconfiguration...

deploy: deployingprimary shard 'sh41' ...

deploy: networklistener configuration successful at destination 'shard1'

deploy: starting DBCAat destination 'shard1' to create primary shard 'sh41' ...

deploy: deployingprimary shard 'sh61' ...

deploy: networklistener configuration successful at destination 'shard2'

deploy: starting DBCAat destination 'shard2' to create primary shard 'sh61' ...

deploy: waiting for 2DBCA primary creation job(s) to complete...

deploy: DBCA primarycreation job succeeded at destination 'shard2' for shard 'sh61'

deploy: waiting for 1DBCA primary creation job(s) to complete...

deploy: DBCA primarycreation job succeeded at destination 'shard1' for shard 'sh41'

deploy: requesting DataGuard configuration on shards via GSM

deploy: shardsconfigured successfully

The operation completedsuccessfully

----查看shard节点后台进程

racle 4684 4681 45 19:17 pts/0 00:00:24/u01/app/oracle/product/12.2.0/dbhome_1/jdk/jre/bin/java-Doracle.installer.not_bootstrap=true-DCV_HOME=/u01/app/oracle/product/12.2.0/dbh

ome_1-DORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1-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/dbhome_1

/assistants/dbca/jlib/dbca.jar:/u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/jlib/dbcaext.jar:/u01/app/oracle/product/12.2.0/dbhome_1/assistants/jlib/assistantsCommon.jar:/u01/app

/oracle/product/12.2.0/dbhome_1/assistants/jlib/rconfig.jar:/u01/app/oracle/product/12.2.0/dbhome_1/assistants/jlib/asstcommonext.jar:/u01/app/oracle/product/12.2.0/dbhome_1/oui/jlib/OraIns

taller.jar:/u01/app/oracle/product/12.2.0/dbhome_1/oui/jlib/OraPrereq.jar:/u01/app/oracle/product/12.2.0/dbhome_1/inventory/prereqs/oui/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db

home_1/oui/jlib/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/dbhome_1/oui/jlib/OraCheckPoint.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/cvu.jar:/u01/app/oracle/product/12.2.0/db

home_1/install/jlib/installcommons_1.0.0b.jar:/u01/app/oracle/product/12.2.0/dbhome_1/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/dbhome_1/oui/jlib/ssh.jar:/u01/app/oracle/product/12.

2.0/dbhome_1/jlib/ewt3.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/ewtcompat-3_3_15.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/share.jar:/u01/app/oracle/product/12.2.0/dbhome_1/j

lib/help4.jar:/u01/app/oracle/product/12.2.0/dbhome_1/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/oracle_ice.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/kodiak.jar:

/u01/app/oracle/product/12.2.0/dbhome_1/lib/xmlparserv2.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/orai18n.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/ldapjclnt12.jar:/u01/app/or

acle/product/12.2.0/dbhome_1/jlib/netcfg.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/ojmisc.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/oraclepki.jar:/u01/app/oracle/product/12.2.

0/dbhome_1/jlib/opm.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jdbc/lib/ojdbc8.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/srvm.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/srvmha

s.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/srvmasm.jar:/u01/app/oracle/product/12.2.0/dbhome_1/dv/jlib/dvca.jar:/u01/app/oracle/product/12.2.0/dbhome_1/jlib/gns.jar:/u01/app/oracle/

product/12.2.0/dbhome_1/jlib/commons-compress-1.8.jaroracle.assistants.dbca.driver.DBConfigurator -silent -responseFile/u01/app/oracle/product/12.2.0/dbhome_1/shard_sh1_dbca.rsp -createDa

tabase -gdbName sh1.com-sid sh1 -initparams db_unique_name=sh1,db_name=sh1,db_domain=com -templateName/u01/app/oracle/product/12.2.0/dbhome_1/shard_sh1_template.dbt -customscripts/u01/ap

p/oracle/product/12.2.0/dbhome_1/shard_sh1_postCR.sql-listeners LISTENER_sh1

****************************

DESTINATION

--------------------------------------------------------------------------------

OUTPUT

--------------------------------------------------------------------------------

SHARD1

Parsing command linearguments:

Parameter "silent" = true

Parameter "responsefile" =/u01/app/oracle/product/12.2.0/dbhome_1/shard_sh1

_netca.rsp

Done parsing commandline arguments.

Oracle Net ServicesConfiguration:

Listener"LISTENER_SH1" already exists.

Profile configurationcomplete.

DESTINATION

--------------------------------------------------------------------------------

OUTPUT

--------------------------------------------------------------------------------

Check the trace filefor details: /u01/app/oracle/cfgtoollogs/netca/trace_OraCli

ent12Home1-17060711AM3540.log

Oracle Net Servicesconfiguration failed. The exit code is 1

---解决办法：通过netmgr删除所有监听

****************************

8.5Verifythat all of the shards are deployed.

--config shard

GDSCTL>config shard

Name Shard Group Status State Region Availability

---- ----------- ------ ----- ------ ------------

sh41 primary_shardgroup Ok Deployed region1 ONLINE

sh61 primary_shardgroup Ok Deployed region1 ONLINE

8.6Verifythat all of the shards are registered

--config shard -shardsh1

GDSCTL>config shard-shard sh41

Name: sh41

Shard Group:primary_shardgroup

Status: Ok

State: Deployed

Region: region1

Connection string:shard1:1521/sh41.com: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>databases;

Database:"sh41" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1Region: region1

Registered instances:

shardcat%1

Database:"sh61" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1Region: region1

Registered instances:

shardcat%11

8.7.Add aglobal service that runs on all of the primary shards.

添加服务

The oltp_rw_srvc globalservice is a global data service that a client can use to connect to thesharded database. The oltp_rw_srvc service runs the OLTP transactions on theprimary shards. Likewise, the oltp_ro_srvc global service is created to runread-only workload on the standby shards.

GDSCTL> add service-service oltp_rw_srvc -role primary

GDSCTL> configservice

Name Network name Pool Started Preferred all

---- ------------ ---- ------- -------------

oltp_rw_srvc oltp_rw_srvc.cust_sdb.oracdbcloudcust_sdb No Yes

-----------------------

GDSCTL>add service-service oltp_rw_srvc -role primary

The operation completedsuccessfully

GDSCTL>configservice

Name Network name Pool Started Preferred all

---- ------------ ---- ------- -------------

oltp_rw_srvc oltp_rw_srvc.shardcat.oradbcl shardcat No Yes

oud

8.8.Startthe oltp_rw_srvc global service.

启动service

GDSCTL> startservice -service oltp_rw_srvc

GDSCTL> statusservice

Service"oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity:ANYWHERE

Instance "cust_sdb%1", name:"sh1", db: "sh1", region: "region1", status:ready.

Instance "cust_sdb%21", name:"sh3", db: "sh3", region: "region1", status:ready.

----启动service

GDSCTL>start service-service oltp_rw_srvc

The operation completedsuccessfully

---查看Service状态

GDSCTL>statusservice

Service"oltp_rw_srvc.shardcat.oradbcloud" has 2 instance(s). Affinity:ANYWHERE

Instance "shardcat%1", name:"sh41", db: "sh41", region: "region1", status:ready.

Instance "shardcat%11", name:"sh61", db: "sh61", region: "region1", status:ready.

~~8.9.Add aglobal service for the read-only workload to run on the standby shards.~~

GDSCTL> add service-service oltp_ro_srvc -role physical_standby

GDSCTL> configservice

Name Network name Pool Started Preferred all

---- ------------ ---- ------- -------------

oltp_rw_srvc oltp_rw_srvc.cust_sdb.oracdbcloudcust_sdb Yes Yes

oltp_ro_srvc oltp_ro_srvc.cust_sdb.oracdbcloudcust_sdb No Yes

8.10.Startthe read-only service, and verify the status of the global services.

GDSCTL> startservice -service oltp_ro_srvc

GDSCTL> status service

Service"oltp_ro_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity:ANYWHERE

Instance "cust_sdb%11", name:"sh2", db: "sh2", region: "region2", status:ready.

Instance "cust_sdb%31", name:"sh4", db: "sh4", region: "region2", status: ready.

Service"oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity:ANYWHERE

Instance "cust_sdb%1", name:"sh1", db: "sh1", region: "region1", status:ready.

Instance "cust_sdb%21", name:"sh3", db: "sh3", region: "region1", status:ready.

---

GDSCTL>statusservice

Service"oltp_rw_srvc.shardcat.oradbcloud" has 2 instance(s). Affinity:ANYWHERE

Instance "shardcat%1", name:"sh41", db: "sh41", region: "region1", status:ready.

Instance "shardcat%11", name:"sh61", db: "sh61", region: "region1", status:ready.

9.配置Shard应用

9.1.Connectto the shard catalog database, create the application schema user, and grantprivileges and roles to the user.

连接到catlog databse创建应用账号app_schema（SDB[shardcat实例]），并授权

In this example, theapplication schema user is called app_schema.

$ sqlplus / as sysdba

SQL> alter sessionenable shard ddl;

SQL> create userapp_schema identified by oracle;

SQL> grant allprivileges to app_schema;

SQL> grantgsmadmin_role to app_schema;

SQL> grantselect_catalog_role to app_schema;

SQL> grant connect,resource to app_schema;

SQL> grant dba toapp_schema;

SQL> grant executeon dbms_crypto to app_schema;

9.2.Createa tablespace set for the sharded tables.

使用app_schema登录创建表空间集

SQL> CREATETABLESPACE SET TSP_SET_1 using template (datafile size 100m autoextend on next10M

maxsize unlimited extent management localsegment space management auto);

Specifying theshardspace is optional when creating the tablespace set. If the shardspace isnot specified in the command, the default shardspace is used.

---

aler session enableshard ddl;

create tablespace setts_set_1 using template(datafile size 100m autoextend on next 10m maxsizeunlimited extent management local segment space management auto);

------创建表空间集

SQL> create tablespaceset ts_set_1 using template(datafile size 100m autoextend on next 10m maxsizeunlimited extent management local segment space management auto);

Tablespace created.

-----创建表空间

CREATE TABLESPACEproducts_tsp datafile size 100m autoextend on next 10M maxsize unlimited extentmanagement local uniform size 1m;

9.3Create asharded table for the root table.

创建分片表

In this example, theroot table is the Customers table in the sample Customers-Orders-Productsschema.

SQL> CONNECT app_schema/app_schema_password

SQL> ALTER SESSIONENABLE SHARD DDL;

SQL> CREATE SHARDEDTABLE 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 TS_SET_1

PARTITION BY CONSISTENT HASH (CustId)PARTITIONS AUTO;

----------

SQL> CREATE SHARDEDTABLE 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 TS_SET_1

PARTITION BY CONSISTENT HASH (CustId)PARTITIONS AUTO;

2 3 4 5 6 7 8 9 10 11 12 13

Table created.

CREATE SHARDED TABLEOrders

(

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);

SQL> CREATE SEQUENCEOrders_Seq;

Sequence created.

SQL> CREATE SHARDEDTABLE 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);

2 3 4 5 6 7 8 9 10 11

Table created.

SQL> CREATEDUPLICATED TABLE Products

(

ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

Name VARCHAR2(128),

DescrUri VARCHAR2(128),

LastPrice NUMBER(19,4)

) TABLESPACE products_tsp;

2 3 4 5 6 7

Table created.

9.4查询各个shard中的chunk分配情况

登录Catalog （SDB）查询：

GDSCTL>config chunks

Chunks

------------------------

Database From To

-------- ---- --

sh41 1 6

sh61 7 12

表示2个shard节点:sh41分配1到6的chunks,sh61分配7-12chunks

9.5登录shardcat数据库验证创建的表空间集以及chunks数量

The number oftablespaces in the tablespace set is based on the number of chunks youspecified in the create shardcatalog command.

The tablespace set withthe first 6 chunks of the 12 that were specified in the shard catalog creationexample, and the duplicated Products tablespace is shown in the followingexample.

验证表空间集

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

TABLESPACE_NAME MB

----------------------------------------------------------------------------------------------------

PRODUCTS_TSP 100

SYSAUX 530

SYSTEM 810

TS_SET_1 100

UNDOTBS1 65

USERS 5

6 rows selected.

9.6登录shard验证chunk以及chunk tablespace创建成功

=============shard1

-----------------------

set pagesize 500;

set lin 300;

col table_name for a40;

col tablespace_name fora40;

col partition_name fora40;

SQL> show parameterdb_unique_name;

NAME TYPE VALUE

--------------------------------------------------------------------- ------------------------------

db_unique_name string sh41

select table_name,partition_name, tablespace_name from dba_tab_partitions where tablespace_namelike '%TS_SET_1' order by tablespace_name;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

------------------------------------------------------------------------------------------------------------------------

ORDERS CUSTOMERS_P1 C001TS_SET_1

CUSTOMERS CUSTOMERS_P1 C001TS_SET_1

LINEITEMS CUSTOMERS_P1 C001TS_SET_1

CUSTOMERS CUSTOMERS_P2 C002TS_SET_1

LINEITEMS CUSTOMERS_P2 C002TS_SET_1

ORDERS CUSTOMERS_P2 C002TS_SET_1

CUSTOMERS CUSTOMERS_P3 C003TS_SET_1

ORDERS CUSTOMERS_P3 C003TS_SET_1

LINEITEMS CUSTOMERS_P3 C003TS_SET_1

ORDERS CUSTOMERS_P4 C004TS_SET_1

CUSTOMERS CUSTOMERS_P4 C004TS_SET_1

LINEITEMS CUSTOMERS_P4 C004TS_SET_1

LINEITEMS CUSTOMERS_P5 C005TS_SET_1

ORDERS CUSTOMERS_P5 C005TS_SET_1

CUSTOMERS CUSTOMERS_P5 C005TS_SET_1

ORDERS CUSTOMERS_P6 C006TS_SET_1

CUSTOMERS CUSTOMERS_P6 C006TS_SET_1

LINEITEMS CUSTOMERS_P6 C006TS_SET_1

ORDERS CUSTOMERS_P8 TS_SET_1

ORDERS CUSTOMERS_P7 TS_SET_1

ORDERS CUSTOMERS_P12 TS_SET_1

ORDERS CUSTOMERS_P11 TS_SET_1

ORDERS CUSTOMERS_P10 TS_SET_1

LINEITEMS CUSTOMERS_P9 TS_SET_1

LINEITEMS CUSTOMERS_P8 TS_SET_1

LINEITEMS CUSTOMERS_P7 TS_SET_1

LINEITEMS CUSTOMERS_P12 TS_SET_1

LINEITEMS CUSTOMERS_P11 TS_SET_1

LINEITEMS CUSTOMERS_P10 TS_SET_1

CUSTOMERS CUSTOMERS_P9 TS_SET_1

CUSTOMERS CUSTOMERS_P8 TS_SET_1

CUSTOMERS CUSTOMERS_P7 TS_SET_1

CUSTOMERS CUSTOMERS_P12 TS_SET_1

CUSTOMERS CUSTOMERS_P11 TS_SET_1

ORDERS CUSTOMERS_P9 TS_SET_1

CUSTOMERS CUSTOMERS_P10 TS_SET_1

36 rows selected.

//可以看到:

//orders表chunk包含了p1-p6

//lineitems表包含了p1-p6

=============shard2

-----------------------

set pagesize 500;

set lin 300;

col table_name for a40;

col tablespace_name fora40;

col partition_name fora40;

SQL> show parameterdb_unique_name;

SQL> select table_name,partition_name, tablespace_name from dba_tab_partitions where tablespace_namelike '%TS_SET_1' order by tablespace_name;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

------------------------------------------------------------------------------------------------------------------------

CUSTOMERS CUSTOMERS_P7 C007TS_SET_1

ORDERS CUSTOMERS_P7 C007TS_SET_1

LINEITEMS CUSTOMERS_P7 C007TS_SET_1

ORDERS CUSTOMERS_P8 C008TS_SET_1

LINEITEMS CUSTOMERS_P8 C008TS_SET_1

CUSTOMERS CUSTOMERS_P8 C008TS_SET_1

ORDERS CUSTOMERS_P9 C009TS_SET_1

CUSTOMERS CUSTOMERS_P9 C009TS_SET_1

LINEITEMS CUSTOMERS_P9 C009TS_SET_1

CUSTOMERS CUSTOMERS_P10 C00ATS_SET_1

LINEITEMS CUSTOMERS_P10 C00ATS_SET_1

ORDERS CUSTOMERS_P10 C00ATS_SET_1

ORDERS CUSTOMERS_P11 C00BTS_SET_1

LINEITEMS CUSTOMERS_P11 C00BTS_SET_1

CUSTOMERS CUSTOMERS_P11 C00BTS_SET_1

LINEITEMS CUSTOMERS_P12 C00CTS_SET_1

ORDERS CUSTOMERS_P12 C00CTS_SET_1

CUSTOMERS CUSTOMERS_P12 C00CTS_SET_1

LINEITEMS CUSTOMERS_P6 TS_SET_1

LINEITEMS CUSTOMERS_P5 TS_SET_1

LINEITEMS CUSTOMERS_P4 TS_SET_1

LINEITEMS CUSTOMERS_P3 TS_SET_1

LINEITEMS CUSTOMERS_P2 TS_SET_1

LINEITEMS CUSTOMERS_P1 TS_SET_1

ORDERS CUSTOMERS_P6 TS_SET_1

ORDERS CUSTOMERS_P5 TS_SET_1

ORDERS CUSTOMERS_P4 TS_SET_1

ORDERS CUSTOMERS_P3 TS_SET_1

ORDERS CUSTOMERS_P2 TS_SET_1

ORDERS CUSTOMERS_P1 TS_SET_1

CUSTOMERS CUSTOMERS_P6 TS_SET_1

CUSTOMERS CUSTOMERS_P5 TS_SET_1

CUSTOMERS CUSTOMERS_P4 TS_SET_1

CUSTOMERS CUSTOMERS_P3 TS_SET_1

CUSTOMERS CUSTOMERS_P1 TS_SET_1

CUSTOMERS CUSTOMERS_P2 TS_SET_1

9.7 登录catalog database验证所有的chunks都是统一分布式的：

set line 300;

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

ORDER BY a.name;

-------

SQL> set line 300;

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

ORDER BY a.name;

SQL> SQL> 2 3 4 5

SHARD NUMBER_OF_CHUNKS

----------------------------------------------------------------------------------------------------------

sh41 6

sh61 6

9.8 Verifythat the sharded and duplicated tables were created.

确认shard和复制表创建完毕

SQL> show user;

USER is"APP_SCHEMA"

SQL> col table_namefor a40;

SQL> selecttable_name from user_tables;

TABLE_NAME

----------------------------------------

CUSTOMERS

ORDERS

LINEITEMS

PRODUCTS

MLOG$_PRODUCTS

RUPD$_PRODUCTS

6 rows selected.

-----------------------Shard1查看表

SQL> set lin 300;

SQL> show user;

USER is "SYS"

SQL> show parameterdb_unique_name;

NAME TYPE VALUE

--------------------------------------------------------------------- ------------------------------

db_unique_name string sh41

SQL> set pagesize400;

SQL> select owner,object_id,object_name,object_type from dba_objects where object_name='CUSTOMERS';

OWNER OBJECT_IDOBJECT_NAME OBJECT_TYPE

-------------------------------------------------- --------------------------------------------------------------------------------

APP_SCHEMA 3000005CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000014CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000015CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000016CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000006CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000007CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000008CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000009CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000010CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000011CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000012 CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000013CUSTOMERS TABLE PARTITION

APP_SCHEMA 3000004CUSTOMERS TABLE

13 rows selected.

---------Shard2查看表信息

NAME TYPE VALUE

--------------------------------------------------------------------- ------------------------------

db_unique_name string sh61

SQL> /

OWNER OBJECT_IDOBJECT_NAME OBJECT_TYPE

-------------------------------------------------- ---------------------------------------- ----------------------------------------

APP_SCHEMA 4000005CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000014CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000015CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000016CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000006 CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000007CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000008CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000009CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000010CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000011CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000012CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000013 CUSTOMERS TABLE PARTITION

APP_SCHEMA 4000004CUSTOMERS TABLE

至此，Shardig环境部署成功

转自http://blog.csdn.net/jinyaqi2013/article/details/76592467

posted @ 2017-12-06 19:06 我是DBA 阅读(4778) 评论(0) 编辑收藏举报

刷新页面返回顶部

我是DBA

求知、探索、分享！

Oracle 12c 分片（Sharding）技术

Sharding特点

Sharding体系结构

Sharding环境部署

公告