[20191112]oracle共享连接模式端口.txt

[20191112]oracle共享连接模式端口.txt

--//如果使用共享服务模式,你可以发现每次重启数据库对应的端口号会发生变化.
# netstat -tunlp | egrep "active|proto|ora_[ds]"
active internet connections (only servers)
proto recv-q send-q local address               foreign address             state       pid/program name
tcp        0      0 :::57864                    :::*                        listen      23134/ora_d000_book
udp        0      0 ::1:48080                   :::*                                    23134/ora_d000_book
udp        0      0 ::1:58231                   :::*                                    23132/ora_s000_book

--//重启数据库后,端口号会发生变化.
# netstat -tunlp | egrep "active|proto|ora_[ds]"
active internet connections (only servers)
proto recv-q send-q local address               foreign address             state       pid/program name
tcp        0      0 :::51056                    :::*                        listen      32421/ora_d000_book
udp        0      0 ::1:55948                   :::*                                    32421/ora_d000_book
udp        0      0 ::1:17992                   :::*                                    32423/ora_s000_book

--//如果通过外网使用共享模式连接端口变化对于配置防火墙非常不方便.看了链接:
https://www.usn-it.de/2008/11/10/oracle-how-to-stop-a-mts-dispatcher-process/
--//可以通过指定端口号实现该功能,自己测试看看:
alter system set dispatchers=
'(address=(partial=true)(protocol=tcp)(host=hostname)(port=3000))(dispatchers=1)(service=test)',
'(address=(partial=true)(protocol=tcp)(host=hostname)(port=3005))(dispatchers=1)(service=test)',
'(address=(partial=true)(protocol=tcp)(host=hostname)(port=3010))(dispatchers=1)(service=test)',
'(address=(partial=true)(protocol=tcp)(host=hostname)(port=3015))(dispatchers=1)(service=test)',
'(address=(partial=true)(protocol=tcp)(host=hostname)(port=3020))(dispatchers=1)(service=test)'
scope=both sid='sid13';

1.环境:
sys@book> @ 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

sys@book> show parameter dispatchers
name            type    value
--------------- ------- -------------------------------------
dispatchers     string  (protocol=tcp) (service=book,bookxdb)
max_dispatchers integer

sys@book> create pfile='/tmp/@.ora' from spfile;
file created.
--//保存1份pfile参数文件.

2. 修改dispatchers参数:
alter system set dispatchers=
'(address=(partial=true)(protocol=tcp)(host=192.168.100.78)(port=30000))(dispatchers=1)(service=book,bookxdb)',
'(address=(partial=true)(protocol=tcp)(host=192.168.100.78)(port=30005))(dispatchers=1)(service=book,bookxdb)'
scope=both sid='*';

sys@book> show parameter dispatchers
name                                 type                                     value
------------------------------------ ---------------------------------------- ----------------------------------------------------------------------------------------------------
dispatchers                          string                                   (protocol=tcp) (service=book,bookxdb), (address=(partial=true)(protocol=tcp)(host=192.168.100.78)(po
                                                                              rt=30000))(dispatchers=1)(service=book,bookxdb), (address=(partial=true)(protocol=tcp)(host=192.168.
                                                                              100.78)(port=30005))(dispatchers=1)(service=book,bookxdb)
max_dispatchers                      integer


3.重启数据库看看:
sys@book> shutdown immediate ;
database closed.
database dismounted.
oracle instance shut down.
sys@book> startup
oracle instance started.
total system global area  643084288 bytes
fixed size                  2255872 bytes
variable size             205521920 bytes
database buffers          427819008 bytes
redo buffers                7487488 bytes
database mounted.
database opened.

# netstat -tunlp | egrep "active|proto|ora_[ds]"
active internet connections (only servers)
proto recv-q send-q local address               foreign address             state       pid/program name
tcp        0      0 192.168.100.78:30005        0.0.0.0:*                   listen      32581/ora_d002_book
tcp        0      0 192.168.100.78:30000        0.0.0.0:*                   listen      32579/ora_d001_book
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp        0      0 :::49854                    :::*                        listen      32577/ora_d000_book
udp        0      0 ::1:45407                   :::*                                    32583/ora_s000_book
udp        0      0 ::1:48884                   :::*                                    32577/ora_d000_book
udp        0      0 ::1:16168                   :::*                                    32579/ora_d001_book
udp        0      0 ::1:16201                   :::*                                    32581/ora_d002_book

--//你可以发现现在端口固定在30000,30005.注意下划线信息.

4.连接测试:
>sqlplus scott/book@192.168.100.78:30000/book
sql*plus: release 12.2.0.1.0 production on tue nov 12 11:28:23 2019
copyright (c) 1982, 2016, 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:30000/book> @ spid
       sid    serial# process                  server    spid                     pid  p_serial# c50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       309          1 10624:9940               shared    32583                     22          1 alter system kill session '309,1' immediate;

--//server=shared,spid=32583
# ps -ef | grep 3258[3]
oracle   32583     1  0 11:26 ?        00:00:00 ora_s000_book

--//sqlplus scott/book@192.168.100.78:30005/book 也是ok的.

5.收尾还原:
sys@book> shutdown immediate ;
database closed.
database dismounted.
oracle instance shut down.

sys@book> startup nomount pfile='/tmp/@.ora';
oracle instance started.
total system global area  643084288 bytes
fixed size                  2255872 bytes
variable size             205521920 bytes
database buffers          427819008 bytes
redo buffers                7487488 bytes

sys@book> create spfile from pfile='/tmp/@.ora';
file created.

sys@book> shutdown immediate ;
ora-01507: database not mounted

oracle instance shut down.
sys@book> startup
oracle instance started.
total system global area  643084288 bytes
fixed size                  2255872 bytes
variable size             205521920 bytes
database buffers          427819008 bytes
redo buffers                7487488 bytes
database mounted.
database opened.

# netstat -tunlp | egrep "active|proto|ora_[ds]"
active internet connections (only servers)
proto recv-q send-q local address               foreign address             state       pid/program name
tcp        0      0 :::20791                    :::*                        listen      32896/ora_d000_book
udp        0      0 ::1:7511                    :::*                                    32898/ora_s000_book
udp        0      0 ::1:7696                    :::*                                    32896/ora_d000_book

--//ok,现在已经还原.端口已经不固定.