当前位置:Gxlcms > mysql > Oracle中tnsping主机名/Ip显示TNS

Oracle中tnsping主机名/Ip显示TNS

时间:2021-07-01 10:21:17 帮助过:42人阅读

今天是个阴天,老天想下不下的,昏昏沉沉的总想睡觉…… 额……废话不多说了。 今天中午想做一个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的端口;默认配置监听还真是坑……

人气教程排行