[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/[email protected]: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
[email protected]:34401/book:POOLED> select sysdate from dual ;
SYSDATE
-------------------
2017-08-25 16:50:52
[email protected]: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
[email protected]: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/[email protected]: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
[email protected]: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