SERVICE_NAME Parameter - Resolving The ORA-12514 Error [ID 77640.1]

ygzhou518發表於2011-12-04
Error

Applies to:

Oracle Net Services - Version: 8.1.7.4.0 to 11.1.0.6.0 - Release: 8.1.7 to 11.1Information in this document applies to any platform.
Checked for relevance on 12-DEC-2007

Purpose

The purpose of this document is to clarify the use of the SERVICE_NAME parameter in the TNSNAMES.ORA and outline the correct values for the parameter and their source. Additionally, to provide a solution for the ORA-12514 error. 

Scope and Application

This document explains current usage of the term SERVICE_NAME, differentiates this from its meaning in previous versions, and clarifies NET SERVICE NAME (SID =) vs. SERVICE_NAME = in tnsnames.ora file. 

SERVICE_NAME Parameter - Resolving The ORA-12514 Error

SERVICE_NAME parameter in Oracle

Beginning with Oracle version 8i, TNS connect strings use the SERVICE_NAME parameter in place of the older pre-8i "SID=" clause. When set incorrectly, this parameter can cause a new error:

"ORA-12514 :TNS:listener could not resolve SERVICE_NAME given in connect descriptor" 

During the creation of a tnsnames entry, the NetCA and NetMGR tools will prompt for a an Oracle8i or later Service Name. The value supplied will be set to SERVICE_NAME in the CONNECT_DATA section of the tnsnames.ora file entry or connect string. 

This value must match the SERVICE_NAMES parameter in the pfile or spfile. If not explicitly set, SERVICE_NAMES will be derived from DB_NAME.DB_DOMAIN. 

When the instance is dynamically registered, the Service will show up in the lsnrctl services output using both DB_NAME.DB_DOMAIN (provided DB_DOMAIN is not null). 

If the database service is statically configured in the listener.ora file, the GLOBAL_DBNAME value should be set to match SERVICE_NAME.

For dynamic instance registration

Use the values of the parameters existing in the pfile or spfile, namely DB_NAME and DB_DOMAIN to determine the value of the SERVICE_NAME that should be used in the TNSNAMES.ORA. The valid construction of this value is DB_NAME.DB_DOMAIN with the dot "." separating the two pfile parameters. This will be equal to the pfile parameter SERVICE_NAMES. 

For example: 
Show parameter SERVICE_NAMES might yield: 

orcl.oracle.com 

This would be true if: 
Show parameter DB_NAME yielded "orcl" and show parameter DB_DOMAIN yielded "oracle.com. 

Example of how dynamic registration looks: 

If your pfile SERVICE_NAMES value is orcl.oracle.com, your tnsnames.ora file 
entry would show a CONNECT_DATA section like this: 

Then Tnsnames.ora entry is:

(CONNECT_DATA = 
(SERVICE_NAME = orcl.oracle.com) 

Your lsnrctl services output ought to show:

$ lsnrctl services 

LSNRCTL for Solaris: Version 10.2.0.3.0 - Production on 19-FEB-2008 11:39:26   
Copyright (c) 1991, 2004, Oracle.  All rights reserved.  

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)) 
STATUS of the LISTENER 
------------------------ 
Alias LISTENER 

Services Summary... 
Service "orcl.oracle.com" has 1 instance(s).    
  Instance "orcl", status READY, has 1 handler(s) for this service...    
    Handler(s):   
      "DEDICATED" established:2 refused:0 state:ready   
         LOCAL SERVER   
The command completed successfully 

Note that if the tnsnames.ora file SERVICE_NAME value doesn't match the name of the registered "Service", this connection would fail with an ORA-12514 error.

Note:  It is often necessary to set the pfile setting  LOCAL_LISTENER in order to get PMON to register the instance properly. 

e.g. 

alter system set LOCAL_LISTENER="(address=(protocol=TCP)(host=yourhost)(port=1521))" scope=both;

For a statically configured SID_DESC in the listener.ora 

Use the GLOBAL_DBNAME parameter in the LISTENER.ORA for each SID that you 
wish to identify as a separate service. Use the value of this parameter as 
the value of the SERVICE_NAME parameter. Of course, any changes made to the 
LISTENER.ORA to accomplish this will need to be made active by stopping and 
then restarting the listener process. 

Example of static configuration for orcl.oracle.com: 

SID_LIST_LISTENER= 
  (SID_LIST= 
    (SID_DESC= 
     (SID_NAME=orcl) 
     (GLOBAL_DBNAME=orcl.oracle.com) 
    ) 
  ) 

GLOBAL_DBNAME matches the tnsnames.ora file entry for SERVICE_NAME. 
If it does not, a connection to this handler would throw an ORA-12514 error.

SUMMARY

The following pfile/spfile parameters are important when setting the value for SERVICE_NAME in the tnsnames.ora file: 

DB_NAME 
DB_DOMAIN 
SERVICE_NAMES 

If using statically defined SIDs in the listener.ora file under SID_DESC, the GLOBAL_DBNAME parameter (within SID_DESC) must match the value set in the tnsnames.ora file for SERVICE_NAME.

References

NOTE:1014156.102 - ORA-12514: CONNECTING TO ORACLE 8I LISTENER
NOTE:437737.1 - Connections Via the Listener Fail With Either ORA-12505 or ORA-12514
NOTE:444705.1 - TroubleShooting Guide For ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor
NOTE:71924.1 - OERR: ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect d

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-712700/，如需轉載，請註明出處，否則將追究法律責任。

相關文章