[20231024]共享伺服器的問題3.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2991962/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- [20231023]共享伺服器的問題2.txt伺服器
- [20181124]關於降序索引問題3.txt索引
- session共享問題？？？Session
- [20190110]rlwrap sqlplus tee相關問題3.txtSQL
- [20210407]分析sql語句的共享記憶體段3.txtSQL記憶體
- [20231024]NULL值在索引的情況.txtNull索引
- [20190423]那個更快的疑問3.txt
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20210205]toad檢視真實執行計劃問題3.txt
- [20191210]降序索引疑問3.txt索引
- vue-router元件複用共享$route的問題Vue元件
- 伺服器常見的問題伺服器
- Ubuntu共享資料夾訪問許可權問題Ubuntu訪問許可權
- 分析伺服器延遲的問題伺服器
- 一個伺服器部署的問題伺服器
- 解決Mac無法共享網路問題Mac
- nfs伺服器搭建和遇到的問題NFS伺服器
- 遊戲伺服器存在的主要問題遊戲伺服器
- 伺服器問題 排查思路伺服器
- [20220413]shared pool latch與使用sga heap的疑問3.txt
- 華為雲伺服器配置遇到的問題伺服器
- php CURL 伺服器響應慢的問題PHP伺服器
- MQTT伺服器連線不上的問題MQQT伺服器
- 伺服器常見的四大問題伺服器
- Oracle共享伺服器的連線模式Oracle伺服器模式
- tomcat伺服器快取問題Tomcat伺服器快取
- nginx伺服器配置問題心得Nginx伺服器
- Chrome89針對sessionStorage的更新導致資料共享問題ChromeSession
- Debian 12 + KDE 螢幕共享失敗問題解決
- Windows共享資料夾常見問題解決方法Windows
- go語言採坑：閉包共享變數問題Go變數
- 代理伺服器的連線問題及解決伺服器
- 解決伺服器被黑上不了網的問題伺服器
- linux伺服器環境搭建問題Linux伺服器
- 客戶端(windows)訪問FTP伺服器遇到的問題（總結）客戶端WindowsFTP伺服器
- [20191220]關於共享記憶體段相關問題.txt記憶體
- Java多執行緒開發|volatile與偽共享問題Java執行緒