将oracle端口1521共享_[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,现在已经还原.端口已经不固定.