[20211012]測試遠端監聽.txt

lfree發表於2021-10-12

[20211012]測試遠端監聽.txt

--//測試一下Remote Listener這種模式是否可行,實際上網路許多連結講不可行,我自己也在7月份看了許多連結,嘗試N久,不成功.
--//放假再次重複看了許多連結,上班再次測試終於成功!!沒有實際的意義,僅僅為了學習。
--//另外我記憶裡好像是一個安全缺陷,檢索許久也沒有找到相關連結。

1.測試環境。

--//資料庫在192.168.100.78 sid=book,監聽埠1521 版本11.2.0.4
--//監聽伺服器在192.168.100.33     ,監聽埠1521 版本10.2.0.4。
--//兩臺機器監聽同時啟動。

2.建立遠端監聽:
--//192.168.100.78上執行:
SCOTT@book> @ ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ hide  remote_listener
NAME            DESCRIPTION     DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
--------------- --------------- ------------- ------------- ------------ ----- ---------
remote_listener remote listener TRUE                                     FALSE IMMEDIATE

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

SYS@book> ALTER SYSTEM SET REMOTE_LISTENER="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.33)(PORT=1521)))" scope=memory;
System altered.

SYS@book> alter system register;
System altered.

3.在192.168.100.33觀察:
--//192.168.100.33上執行:
$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 12-OCT-2021 08:33:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.33)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                12-OCT-2021 08:30:51
Uptime                    0 days 0 hr. 2 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.33)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Instance "book", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "book" has 1 instance(s).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  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...
Service "iiiii" has 1 instance(s).
  Instance "iiiiidg", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
--//已經看到100.78的服務名.

$ lsnrctl service LISTENER
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 12-OCT-2021 08:34:54
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.33)(PORT=1521)))
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
         REMOTE SERVER
~~~~~~~~~~~~~~~~~~~~~~        
         (ADDRESS=(PROTOCOL=TCP)(HOST=ppppdg4)(PORT=1521))
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "book" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
~~~~~~~~~~~~~~~~~~~~~~~~        
         (ADDRESS=(PROTOCOL=TCP)(HOST=ppppdg4)(PORT=1521))
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: ppppdg4, pid: 47168>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ppppdg4.com)(PORT=57181))
Service "iiiii" has 1 instance(s).
  Instance "iiiiidg", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: iiiiidg, pid: 23483>
         (ADDRESS=(PROTOCOL=tcp)(HOST=iiiiidg)(PORT=19254))
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
--//也看到遠端的服務.

4.開始測試:
--//在其他客戶端執行:
d:\>sqlplus -s -l scott/book@192.168.100.33:1521/book @ ver1
ERROR:
ORA-12545: Connect failed because target host or object does not exist
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//不行.

--//192.168.100.78上執行:
SYS@book> alter system set local_listener="(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))" scope=memory;
System altered.

d:\>echo @ver | sqlplus -s -l scott/book@192.168.100.33:1521/book
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//OK,透過192.168.100.33的監聽伺服器連線了192.168.100.78的資料庫book.

d:\>d:\tools\linux\usr\local\wbin\echo -n "select * from v$instance\n@prxx" | sqlplus -s -l scott/book@192.168.100.33:1521/book
==============================
INSTANCE_NUMBER               : 1
INSTANCE_NAME                 : book
HOST_NAME                     : ppppdg4
VERSION                       : 11.2.0.4.0
STARTUP_TIME                  : 2021-10-12 08:32:18
STATUS                        : OPEN
PARALLEL                      : NO
THREAD#                       : 1
ARCHIVER                      : STARTED
LOG_SWITCH_WAIT               :
LOGINS                        : ALLOWED
SHUTDOWN_PENDING              : NO
DATABASE_STATUS               : ACTIVE
INSTANCE_ROLE                 : PRIMARY_INSTANCE
ACTIVE_STATE                  : NORMAL
BLOCKED                       : NO
PL/SQL procedure successfully completed.

d:\>d:\tools\linux\usr\local\wbin\echo -n "select * from v$instance\n@prxx" | sqlplus -s -l scott/btbtms@192.168.100.33:1521/test
==============================
INSTANCE_NUMBER               : 1
INSTANCE_NAME                 : test
HOST_NAME                     : iiiiidg
VERSION                       : 10.2.0.4.0
STARTUP_TIME                  : 2021-10-11 11:20:21
STATUS                        : OPEN
PARALLEL                      : NO
THREAD#                       : 1
ARCHIVER                      : STARTED
LOG_SWITCH_WAIT               :
LOGINS                        : ALLOWED
SHUTDOWN_PENDING              : NO
DATABASE_STATUS               : ACTIVE
INSTANCE_ROLE                 : PRIMARY_INSTANCE
ACTIVE_STATE                  : NORMAL
BLOCKED                       : NO
PL/SQL procedure successfully completed.

--//連上192.168.100.33的資料庫也是ok的.

5.實際上我個人的感覺與rac的方式類似.
--//在我結束測試時,我發現我在192.168.100.78的listener.ora檔案有一行.
#SECURE_REGISTER_LISTENER= (TCP)

--//我估計當時這個就是限制遠端註冊的問題,估計當時我做過類似測試然後註解了.時間太久,記不住了.
--//我檢索SECURE_REGISTER_LISTENER= (TCP),發現如下連結:
https://www.oracle.com/security-alerts/alert-cve-2012-1675.html
https://blog.csdn.net/brj880719/article/details/53158507

--//這樣如果在192.168.100.33的listener.ora檔案中上加入:
SECURE_REGISTER_LISTENER= (TCP)

--//就可以限制遠端註冊監聽.但我的測試無效,不知道為什麼?
--//我在192.168.100.78listener.ora檔案中上加入:
SECURE_REGISTER_LISTENER= (TCP)

--//並且兩邊監聽我都重啟了還是無效.

--//實際上沒有意思,因為如果在192.168.100.78上停止監聽,就無法連上資料庫了.因為還是要轉到本地監聽上來.
--//192.168.100.78上執行:
$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-OCT-2021 09:03:53
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

--//在其他客戶端執行:
d:\>echo select * from v$instance ; | sqlplus -s -l scott/book@192.168.100.33:1521/book
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//記錄一下tcpdump的情況:

--//192.168.100.33上執行:
# tcpdump -i eth0 host 192.168.98.6 and not port 22 -nnn -vvv
09:19:55.025273 IP (tos 0x0, ttl 198, id 28733, offset 0, flags [DF], proto: TCP (6), length: 60) 192.168.98.6.53911 > 192.168.100.33.1521: S, cksum 0x583a (correct), 3273750722:3273750722(0) win 8192 <mss 1460,nop,wscale 2,sackOK,timestamp 357344 0>
09:19:55.025316 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto: TCP (6), length: 60) 192.168.100.33.1521 > 192.168.98.6.53911: S, cksum 0xd45c (correct), 2905775146:2905775146(0) ack 3273750723 win 5792 <mss 1460,sackOK,timestamp 3900857159 357344,nop,wscale 7>
09:19:55.027141 IP (tos 0x0, ttl 198, id 28734, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.53911 > 192.168.100.33.1521: ., cksum 0xd8bc (correct), 1:1(0) ack 1 win 16652 <nop,nop,timestamp 357344 3900857159>
09:19:55.027802 IP (tos 0x0, ttl 198, id 28735, offset 0, flags [DF], proto: TCP (6), length: 330) 192.168.98.6.53911 > 192.168.100.33.1521: P 1:279(278) ack 1 win 16652 <nop,nop,timestamp 357344 3900857159>
09:19:55.027833 IP (tos 0x0, ttl  64, id 51042, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.33.1521 > 192.168.98.6.53911: ., cksum 0x187b (correct), 1:1(0) ack 279 win 54 <nop,nop,timestamp 3900857161 357344>
09:19:55.028046 IP (tos 0x0, ttl  64, id 51043, offset 0, flags [DF], proto: TCP (6), length: 62) 192.168.100.33.1521 > 192.168.98.6.53911: P, cksum 0x121f (correct), 1:11(10) ack 279 win 54 <nop,nop,timestamp 3900857162 357344>
09:19:55.028079 IP (tos 0x0, ttl  64, id 51044, offset 0, flags [DF], proto: TCP (6), length: 379) 192.168.100.33.1521 > 192.168.98.6.53911: P 11:338(327) ack 279 win 54 <nop,nop,timestamp 3900857162 357344>
09:19:55.028096 IP (tos 0x0, ttl  64, id 51045, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.33.1521 > 192.168.98.6.53911: F, cksum 0x1728 (correct), 338:338(0) ack 279 win 54 <nop,nop,timestamp 3900857162 357344>
09:19:55.029280 IP (tos 0x0, ttl 198, id 28737, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.53911 > 192.168.100.33.1521: ., cksum 0xd6a7 (correct), 279:279(0) ack 338 win 16567 <nop,nop,timestamp 357344 3900857162>
09:19:55.029339 IP (tos 0x0, ttl 198, id 28738, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.53911 > 192.168.100.33.1521: ., cksum 0xd6a6 (correct), 279:279(0) ack 339 win 16567 <nop,nop,timestamp 357344 3900857162>
09:19:55.029353 IP (tos 0x0, ttl 198, id 28739, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.53911 > 192.168.100.33.1521: F, cksum 0xd6a5 (correct), 279:279(0) ack 339 win 16567 <nop,nop,timestamp 357344 3900857162>
09:19:55.029366 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.33.1521 > 192.168.98.6.53911: ., cksum 0x1726 (correct), 339:339(0) ack 280 win 54 <nop,nop,timestamp 3900857163 357344>

# tcpdump -i eth0 host   192.168.98.6 and not port 22 and not port 1522 -nnn -vvv
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
09:19:55.022505 IP (tos 0x0, ttl 198, id 28741, offset 0, flags [DF], proto: TCP (6), length: 60) 192.168.98.6.53912 > 192.168.100.78.1521: S, cksum 0x2c50 (correct), 2881010663:2881010663(0) win 8192 <mss 1460,nop,wscale 2,sackOK,timestamp 357344 0>
09:19:55.022660 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto: TCP (6), length: 60) 192.168.100.78.1521 > 192.168.98.6.53912: S, cksum 0x47d4 (incorrect (-> 0xe013), 444229427:444229427(0) ack 2881010664 win 14480 <mss 1460,sackOK,timestamp 3872377624 357344,nop,wscale 7>
09:19:55.024420 IP (tos 0x0, ttl 198, id 28744, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.53912 > 192.168.100.78.1521: ., cksum 0x0663 (correct), 1:1(0) ack 1 win 16652 <nop,nop,timestamp 357345 3872377624>
09:19:55.024522 IP (tos 0x0, ttl 198, id 28745, offset 0, flags [DF], proto: TCP (6), length: 122) 192.168.98.6.53912 > 192.168.100.78.1521: P 1:71(70) ack 1 win 16652 <nop,nop,timestamp 357345 3872377624>
09:19:55.024533 IP (tos 0x0, ttl  64, id 44078, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.78.1521 > 192.168.98.6.53912: ., cksum 0x47cc (incorrect (-> 0x46b5), 1:1(0) ack 71 win 114 <nop,nop,timestamp 3872377626 357345>
09:19:55.024683 IP (tos 0x0, ttl 198, id 28746, offset 0, flags [DF], proto: TCP (6), length: 308) 192.168.98.6.53912 > 192.168.100.78.1521: P 71:327(256) ack 1 win 16652 <nop,nop,timestamp 357345 3872377624>
09:19:55.024692 IP (tos 0x0, ttl  64, id 44079, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.78.1521 > 192.168.98.6.53912: ., cksum 0x47cc (incorrect (-> 0x45ad), 1:1(0) ack 327 win 122 <nop,nop,timestamp 3872377626 357345>
09:19:55.048613 IP (tos 0x0, ttl  64, id 44080, offset 0, flags [DF], proto: TCP (6), length: 60) 192.168.100.78.1521 > 192.168.98.6.53912: P, cksum 0x47d4 (incorrect (-> 0x3a79), 1:9(8) ack 327 win 122 <nop,nop,timestamp 3872377650 357345>
09:19:55.049265 IP (tos 0x0, ttl 198, id 28749, offset 0, flags [DF], proto: TCP (6), length: 122) 192.168.98.6.53912 > 192.168.100.78.1521: P 327:397(70) ack 9 win 16650 <nop,nop,timestamp 357347 3872377650>
09:19:55.049367 IP (tos 0x0, ttl 198, id 28750, offset 0, flags [DF], proto: TCP (6), length: 308) 192.168.98.6.53912 > 192.168.100.78.1521: P 397:653(256) ack 9 win 16650 <nop,nop,timestamp 357347 3872377650>
09:19:55.049408 IP (tos 0x0, ttl  64, id 44081, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.78.1521 > 192.168.98.6.53912: ., cksum 0x47cc (incorrect (-> 0x443c), 9:9(0) ack 653 win 130 <nop,nop,timestamp 3872377651 357347>
09:19:55.049461 IP (tos 0x0, ttl  64, id 44082, offset 0, flags [DF], proto: TCP (6), length: 84) 192.168.100.78.1521 > 192.168.98.6.53912: P 9:41(32) ack 653 win 130 <nop,nop,timestamp 3872377651 357347>
09:19:55.050654 IP (tos 0x0, ttl 198, id 28752, offset 0, flags [DF], proto: TCP (6), length: 228) 192.168.98.6.53912 > 192.168.100.78.1521: P 653:829(176) ack 41 win 16642 <nop,nop,timestamp 357347 3872377651>
09:19:55.050899 IP (tos 0x0, ttl  64, id 44083, offset 0, flags [DF], proto: TCP (6), length: 179) 192.168.100.78.1521 > 192.168.98.6.53912: P 41:168(127) ack 829 win 139 <nop,nop,timestamp 3872377652 357347>
09:19:55.068381 IP (tos 0x0, ttl 198, id 28754, offset 0, flags [DF], proto: TCP (6), length: 87) 192.168.98.6.53912 > 192.168.100.78.1521: P 829:864(35) ack 168 win 16610 <nop,nop,timestamp 357349 3872377652>
09:19:55.068553 IP (tos 0x0, ttl  64, id 44084, offset 0, flags [DF], proto: TCP (6), length: 270) 192.168.100.78.1521 > 192.168.98.6.53912: P 168:386(218) ack 864 win 139 <nop,nop,timestamp 3872377670 357349>
09:19:55.070468 IP (tos 0x0, ttl 198, id 28757, offset 0, flags [DF], proto: TCP (6), length: 1500) 192.168.98.6.53912 > 192.168.100.78.1521: . 864:2312(1448) ack 386 win 16555 <nop,nop,timestamp 357349 3872377670>
09:19:55.070561 IP (tos 0x0, ttl 198, id 28758, offset 0, flags [DF], proto: TCP (6), length: 1666) 192.168.98.6.53912 > 192.168.100.78.1521: P 2312:3926(1614) ack 386 win 16555 <nop,nop,timestamp 357349 3872377670>
09:19:55.070597 IP (tos 0x0, ttl  64, id 44085, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.78.1521 > 192.168.98.6.53912: ., cksum 0x47cc (incorrect (-> 0x35aa), 386:386(0) ack 3926 win 187 <nop,nop,timestamp 3872377672 357349>
09:19:55.071435 IP (tos 0x0, ttl  64, id 44086, offset 0, flags [DF], proto: TCP (6), length: 1500) 192.168.100.78.1521 > 192.168.98.6.53912: . 386:1834(1448) ack 3926 win 187 <nop,nop,timestamp 3872377673 357349>
09:19:55.071447 IP (tos 0x0, ttl  64, id 44087, offset 0, flags [DF], proto: TCP (6), length: 1104) 192.168.100.78.1521 > 192.168.98.6.53912: P 1834:2886(1052) ack 3926 win 187 <nop,nop,timestamp 3872377673 357349>
09:19:55.077711 IP (tos 0x0, ttl 198, id 28761, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.53912 > 192.168.100.78.1521: ., cksum 0xeb92 (correct), 3926:3926(0) ack 2886 win 16652 <nop,nop,timestamp 357350 3872377673>
09:19:55.077866 IP (tos 0x0, ttl 198, id 28762, offset 0, flags [DF], proto: TCP (6), length: 299) 192.168.98.6.53912 > 192.168.100.78.1521: P 3926:4173(247) ack 2886 win 16652 <nop,nop,timestamp 357350 3872377673>
09:19:55.079765 IP (tos 0x0, ttl  64, id 44088, offset 0, flags [DF], proto: TCP (6), length: 372) 192.168.100.78.1521 > 192.168.98.6.53912: P 2886:3206(320) ack 4173 win 209 <nop,nop,timestamp 3872377681 357350>
09:19:55.084628 IP (tos 0x0, ttl 198, id 28766, offset 0, flags [DF], proto: TCP (6), length: 234) 192.168.98.6.53912 > 192.168.100.78.1521: P 5621:5803(182) ack 3206 win 16572 <nop,nop,timestamp 357351 3872377681>
09:19:55.084649 IP (tos 0x0, ttl 198, id 28765, offset 0, flags [DF], proto: TCP (6), length: 1500) 192.168.98.6.53912 > 192.168.100.78.1521: . 4173:5621(1448) ack 3206 win 16572 <nop,nop,timestamp 357351 3872377681>
09:19:55.084673 IP (tos 0x0, ttl  64, id 44089, offset 0, flags [DF], proto: TCP (6), length: 64) 192.168.100.78.1521 > 192.168.98.6.53912: ., cksum 0x47d8 (incorrect (-> 0xf791), 3206:3206(0) ack 4173 win 209 <nop,nop,timestamp 3872377686 357350,nop,nop,sack 1 {5621:5803}>

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2795449/,如需轉載,請註明出處,否則將追究法律責任。

相關文章