手把手地教你搭建Oracle Sharding

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

本文將向您展示如何從頭一步一步搭建Sharded Database的過程，展示過程中還會穿插講解一些相關的概念。

本例將搭建如下 Sharded Database (SDB).

· 一共3臺Host，即 SDB1, SDB2, SDB3。均已安裝Oracle Linux 6.7。

· 在HostSDB1上安裝Shard Director和Shard Catalog。

· 在HostSDB2和HostSDB3上各安裝一個Shard。

下面是對Oracle Sharding 主要部件的簡要解釋：

Sharded Database(SDB) – 是一個邏輯上的Oracle Database,它由多個物理上互相獨立的Oracle Databases (Shards) 組成，Shards之間不共享任何軟體和硬體，即Share Nothing.

Shards – 是一個獨立的資料庫。

ShardCatalog - 它也是一個Oracle Database，主要用於Shard的自動部署、集中管理以及跨Shard的查詢。

ShardDirectors - 跟據Sharding Key來提供到Shard的路由。OracleSharding中可以設定多個位於不同Host的Shard Director。

Oracle Sharding 支援如下三種Sharding方式：

System-ManagedSharding – 這種Sharding方式不需要使用者指定資料到Shard的mapping關係（可以簡單理解為資料按consistenthash之後再分佈到各個Shard），本例中將使用這種Sharding 方式。

CompositeSharding –這種Sharding方式需要使用者指定資料到Shard的mapping關係（這裡將不做詳細講解）。

UsingSubpartitions with Sharding – Oracle Sharding是基於表分割槽技術的，因此 Sharding可以支援所有的subpartition方法。

下面開始搭建環境。

一．安裝軟體：

OracleDatabase 12c Release 2

OracleDatabase 12c Release 2 Global Service Manager (GSM/GDS)

1. 在所有節點上安裝Oracle Database 12c Release 2。(注：只安裝軟體，不建立DB)

主機hosts檔案寫上本機和各個shard node的IP解析

1.1 安裝前準備。

yum install oracle-database-server-12cR2-preinstall-1.0-1.el6.x86_64

yum install gcc-c++.x86_64

echo 'oracle:oracle' | chpasswd

mkdir /u01

chown -R oracle:oinstall /u01

su - oracle

./runInstaller

1.2 開始安裝，點選Next。

1.3 選擇Installdatabase software only，點選Next。

1.4 選擇Singleinstance database installation，點選Next。

1.5 選擇EnterpriseEdition，點選Next。

1.6 接下來的所有步驟都使用預設值。點選Next。

1.7 在Summary頁面，選擇SaveResponse File，將用於其它節點的安裝。

1.8 安裝過程中，應該按要求新開一個終端，用root執行指令碼。

1.9 在另外兩個節點準備環境並靜默安裝Oracle Database 12c Release 2。

yum install oracle-database-server-12cR2-preinstall-1.0-1.el6.x86_64

yum install gcc-c++.x86_64

echo 'oracle:oracle' | chpasswd

mkdir /u01

chown -R oracle:oinstall /u01

su -oracle

./runInstaller -silent -responseFile /home/oracle/db.rsp

2. 在Shard Director 所在節點安裝Oracle Database 12c Release 2 Global Service Manager (GSM/GDS)，本例中即HOST SDB1。安裝過程與安裝OracleDatabase 12c Release 2類似，均使用預設值。

二．建立ShardCatalog Database。本例中將會在HostSDB1上建立。

1. 準備環境並啟動DBCA。

export ORACLE_BASE=/u01/app/oracle

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

mkdir /u01/app/oracle/oradata

mkdir /u01/app/oracle/fast_recovery_area

$ORACLE_HOME/bin/dbca

2. 選擇Create a database，點選Next。

3. 選擇Advanced configuration，點選Next。

4. 選擇Oracle Single Instance database, 選擇General Purpose orTransaction Process模板，點選Next。

5. 輸入Global Database name和SID，請不要選中Create asContainer database，點選Next。

6. 選擇File System，選擇Oracle-Managed Files(OMF)，點選Next。

7. 選擇Specify Fast Recovery Area ，Enable archiving，點選Next。

8. 接下來的頁使用預設值，並跳過Data Vault 選項頁。然後選擇Use Automatic Shared Memory Management。

9. 選擇Use Unicode(AL32UTF8)，點選Next。

10. 不要選擇Configure EnterpriseManager(EM) database express，點選Next。

11. 本例所有的使用者都用同樣密碼 : oracle。

12. 選擇Create database。

13. 點選Finish。

三．設定OracleSharding Manage和路由層。本例中在HostSDB1上設定。

1. 設定catalog database 環境變數並啟動監聽。

export ORACLE_BASE=/u01/app/oracle

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

export ORACLE_SID=shard

export PATH=$ORACLE_HOME/bin:$PATH

lsnrctl start

2. 賦角色和許可權。GSMCATUSER是12c內建的一個使用者，shard director 用這個使用者連到catalog database。

sqlplus / as sysdba

alter user gsmcatuser account unlock;

alter user gsmcatuser identified by oracle;

create user mysdbadmin identified by oracle;

grant connect,create session,gsmadmin_role to mysdbadmin;

grant inherit privileges on user sys to GSMADMIN_INTERNAL;

exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracle');

3. 進入到GDSCTL命令列，建立shard catalog。注：GDSCTL是一個命令列工具，用於管理和配置Global Data Services framework。

export ORACLE_BASE=/u01/app/oracle

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

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

gdsctl

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

4. 建立並啟動shard director。並設定作業系統安全認證。

add gsm -gsm sharddirector1 -listener 1522 -pwd oracle -catalog 10.10.9.30:1521:shard -region region1

start gsm -gsm sharddirector1

add credential -credential region1_cred -osaccount oracle -ospassword oracle

exit

5. 連線到每一個Shard Hosts(本例中為HOST SDB2和HOST SDB3)，註冊Scheduler agents, 並建立好oradata和fast_recovery_area資料夾。

ssh oracle@sdb2

export ORACLE_BASE=/u01/app/oracle

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

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

schagent -start

schagent -status

echo oracle | schagent -registerdatabase 10.10.9.30 8080

mkdir /u01/app/oracle/oradata

mkdir /u01/app/oracle/fast_recovery_area

ssh oracle@sdb3

export ORACLE_BASE=/u01/app/oracle

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

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

schagent -start

schagent -status

echo oracle | schagent -registerdatabase 10.10.9.30 8080

mkdir /u01/app/oracle/oradata

mkdir /u01/app/oracle/fast_recovery_area

四．開始佈署SharedDatabase。本例將佈署System-ManagedSDB。

1. 準備。

export ORACLE_BASE=/u01/app/oracle

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

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

gdsctl

set gsm -gsm sharddirector1

connect mysdbadmin/oracle

add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1

create shard -shardgroup primary_shardgroup -destination sdb2 -credential region1_cred -sys_password oracle

create shard -shardgroup primary_shardgroup -destination sdb3 -credential region1_cred -sys_password oracle

config shard

2. 佈署。

deploy

TIPs:

deploy命令會呼叫遠端每一個節點上的dbca去靜默安裝sharded database。我們可以透過dbca的日誌檔案去監控安裝進度。

deploy 還會在catalogdatabase 上提交一些job來完成相關事務。我們可以查詢dba_scheduler_jobs 來監控進度。

另外，GSM日誌可以用於deploy過程的監控和診斷。GSM日誌的位置可以透過如下命令查到。

GDSCTL>status gsm

3. 驗證安裝是否成功。

Sharded Database (SDB) 安裝佈署到此完成。我們看到，SDB的安裝佈署非常容易，幾乎所有的管理配置都是透過GDSCTL的幾條簡單命令完成的。另外，Oracle Sharding還高度整合了Oracle Data Guard：如果你想佈署standby database，可以透過GDSCTL的一兩條命令來定義，Oracle Sharding 會自動幫你佈署好standbys。

建立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漂移到備庫上。）

<ORA-28040>所有節點上均修改該檔案 sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER =8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT =8

利用應用使用者登入，建立sharded table和duplicated table

[oracle12c@sdb1 ~]$ db_env

[oracle12c@sdb1 ~]$ sqlplus "/ as sysdba"

alter session enable shard ddl;

create user app_schema identified by oracle;

grant all privileges to app_schema;

grant gsmadmin_role to app_schema;

grant select_catalog_role to app_schema;

grant connect, resource to app_schema;

grant dba to app_schema;

grant execute on dbms_crypto to app_schema;

利用應用使用者登入，建立sharded table和duplicated table

conn app_schema/oracle

alter session enable shard ddl;

CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m extent management local segment space management auto );

CREATE TABLESPACE products_tsp datafile size 100m extent management local uniform size 1m;

-- Create sharded table family

CREATE SHARDED TABLE 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 TSP_SET_1

PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

CREATE SHARDED TABLE Orders

(

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

CREATE SEQUENCE Orders_Seq;

CREATE SHARDED TABLE 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);

-- duplicated table

CREATE DUPLICATED TABLE Products

(

ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

Name VARCHAR2(128),

DescrUri VARCHAR2(128),

LastPrice NUMBER(19,4)

) TABLESPACE products_tsp;

在shardcat檢查:

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

TABLESPACE_NAME MB

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

PRODUCTS_TSP 100

SYSAUX 690

SYSTEM 880

TSP_SET_1 100

UNDOTBS1 410

USERS 5

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

select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like '%SET%';

col TABLE_NAME for a20

col PARTITION_NAME for a20

col TABLESPACE_NAME for a20

/

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

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

CUSTOMERS CUSTOMERS_P1 TSP_SET_1

ORDERS CUSTOMERS_P1 TSP_SET_1

LINEITEMS CUSTOMERS_P1 TSP_SET_1

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

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

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]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.0.2 Beta on Mon May 9 23:52:06 2016

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

oracle18c shard技術分享-安裝部署

相關文章