[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,现在已经还原.端口已经不固定.