In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >
Share
Shulou(Shulou.com)06/01 Report--
What is the relationship between service_name and service_names in Oracle? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
Starting from oracle9i, the background process PMON automatically registers the service name defined in the initialization parameter SERVICE_NAMES with the listener, and SERVICE_ names defaults to db_name.db_dimain. The name of the SERVICE_NAME in the client tns configuration must be SERVICE_NAMES or one of the NAME.
1 when the configuration of GLOBAL_DBNAME in listenter.ora file is the same as that of tnsnames.ora file SERVICE_NAME, GLOBAL_DBNAME = An and SERVICE_NAME = An in the connection string, you can connect to the database at this time. According to the SERVICE_NAME = An in the connection string in the tnsnames.ora file, find the SID_DESC = (GLOBAL_DBNAME= A) in the listener listener.ora, and then find SID_NAME to connect to the database instance.
When the configuration of the GLOBAL_DBNAME in the 2listenter.ora file is the same as the SERVICE_NAME in the tnsnames.ora file, the SERVICE_NAME in the tnsnames.ora must be the same or one of the SERVICE_NAMES parameters in the parameter file, otherwise you cannot connect to the database through tns.
Service_name in the tnsnames.ora file is the same as GLOBAL_DBNAME in the listener.oar file:
View the configuration information for the tnsnames.ora file:
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)
)
)
Listen for configuration information in the file:
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
The parameter service_names is static and must be restarted to take effect.
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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@yangdb-rac3 > startup
ORACLE instance started.
Database mounted.
Database opened.
SYS@yangdb-rac3 > show parameter service_names
NAME TYPE VALUE
-
Service_names string yangdb,yangdb1,yangdb2,yangdb3
Conduct a connectivity test:
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > sqlplus yang/yang@yangdb1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 2011: 12:13 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
YANG@yangdb-rac3 > show parameter service_
NAME TYPE VALUE
-
Service_names string yangdb,yangdb1,yangdb2,yangdb3
YANG@yangdb-rac3 > exit
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin >
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > sqlplus yang/yang@yangdb2
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 2011: 57:26 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
YANG@yangdb-rac3 > show parameter service_
NAME TYPE VALUE
-
Service_names string yangdb,yangdb1,yangdb2,yangdb3
YANG@yangdb-rac3 > exit
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > sqlplus yang/yang@yangdb3
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 2011: 57:38 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
YANG@yangdb-rac3 > show parameter service_
NAME TYPE VALUE
-
Service_names string yangdb,yangdb1,yangdb2,yangdb3
YANG@yangdb-rac3 > exit
Check the status of the monitoring service, you can see that there are a total of 6 monitoring service names.
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl service
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 20-OCT-2011 11:59:11
Copyright (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 SERVER
Service "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 SERVER
Service "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 SERVER
Service "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 SERVER
Service "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 SERVER
Service "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 successfully
The service_name in the tnsnames.ora file is different from the service_name in the parameter file:
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 =-connection ID, which can be any value
(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 has been written
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
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 20-OCT-2011 10:58:30
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS= (PROTOCOL=tcp) (HOST=) (PORT=1521))
STATUS of the LISTENER
-
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 27-SEP-2011 21:47:16
Uptime 22 days 13 hr. 11 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File / opt/oracle/11.2.0/alifpre/network/admin/listener.ora
Listener Log File / opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xml
Listening 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 successfully
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl service
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 20-OCT-2011 10:58:37
Copyright (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 SERVER
Service "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 SERVER
Service "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 SERVER
Service "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 SERVER
Service "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 SERVER
Service "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 successfully
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 20-OCT-2011 10:58:45
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS= (PROTOCOL=tcp) (HOST=) (PORT=1521))
The command completed successfully
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin >
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin >
You can also successfully connect to the database by changing the value yangdb of service_name in the tnsnames.ora file to lily and modifying the value of global_dbname in the listener.ora file to lily accordingly.
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 has been written
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > sqlplus yang/yang@lily
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 2011: 00:11 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
YANG@yangdb-rac3 > show parameter service_
NAME TYPE VALUE
-
Service_names string yangdb,yangdb1,yangdb2,yangdb3-there is no service like lily in dynamic monitoring.
YANG@yangdb-rac3 > exit
Note: if the service_names in the parameter file does not have the value of lily, but there is a corresponding GLOBAL_DBNAME in the listener.ora file, and the static listening service has been started, then sqlplus can also connect to the database through @ lily.
If static snooping is not enabled, that is, if there is no service like lily, you cannot connect to the database. (of course, if you do not perform reload or restart snooping after modification, you can still connect, because it existed before and such examples are not discussed)
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
After editing, restart the database (optional), restart listening, in order to re-register the service, remove the static monitoring service.
SYS@yangdb-rac3 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@yangdb-rac3 > startup
Database mounted.
Database opened.
SYS@yangdb-rac3 >
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 20-OCT-2011 11:11:24
Copyright (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-Production
System parameter file is / opt/oracle/11.2.0/alifpre/network/admin/listener.ora
Log messages written to / opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xml
Listening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=rac3) (PORT=1521)
Connecting to (ADDRESS= (PROTOCOL=tcp) (HOST=) (PORT=1521))
STATUS of the LISTENER
-
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 20-OCT-2011 11:11:24
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File / opt/oracle/11.2.0/alifpre/network/admin/listener.ora
Listener Log File / opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=rac3) (PORT=1521))
The listener supports no services-there is no static listening service, so you can only connect to the database through a dynamic listening service.
The command completed successfully
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin >
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > sqlplus yang/yang@lily
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 2011: 11:35 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
Descriptor-error reporting. There is no service like lily for monitoring.
Enter user-name: ^ [
Connect to the database through the service name of pmon dynamic snooping.
Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > sqlplus yang/yang@yangdb1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 2011: 12:13 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
YANG@yangdb-rac3 > show parameter service_
NAME TYPE VALUE
-
Service_names string yangdb,yangdb1,yangdb2,yangdb3
After reading the above, have you mastered the relationship between service_name and service_names in Oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.