Oracle 12C Sharding安装

1、 安装前介绍

1、1 Oracle Sharding****架构介绍

l 管理节点：

这类节点的作用时管理MYSQLCluster内的其他节点，如提供配置数据、启动并停止节点、运行备份等。一般应在启动其他节点之前首先启动该节点，对硬件要求较低。

l SQL节点：

这是用来访问cluster数据的节点。对于MYSQLCluster，客户端节点是使用NDBCluster存储引擎的传统mysql服务器。

对CPU要求较高，建议选择多核，高频CPU

l DB节点：

这类节点是用于保存cluster的数据。支持分布式存储。

内存需求量巨大。

备注：Cluster使用自动键值识别数据分片方案，用户无需关心数据切片方案，透明实现分布式数据库，数据分片规则根据主键、唯一索引、自动行标志rowid完成，根据集群个数进行分布，其访问数据犹如RAID访问机制，能并行从各个节点抽取数据，散列数据，当使用非主键或分区键访问时，将导致所有簇节点扫描，影响性能。

1、2 安装规划

集群软件版本：Mysql Cluster 7.6.7

操作系统版本：Redhat Linux 7.4

安装环境配置如下：

2、 安装

2、1 Oracle12c 安装

备注：在三个节点均安装，完成预安装步骤，完成数据库软件安装

2、1.1 安装环境准备

（一）关闭防火墙及selinux

[root@testdb1 mnt]# systemctl stop firewalld

[root@testdb1 mnt]# systemctl disable firewalld

[root@testdb1 mnt]# vi /etc/selinux/config

SELINUX=disabled

（二）创建用户

[root@testdb1 mnt]# groupadd -g 1000 oinstall

[root@testdb1 mnt]# groupadd -g 1300 dba

[root@testdb1 mnt]# groupadd -g 1301 oper

[root@testdb1 mnt]# useradd -u 1101 -g oinstall -G dba,oper oracle

（三）创建安装目录

[root@testdb1 mnt]# mkdir -p /u01/app/oracle/

[root@testdb1 mnt]# mkdir -p /u01/app/oracle/

[root@testdb1 mnt]# chown -R oracle:oinstall /u01/app/oracle/

（四）内核资源与参数配置

[root@testdb1 mnt]# vi /etc/sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 1987485696

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

kernel.panic_on_oops=1

（五）用户资源限制

[root@testdb1 mnt]# vi /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 10240

（六）安装必备包

yum -y install bc

yum -y install binutils

yum -y install compat-libcap1

yum -y install compat-libstdc++.i686

yum -y install compat-libstdc++

yum -y install glibc.i686

yum -y install glibc

yum -y install glibc-devel.i686

yum -y install glibc-devel

yum -y install ksh

yum -y install gcc-c++

yum -y install libaio.i686

yum -y install libaio

yum -y install libaio-devel.i686

yum -y install libaio-devel

yum -y install libgcc.i686

yum -y install libgcc

yum -y install libstdc++.i686

yum -y install libstdc++

yum -y install libstdc++-devel.i686

yum -y install libstdc++-devel

yum -y install libxcb.i686

yum -y install libxcb

yum -y install libX11.i686

yum -y install libX11

yum -y install libXau.i686

yum -y install libXau

yum -y install libXi.i686

yum -y install libXi

yum -y install libXtst.i686

yum -y install libXtst

yum -y install make

yum -y install net-tools

yum -y install nfs-utils

yum -y install smartmontools

yum -y install sysstat

2、1.2 数据库安装

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

2、2 创建Shared目录数据库（192.168.3.122）

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

![img][]

密码：123456789

![img][]

![img][]

2、3 安装GSM（192.168.3.122）

![img][]

![img][]

![img][]

2、4 建立Oracle共享管理和路由层

（一） 共享目录数据库参数设置

alter system set db_create_file_dest=’/u01/app/oracle/oradata/’ scope=spfile;

alter system set open_links=16 scope=spfile;

alter system set open_links_per_instance=16 scope=spfile;

shut immediate;

startup;

（二） 创建用户并授权

set echo on

set termout on

spool setup_grants_privs.lst

alter user gsmcatuser account unlock;

alter user gsmcatuser identified by gsmcatuser;

create user mysdbadmin identified by mysdbadmin;

grant connect, create session, gsmadmin_role to mysdbadmin;

grant inherit privileges on user SYS to GSMADMIN_INTERNAL;

spool off

（三） 创建共享目录和配置代理

[oracle@testdb1 ~]$ vi gsm_profile

export ORACLE_BASE=/u01/app/gsm/

export ORACLE_HOME=/u01/app/gsm/product/12.2.0/gsmhome_1

export PATH

export PATH=PATH

[oracle@testdb1 ~]$ source gsm_profile

[oracle@testdb1 ~]$ gdsctl

GDSCTL> create shardcatalog -database 192.168.3.122:1521:scat -chunks 12 -user mysdbadmin/mysdbadmin -sdb scat -region region2 -agent_port 8080 -agent_password oracle

GDSCTL>add gsm -gsm sharddirector1 -listener 1571 -pwd gsmcatuser -catalog 192.168.3.122:1521:scat -region region2

GDSCTL>start gsm -gsm sharddirector1

GDSCTL> add credential -credential oracle_cred -osaccount oracle -ospassword oracle

（四） 开启代理并注册共享节点（两个节点均开启）

[oracle@testdb2 ~]$ schagent -start

Scheduler agent started using port 22389

[oracle@testdb2 ~]$ schagent status

Agent running with PID 14756

Agent_version:12.2.0.1.2

Running_time:00:01:27

Total_jobs_run:0

Running_jobs:0

Platform:Linux

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

ORACLE_BASE:null

Port:22389

Host:testdb2

[oracle@testdb2 ~]$ echo oracle | schagent -registerdatabase testdb1 8080

Agent Registration Password ?

Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent

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

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

[oracle@testdb2 ~]$ mkdir -p /u01/app/oracle/admin/scat/dpdump/

2、5 创建系统管理的SDB

2、5.1 部署

（一）验证数据库环境

SQL> set serveroutput on

SQL> execute DBMS_GSM_FIX.validateShard

![img][]

备注：修复warning和ERROR信息

SQL> alter database force logging;

SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’ scope=spfile;

SQL> alter user GSMUSER identified by gsmuser account unlock;

（二）连接GSM，创建目录组

GDSCTL>set gsm -gsm SHARDDIRECTOR1

GDSCTL>connect mysdbadmin/mysdbadmin

Catalog connection is established

GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region2;

（三）添加节点

添加testdb2

GDSCTL>add invitednode testdb2

GDSCTL>create shard -shardgroup primary_shardgroup -destination testdb2 -credential oracle_cred -sys_password 123456789

The operation completed successfully

DB Unique Name: sh1

添加testdb3

GDSCTL>add invitednode testdb3

GDSCTL>create shard -shardgroup primary_shardgroup -destination testdb3 -credential oracle_cred -sys_password 123456789

The operation completed successfully

DB Unique Name: sh21

GDSCTL>config

![img][]

（五） 部署

GDSCTL>deploy

![img][]

（六） 验证shared状态

GDSCTL>config shard

![img][]

GDSCTL>databases

![img][]

2、5.2 创建服务

（一）创建服务

GDSCTL>add service -service shared_tpcds -role primary

GDSCTL>config service

Name Network name Pool Started Preferred all

shared_tpcds shared_tpcds.scat.oradbcloud scat No Yes

（二）启动服务

GDSCTL>start service -service shared_tpcds

The operation completed successfully

GDSCTL>status service

Service “shared_tpcds.scat.oradbcloud” has 2 instance(s). Affinity: ANYWHERE

Instance “scat%1”, name: “sh1”, db: “sh1”, region: “region2”, status: ready.

Instance “scat%11”, name: “sh21”, db: “sh21”, region: “region2”, status: ready.

2、5.2 创建****schema

登录testdb1

(一)创建用户

[oracle@testdb1 ~]$ sqlplus / as sysdba

SQL> alter session enable shard ddl;

SQL> create user app_schema identified by oracle;

SQL> grant all privileges to app_schema;

SQL> grant gsmadmin_role to app_schema;

SQL> grant select_catalog_role to app_schema;

SQL> grant connect, resource to app_schema;

SQL> grant dba to app_schema;

SQL> grant execute on dbms_crypto to app_schema;

(二)创建表空间

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

SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;

（三）创建表

[oracle@testdb1 ~]$ sqlplus app_schema/oracle

SQL> ALTER SESSION ENABLE SHARD DDL;

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;

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

SQL> CREATE SEQUENCE Orders_Seq;

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

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;

（四）验证表分片

验证表空间

登录:testdb2

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 470

SYSTEM 800

TSP_SET_1 100

UNDOTBS1 70

TABLESPACE_NAME MB

USERS 5

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

ORDERS CUSTOMERS_P1 C001TSP_SET_1

CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1

LINEITEMS CUSTOMERS_P1 C001TSP_SET_1

CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1

LINEITEMS CUSTOMERS_P2 C002TSP_SET_1

ORDERS CUSTOMERS_P2 C002TSP_SET_1

CUSTOMERS CUSTOMERS_P3 C003TSP_SET_1

ORDERS CUSTOMERS_P3 C003TSP_SET_1

LINEITEMS CUSTOMERS_P3 C003TSP_SET_1

ORDERS CUSTOMERS_P4 C004TSP_SET_1

CUSTOMERS CUSTOMERS_P4 C004TSP_SET_1

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

LINEITEMS CUSTOMERS_P4 C004TSP_SET_1

CUSTOMERS CUSTOMERS_P5 C005TSP_SET_1

LINEITEMS CUSTOMERS_P5 C005TSP_SET_1

ORDERS CUSTOMERS_P5 C005TSP_SET_1

CUSTOMERS CUSTOMERS_P6 C006TSP_SET_1

LINEITEMS CUSTOMERS_P6 C006TSP_SET_1

ORDERS CUSTOMERS_P6 C006TSP_SET_1

[img]:
文章永久链接：https://tech.souyunku.com/29688