Home > Article > Database > Oracle中tnsping 主机名/Ip 显示TNS

Oracle中tnsping 主机名/Ip 显示TNS

WBOYOriginal: 2016-06-07 15:51:161723browse

今天是个阴天，老天想下不下的，昏昏沉沉的总想睡觉…… 额……废话不多说了。今天中午想做一个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

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

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

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

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

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

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

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

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

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

Used parameter files:

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

Used parameter files:

都不行，好像必须走1521这个端口，但默认的已经改了，

还是这个端口……嗯谁有好的办法让它用1526这个端口的请留言啊……

结果成功了，用的是1526的端口；默认配置监听还真是坑……

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：sqlserver2008存取二进制图片Next article：终于编译好了qt的oracle驱动QOCI,连接driver not loaded解决方法

See more

Oracle中tnsping 主机名/Ip 显示TNS

Related articles