签到成功

知道了

CNDBA社区CNDBA社区

Oracle 18c 必须掌握的新特性 Sharding系列(7) -- SDB的启用/关闭

2019-10-30 17:08 2299 0 原创 Oracle 18c
作者: dave

SDB由多个组件共同组成的,在启动SDB时,需要注意一下各组件的启动顺序。

1 启动Sharding

启动SDB包含以下步骤:

1)启用目录数据库和监听
2)启动GSM(Shard directors)
3)启动分片的数据库、监听、代理
4)启动全局服务(global service)
5)通过Service连接sharing

1.1.启动目录数据库和监听:

[dave@www.cndba.cn ~]# su - oracle
Last login: Mon Oct 15 11:39:04 CST 2018 on pts/0
[dave@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 15 11:41:35 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1660943808 bytes
Fixed Size            8658368 bytes
Variable Size         1056964608 bytes
Database Buffers      587202560 bytes
Redo Buffers            8118272 bytes
Database mounted.
Database opened.
SQL> !lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 15-OCT-2018 11:42:05

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/18.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/shardcatalog/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shardcatalog)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shardcatalog)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                15-OCT-2018 11:42:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shardcatalog/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shardcatalog)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

1.2. 启动GSM(Shard directors)

[dave@www.cndba.cn ~]$ gdsctl
GDSCTL: Version 18.0.0.0.0 - Production on Mon Oct 15 11:47:54 CST 2018

Copyright (c) 2011, 2018, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to SHARDDIRECTOR
GDSCTL> connect sdbadmin/oracle
Catalog connection is established
GDSCTL> start gsm
GSM is started successfully
GDSCTL> status gsm
Alias                     SHARDDIRECTOR
Version                   18.0.0.0.0
Start Date                15-OCT-2018 11:48:05
Trace Level               off
Listener Log File         /u01/app/oracle/diag/gsm/shardcatalog/sharddirector/alert/log.xml
Listener Trace File       /u01/app/oracle/diag/gsm/shardcatalog/sharddirector/trace/ora_4024_139695696046976.trc
Endpoint summary          (ADDRESS=(HOST=shardcatalog)(PORT=1571)(PROTOCOL=tcp))
GSMOCI Version            2.2.1
Mastership                Y
Connected to GDS catalog  Y
Process Id                4026
Number of reconnections   0
Pending tasks.     Total  0
Tasks in  process. Total  0
Regional Mastership       TRUE
Total messages published  0
Time Zone                 +08:00
Orphaned Buddy Regions:   
     None
GDS region                region1

1.3. 启动分片的数据库、监听、代理

可以通过config shard目录查看到当前Sharding环境中分片的信息:https://www.cndba.cn/cndba/dave/article/3764

https://www.cndba.cn/cndba/dave/article/3764

GDSCTL> config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh164               primary_grp         Ok        Deployed    region1   ONLINE       
sh165               primary_grp         Ok        Deployed    region1   ONLINE       
sh166               primary_grp         Ok        Deployed    region1   ONLINE

在Sharding部署的时候,每次添加删除,分片数据库的实例名都会发生变化,所以如果没有修改对应环境变量时,这里必须先指定ORACLE_SID,然后再进行操作。 https://www.cndba.cn/cndba/dave/article/3764

[dave@www.cndba.cn ~]$ export ORACLE_SID=sh164
[dave@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 15 11:50:38 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1660943808 bytes
Fixed Size            8658368 bytes
Variable Size         1056964608 bytes
Database Buffers      587202560 bytes
Redo Buffers            8118272 bytes
Database mounted.
Database opened.
SQL> !lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 15-OCT-2018 11:51:20

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/18.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                15-OCT-2018 11:51:21
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
The listener supports no services
The command completed successfully

SQL> !schagent -start
Scheduler agent started using port 14565

SQL> !schagent -status
Agent running with PID 4060

Agent_version:18.1.0.0
Running_time:00:00:07
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/18.3.0/dbhome_1
ORACLE_BASE:/u01/app/oracle
Port:14565
Host:shard1

至此分片以上的操作已经完成,如果有多个分片,在每个分片上重复该操作即可。https://www.cndba.cn/cndba/dave/article/3764

https://www.cndba.cn/cndba/dave/article/3764

1.4. 启动全局服务

检查service状态:https://www.cndba.cn/cndba/dave/article/3764

GDSCTL> status service
Service "oltp_rw_servcie.cust_sdb.oradbcloud" has 3 instance(s). Affinity: ANYWHERE
   Instance "cust_sdb%1", name: "sh164", db: "sh164", region: "region1", status: ready.
   Instance "cust_sdb%11", name: "sh165", db: "sh165", region: "region1", status: ready.
   Instance "cust_sdb%21", name: "sh166", db: "sh166", region: "region1", status: ready.

GDSCTL> config service

Name           Network name                  Pool           Started Preferred all 
----           ------------                  ----           ------- ------------- 
oltp_rw_servci oltp_rw_servcie.cust_sdb.orad cust_sdb       Yes     Yes           
e              bcloud

如果没有启用使用start service启动:https://www.cndba.cn/cndba/dave/article/3764

https://www.cndba.cn/cndba/dave/article/3764

GDSCTL> start service -service oltp_rw_servicehttps://www.cndba.cn/cndba/dave/article/3764

1.5. 通过Service连接sharding

[dave@www.cndba.cn admin]$ sqlplus shard/oracle@sharding

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 15 23:02:27 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Mon Oct 15 2018 22:00:52 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> set lines 120
SQL> col name for a20
SQL> show parameter name

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
_cloud_name                 string                   oradbcloud
_dbpool_name                 string                   cust_sdb
_gwm_db_unique_name             string                   sh164
_region_name                 string                   region1
_shardgroup_name             string                   primary_grp
_shardspace_name             string                   shardspaceora
cdb_cluster_name             string
cell_offloadgroup_name             string
db_file_name_convert             string                   *, /u01/app/oracle/oradata/SH164/datafile/
db_name                  string                   sh164
db_unique_name                 string                   sh164
global_names                 boolean                   FALSE
instance_name                 string                   sh164
lock_name_space              string
log_file_name_convert             string
pdb_file_name_convert             string
processor_group_name             string
service_names                 string                   sh164

2 关闭Sharding

关闭顺序和打开顺序相反:

https://www.cndba.cn/cndba/dave/article/3764

1)关闭连接池和客户端
2)关闭全局服务
3)关闭分片的数据库和监听
4)关闭GSM
5)关闭目录数据库和监听

2.1.关闭服务:

GDSCTL> config service        
Name           Network name                  Pool           Started Preferred all 
----           ------------                  ----           ------- ------------- 
oltp_rw_servci oltp_rw_servcie.cust_sdb.orad cust_sdb       Yes     Yes           
e              bcloud  
GDSCTL> stop service -gdspool cust_sdb -service oltp_rw_servcie
The operation completed successfully

2.2.在每个分片关闭数据库、监听、代理

在每个分片上执行,这里我们演示shard1上的操作:

[dave@www.cndba.cn ~]$ lsnrctl stop

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 15-OCT-2018 23:35:39

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[dave@www.cndba.cn ~]$ schagent -stop
Agent running with PID 16249

Done stopping all running jobs
Terminating agent gracefully

[dave@www.cndba.cn ~]$ ps -ef|grep smon
oracle    3720     1  0 11:50 ?        00:00:00 ora_smon_sh164
oracle   16369 10144  0 23:37 pts/0    00:00:00 grep --color=auto smon
[dave@www.cndba.cn ~]$ export ORACLE_SID=sh164
[dave@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 15 23:37:51 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

2.3. 关闭GSM

GDSCTL> config gsm
Name      Region    ENDPOINT                                                    
----      ------    --------                                                    
sharddire region1   (ADDRESS=(HOST=shardcatalog)(PORT=1571)(PROTOCOL=tcp))      
ctor                                                                            

GDSCTL> stop gsm -gsm sharddirector
GSM is stopped successfully
GDSCTL> config gsm
Name      Region    ENDPOINT                                                    
----      ------    --------                                                    
sharddire region1   (ADDRESS=(HOST=shardcatalog)(PORT=1571)(PROTOCOL=tcp))      
ctor                                                                            

GDSCTL> config gsm -gsm sharddirector
Name: sharddirector
Endpoint 1: (ADDRESS=(HOST=shardcatalog)(PORT=1571)(PROTOCOL=tcp))
Local ONS port: 6123
Remote ONS port: 6234
ORACLE_HOME path: /u01/app/oracle/product/18.3.0/gsm
GSM Host name: shardcatalog
Region: region1

Buddy
------------------------

GDSCTL> status gsm
GSM-45075: No response from GSM

4. 关闭目录数据库和监听
[dave@www.cndba.cn admin]$ lsnrctl stop

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 15-OCT-2018 23:42:33

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shardcatalog)(PORT=1521)))
The command completed successfully
[dave@www.cndba.cn admin]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 15 23:42:36 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3 GSM 的启停说明

本章开始的时候提到,Oracle Sharding 的GSM是客户端和SDB之间的监听,维护SDB环境中的拓扑图(记录了所有分片和它们之间的关系),然后会根据客户端请求传递过来的分片键,将连接路由到相应的分片上进行查询。其本质上就是一个监听程序,默认的端口是1522.
GSM 的状态可以通过gdsctl工具查看,也可以通过lsnrctl来查看:

GDSCTL> status gsm
Alias                     SHARDDIRECTOR
Version                   18.0.0.0.0
Start Date                21-OCT-2018 14:11:48
Trace Level               off
Listener Log File         /u01/app/oracle/diag/gsm/sdb/sharddirector/alert/log.xml
Listener Trace File       /u01/app/oracle/diag/gsm/sdb/sharddirector/trace/ora_986_139632118659968.trc
Endpoint summary          (ADDRESS=(HOST=sdb)(PORT=1522)(PROTOCOL=TCP))
GSMOCI Version            2.2.1
Mastership                Y
Connected to GDS catalog  Y
Process Id                1019
Number of reconnections   0
Pending tasks.     Total  0
Tasks in  process. Total  0
Regional Mastership       TRUE
Total messages published  7
Time Zone                 +08:00
Orphaned Buddy Regions:   
     None
GDS region                region1

GDSCTL>

[dave@www.cndba.cn admin]$ lsnrctl status sharddirector
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2018 14:12:11

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(HOST=sdb)(PORT=1522)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=GDS$CATALOG.oradbcloud)))
STATUS of the LISTENER
------------------------
Alias                     SHARDDIRECTOR
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                21-OCT-2018 14:11:50
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.3.0/db_1/network/admin/gsm.ora
Listener Log File         /u01/app/oracle/diag/gsm/sdb/sharddirector/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb)(PORT=1522)))
Services Summary...
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
  Instance "shardcat", status READY, has 1 handler(s) for this service...
Service "_MONITOR" has 1 instance(s).
  Instance "SHARDDIRECTOR", status READY, has 1 handler(s) for this service...
Service "_PINGER" has 1 instance(s).
  Instance "SHARDDIRECTOR", status READY, has 1 handler(s) for this service...
Service "oltp_rw_servcie.cust_sdb.oradbcloud" has 3 instance(s).
  Instance "cust_sdb%1", status READY, has 1 handler(s) for this service...
  Instance "cust_sdb%11", status READY, has 1 handler(s) for this service...
  Instance "cust_sdb%21", status READY, has 1 handler(s) for this service...
The command completed successfully
[dave@www.cndba.cn admin]$

关闭GSM 可以使用2种方法:

[dave@www.cndba.cn admin]$ lsnrctl stop sharddirector

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2018 14:20:14

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(HOST=sdb)(PORT=1522)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=GDS$CATALOG.oradbcloud)))
The command completed successfully
或者使用gdsctl工具:
GDSCTL> stop gsm
GSM is stopped successfully
GDSCTL>

[dave@www.cndba.cn admin]$  lsnrctl status sharddirector

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2018 14:16:46

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(HOST=sdb)(PORT=1522)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=GDS$CATALOG.oradbcloud)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[dave@www.cndba.cn admin]$

但启动GSM 只能通过gdsctl来操作,如果使用lsnrctl则报错:
[dave@www.cndba.cn admin]$  lsnrctl start sharddirector

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2018 14:18:00

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/sdb/sharddirector/alert/log.xml
TNS-01151: Missing listener name, sharddirector, in LISTENER.ORA

Listener failed to start. See the error message(s) above...

GDSCTL> start gsm
GSM is started successfully

[dave@www.cndba.cn admin]$ lsnrctl status sharddirector

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2018 14:18:44

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(HOST=sdb)(PORT=1522)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=GDS$CATALOG.oradbcloud)))
STATUS of the LISTENER
------------------------
Alias                     SHARDDIRECTOR
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                21-OCT-2018 14:18:17
Uptime                    0 days 0 hr. 0 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.3.0/db_1/network/admin/gsm.ora
Listener Log File         /u01/app/oracle/diag/gsm/sdb/sharddirector/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sdb)(PORT=1522)))
Services Summary...
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
  Instance "shardcat", status READY, has 1 handler(s) for this service...
Service "_MONITOR" has 1 instance(s).
  Instance "SHARDDIRECTOR", status READY, has 1 handler(s) for this service...
Service "_PINGER" has 1 instance(s).
  Instance "SHARDDIRECTOR", status READY, has 1 handler(s) for this service...
Service "oltp_rw_servcie.cust_sdb.oradbcloud" has 3 instance(s).
  Instance "cust_sdb%1", status READY, has 1 handler(s) for this service...
  Instance "cust_sdb%11", status READY, has 1 handler(s) for this service...
  Instance "cust_sdb%21", status READY, has 1 handler(s) for this service...
The command completed successfully
[dave@www.cndba.cn admin]$

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2239
    原创
  • 3
    翻译
  • 547
    转载
  • 186
    评论
  • 访问:6666353次
  • 积分:4249
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ