[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

--//如果透過外網使用共享模式連線埠變化對於配置防火牆非常不方便.看了連結:

--//可以透過指定埠號實現該功能,自己測試看看:
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,現在已經還原.埠已經不固定.