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:
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.
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.
Show parameter SERVICE_NAMES might yield:
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:
Your lsnrctl services output ought to show:
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.
alter system set LOCAL_LISTENER="(address=(protocol=TCP)(host=yourhost)(port=1521))" scope=both;
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:
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.
The following pfile/spfile parameters are important when setting the value for SERVICE_NAME in the tnsnames.ora file:
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867586/viewspace-712700/，如需转载，请注明出处，否则将追究法律责任。