tnsping通但无法连接的问题处理

C:\Documents and Settings\sn10712>tnsping MYTESTDB

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 17-4月 -
2013 13:00:49

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

已使用的参数文件:
F:\app\SN10712\product\11.1.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 10.10.21.25)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb)))
OK (80 毫秒)

C:\Documents and Settings\sn10712>

SQL> conn scott/tiger@MYTESTDB
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

 

SQL> show parameter serv

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string
gcs_server_processes                 integer     0
max_shared_servers                   integer
parallel_max_servers                 integer     10
parallel_min_servers                 integer     0
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     4
rdbms_server_dn                      string
sec_return_server_release_banner     boolean     FALSE
service_names                        string      testdb.localdomain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 7759462
shared_server_sessions               integer
shared_servers                       integer     1
smtp_out_server                      string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-APR-2013 01:35:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                08-APR-2013 01:27:23
Uptime                    0 days 0 hr. 8 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u02/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "testdb.localdomain" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB.localdomain" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$

ORACLE_SID跟service_name不同。如果你用的是ORACLE_SID，在tnsnames中，将service_name更改为SID。

如果要使用service_name，则在服务器上运行lsnrctl status，查看对应instance的service_name是什么。或者查看数据库启动参数：service_names。

 

TNSNAMES.ORA 把service_name改成 testdb.localdomain后正常

 

或者改成SID进行连接,也是没问题的

MYTESTDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.21.25)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = testdb)
    )
  )