Thursday, December 4, 2008

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

This can occur when you directly connecting to database or when you try to access remote database using dblink.

When you encounter this error first thing we should collect the following information from init.ora file.

1.DB_NAME

2.DB_DOMAIN

3.SERVICE_NAME

assume that your DB_NAME value is “smplorcl” ,DB_DOMAIN is “world” and SERVICE_NAME value is “smplorcl.world”


above values also can be obtained using show parameter command from SQLPLUS


Ex: sql> show parameter DB_NAME

Next you need to find out the current services registered with listener.

Lsnrctl services

LSNRCTL> services listener

1. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1545))

2. Services Summary...

3 .Service "smplorcl" has 1 instance(s).

4. Instance "smplorcl", status READY, has 1 handler(s) for this service...

5. Handler(s):

6. "DEDICATED" established:0 refused:0 state:ready

7. LOCAL SERVER

8. Service "smplorcl.world" has 1 instance(s).

9. Instance "smplorcl", status READY, has 1 handler(s) for this service...

10. Handler(s):

11."DEDICATED" established:0 refused:0 state:ready

12.LOCAL SERVER

Note : I have given lines numbers for each lane in listener services output.

Now compare your service name value you have obtained from init.ora is matching with lane number 8 value . If not

you need to make changes to init.ora or tnsnames.ora (if your init.ora has right service_name then you change your tnsnames.ora SERVICE_NAME parameter value or Vice versa)


this error also can occur when yo accessing remote databases using dblibk. To verify tnsname alias first test the tnsname alias by using it to connect in SQLPLUS directly . If you still get this error , that means you have issues with your tnsnames, first correct tnsnames issue.


If your SQLPLUS connection is successful then you need to verify db_link connection parameter values

query dba_db_links database table.

Select db_link,host from dba_db_links;  from the above output verify your host value is identical to your remote 
TNSNAMES ALIAS parameter values, verify the domain reference also , this should include “world” part in 
the domain name
ex: smplorcl.world.   Also try restarting listener and give some time for listener to register service names .

Hope my notes help you resolving ORA-12514 error.

Reference document URL. 

http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm

0 comments: