linux监听配置错误,各种TNS报错总结--监听器及tnsnames.ora配置文件各种出错总结...

7.tnsnames.ora 中test本地名对应的字段描述格式正确，数据库监听开启。但是tnsnames.ora 中HOST = 字段指定的IP或主机可以连接，PORT =字段指定的端口打开。服务名不对。即tnsnames.ora 中test本地名对应的字段描述格式正确，监听开启，但是监听中不包括TNS中SERVICE_NAME =描述的服务名。测试时：TNSPING可以通。SQLPLUS不能连

嘻嘻哒的小兔子

580人浏览 · 2021-05-12 19:48:21

嘻嘻哒的小兔子 · 2021-05-12 19:48:21 发布

7.tnsnames.ora 中test本地名对应的字段描述格式正确，数据库监听开启。但是tnsnames.ora 中HOST = 字段指定的IP或主机可以连接，PORT =字段指定的端口打开。服务名不对。

即tnsnames.ora 中test本地名对应的字段描述格式正确，监听开启，但是监听中不包括TNS中SERVICE_NAME =描述的服务名。

测试时：

TNSPING可以通。

SQLPLUS不能连接，报错：ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

实验数据：

[Oracle@bys001 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

bys1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bys1)

)

test =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bys999) 手动改为一个不存在的服务名bys999

)

[oracle@bys001 admin]$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:10:38

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys001.oel.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 05-NOV-2013 16:10:23

Uptime 2 days 7 hr. 0 min. 15 sec

Trace Level off

Security ON: Password or 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/diag/tnslsnr/bys001/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys001.oel.com)(PORT=1521)))

Services Summary...

Service "bys1" has 1 instance(s).

Instance "bys1", status READY,has 1 handler(s) for this service...

Service "bys1XDB" has 1 instance(s).

Instance "bys1", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@bys001 admin]$ tnsping bys1

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:10:43

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))

OK (10 msec)

[oracle@bys001 admin]$ tnsping test ----一个不存在的服务名，只要主机和端口正确，也是可以用TNSPING测通的。

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:10:46

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = bys999)))

OK (10 msec)

[oracle@bys001 admin]$ sqlplus bys/bys@bys1

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:10:53 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

BYS@bys1>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@bys001 admin]$ sqlplus bys/bys@test 服务名不对，测试时TNSPING可能测通，SQLPLUS不能连接

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:10:59 2013

ERROR: