24 August 2012

Oracle XE + Oracle Client = cannot "connect / as sysdba"

While having fun with some complicated (and likely illegal!) scenarios I won't go into now, I ended up this morning with an Oracle XE database to which I wanted to connect with the usual DBA routine:

sqlplus /nolog
connect / as sysdba
I kept getting the dreadful "ORA-12560: TNS:protocol adapter error" and couldn't understand why. Surely I didn't break it that much? The service was still up and I could connect regularly in every way except that.

It turns out this is what happens when you try to do too many things on the same machine. I had installed an Oracle Client on top, because I needed a few things from there (OleDB provider etc) and because other products are guaranteed to work with that client rather than XE.

The problem is that the sqlplus version that comes with the Client is older (!) than the one from XE, but because of how the Windows PATH system variable gets manipulated by the installer, the old version takes over and gives you this problem.

Solution? In your PATH, make sure the folder from the oraclexe directory comes before the one from any client you might have there. However, understand that this is likely to break your Client, so either make sure the change is temporary, or set it locally only when you need it (batch files!).

No comments: