A User reported ORA-12514 error from JDBC application connecting to RAC database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
To troubleshoot it,
a)I checked following things to ensure specified service is running
srvctl status service -d dbname -s serv_name
b) Ensured service is registered with listener
Note that if service has domain name like *.us.oracle.com then TNS entry should also have same service name.
Service "orcl.us.oracle.com" has 1 instance(s). Instance "orcl1", status READY, has 1 handler(s) for this service...
Please note that tnsping only helps to check what tns entry is being used (in case multiple entries are present ) and whether the listener is up. It doesn't check if database being connected to is up/down.
Tip: In case tnsping utility hangs you can try doing telnet hostname port. If this hangs too, then it could be firewall issue
Coming back to issue. I decided to test if I can connect to database using same TNS entry. Tnsnames.ora has issues if there are extra spaces. I added tns entry in same db host and tested it and I was able to login.
Since user was still not able to connect I decided to use some other machine and check if I can connect to db ( I didnt had access on application host and we had already checked that it was not firewall issue)
Trying from different host gave me dreaded error "ORA-12514"
I did quick check of VIP address using ping/nslookup (nslookup node1-vip) utility and found that node1-vip ip was same as node2-vip in DNS. Service was running on node1-vip. Modifying the DNS resolved the connection issue.
In case you are wondering why I didn't get this error earlier.This is because name resolution was being done by /etc/hosts on database host which had correct entries for node1-vip.