[20160126]建立loopback link问题.txt

[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/[email protected]: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:

$ rlsqlplus scott/[email protected]: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.

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

--测试都可以通过。

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--我对比别的机器发现在配置文件listener.ora 是写入主机名。设置修改主机名看看。

#      (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))

--再重新启动监听。

tcp        0      0 :::1521                     :::*                        LISTEN      24817/tnslsnr

--可以发现这个时候监听在该主机的任何接口。测试上面的语句通过。

--从这里看出一些小细节。在监听指定IP，仅仅在特定的IP上监听。而如果使用主机名可以在该机器的任意IP上监听。