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

来源：互联网发布：saas软件推广编辑：程序博客网时间：2024/06/15 07:06

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

已使用的参数文件:
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 Release11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options
[oracle@localhost ~]$ lsnrctl status

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

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
StartDate               08-APR-2013 01:27:23
Uptime                   0 days 0 hr. 8 min. 17 sec
TraceLevel              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u02/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener LogFile        /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 1handler(s) for this service...
Service "testdbXDB.localdomain" has 1 instance(s).
Instance "testdb", status READY, has 1handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$

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

如果要使用service_name，则在服务器上运行lsnrctlstatus，查看对应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)
    )
)

0 0