客户端连接oracle数据库报TNS-12514

lai

操作系统：
Welcome to SUSE Linux Enterprise Server 10 SP2 (x86_64) - Kernel %r (%t).
数据库版本：11.1.0.7.0
这是新装的数据库，做的是主备，双机切换没有问题，但是客户端连接服务器的时候，报：
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
alert中只有如下报错：
ORA-00942: table or view does not exist

tnsname.ora内容如下：
ISAP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.163.160 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ISAP)
    )
  )
service_name为：
SQL> show parameter service_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ISAP

为什么会报
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
这个错，现在导致这个新装的双机无法投入生产，麻烦帮忙解决下，多谢了

Maclean Liu(刘相兵

action plan:

在server段

cat $ORACLE_HOME/network/admin/sqlnet.ora
cat $ORACLE_HOME/network/admin/tnsnames.ora
cat $ORACLE_HOME/network/admin/listener.ora
cat $ORACLE_HOME/network/admin/endpoints_listener.ora

lsnrctl status 监听器名字
lsnrctl service 监听器名字

fengyunsen

可恶的TNS-125**，最近也给这东西搞得烦死

lai

oracle@FJ:~> cat $ORACLE_HOME/network/admin/sqlnet.ora | grep -v ^#

NAMES.DIRECTORY_PATH= (TNSNAMES)

oracle@FJ:~> cat $ORACLE_HOME/network/admin/tnsnames.ora | grep -v ^#

ISAP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.163.160 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ISAP)
    )
  )
oracle@FJ:~> cat $ORACLE_HOME/network/admin/listener.ora | grep -v ^#

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.163.160)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
        (GLOBAL_DBNAME = ora11g)
        (ORACLE_HOME = /opt/oracle/product/11g/db)
        (SID_NAME = ISAP)
        )
        )

没有$ORACLE_HOME/network/admin/endpoints_listener.ora文件

lai

oracle@FJXM-DM-PTLDB4:~> lsnrctl status isap

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 10-FEB-2012 16:12:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.46.163.160)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ISAP)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                19-JAN-2012 21:18:05
Uptime                    21 days 18 hr. 54 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/11g/db/network/admin/listener.ora
Listener Log File         /opt/oracle/product/11g/db/log/diag/tnslsnr/FJXM-DM-PTLDB4/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.46.163.160)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ISAP", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@FJXM-DM-PTLDB4:~> lsnrctl service isap

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 10-FEB-2012 16:12:40

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.46.163.160)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ISAP)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ISAP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

Maclean Liu(刘相兵

SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
        (GLOBAL_DBNAME = ora11g)
        (ORACLE_HOME = /opt/oracle/product/11g/db)
        (SID_NAME = ISAP)
        )
        )



把GLOBAL_DBNAME 改成 ISAP 然后  重启下监听

TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

找不到合适的 service ， 你的监听里确实没有静态的ISAP这个服务名

lai

问题已解决，谢谢！！！

aaaaaaaa2000

将GLOBAL_DBNAME = ora11g换成isap是什么意思？静态注册了这个？
好像ocm联系中有这个呵呵