I like to spend time with Oracle DB just as much time I spend with SQL Server, but we exclusively use SQL Server at our work so I am not getting enough time with Oracle anymore. And another reason is it with Oracle there are way too many variable to consider when you are troubleshooting something. So when couple of days back when I boot up my VM of Oracle, I ran into this strange error and I was genuinely surprised to find how minute the cause could be.
So, this how the story goes …. after booting my VM, I started SQL PLUS utility (it is kind of SQLCMD equivalent of SQL Server, it is CLI to access Oracle DB to be exact). When I was asked to provide login credentials, when I provided ‘em I was greeted with following error message,
ORA-12560: TNS: protocol adapter error
If you ask any Oracle guy (or gal), probably it would mean to them is Oracle TNS listener is not running. How I understand,TNS Listener is basically a service that listens for incoming connection requests for database and the connected those requests to appropriate database instance (kind of same as SQL Server Browser). So I thought that it is just matter of starting that service. But I found that service is already running !!
Another suggestion over internet was to make sure that PATH variable has your oracle home directory in it. Which was also present in my case. I even disabled firewall (I know, for local connection that should not matter … but I still did it). This user that was trying access database was SYS, so it was definitely not an issue of privileges.
And weirdest past was, I had installed TOAD community edition and it was perfectly able to connect to my instance. But issue was, because of this error … I was not able to make Oracle DB connection from my Informatica.
Problem with troubleshooting Oracle issue is … If you are not 100% good at Oracle, then sometimes it is very difficult to find even help about Oracle. mostly because half of the time you will not understand a word . So after searching Google, I got lucky (yes, I would say it was 99% luck … and 1% my ability to skim through help quickly ). I stumbled upon this post, and in it one of the suggestion was to make sure that PATH variable need to set in correct order. More importantly, it explains that if you have Oracle Client installed in same machine (which was true in my case, since it is only for me to play with it), then you have to make sure that your ORACLE DB Home directory is listed before your path of Oracle Client Home !! … And then it hit me that I did installed client recently, couple of days AFTER I installed Oracle Server. And oddly, Oracle Client install blindly just adds PATH of Client Home in front of everything (I guess, because in real life you never have both of them installed on same machine). And that was the issue. So once I moved path of Oracle Client home directory to after Oracle Database home directory, that error simply went away.
So lesson learned from this experience … there is so many variables that can effect Oracle in many ways and you just can’t know them all, so always try ALL solutions.
That’s it for now …
It’s Just A Thought …