[20190201]測試服務名支援靜態和動態註冊的情況下優先選擇那個.txt

--//測試服務名支援靜態和動態註冊的情況下優先選擇那個,測試看看.

1.環境:

SCOTT@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 service

NAME TYPE VALUE

------------- ------ ---------------

service_names string BOOK, BOOKSHARE

$ grep -v "^#" listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU=32767)

(GLOBAL_DBNAME = booK)

(ARGV0=myapp0)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)

(SID_NAME=book)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(ARGV0=LLLLLL)(HOST = 0.0.0.0)(PORT = 1521)(ARGV0=KKKKK))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SECURE_REGISTER_LISTENER = (TCP)

ADR_BASE_LISTENER = /u01/app/oracle

DIAG_ADR_ENABLED_LISTENER=OFF

USE_NS_PROBES_FOR_DCD=true

INBOUND_CONNECT_TIMEOUT_LISTENER=10

--//檢查監聽服務狀態:

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:33:53

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 01-FEB-2019 09:30:51

Uptime 0 days 0 hr. 3 min. 1 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 2 handler(s) for this service...

--//BOOK服務名支援靜態與動態註冊. UNKNOW表示使用靜態註冊.

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

The command completed successfully

$ tnsping book

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:41:27

Used parameter files:

/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = book)))

OK (0 msec)

2.測試一:

--//執行如下:

$ seq 150 | xargs -i{} echo "sqlplus -s -l scott/book@book <<< 'host sleep 20' &" | bash &

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:39:00

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "BOOKSHARE" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:302 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

"DEDICATED" established:302 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

The command completed successfully

--//再次執行:

$ seq 150 | xargs -i{} echo "sqlplus -s -l scott/book@book <<< 'host sleep 20' &" | bash &

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:42:09

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "BOOKSHARE" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:452 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

"DEDICATED" established:452 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

The command completed successfully

--//注意看下劃線,感覺oracle設計不好,無法區分那個服務名連線資料庫,服務名BOOKSHARE下也有.

3.測試二:

SYS@book> alter system set service_names=BOOK scope=memory;

System altered.

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:46:50

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

The command completed successfully

--//執行如下:

$ seq 140 | xargs -i{} echo "sqlplus -s -l scott/book@book <<< 'host sleep 20' &" | bash

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:48:56

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

"DEDICATED" established:140 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

The command completed successfully

--//注意看下劃線內容,說明使用動態註冊的服務.也就是優先使用動態註冊的服務名.再次執行1次:

$ seq 140 | xargs -i{} echo "sqlplus -s -l scott/book@book <<< 'host sleep 20' &" | bash

--//注:前面測試都在本機,這次換1臺遠端機器測試.

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:52:43

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

"DEDICATED" established:280 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

The command completed successfully

--//測試一樣,還可以看出這個記數是累計的.疑問如果遠端以sys使用者關閉資料庫,連線會斷開嗎?測試看看:

4.繼續測試:

d:\tools\sqltemp>sqlplus sys/oracle@78 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 1 09:54:56 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@78> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

274 5 7896:7780 DEDICATED 53901 21 3 alter system kill session '274,5' immediate;

--//服務端程式號=53901.

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:56:17

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

"DEDICATED" established:281 refused:0 state:ready

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 49011>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=52751))

The command completed successfully

SYS@78> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 09:57:23

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "booK" has 1 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LOCAL SERVER

The command completed successfully

$ ps -ef | grep 5390[1]

--//服務端程式號已經不存在.

--//自動使用靜態服務,對比前面靜態註冊的established:0.現在變成了established:1.

--//如果沒有靜態服務遠端的連線就斷開了.

SYS@78> 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@78> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

274 3 7896:7780 DEDICATED 53961 21 2 alter system kill session '274,3' immediate;

--//服務端程式號=53961,已經發生了變化.也就是重新連上資料庫.

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-FEB-2019 10:04:17

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))

Services Summary...

Service "BOOKSHARE" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "booK" has 2 instance(s).

Instance "book", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:3 refused:0

LOCAL SERVER

Instance "book", status READY, has 2 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 53851>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=30696))

Service "bookXDB" has 1 instance(s).

Instance "book", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: xxzzzdg4, pid: 53851>

(ADDRESS=(PROTOCOL=tcp)(HOST=xxzzzdg4.com)(PORT=30696))

The command completed successfully

4.重新測試程式號相關部分內容:

--//重啟資料庫,取消service_names=BOOKshare.

SYS@book> alter system set service_names=BOOK scope=memory;

System altered.

SYS@78> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- ------------------------------------------------

274 9 6732:5724 DEDICATED 54224 21 5 alter system kill session '274,9' immediate;

--//服務端程式號=54224.

$ ps -ef | grep 5422[4]

oracle 54224 1 0 10:21 ? 00:00:00 oraclebook (LOCAL=NO)

# lsof -i -n -P | egrep "^COMMAND|54224"

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

oracle 54224 oracle 13u IPv4 63126287 0t0 TCP 192.168.100.78:1521->192.168.98.6:55485 (ESTABLISHED)

--//192.168.98.6是我的客戶端地址.

SYS@78> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

$ ps -ef | grep 5422[4]

--//服務端程式號已經關閉.

# lsof -i -n -P | egrep "^COMMAND|192.168.98.6:"

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

sshd 48871 root 3r IPv4 63057705 0t0 TCP 192.168.100.78:22->192.168.98.6:52323 (ESTABLISHED)

sshd 54021 root 3r IPv4 63126212 0t0 TCP 192.168.100.78:22->192.168.98.6:55150 (ESTABLISHED)

oracle 54302 oracle 12u IPv4 63126300 0t0 TCP 192.168.100.78:1521->192.168.98.6:56266 (ESTABLISHED)

--//關閉資料庫後,新啟動一個程式重新連線伺服器.程式號=54302.

$ ps -ef | grep 5430[2]

oracle 54302 1 0 10:32 ? 00:00:00 myapp0 (DESCRIPTION=(LOCAL=NO)(SDU=32767))

--//我前面監聽配置修改引數名0.看前面listener.ora配置.

SYS@78> 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.

$ ps -ef | grep 5430[2]

--//程式號=54302已經不存在.

SYS@78> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

274 3 6732:5724 DEDICATED 54375 21 2 alter system kill session '274,3' immediate;

--//現在程式號是54375

# lsof -i -n -P | egrep "^COMMAND|192.168.98.6:"

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

sshd 48871 root 3r IPv4 63057705 0t0 TCP 192.168.100.78:22->192.168.98.6:52323 (ESTABLISHED)

sshd 54021 root 3r IPv4 63126212 0t0 TCP 192.168.100.78:22->192.168.98.6:55150 (ESTABLISHED)

oracle 54375 oracle 13u IPv4 63126333 0t0 TCP 192.168.100.78:1521->192.168.98.6:56568 (ESTABLISHED)

$ ps -ef | grep 5437[5]

oracle 54375 1 0 10:38 ? 00:00:00 myapp0 (DESCRIPTION=(LOCAL=NO)(SDU=32767))

--//從引數可以看出依舊使用靜態註冊監聽的服務名.

--//測試好亂,也許是快過節的原因.^_^

--//實際上說明一點: 服務名支援靜態和動態註冊的情況下優先選擇動態註冊的服務名.

--//我以前測試了服務名支援共享和專用模式的情況下,如果不明確連線模式.(也就是tns連線串不配置SERVER = DEDICATED或者SERVER =SHARED)

--//的情況下優先選擇共享模式.

--//我在連結http://blog.itpub.net/267265/viewspace-2558415/=>[20190115]共享服務模式與啟動到mount狀態.txt 裡面提到:

--//從這裡說明看出,從安全形度考慮,配置服務名也許最佳的方法就是不要每個服務名支援兩種連線模式.

--//靜態註冊使用服務名最好也與資料庫動態的服務名不同.共享模式的服務名也最好分開配置.

--//我曾經遇到的問題.連結如下:

http://blog.itpub.net/267265/viewspace-1797866/

http://blog.itpub.net/267265/viewspace-1443469/

--//第一次遇到我天真認為遠端啟動都存在這個問題,導致本地sys無法連線資料庫.實際上本質問題是監聽ORACLE_HOME環境變數裡面帶有/.

--//這樣遠端啟動資料庫,環境變數ORACLE_HOME結尾是帶/的.這樣本機配置的ORACLE_HOME環境變數不帶/的無法連線資料庫.

--//這個問題直到第2次出現我才定位問題.

--//實際上這個問題主要在我同事安裝配置oracle文件裡面監聽配置的ORACLE_HOME是帶/的,不統一.

--//另外關於服務名同事支援共享和專用模式,我在這個問題吃過許多苦頭,我們許多客戶端配置連線串不明確連線模式.

--//這也是我個人建議為共享模式單獨配置服務名.

--//順便說一下11.2.0.4.ORACLE_HOME環境變數帶斜線的問題已經不在存在.參考連線:

http://blog.itpub.net/267265/viewspace-2055052/

[20190201]測試服務名支援靜態和動態註冊的情況下優先選擇那個.txt

相關文章