[20151023]不启动监听远程能连接数据库吗?

[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端口，也就是不通
--过监听，而直接通过共享模式打开的端口来连接数据库。实际上还是通过服务器打开的端口来访问服务器的，或者用一句安全的术语讲
--"苍蝇不叮无缝的蛋"。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1816211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1816211/