Oracle中service_name和service_names的关系是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。从oracle9i开始，后台进程PMON自动在监听器中注册初始化参数SERVICE_NAMES中定义的服务名，SERVICE_NAMES默认为db_name.db_dimain。客户端tns配置中SERVICE_NAME的名称必须是SERVICE_NAMES或其中的一个NAME。1 listenter.ora文件中的 GLOBAL_DBNAME和tnsnames.ora文件SERVICE_NAME的配置一样时，GLOBAL_DBNAME = A, 连接串中的SERVICE_NAME = A,此时就可以连接上数据库。根据tnsnames.ora文件中的连接串中的SERVICE_NAME = A 找到监听器listener.ora中的SID_DESC = (GLOBAL_DBNAME= A)，之后在找到SID_NAME, 来连接到数据库实例。2listenter.ora文件中的 GLOBAL_DBNAME和tnsnames.ora文件的SE开发云主机域名RVICE_NAME的配置一样时，tnsnames.ora中的SERVICE_NAME要与参数文件中的SERVICE_NAMES 参数一样或者是其中之一，否则无法通过tns连接到数据库。tnsnames.ora文件中的service_name 和 listener.oar文件中的GLOBAL_DBNAME一样的情况：查看tnsnames.ora文件的配置信息：oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>cat tnsnames.ora# tnsnames.ora Network ConfiguratiON File: /opt/oracle/11.2.0/alifpre/network/admin/tnsnames.ora# Generated by Oracle configuration tools.YANGDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb) ) )YANGDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb1) ) )YANGDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb2) ) )YANGDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb3) ) )RAC = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.200)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = rac) ) )监听文件中的配置信息：oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>cat listener.ora# listener.ora Network Configuration File: /opt/oracle/11.2.0/alifpre/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/11.2.0/alifpre) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = yangdb) (ORACLE_HOME = /opt/oracle/11.2.0/alifpre) (SID_NAME = yangdb) ) )yangdb = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) ) )ADR_BASE_LISTENER = /opt/oracle参数service_names是静态的，必须重启机器才能够生效。oracle@rac3:/home/oracle>sqlplus “/as sysdba”SYS@yangdb-rac3> alter system set service_names=’yangdb,yangdb1,yangdb2,yangdb3′ scope=both;System altered.SYS@yangdb-rac3> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@yangdb-rac3> startupORACLE instance started.Database mounted.Database opened.SYS@yangdb-rac3> show parameter service_namesNAME TYPE VALUE———————————— ———– —————————-开发云主机域名—service_names string yangdb,yangdb1,yangdb2,yangdb3进行连接测试：oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>sqlplus yang/yang@yangdb1SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 11:12:13 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.YANG@yangdb-rac3> show parameter service_NAME TYPE VALUE———————————— ———– ——————————service_names string yangdb,yangdb1,yangdb2,yangdb3YANG@yangdb-rac3> exitoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>sqlplus yang/yang@yangdb2SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 11:57:26 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.YANG@yangdb-rac3> show parameter service_NAME TYPE VALUE———————————— ———– ——————————service_names string yangdb,yangdb1,yangdb2,yangdb3YANG@yangdb-rac3> exitoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>sqlplus yang/yang@yangdb3SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 11:57:38 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:YANG@yangdb-rac3> show parameter service_NAME TYPE VALUE———————————— ———– ——————————service_names string yangdb,yangdb1,yangdb2,yangdb3YANG@yangdb-rac3> exit查看监听服务状态，可以知道共有6个监听服务名。oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>lsnrctl serviceLSNRCTL for Linux: Version 11.2.0.1.0 – Production on 20-OCT-2011 11:59:11Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))Services Summary…Service “PLSExtProc” has 1 instance(s). Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:0 refused:0 LOCAL SERVERService “yangdb” has 2 instance(s). Instance “yangdb”, status UNKNOWN, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:0 refused:0 LOCAL SERVER Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdb1” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdb2” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdb3” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdbXDB” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “D000” established:0 refused:0 current:0 max:1022 state:ready D开发云主机域名ISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=rac3)(PORT=45357))The command completed successfullytnsnames.ora文件中的service_name 和 参数文件中的service_name不一样的情况：oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>vi tnsnames.ora# tnsnames.ora Network Configuration File: /opt/oracle/11.2.0/alifpre/network/admin/tnsnames.ora# Generated by Oracle configuration tools.lily = —-连接标识，可以为任意值 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lily) ) )YANGDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb1) ) )YANGDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb2) ) )YANGDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb3) ) )“tnsnames.ora” 60L, 1198C 已写入 oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>vi listener.ora# listener.ora Network Configuration File: /opt/oracle/11.2.0/alifpre/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/11.2.0/alifpre) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = lily) (ORACLE_HOME = /opt/oracle/11.2.0/alifpre) (SID_NAME = yangdb) ) )yangdb = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) ) )ADR_BASE_LISTENER = /opt/oracleoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 – Production on 20-OCT-2011 10:58:30Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 27-SEP-2011 21:47:16Uptime 22 days 13 hr. 11 min. 14 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/oracle/11.2.0/alifpre/network/admin/listener.oraListener Log File /opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xmlListening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3)(PORT=1521)))Services Summary…Service “PLSExtProc” has 1 instance(s). Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…Service “yangdb” has 2 instance(s). Instance “yangdb”, status UNKNOWN, has 1 handler(s) for this service… Instance “yangdb”, status READY, has 1 handler(s) for this service…Service “yangdb1” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service…Service “yangdb2” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service…Service “yangdb3” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service…Service “yangdbXDB” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service…The command completed successfullyoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>lsnrctl serviceLSNRCTL for Linux: Version 11.2.0.1.0 – Production on 20-OCT-2011 10:58:37Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))Services Summary…Service “PLSExtProc” has 1 instance(s). Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:0 refused:0 LOCAL SERVERService “yangdb” has 2 instance(s). Instance “yangdb”, status UNKNOWN, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:0 refused:0 LOCAL SERVER Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdb1” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdb2” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdb3” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “DEDICATED” established:4 refused:0 state:ready LOCAL SERVERService “yangdbXDB” has 1 instance(s). Instance “yangdb”, status READY, has 1 handler(s) for this service… Handler(s): “D000” established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=rac3)(PORT=45357))The command completed successfullyoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>lsnrctl reloadLSNRCTL for Linux: Version 11.2.0.1.0 – Production on 20-OCT-2011 10:58:45Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))The command completed successfullyoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>更改tnsnames.ora文件中的service_name的值 yangdb为lily，并相应的修改listener.ora文件中的global_dbname的值为lily ，也可以成功连接数据库。oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>vi tnsnames.ora# tnsnames.ora Network Configuration File: /opt/oracle/11.2.0/alifpre/network/admin/tnsnames.ora# Generated by Oracle configuration tools.lily = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lily) ) )YANGDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb1) ) )YANGDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb2) ) )YANGDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb3) ) )“tnsnames.ora” 60L, 1196C 已写入oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>sqlplus yang/yang@lilySQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 11:00:11 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsYANG@yangdb-rac3> show parameter service_ NAME TYPE VALUE———————————— ———– ——————————service_names string yangdb,yangdb1,yangdb2,yangdb3 –动态监听里面没有lily 这样的服务YANG@yangdb-rac3> exit说明：如果 参数文件中的service_names没有lily这个值，但是listener.ora文件中有与之对应的GLOBAL_DBNAME，并且已经启动了静态监听服务，则sqlplus通过@lily也可以连接数据库。如果没有启动静态监听，即没有lily 这样的service,则不可以连接数据库，(当然修改过之后没有执行reload 或没有重启监听，则还可以连接，因为之前存在，这样的例子不讨论)oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>vi listener.ora # listener.ora Network Configuration File: /opt/oracle/11.2.0/alifpre/network/admin/listener.ora# Generated by Oracle configuration tools.# SID_LIST_LISTENER =# (SID_LIST =# (SID_DESC =# (SID_NAME = PLSExtProc)# (ORACLE_HOME = /opt/oracle/11.2.0/alifpre)# (PROGRAM = extproc)# )# (SID_DESC =# (GLOBAL_DBNAME = lily)# (ORACLE_HOME = /opt/oracle/11.2.0/alifpre)# (SID_NAME = yangdb)# )# )yangdb = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3)(PORT = 1521)) ) )ADR_BASE_LISTENER = /opt/oracle 编辑过之后重新启动数据库（可选），重启监听，目的重新注册服务，去掉静态监听的服务。SYS@yangdb-rac3> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@yangdb-rac3>startupDatabase mounted.Database opened.SYS@yangdb-rac3>oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>lsnrctl startLSNRCTL for Linux: Version 11.2.0.1.0 – Production on 20-OCT-2011 11:11:24Copyright (c) 1991, 2009, Oracle. All rights reserved.Starting /opt/oracle/11.2.0/alifpre/bin/tnslsnr: please wait…TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionSystem parameter file is /opt/oracle/11.2.0/alifpre/network/admin/listener.oraLog messages written to /opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3)(PORT=1521)))Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 20-OCT-2011 11:11:24Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/oracle/11.2.0/alifpre/network/admin/listener.oraListener Log File /opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xmlListening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3)(PORT=1521)))The listener supports no services –没有静态监听服务，这时只能通过动态监听的服务来连接到数据库。The command completed successfullyoracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>sqlplus yang/yang@lilySQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 11:11:35 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.ERROR:ORA-12514: TNS:listener does not currently know of service requested in connectdescriptor –报错，监听没有lily这样的服务Enter user-name: ^[[–通过pmon动态监听的服务名来连接数据库。oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>sqlplus yang/yang@yangdb1SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 11:12:13 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsYANG@yangdb-rac3> show parameter service_NAME TYPE VALUE———————————— ———– ——————————service_names string yangdb,yangdb1,yangdb2,yangdb3看完上述内容，你们掌握Oracle中service_name和service_names的关系是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注开发云行业资讯频道，感谢各位的阅读！
本文从转载，原作者保留一切权利，若侵权请联系删除。
《Oracle中service_name和service_names的关系是什么》来自互联网同行内容，若有侵权，请联系我们删除！
