转载 ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

2014-07-29 12:35:47 呆瓜呆呆 阅读数 45611更多

分类专栏： oracle 11g ORA-12504

 

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/ch7543658/article/details/38265621

在配置oracle监听的时候竟然报了错：ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

以下是我报错的配置：

listener.ora的配置：

1.  
   [oracle@sm2 admin]$ cat listener.ora
2.  
   LISTENER = (
3.  
   DESCRIPTION_LIST = (
4.  
   DESCRIPTION =
5.  
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521))
6.  
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
7.  
   )
8.  
   )
9.  
    
10.  
    SID_LIST_LISTENER = (
11.  
    SID_LIST = (
12.  
    SID_DESC =
13.  
    (GLOBAL_DBNAME = sm2)
14.  
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
15.  
    (SID_NAME=sm2)
16.  
    )
17.  
    )
18.  
    ADR_BASE_LISTENER = /u01/app/oracle

tnsname.ora的配置：

1.  
   [oracle@sm2 admin]$ cat tnsnames.ora
2.  
   sm2 = (
3.  
   DESCRIPTION = (
4.  
   ADDRESS_LIST = (
5.  
   ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521)
6.  
   )
7.  
   )
8.  
   (ONNECT_DATA =
9.  
   (SERVICE_NAME = sm2)
10.  
    (SERVER = DEDICATED)
11.  
    )
12.  
    )

监听能正常启动：

1.  
   [oracle@sm2 admin]$ lsnrctl status
2.  
    
3.  
   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-JUL-2014 12:33:23
4.  
    
5.  
   Copyright (c) 1991, 2011, Oracle. All rights reserved.
6.  
    
7.  
   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.137.82)(PORT=1521)))
8.  
   STATUS of the LISTENER
9.  
   ------------------------
10.  
    Alias LISTENER
11.  
    Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
12.  
    Start Date 29-JUL-2014 11:44:04
13.  
    Uptime 0 days 0 hr. 49 min. 19 sec
14.  
    Trace Level off
15.  
    Security ON: Local OS Authentication
16.  
    SNMP OFF
17.  
    Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
18.  
    Listener Log File /u01/app/oracle/diag/tnslsnr/sm2/listener/alert/log.xml
19.  
    Listening Endpoints Summary...
20.  
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.82)(PORT=1521)))
21.  
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
22.  
    Services Summary...
23.  
    Service "SM2XDB" has 1 instance(s).
24.  
    Instance "sm2", status READY, has 1 handler(s) for this service...
25.  
    Service "sm2" has 2 instance(s).
26.  
    Instance "sm2", status UNKNOWN, has 1 handler(s) for this service...
27.  
    Instance "sm2", status READY, has 1 handler(s) for this service...
28.  
    The command completed successfully

tnsping 能ping通：

1.  
   [oracle@sm2 admin]$ tnsping sm2
2.  
    
3.  
   TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-JUL-2014 12:27:30
4.  
    
5.  
   Copyright (c) 1997, 2011, Oracle. All rights reserved.
6.  
    
7.  
   Used parameter files:
8.  
   /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
9.  
    
10.  
     
11.  
    Used TNSNAMES adapter to resolve the alias
12.  
    Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521))) (ONNECT_DATA = (SERVICE_NAME = sm2) (SERVER = DEDICATED)))
13.  
    OK (20 msec)

使用sqlplus登陆时报错了：

1.  
   [oracle@sm2 admin]$ sqlplus sys/oracle@sm2 as sysdba
2.  
    
3.  
   SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 29 12:27:48 2014
4.  
    
5.  
   Copyright (c) 1982, 2011, Oracle. All rights reserved.
6.  
    
7.  
   ERROR:
8.  
   ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

解决办法：

说明：其实上面出现这样的情况是在tnsname.ora中配置错了，将tnsname.ora中的ADDRESS_LIST参数去掉就行了。

最终的tnsname.ora中的配置变为：

1.  
   [oracle@sm2 admin]$ cat tnsnames.ora
2.  
    
3.  
   sm2 = (
4.  
   DESCRIPTION = (
5.  
   ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521)
6.  
   )
7.  
   (CONNECT_DATA =
8.  
   (SERVER = DEDICATED)
9.  
   (SERVICE_NAME = sm2)
10.  
    )
11.  
    )

这样就行了

验证：

1.  
   [oracle@sm2 admin]$ sqlplus sys/oracle@sm2 as sysdba
2.  
    
3.  
   SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 29 12:39:45 2014
4.  
    
5.  
   Copyright (c) 1982, 2011, Oracle. All rights reserved.
6.  
    
7.  
    
8.  
   Connected to:
9.  
   Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
10.  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
11.  
     
12.  
    SQL>

就这个问题弄了我好久，必须记下来啊！~