[20170825]不啟動監聽遠端能連線資料庫嗎2.txt

--//曾經寫過一篇不啟動監聽連線資料庫的帖子:http://blog.itpub.net/267265/viewspace-1816211/
--//利用共享伺服器模式.
--//昨天測試備庫啟動drcp,突然想到這個跟共享模式有一些類似.自己也測試看看.

1.環境:
SYS@book> @ &r/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

--//啟動drcp.
SYS@book> exec dbms_connection_pool.start_pool()
PL/SQL procedure successfully completed.

$ lsnrctl service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2017 16:40:36
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "book" has 2 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:23 refused:0
         LOCAL SERVER
Instance "book", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:31 refused:0 state:ready
         LOCAL SERVER
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: xxxxx, pid: 22244>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=33994))
      "N000" established:0 refused:0 current:0 max:40000 state:ready
         CMON <machine: xxxxx, pid: 34251>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=34401))
          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Service "book123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         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: xxxxx, pid: 22244>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=33994))
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: xxxxx, pid: 53522>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=64784))
The command completed successfully

--//注意看下劃線的埠號34401.
# lsof -i -P -n | grep 34401
oracle 34251 oracle 11u IPv6 32813593 0t0 TCP *:34401 (LISTEN)

# ps -ef | grep 3425[1]
oracle 34251 1 0 16:40 ? 00:00:00 ora_n000_book
--//對應程式是34251.程式名為ora_n000_book.

$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2017 16:45:56
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
The command completed successfully

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2017 16:46:54
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
   Linux Error: 2: No such file or directory
--//可以發現監聽已經關閉.

3.測試連線:
--//採用ezconnect模式,這樣快捷一些.
$ rlsql scott/book@192.168.100.78:34401/book:POOLED
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 25 16:50:41 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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

SCOTT@192.168.100.78:34401/book:POOLED> select sysdate from dual ;
SYSDATE
-------------------
2017-08-25 16:50:52

SCOTT@192.168.100.78:34401/book:POOLED> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
SID SERIAL# SERVER
---------- ---------- ---------
31 57 POOLED

SCOTT@192.168.100.78:34401/book:POOLED> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
31 57 34257 26 29 alter system kill session '31,57' immediate;

$ ps -ef | grep 3425[7]
oracle 34257 1 0 16:40 ? 00:00:00 ora_l002_book

--//同樣對於共享模式也一樣,前面知道埠號33994.

$ rlsql scott/book@192.168.100.78:33994/book:shared
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 25 16:57:06 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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

SCOTT@192.168.100.78:33994/book:shared> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);

SID SERIAL# SERVER
---------- ---------- ---------
263 9 SHARED

總結:
--//再次用一句安全的術語講"蒼蠅不叮無縫的蛋"。^_^.
--//只要知道drcp的埠號(當然要啟動這個服務)或者共享模式的埠號,不啟動監聽一樣能連線資料庫.
--//補充netstat -tnlp | grep ora的輸出.
# netstat -tnlp | egrep 'Active|Proto|ora'
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 :::34401                    :::*                        LISTEN      34251/ora_n000_book
tcp        0      0 :::33994                    :::*                        LISTEN      22244/ora_d000_book

[20170825]不啟動監聽遠端能連線資料庫嗎2

相關文章