[20191112]oracle共用連接模式埠.txt--//如果使用共用服務模式,你可以發現每次重啟資料庫對應的埠號會發生變化.# netstat -tunlp | egrep "Active|Proto|ora_[ds]"Active Internet connections (only s ...
[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/[email protected]: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
[email protected]: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/[email protected]: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,現在已經還原.埠已經不固定.
