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 