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
Mathijs Bruggink 