[20151023]不启动监听远程能连接数据库吗？.txt
--如果有人问你不启动监听远程能连接数据库吗？我想我开始的回答不行。
--但是不要忘了,oracle支持专有服务器以及共享服务器模式，而共享服务器模式可以不使用1521端口，这样就可以不用启动监听远程连
--接服务器。
--通过测试来说明问题。
1.测试环境：
SYS@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> show parameter shared
NAME TYPE VALUE
------------------------------------ ------------- ----------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 13421772
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SYS@test> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ -------------- --------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=testXDB)
max_dispatchers integer
$ ps -ef | grep ora_d00[0]
oracle 7697 1 0 08:28 ? 00:00:00 ora_d000_test
--这是一个安装好以后没有改动以上参数的设置。可以发现缺省就启动一个ora_d000_test.这个就是用于共享服务器的连接，缺省仅仅启
--动1个,如果多个是ora_d001_test....
$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 23-OCT-2015 08:54:02
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.89)(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=EXTPROC0)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$ netstat -na | grep 1521
--可以发现监听没有启动。
2.修改远程的client,在tnsnames.ora加入如下：
89S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.89)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
(SERVER = SHARED)
)
)
--很明显这样是无法连接数据库的，因为1521端口没有打开。
--我们必须要要知道进程ora_d000_test打开的端口号。
$ netstat -tnlp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:29018 0.0.0.0:* LISTEN 7697/ora_d000_test
tcp 0 0 0.0.0.0:51206 0.0.0.0:* LISTEN -
tcp 0 0 :::111 :::* LISTEN -
tcp 0 0 :::22 :::* LISTEN -
--从以上输出可以知道远程端口29018(对应ora_d000_test进程)。修改tnsnames.ora文件如下：
89S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.89)(PORT = 29018))
)
(CONNECT_DATA =
(SID = test)
(SERVER = SHARED)
)
)
d:\tools\rlwrap>sqlplus scott/btbtms@89s
SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 23 08:59:14 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@89s> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
146 21 7699 alter system kill session '146,21' immediate;
SCOTT@89s> select sysdate from dual ;
SYSDATE
-------------------
2015-10-23 09:07:33
SCOTT@89s> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
SID SERIAL# SERVER
---------- ---------- ---------
146 21 SHARED
--这样远程就可以连接数据库。注意上面的配置只能使用(SID = test),因为dispatchers缺省配置仅仅支持SERVICE=testXDB。
$ ps -ef | egrep "ora_d000|7699" | grep -v egrep
oracle 7697 1 0 08:28 ? 00:00:00 ora_d000_test
oracle 7699 1 0 08:28 ? 00:00:00 ora_s000_test
--再启动一个会话：
SCOTT@89s> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
142 32 7699 alter system kill session '142,32' immediate;
--依旧使用进程号(ora_s000_test)是7699.这也是共享的含义，节约了服务器内存的使用。特别适合连接非常巨大，执行sql语句完成很快的情况。
3.我的测试client是12c，11gR2开始增强了简易连接魔术：
d:\tools\rlwrap>sqlplus scott/btbtms@192.168.100.89:29018/test:shared
SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 23 09:17:16 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error
--因为不支持服务名test(主要是没有配置)。执行如下：
d:\tools\rlwrap>sqlplus scott/btbtms@192.168.100.89:29018/testXDB:shared
SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 23 09:18:32 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@192.168.100.89:29018/testXDB:shared> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
142 40 7699 alter system kill session '142,40' immediate;
SCOTT@192.168.100.89:29018/testXDB:shared> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
SID SERIAL# SERVER
---------- ---------- ---------
142 40 SHARED
4.修改参数dispatchers:
SYS@test> alter system set dispatchers='(PROTOCOL=TCP) (DISP=2)(SERVICE=test,testXDB)' scope=memory;
System altered.
$ ps -ef | egrep "ora_d00|7699" | grep -v egrep
oracle 7697 1 0 08:28 ? 00:00:00 ora_d000_test
oracle 7699 1 0 08:28 ? 00:00:00 ora_s000_test
oracle 8793 1 0 09:21 ? 00:00:00 ora_d001_test
--修改参数加入DISP=2，这样dispatchers启动了2个。另外我还增加服务名test，下面测试使用test服务的情况：
d:\tools\rlwrap>sqlplus scott/btbtms@192.168.100.89:29018/test:shared
SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 23 09:24:17 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--ok通过。
5.修改tnsnames.ora配置：
--说明：采用SERVICE_NAME ，不使用sid。
89S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.89)(PORT = 29018))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
(SERVER = SHARED)
)
)
d:\tools\rlwrap>sqlplus scott/btbtms@89s
...
SCOTT@89s> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
142 48 7699 alter system kill session '142,48' immediate;
SCOTT@89s> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
SID SERIAL# SERVER
---------- ---------- ---------
142 48 SHARED
d:\tools\rlwrap>sqlplus scott/btbtms@192.168.100.89:29018/test:shared
...
SCOTT@192.168.100.89:29018/test:shared> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
SID SERIAL# SERVER
---------- ---------- ---------
142 50 SHARED
6.当然共享服务器的端口随数据库启动动态变化的。
--也可以通过视图v$dispatcher知道对应的端口号。
SYS@test> select * from v$dispatcher;
NAME NETWORK PADDR STATUS ACC MESSAGES BYTES BREAKS OWNED CREATED IDLE BUSY LISTENER CONF_INDX
----- --------------------------------------------------- ---------------- ------ --- -------- ------ ------ ----- ------- ------ ---- -------- ---------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=icaredg3)(PORT=29018)) 000000007C262C40 WAIT YES 348 133567 0 2 10 380509 2 0 0
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=icaredg3)(PORT=22202)) 000000007C266398 WAIT YES 0 0 0 0 0 59654 0 0 0
7.现在启动监听修改tnsnames.ora文件端口号为1521：
89S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.89)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
(SERVER = SHARED)
)
)
$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 23-OCT-2015 09:35:31
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.89)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.89)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 23-OCT-2015 09:35:33
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/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.89)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$ netstat -tnlp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
tcp 0 0 192.168.100.89:1521 0.0.0.0:* LISTEN 8931/tnslsnr
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22202 0.0.0.0:* LISTEN 8793/ora_d001_test
tcp 0 0 0.0.0.0:29018 0.0.0.0:* LISTEN 7697/ora_d000_test
tcp 0 0 0.0.0.0:51206 0.0.0.0:* LISTEN -
tcp 0 0 :::111 :::* LISTEN -
tcp 0 0 :::22 :::* LISTEN -
--可以发现启动了1521端口。
d:\tools\rlwrap>sqlplus scott/btbtms@89s
....
SCOTT@89s> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
SID SERIAL# SERVER
---------- ---------- ---------
142 54 SHARED
SCOTT@89s> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
142 54 7699 alter system kill session '142,54' immediate;
--很明显这种模式通过1521端口，因为使用共享服务器模式，然后再转到29018端口。我们前面的情况仅仅绕过了1521端口，也就是不通
--过监听，而直接通过共享模式打开的端口来连接数据库。实际上还是通过服务器打开的端口来访问服务器的，或者用一句安全的术语讲
--"苍蝇不叮无缝的蛋"。