IT博客汇 | 创建Oracle sharding database

创建Oracle sharding database

小荷发表于 2016-05-13 10:11:09

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

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

（四）建立应用用户，创建应用用户建立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