[20180129]db_link使用ezconnect注意.txt
--//一般以前建立db_link都是在服务端tnsnames.ora文件建立想到链接,很早都知道可以使用ezconnect方式配置
--//db_link,例子如下:
CREATE PUBLIC DATABASE LINK TEST040
CONNECT TO SCOTT
IDENTIFIED BY <PWD>
USING '192.168.100.40:1521/bookdg';
--//但是要注意一些细节问题,克隆数据库时也把这些设置带到克隆到测试环境,如果有应用通过dblink修改别的服务器数据,要非常小心,
--//特别一些应用与开发环境没有网络隔离的情况下更加要小心.
--//我这里提到另外的问题,就是按照上面的写法隐含的一个问题,就是可能导致链接使用共享模式.通过例子来说明:
1.环境:
SCOTT@book> @ &r/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
SCOTT@book> CREATE PUBLIC DATABASE LINK LOOPBACK CONNECT TO SCOTT IDENTIFIED BY book USING 'localhost:1521/book';
Database link created.
SCOTT@book> show parameter dispatchers
NAME TYPE VALUE
--------------- --------- --------------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=book,bookXDB)
max_dispatchers integer
--//在服务名=book上打开了共享服务模式.
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- -------- --------- ------ ------- ---------- --------------------------------------------------
80 55 51635 DEDICATED 51636 30 20 alter system kill session '80,55' immediate;
SCOTT@book> select * from dept@loopback;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
$ netstat -tunp | grep 127.0.0.1:1521 | grep oraclebook
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:60690 127.0.0.1:1521 ESTABLISHED 51636/oraclebook
--//知道进程号51636(与前面对上).从IP=127.0.0.1,端口=:60690发起.
$ netstat -tunp | grep -i 127.0.0.1:60690
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:60690 127.0.0.1:1521 ESTABLISHED 51636/oraclebook
tcp 0 0 127.0.0.1:1521 127.0.0.1:60690 ESTABLISHED 50153/ora_d000_book
--//注意看进程号50153,使用ora_d000_book,也就是这样使用的是共享服务模式.
SCOTT@book> select saddr,sid,paddr,status,server from vsessionwherepaddrin(selectaddrfromvprocess where spid=50153);
SADDR SID PADDR STATUS SERVER
---------------- ---------- ---------------- -------- ---------
00000000855F81A0 261 00000000859B7298 INACTIVE NONE
--//也就是要在配置时明确使用的模式.修改如下就ok了.
SCOTT@book> drop public database link LOOPBACK ;
Database link dropped.
SCOTT@book> CREATE PUBLIC DATABASE LINK LOOPBACK CONNECT TO SCOTT IDENTIFIED BY book USING 'localhost:1521/book:DEDICATED';
Database link created.
SCOTT@book> select * from dept@loopback ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
80 57 51820 DEDICATED 51821 30 21 alter system kill session '80,57' immediate;
$ netstat -tunp | grep -i 127.0.0.1
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:60943 ESTABLISHED 51832/oraclebook
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp 0 0 127.0.0.1:60943 127.0.0.1:1521 ESTABLISHED 51821/oraclebook
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp 0 0 127.0.0.1:1521 127.0.0.1:7801 ESTABLISHED 24802/tnslsnr
tcp 0 0 127.0.0.1:7801 127.0.0.1:1521 ESTABLISHED 14308/ora_pmon_orcl
--//注意看下划线.
SCOTT@book> select saddr,sid,paddr,status,server from vsessionwherepaddrin(selectaddrfromvprocess where spid in (51821,51832));
SADDR SID PADDR STATUS SERVER
---------------- ---------- ---------------- -------- ---------
0000000085B95010 80 00000000859C2A80 ACTIVE DEDICATED
000000008561FD00 274 00000000859B9408 INACTIVE DEDICATED
--//2个都是专用模式.另外配置tns时要明确指定模式:
http://blog.itpub.net/267265/viewspace-2133551/
