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.