Sharding特点
Oracle12c Sharding技术实现了跨数据库级别的数据分片,实现了分布式IO大数据扩展的云数据库架构体系,可满足大数据海量存储、分布式数据读写以及满足Scale Out/ScaleUp扩展性需求。Sharding的主要特点:
q 线性伸缩性(Scale out):Sharding技术减少性能瓶颈,同时可以通过添加Shard节点的方式扩展容量和性能;
q 错误纠正机制:Sharding是基于非共享物理资源的架构设计,有效消除了单点故障,例如共享磁盘以及SAN存储扥。它的错误纠正机制保证了单个Shard故障不会影响整个集群的使用和性能
q 地域分布式数据:Sharding可以将数据就近存储在消费者的同时,可以有效的满足监管范围内的管控需要
q 滚动式更新:应用更新一次仅在一个Shard节点上进行,不会影响其他节点,这样可以保证在小部分数据更新时,可以及时进行数据验证。
q 简化云发布:Sharding可以简化云发布
Sharding体系结构
Oracle12c 应用程序通过数据库pool将数据库在逻辑层当做1个数据库:数据、事务、用户以及所有Level,通过将Shards添加到pool。Oracle12c支持1000个Shards。
Sharding技术是数据层架构体系,在各个数据库间进行垂直分片。Sharding主要组成结构如下:
其中:
q SDB(Sharded database):一个逻辑层的数据库,该数据库跨越多个物理分片数据库(Shard)组成的数据池进行水平分区。
q Shards:独立的物理数据库集(SDB)组成节点。
q ShardCatalog - 它也是一个Oracle Database,主要用于Shard的自动部署、集中管理以及跨Shard的查询。
q 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>
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主数据库服务器和安装有