[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/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上監聽。