The dreaded ORA-12154: TNS:could not resolve the connect identifier specified.

Introduction

Actually  I wanted to start this one with  … and a funny thing happened on the way to the circus , but lets save that one for another occasion okay ? Last week  I got a mail from one of the users  who tried to connect  via ezconnect to an existing database from new client that i had set up for him.  AS always a challenge to see what is going on and of course it takes time to find out the real deal.

Details:

## This case occurred in a 11.2 environment on one of the test boxes . They were trying to connect via ezconnect to one of the existing databases which failed:

 [10:30:23] [ INFO] SQL Runner: Starting runing script on database SCOTT/SCOTT@MYSRVR1:1521/MYDB1
[10:30:26] [ INFO] INPUT> ERROR:
[10:30:26] [ INFO] INPUT> ORA-12154: TNS:could not resolve the connect identifier specified
[10:30:26] [ INFO] INPUT>
[10:30:26] [ INFO] INPUT>
[10:30:26] [ INFO] INPUT> SP2-0306: Invalid option.
[10:30:26] [ INFO] INPUT> Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
[10:30:26] [ INFO] INPUT> where  ::= [/][@<connect_identifier>]
[10:30:26] [ INFO] INPUT>        ::= [][/][@<connect_identifier>]
[10:30:26] [ INFO] INPUT> ERROR:
[10:30:26] [ INFO] INPUT> ORA-12162: TNS:net service name is incorrectly specified
[10:30:26] [ INFO] INPUT>
[10:30:26] [ INFO] INPUT>

##However a  Tnsping  was working correctly

tnsping MYDB1
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 10-FEB-2014 10:31:14
 Copyright (c) 1997, 2011, Oracle.  All rights reserved.
 Used parameter files:
/opt/SP/STORAGE/TNS_ADMIN/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MYSRVR1.vfnl.dc-ratingen.de)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=MYDB1.test.nl)))
OK (130 msec)

## Since this was a 11.2 test environment I was able to play a bit with the environment. What puzzled me most  was that  a different database showed both the short service name and the fully qualified name of the service ( with the domain name in it ) in the listener. And when added the short  service name  MYDB1  next to the qualified service name  MYDB1.test.nl  listener would still not pick up both  services , even when I read  that pmon was supposed to register the services automatically  in frequent intervals (60 seconds) .

Added some entries to my tnsnames.ora and started testing . And indeed the full service name worked  and the short service refused to !  Performed the test that I bounced the database ( again this was test so not that much harm done) with no better effect . Even the restart of listener  did not bring the solution.

So it was clear that  I needed to see what was different between the two environments  since I had one other database automatically registered in the listener  with both the services I was looking for.

Bottom-line  after investigation is it works now after the restart of the database and setting some parts different.  🙂   Lets check .

Oh and I performed three  actions in the database  to make it work. And yes the database and not the listener cause  the 11.2 environment lets the database register the services automatically with a listener  (well as long as one plays by the rules):

##First I  added the short service name to the database ( this is not a Rac environment so I did not set up a service in the clusterware using srvctl  ( and ok  I admit it i tried and clusterware replied that you cannot add a service with the same name as the database).

T his is how my services look now:

SQL> show parameter  service

NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names                                      string               MYDB1.test.nl, MYDB1

##Made sure that if the local listener is in place it is pointing to the correct listener

## Just wanted to make sure that  the correct listener would be used so in this test i added both my listeners:

SQL>show parameter  listener
NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks                                string
local_listener                                         string               (DESCRIPTION=(ADDRESS_LIST=(AD
                                                                                          DRESS=(PROTOCOL=TCP)(HOST=195.
                                                                                          233.124.139)(PORT=1522))(ADDRE
                                                                                          SS=(PROTOCOL=TCP)(HOST=195.233
                                                                                          .124.139)(PORT=1521))))
remote_listener                                    string

## And yes the  Domain was set:

SQL> show parameter domain
 NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain                                             string               test.nl

## The environment that was working did not have set the db_domain parameter so in this test I removed it too:  It is now

SQL> show parameter domain
 NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain                                             string

Now the listener ,  it is showing both services :

 Service "MYDB1" has 1 instance(s).
  Instance "MYDB1", status READY, has 6 handler(s) for this service...
Service "MYDB1.test.nl" has 1 instance(s).
  Instance "MYDB1", status READY, has 6 handler(s) for this service...

I tested it myself with two entries in tnsnames ( short name and long one and both work

MYDB1MBK1 =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = MYSRVR1.vfnl.dc-ratingen.de)(PORT = 1522))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = MYDB1.test.nl)
     )
   )

 MYDB1MBK2 =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = MYSRVR1.vfnl.dc-ratingen.de)(PORT = 1522))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = MYDB1)
     )

This workaround worked so I informed customer to test it.  Will have to test on another environment the effect if i keep db_domain ( and of course db_unique_name) and leave the services to blank.

Happy reading ,

Mathijs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s