Oracle中tnsping主機名/Ip顯示TNS

今天是個陰天，老天想下不下的，昏昏沉沉的總想睡覺…… 額……廢話不多說了。 今天中午想做一個catalog庫，我就在虛擬機上裝了Oracle11g，本想不同于target數據庫， 所以當時配置監聽時就沒有用默認的端口號1521，和平常一樣，我也懶得手工創建監聽， netca

今天是個陰天，老天想下不下的，昏昏沉沉的總想睡覺……

額……廢話不多說了。

今天中午想做一個catalog庫，我就在虛擬機上裝了Oracle11g，本想不同于target數據庫，

所以當時配置監聽時就沒有用默認的端口號1521，和平常一樣，我也懶得手工創建監聽，

netca創建監聽，監聽名默認名LISTENER，端口號改為1526；

接著就是啟動數據庫，注冊 alter system register，啟動監聽，[oracle@jibo admin]$ lsnrctl start

查看監聽狀態：

[oracle@jibo ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-SEP-2014 21:25:12

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jibo)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-SEP-2014 15:59:46
Uptime 0 days 5 hr. 25 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jibo/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jibo)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
Services Summary...
Service "PROD" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully

嗯……很好，一切正常；

我就拷貝了一份catalog庫的tnsnames.ora到target數據庫的tnsnames.ora中，好讓target數據庫遠程訪問catalog庫；

先測試一下是否可以連通：

sqlplus hr/hr@PROD

(catalog庫的服務名我設置為PROD，target庫服務名我設置為ORCL )

結果…… 呃I 沒通，報錯……

我就納悶了……安裝數據庫什么的都設置好好的啊，不記得有哪錯了……

我想了一下，是catalog庫的問題還是target庫的問題呢？

不用想，target庫一直用的好好的，監聽什么的都沒有問題，

（我target 主機名Demon ip192.168.1.8/ catalog庫主機名 jibo ip192.168.1.4）

檢驗一下target吧 ，tnsping 192.168.1.4

[oracle@Demon admin]$ tnsping 192.168.1.4

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 00:43:42

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=1521)))
TNS-12541: TNS:no listener
有錯誤……沒有啟動監聽？ 不會啊

上面看已經啟動了……

再自己tnsping一下;

[oracle@Demon admin]$ tnsping 192.168.1.8

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 00:45:33

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.8)(PORT=1521)))
OK (110 msec)

自己tnsping自己沒有問題

再測試一下catalog庫

[oracle@jibo ~]$ tnsping 192.168.1.8

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 00:48:02

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.8)(PORT=1521)))
OK (100 msec)
tnsping target庫沒問題

再自己tnsping自己一下：

[oracle@jibo ~]$ tnsping 192.168.1.4

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 00:48:14

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=1521)))
TNS-12541: TNS:no listener

額，錯誤，無監聽，查看監聽，什么原因呢……

[oracle@jibo admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-SEP-2014 23:09:33

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jibo)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-SEP-2014 23:01:40
Uptime 0 days 0 hr. 7 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jibo/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jibo)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
Services Summary...
Service "PROD" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully

挺正常啊；catalog庫有問題，啥問題呢……

再嘗試tnsping 主機名

[oracle@jibo ~]$ tnsping jibo

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 00:51:55

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=1521)))
TNS-12541: TNS:no listener

錯誤……

tnsping 服務名 試試……

[oracle@jibo ~]$ tnsping prod

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 00:53:09

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = jibo)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))
OK (120 msec)

這個可以啊……那啥問題呢……

難道主機名和ip地址不匹配？

查看 /etc/hosts

[root@jibo ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6

192.168.1.4 jibo
[root@jibo ~]#
正常啊

重啟監聽lsnrctl reload

再試試……

仍然報TNS-12541: TNS:no listener錯誤；查看tnsnames.ora

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jibo)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)

LISTENER_PROD =
(ADDRESS = (PROTOCOL = TCP)(HOST = jibo)(PORT = 1526))

在查看listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jibo)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

我頓時有些納悶，為啥netca自動創建的監聽中tnsnames.ora和listener.ora的監聽名不一樣的，難道自動生成還有錯不成……

那么在數據庫中注冊的監聽到底用哪個名字啊

我再查看local_listener這個參數

SYS@PROD>show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_PROD
SYS@PROD>
額，不是我創建的listener的監聽名，為什么？

嗯……

哦，我想起來了listener監聽名是Oracle默認的監聽名，同時默認的監聽端口為1521，

我一直tnsping 主機名/Ip時，根據默認監聽名，它自動找端口為1521的監聽，那當然找不到了……

netstat -pan|grep 1521 就沒有啟用這個端口……

看來默認監聽名不是那么好用的，

我在listener.ora中手動把監聽名改為LISTENER_PROD

重啟監聽 lsnrctl reload LISTENER_PROD

然后再關了默認監聽lsnrctl stop

[oracle@jibo ~]$ sqlplus hr/hr@jibo:1526/prod

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 24 09:01:14 2014

Copyright (c) 1982, 2013, 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

SQL>

簡單連接沒問題，說明1526這個端口是可以用的

再tnsping試試

[oracle@jibo admin]$ tnsping 192.168.1.4

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 09:01:28

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=1521)))
TNS-12541: TNS:no listener
[oracle@jibo admin]$ tnsping jibo

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2014 09:01:41

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=1521)))
TNS-12541: TNS:no listener
[oracle@jibo admin]$

都不行，好像必須走1521這個端口，但默認的已經改了，

還是這個端口……嗯 誰有好的辦法讓它用1526這個端口的請留言啊……

結果成功了 ，用的是1526的端口；默認配置監聽還真是坑……

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com