创建Oracle sharding database

本文继『Oracle sharding database的一些概念』后,介绍如下搭建一个oracle sharding database的环境,以及可能在搭建过程中可能会遇到的known issue(有很多坑,且在mos上还没有解决方案,都是一个一个自己摸索解决的。)。

你在本文中可以看到:
(一)安装介质需求。
(二)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="oracle12c"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 14499
ORA-06512: at line 1
 
GDSCTL>
GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability
----                -----------         ------    -----       ------    ------------
sh1                 shgrp1              U         Created     region1   -           
sh2                 shgrp2              U         Created     region2   -           
 
 
解决方法:重建shard
GDSCTL>remove shard -shardgroup shgrp2 -force
GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability
----                -----------         ------    -----       ------    ------------
sh1                 shgrp1              U         Created     region1   -           
 
GDSCTL>
GDSCTL>
GDSCTL>
GDSCTL>
GDSCTL>
GDSCTL>create shard -shardgroup shgrp2 -destination sdb3 -credential oracle_cred
DB Unique Name: sh3
GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability
----                -----------         ------    -----       ------    ------------
sh1                 shgrp1              U         Created     region1   -           
sh3                 shgrp2              U         none        region2   -           
 
GDSCTL>deploy
 GDSCTL>
GDSCTL>
GDSCTL>
GDSCTL>
GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability
----                -----------         ------    -----       ------    ------------
sh1                 shgrp1              Ok        Replicated  region1   -           
sh3                 shgrp2              Ok        Replicated  region2   -           
 
GDSCTL>

Known Issue(3)NET-40002: GSM endpoint not found in GSM.ora

现象:
GDSCTL>databases
GSM-45054: GSM error
NET-40002: GSM endpoint not found in GSM.ora
GDSCTL>status database
GSM-45054: GSM error
NET-40002: GSM endpoint not found in GSM.ora
GDSCTL>
 
解决方式:指定gsm名登录gdsctl。
[oracle12c@sdb1 ~]$ gdsctl gsm1
GDSCTL: Version 12.2.0.0.0 - Beta on Sat Dec 12 19:31:12 CST 2015
 
Copyright (c) 2011, 2015, Oracle.  All rights reserved.
 
Welcome to GDSCTL, type "help" for information.
 
GDSCTL>databases
Database: "sh1" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: region1
Database: "sh3" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: region2
 
GDSCTL>exit


Known Issue(4)ORA-02511: SQL query not allowed; the shard DDL is disabled.

现象:
-- on shard cat
[oracle12c@sdb1 ~]$ db_env
[oracle12c@sdb1 ~]$
[oracle12c@sdb1 ~]$
[oracle12c@sdb1 ~]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 12.2.0.0.0 Beta on Mon Feb 15 13:44:26 2016
 
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL>
SQL> CREATE TABLESPACE SET ts1 IN SHARDSPACE shardspaceora
  2  using template
  3  (datafile size 10m
  4  extent management local uniform size 256k
  5  segment space management auto
  6  online
  7  )
  8  /
CREATE TABLESPACE SET ts1 IN SHARDSPACE shardspaceora
*
ERROR at line 1:
ORA-02511: SQL query not allowed; the shard DDL is disabled.
 
 
解决方式:
SQL> alter session enable shard ddl;
 
Session altered.
 
SQL> CREATE TABLESPACE SET ts1 IN SHARDSPACE shardspaceora
  2  using template
  3  (datafile size 10m
  4  extent management local uniform size 256k
  5  segment space management auto
  6  online
  7  )
  8  /
 
Tablespace created.
 
SQL>

Known Issue(5)Linux Error: 1: Operation not permitted

现象:deploy的时候,通过agent调用netca的时候报错,无法建立listener,报错Linux Error: 1: Operation not permitted
GDSCTL>deploy
GSM Errors:
CATALOG:ORA-45575: Deployment has terminated due to previous errors.
CATALOG:ORA-02610: Remote job failed with error:
EXTERNAL_LOG_ID="job_22857_8",
USERNAME="oracle12c"
For more details:
  select destination, output from all_scheduler_job_run_details
  where job_name='SHARD_SH1_NETCA'
CATALOG:ORA-02610: Remote job failed with error:
EXTERNAL_LOG_ID="job_22869_8",
USERNAME="oracle12c"
For more details:
  select destination, output from all_scheduler_job_run_details
  where job_name='SHARD_SH3_NETCA'
 
GDSCTL>
 
 
SQL> col OUTPUT for a60
SQL> /
 
DESTINATIO OUTPUT
---------- ------------------------------------------------------------
SDB2
           Parsing command line arguments:
               Parameter "silent" = true
               Parameter "responsefile" = /u01/ora12c/app/oracle/produc
           t/12.2.0/db_1/shard_sh1_netca.rsp
           Done parsing command line arguments.
           Oracle Net Services Configuration:
           Configuring Listener:LISTENER_sh1
           Listener configuration complete.
           Oracle Net Listener Startup:
               Running Listener Control:
                 /u01/ora12c/app/oracle/product/12.2.0/db_1/bin/lsnrctl
            start LISTENER_sh1
               Listener Control complete.
               Listener start failed.
           Profile configuration complete.
           Check the trace file for details: /u01/ora12c/app/oracle/cfg
           toollogs/netca/trace_OraDB12Home1-16050310PM5414.log
           Oracle Net Services configuration failed.  The exit code is
           1
 
 
SQL>           
SQL>
SQL>
SQL>
SQL> select destination, output from all_scheduler_job_run_details
  2    where job_name='SHARD_SH3_NETCA'
  3  /
 
DESTINATIO OUTPUT
---------- ------------------------------------------------------------
SDB4
           Parsing command line arguments:
               Parameter "silent" = true
               Parameter "responsefile" = /u01/ora12c/app/oracle/produc
           t/12.2.0/db_1/shard_sh3_netca.rsp
           Done parsing command line arguments.
           Oracle Net Services Configuration:
           Configuring Listener:LISTENER_sh3
           Listener configuration complete.
           Oracle Net Listener Startup:
               Running Listener Control:
                 /u01/ora12c/app/oracle/product/12.2.0/db_1/bin/lsnrctl
            start LISTENER_sh3
               Listener Control complete.
               Listener start failed.
           Profile configuration complete.
           Check the trace file for details: /u01/ora12c/app/oracle/cfg
           toollogs/netca/trace_OraDB12Home1-1605042PM0921.log
           Oracle Net Services configuration failed.  The exit code is
           1
 
 
SQL>
SQL>
 
 
[oracle12c@sdb2 ~]$ lsnrctl start LISTENER_sh1
 
LSNRCTL for Linux: Version 12.2.0.0.0 - Beta on 03-MAY-2016 23:04:02
 
Copyright (c) 1991, 2015, Oracle.  All rights reserved.
 
Starting /u01/ora12c/app/oracle/product/12.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 12.2.0.0.0 - Beta
System parameter file is /u01/ora12c/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/ora12c/app/oracle/diag/tnslsnr/sdb2/listener_sh1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb2)(PORT=1521)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12555: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00525: Insufficient privilege for operation
   Linux Error: 1: Operation not permitted
 
Listener failed to start. See the error message(s) above...
 
[oracle12c@sdb2 ~]$
 
解决方法:删除/var/tmp/.oracle下的文件
[root@sdb2 ~]# cd /var/tmp/.oracle
[root@sdb2 .oracle]# ls
s#16010.1  s#1923.2  s#1949.1  s#1989.2   s#2047.1  s#2088.2  s#2212.1  s#2417.2  s#2494.1  s#2641.2  s#3130.1  s#8114.2
s#16010.2  s#1924.1  s#1949.2  s#1991.1   s#2047.2  s#2102.1  s#2212.2  s#2434.1  s#2494.2  s#2667.1  s#3130.2  s#9056.1
s#1886.1   s#1924.2  s#1955.1  s#1991.2   s#2047.3  s#2102.2  s#2274.1  s#2434.2  s#2503.1  s#2667.2  s#3249.1  s#9056.2
s#1886.2   s#1931.1  s#1955.2  s#19963.1  s#2047.4  s#2108.1  s#2274.2  s#2435.1  s#2503.2  s#2708.1  s#3249.2  sEXTPROC1258
s#1902.1   s#1931.2  s#1958.1  s#19963.2  s#2049.1  s#2108.2  s#2307.1  s#2435.2  s#2547.1  s#2708.2  s#3289.1  sEXTPROC1521
s#1902.2   s#1934.1  s#1958.2  s#1999.1   s#2049.2  s#2126.1  s#2307.2  s#2441.1  s#2547.2  s#2771.1  s#3289.2
s#1906.1   s#1934.2  s#1961.1  s#1999.2   s#2052.1  s#2126.2  s#2333.1  s#2441.2  s#2574.1  s#2771.2  s#3491.1
s#1906.2   s#1938.1  s#1961.2  s#2020.1   s#2052.2  s#2128.1  s#2333.2  s#2452.1  s#2574.2  s#2836.1  s#3491.2
s#1909.1   s#1938.2  s#1964.1  s#2020.2   s#2056.1  s#2128.2  s#2339.1  s#2452.2  s#2591.1  s#2836.2  s#3643.1
s#1909.2   s#1939.1  s#1964.2  s#2030.1   s#2056.2  s#2130.1  s#2339.2  s#2471.1  s#2591.2  s#2849.1  s#3643.2
s#1909.3   s#1939.2  s#1966.1  s#2030.2   s#2067.1  s#2130.2  s#2356.1  s#2471.2  s#2591.3  s#2849.2  s#3980.1
s#1909.4   s#1942.1  s#1966.2  s#2034.1   s#2067.2  s#2133.1  s#2356.2  s#2477.1  s#2591.4  s#3018.1  s#3980.2
s#1912.1   s#1942.2  s#1982.1  s#2034.2   s#2083.1  s#2133.2  s#2383.3  s#2477.2  s#2607.1  s#3018.2  s#7211.1
s#1912.2   s#1945.1  s#1982.2  s#2036.1   s#2083.2  s#2190.1  s#2383.4  s#2483.1  s#2607.2  s#3079.1  s#7211.2
s#1923.1   s#1945.2  s#1989.1  s#2036.2   s#2088.1  s#2190.2  s#2417.1  s#2483.2  s#2641.1  s#3079.2  s#8114.1
[root@sdb2 .oracle]# rm -rf *
[root@sdb2 .oracle]#
 
[oracle12c@sdb2 admin]$ lsnrctl start LISTENER_SH1
 
LSNRCTL for Linux: Version 12.2.0.0.0 - Beta on 03-MAY-2016 23:19:18
 
Copyright (c) 1991, 2015, Oracle.  All rights reserved.
 
Starting /u01/ora12c/app/oracle/product/12.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 12.2.0.0.0 - Beta
System parameter file is /u01/ora12c/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/ora12c/app/oracle/diag/tnslsnr/sdb2/listener_sh1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sdb2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SH1
Version                   TNSLSNR for Linux: Version 12.2.0.0.0 - Beta
Start Date                03-MAY-2016 23:19:18
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/ora12c/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/ora12c/app/oracle/diag/tnslsnr/sdb2/listener_sh1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle12c@sdb2 admin]$ 
[oracle12c@sdb2 admin]$
[oracle12c@sdb2 admin]$ lsnrctl stop LISTENER_SH1
 
LSNRCTL for Linux: Version 12.2.0.0.0 - Beta on 03-MAY-2016 23:21:33
 
Copyright (c) 1991, 2015, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sdb2)(PORT=1521)))
The command completed successfully
[oracle12c@sdb2 admin]$

Known Issue(6)Listener “LISTENER_SH1″ already exists

现象:
GDSCTL>deploy
GSM Errors:
CATALOG:ORA-45575: Deployment has terminated due to previous errors.
CATALOG:ORA-02610: Remote job failed with error:
EXTERNAL_LOG_ID="job_22857_11",
USERNAME="oracle12c"
For more details:
  select destination, output from all_scheduler_job_run_details
  where job_name='SHARD_SH1_NETCA'
CATALOG:ORA-02610: Remote job failed with error:
EXTERNAL_LOG_ID="job_22869_11",
USERNAME="oracle12c"
For more details:
  select destination, output from all_scheduler_job_run_details
  where job_name='SHARD_SH3_NETCA'
 
GDSCTL>
 
 
SDB2       Parsing command line arguments:
               Parameter "silent" = true
               Parameter "responsefile" = /u01/ora12c/app/oracle/produc
           t/12.2.0/db_1/shard_sh1_netca.rsp
           Done parsing command line arguments.
           Oracle Net Services Configuration:
           Listener "LISTENER_SH1" already exists.
           Profile configuration complete.
           Check the trace file for details: /u01/ora12c/app/oracle/cfg
           toollogs/netca/trace_OraDB12Home1-16050311PM2533.log
           Oracle Net Services configuration failed.  The exit code is
           1
 
 
SQL> 
 
解决方法,删除已经存在的listener.ora文件


Known Issue(7)ERROR: Insecure database cannot be registered

现象:
[oracle12c@sdb2 ~]$ echo oracleagent|schagent -registerdatabase sdb1 8080
Agent Registration Password ? 
ERROR: Insecure database cannot be registered.http://sdb1:8080/remote_scheduler_agent/register_agent
 
[oracle12c@sdb2 ~]$
 
解决:在shardcat上,执行DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS
SQL> !hostname
sdb1
 
SQL>
SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracleagent'); 
 
PL/SQL procedure successfully completed.

Known Issue(8)BEGIN dbms_gsm_fixed.validateParameters(0); END;

现象:
GDSCTL>deploy
        GSM Errors:
CATALOG:ORA-02610: Remote job failed with error:
For more details, check the contents of $ORACLE_BASE/cfgtoollogs/dbca/sh1/customScripts.log on the destination host.
CATALOG:ORA-02610: Remote job failed with error:
For more details, check the contents of $ORACLE_BASE/cfgtoollogs/dbca/sh3/customScripts.log on the destination host.
CATALOG:ORA-45575: Deployment has terminated due to previous errors.
 
GDSCTL>
[oracle12c@sdb2 ~]$ cat  $ORACLE_BASE/cfgtoollogs/dbca/sh1/customScripts.log
BEGIN CUSTOM SCRIPT
DBID=707889309,
BEGIN dbms_gsm_fixed.validateParameters(0); END;
 
                     *
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00302: component 'VALIDATEPARAMETERS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
 
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1627389952 bytes
Fixed Size                  4411288 bytes
Variable Size            1040187496 bytes
Database Buffers          570425344 bytes
Redo Buffers               12365824 bytes
Database mounted.
END CUSTOM SCRIPT
[oracle12c@sdb2 ~]$
 
解决方法:shardcat数据库和shard node数据库软件版本要一致,不能shardcat用12.2 beta2,而shard node上用12.2 beta1

(六)sharded table的一些测试,以及发现其对dml的一些限制:
(1)当down掉一个shard node的时候进行查询sharded table的时候报错:

ORA-02519: cannot perform cross-shard operation. Chunk "7" is unavailable
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 16487
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 16464
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 16503
ORA-06512: at line 1
 
但是duplicated table可以,不报错。

(2)sharded table不允许insert as select,也不允许PL/SQL function,所以加载数据有点麻烦:

SQL> insert into customers
  2  select rownum,dbms_random.STRING('U',2),dbms_random.STRING('l',4),dbms_random.STRING('U',10),
  3  dbms_random.STRING('U',8),dbms_random.STRING('A',200),dbms_random.STRING('A',20) from dual  connect by level
<=1000000;
 
insert into customers
select rownum,dbms_random.STRING('U',2),dbms_random.STRING('l',4),dbms_random.STRING('U',10),
dbms_random.STRING('U',8),dbms_random.STRING('A',200),dbms_random.STRING('A',20) from dual  connect by level<=1000000
 
ORA-02670: unsupported SQL construct: Insert As Select on Sharded table
 
SQL> 
SQL> begin
  2    for k in 1 .. 10 loop
  3      insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd)
  4      values
  5        (round(dbms_random.value(1, 10), 0),
  6         dbms_random.STRING('U', 2),
  7         dbms_random.STRING('l', 4),
  8         dbms_random.STRING('U', 10),
  9         dbms_random.STRING('U', 8),
 10         dbms_random.STRING('A', 200),
 11         dbms_random.STRING('A', 20));
 12    end loop;
 13    commit;
 14  end;
 15  /
 
begin
  for k in 1 .. 10000 loop
    insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd)
    values
      (round(dbms_random.value(1, 10), 0),
       dbms_random.STRING('U', 2),
       dbms_random.STRING('l', 4),
       dbms_random.STRING('U', 10),
       dbms_random.STRING('U', 8),
       dbms_random.STRING('A', 200),
       dbms_random.STRING('A', 20));
  end loop;
  commit;
end;
 
ORA-02670: unsupported SQL construct: PL/SQL function
ORA-06512: at line 4
 
SQL>
 
最后我用一个很土鳖的方式加载了数据:
SQL> begin
  2    for k in 1 .. 1000 loop
  3      insert into customers(custid,firstname,lastname,class,geo,custprofile,passwd)
  4      values
  5        (k, 'HE', 'Jimmy', 'A', 'CHINA', 'DBA', '123456');
  6    end loop;
  7    commit;
  8  end;
  9  /
 
PL/SQL procedure successfully completed
 
SQL>

但是如果是对duplicated table,这不存在上述2种的限制:

SQL> insert into products
  2  select rownum,dbms_random.STRING('U',8),dbms_random.STRING('A',64),round(dbms_random.value(1,1000),2) from dual
  3  connect by level
<=1000;
 
1000 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL>

(3)sharded table不允许跨shard做delete:

SQL> delete from customers;
 
delete from customers
 
ORA-02671: DML are not allowed on more than one shard
 
SQL>
 
我后来是很土鳖的到一个一个shard node上去删除的。

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569