今天一位網友找我說用sqlplus遠端連接配接總是報錯,連接配接不上,最終檢查發現是他本機的tnsnames檔案配置格式不對導緻的
以下是我模拟的環境,如下:
[[email protected] admin]$ sqlplus scott/[email protected]
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 26 20:23:45 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
[[email protected] admin]$ tnsping goolen
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-DEC-2013 20:27:55
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.50)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = goolen)
TNS-12533: TNS:illegal ADDRESS parameters
++++telnet 1521端口沒有問題
[[email protected] admin]$ telnet 192.168.100.50 1521
Trying 192.168.100.50...
Connected to 192.168.100.50 (192.168.100.50).
Escape character is '^]'.
q
quit
Connection closed by foreign host.
++++我突然想起來很早以前看過一片文章,說過這個檔案的格式配置不對會導緻連接配接失敗,但是記不太清楚了,我想他的應該也是這個問題
然後我在本地自己測試後發現确實如此,如果SERVICE_NAME下的一行缺少空格,就會導緻相同的錯誤
然後讓他檢查他的tnsnames檔案,果然如此,他說是在網上随便貼的格式
+++檢視tnsnames檔案内容:
[[email protected] admin]$ cat tnsnames.ora
goolen =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.50)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = goolen)
)
)
[email protected] admin]$ sqlplus scott/[email protected]
SQL*Pls: Release 11.2.0.1.0 Production on Thu Dec 26 20:23:45 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
+++然後讓他把格式改過來,在前面加個空格,然後再次連接配接,就ok了
[[email protected] admin]$ cat tnsnames.ora
goolen =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.50)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = goolen)
)
)
[[email protected] admin]$ sqlplus scott/[email protected]
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 26 20:41:40 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>