[20160126]建立loopback link问题.txt
--今天为了测试的需要，我想建立loop link。执行如下：
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
CREATE PUBLIC DATABASE LINK loopback USING 'localhost:1521/book';
SCOTT@book> select * from dept@loopback;
select * from dept@loopback
*
ERROR at line 1:
ORA-12541: TNS:no listener
$ oerr ora 12541
12541, 00000, "TNS:no listener"
// *Cause: The connection request could not be completed because the listener
// is not running.
// *Action: Ensure that the supplied destination address matches one of
// the addresses used by the listener - compare the TNSNAMES.ORA entry with
// the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
// go by way of an Interchange). Start the listener on the remote machine.
--奇怪给这么简单的问题给难住了。
$ rlsqlplus scott/book@127.0.0.1:1521/book
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 09:19:33 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
$ rlsqlplus scott/book@192.168.100.78:1521/book
--通过。难道监听配置有什么问题吗？
$ netstat -tnlp | grep 1521
(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 192.168.100.78:1521 0.0.0.0:* LISTEN 24647/tnslsnr
--难道我要给在listener.ora加入127.0.0.1的IP吗? 试着加入看看：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF
$ lsnrctl start
SYS@book> alter system register;
System altered.
$ netstat -tnlp | grep 1521
(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 192.168.100.78:1521 0.0.0.0:* LISTEN 24739/tnslsnr
tcp 0 0 127.0.0.1:1521 0.0.0.0:* LISTEN 24739/tnslsnr
$ rlsqlplus scott/book@127.0.0.1:1521/book
$ rlsqlplus scott/book@192.168.100.78:1521/book
--测试都可以通过。
SCOTT@book> select * from dept@loopback;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--我对比别的机器发现在配置文件listener.ora 是写入主机名。设置修改主机名看看。
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF
--再重新启动监听。
$ netstat -tnlp | grep 1521
(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 :::1521 :::* LISTEN 24817/tnslsnr
--可以发现这个时候监听在该主机的任何接口。测试上面的语句通过。
--从这里看出一些小细节。在监听指定IP，仅仅在特定的IP上监听。而如果使用主机名可以在该机器的任意IP上监听。