ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20231024]共享服务器的问题3.txt

[20231024]共享服务器的问题3.txt

原创 Oracle 作者：lfree 时间：2023-10-30 21:46:30 0 删除 编辑

[20231024]共享服务器的问题3.txt

--//测试遇到的问题，当设置dispatchers='(PROTOCOL=TCP)'时,lsnrctl status依旧显示bookXDB服务,验证重启应该看不到该服务。

1.环境：
SYS@book> @ ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter dispatchers
PARAMETER_NAME   TYPE      VALUE
---------------- --------- --------------------------------
dispatchers      string    (PROTOCOL=TCP) (SERVICE=bookXDB)
max_dispatchers  integer

SYS@book> show parameter service
PARAMETER_NAME TYPE    VALUE
-------------- ------- ---------------
service_names  string  BOOK, BOOKSHARE

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-OCT-2023 10:12:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-APR-2023 08:58:39
Uptime                    182 days 1 hr. 14 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
--//显示bookXDB服务.

2.开始测试:
SYS@book> alter system set dispatchers='(PROTOCOL=TCP)' scope=memory;
System altered.

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookXDB @spid<<<quit
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookxdb @spid<<<quit
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//但是不支持bookXDB服务名.

--//尝试重启监听看看。
$ lsnrctl stop ; lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-OCT-2023 10:36:15
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-OCT-2023 10:36:01
Uptime                    0 days 0 hr. 0 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 2 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 0 handler(s) for this service...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The command completed successfully
--//注册的服务名bookXDB是存在,但是无论怎么,都无法通过bookxdb连上服务,可以注意一个细节handler=0.前面是1,也就是没有获得句
--//柄.(我估计如果我重启数据库，这项内容应该不存在，补充测试看看!!)

3.继续测试：

SYS@book> alter system set dispatchers='(PROTOCOL=TCP)' scope=both;
System altered.

SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> alter system register;
System altered.

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-OCT-2023 08:41:50
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-OCT-2023 10:37:31
Uptime                    6 days 22 hr. 4 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 2 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 2 handler(s) for this service...
The command completed successfully
--//没有bookXDB服务了.

$ lsnrctl stop ; lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-OCT-2023 08:43:35
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
The command completed successfully
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-OCT-2023 08:43:35
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-OCT-2023 08:43:35
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/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "Book" has 1 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SYS@book> alter system register;
System altered.

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-OCT-2023 08:44:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-OCT-2023 08:43:35
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/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 2 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 2 handler(s) for this service...
The command completed successfully

SYS@book> column NETWORK_NAME format a30
SYS@book> select * from V$SERVICES order by 1;
SERVICE_ID NAME            NAME_HASH NETWORK_NAME CREATION_DATE       CREATION_DATE_HASH GOAL         D AQ_ CLB_G
---------- -------------- ---------- ------------ ------------------- ------------------ ------------ - --- -----
         1 SYS$BACKGROUND  165959219              2013-08-24 11:37:40                  0 NONE         N NO  SHORT
         2 SYS$USERS      3427055676              2013-08-24 11:37:40                  0 NONE         N NO  SHORT
         4 BOOKSHARE      3041418781 BOOKSHARE    2023-10-18 08:49:31         1313067792 NONE         N NO  LONG
         6 book           1062280681 book         2015-11-24 09:11:13         3631320093 NONE         N NO  LONG

$ rlsql -s -l scott/book@192.168.100.78:1521/book <<<@spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       341          3 61174                    SHARED    61111       20          1 alter system kill session '341,3' immediate;
--//问题再现.

4.收尾:

SYS@book> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=bookXDB)' scope=both;
System altered.

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-OCT-2023 08:46:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-OCT-2023 08:43:35
Uptime                    0 days 0 hr. 2 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

$ rlsql -s -l scott/book@192.168.100.78:1521/book <<<@spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
         1          7 61182                    DEDICATED 61187       24          4 alter system kill session '1,7' immediate;

--//顺便提一下,要彻底关闭共享服务,可以设置shared_servers=0.

SYS@book> show parameter shared_servers
PARAMETER_NAME     TYPE    VALUE
------------------ ------- -----
max_shared_servers integer 1
shared_servers     integer 1

SYS@book> alter system set shared_servers=0;
System altered.

--//重启数据库:
$ rlsql -s -l scott/book@192.168.100.78:1521/book <<<@spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       392          7 61528                    DEDICATED 61533       23          4 alter system kill session '392,7' immediate;

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-OCT-2023 09:11:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-OCT-2023 08:43:35
Uptime                    0 days 0 hr. 27 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
--//没有bookXDB服务.

--//收尾:
SYS@book> alter system reset shared_servers;
System altered.


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2991962/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20231029]使用cygwin调式bash shell引出的问题.txt
下一篇： [20231023]为什么刷新缓存后输出记录顺序发生变化6.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4014
  • 访问量
    7991065

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593