Wednesday, November 2, 2011

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

POSSIBLE CAUSE

Your TNS Entry is reachable to the Database listener. But SERVICE_NAME specified in the TNS Entry is not reachable or not Contactable
Begin by looking at your tnsnames.ora. you will a setting like (SERVICE_NAME=<name>)

Note: service name will be registered in the listener as DB_NAME.DOMAIN_NAME.

Example of tns entry with service name

bhu =
(DESCRIPTION =
  (ADDRESS=(PROTOCOL=TCP)(HOST=BHUVANRACA-SCAN)(PORT=1526))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=bhuvanrac))
)

1)   Check whether specified service_name is correct in the database

SQL> Show parameter service_name

ADD SERVICE IN RAC (Oracle 11gR2)

$ srvctl add service -d bhu_a -s bhuvanrac -r bhu_1 -a bhu_2 -l PRIMARY -q FALSE -e NONE -m NONE -w 0 -z 0

ADD SERVICE IN SINGLE INSTANCE DATABASE

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'bhuvan', NETWORK_NAME=>'bhuvan');
SQL> exec dbms_service.START_SERVICE('bhuvan');
SQL> show parameter service

Note: you can have multiple service_name for a single database. if you have already a service, this will come second.

2)      Check the specified service is listening in the LISTERNER

$ lsnrctl service LSNR_DGA_ZS2

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-NOV-2011 21:13:42
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_DGA_BHU)))
Services Summary...
Service "bhuvanrac " has 1 instance(s).
  Instance "bhu_1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:47 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

3)      Check the status of the LISTENER

$ lsnrctl status LSNR_DGA_BHU
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-NOV-2011 21:17:37
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_DGA_BHU)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_DGA_BHU
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                12-OCT-2011 17:18:01
Uptime                    21 days 4 hr. 59 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
(REMOVED SOME LINES)
Service "oraclerac " has 1 instance(s).
  Instance "BHU_1", status READY, has 1 handler(s) for this service...
The command completed successfully

4) If you are running on RAC environment and having a second IP Configured. Check whether you have properly configured(Entry, server details & port number) with the LOCAL_LISTENER, REMOTE_LISTENER & LISTENER_NETWORKS.

5) Check your LISTENER log for more details.

SOLUTION

1)      if the service is not register with the listener, you can register it
Log in to oracle as sys user
SQL> alter system register;

2)      if the service is not start
RAC Environment
$ crsctl start res <service_name>    √† 11gR2
Or
$ srvctl start service –d <DB_NAME> -s <SERVICE_NAME>

In Single Instance Environment
SQL> exec dbms_service.START_SERVICE('bhuvan');


if it is related to ORA-12514 + SID

ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

solution: you need to dynamically register the SID in the listener.ora file
Ex:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
        (ORACLE_HOME = /u01/oracle/product/11.2.0.4)
        (SID_NAME = BHUVAN)
    )
  )

some environment will have the global_dbname as well; if you have, you can use the below one
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)
Hope this documents helps you in resolving your problem.

No comments:

Post a Comment