25 August, 2014

Oracle XE, ODP.NET and Oracle 9i in the same Windows XP machine

English: The logo of Oracle Corporation de:Bil...
English: The logo of Oracle Corporation de:Bild:Oracle-Logo.svg he:תמונה:Oracle Logo.jpg (Photo credit: Wikipedia)
Saturday night, 23-Aug-2014


For a couple of weeks now, I have been figuring out how to make Oracle 9i and Oracle XE work in the same machine. For a long time, I have been running Oracle 9i and ODP.NET, designing, developing and maintaining applications using the Oracle RDBMS. Only lately when I started working in a new company that I am having restricted access to databases that I had to resort to some local DB, even on my old XP machine. I have the disc space to begin with.

And having some access on some Oracle dB means I had to also create and maintain a local dB that is Oracle-based. In comes Oracle XE.

That is when I started experimenting with Oracle Express Edition, and with 11g as the latest, and with ODP.NET version of 10.x.x, did not think of having a compatibility issue, even then, since I was doing all these in a Windows XP-based unit.

Then I got the problem. I mean, I don’t know how it happened, but for a moment, after initially installing oracle XE, I was getting both local dB and data warehouse dB (using TNS) to work. I was very happy. And as in all installs, I did a reboot. And if in most cases, a reboot would rebuild and repair and renew settings and realign once again everything – as new – something now doesn’t work. I either would get the DW dB to be working, and Oracle XE dB to be broken, or vice-versa.
I did many things, like tweaking the Path variables, the 3 .ora files, even the TNS_ADMIN variable and value, pointing either to the Oracle 9i directory, or the Oracle XE, and like I said, I would either get one or the other, but not both. And in most cases, I get the DW dB which is using TNS, to be the one working. Doing a PING to XE would result in error, but TNSPING to XE would give a positive result. But both PING and TNSPING to 127.0.0.1 would give both a positive result.

As in previous issues and problems, I did a lot of searching. And this becomes complicated, because I have to wait until I get home then I will be able to look up ORA-12154, ORA-12514, ORA-12528, etc., etc., because internet access is also restricted! Bummer!

There were some suggestions that focused on setting up, if not already, TNS_ADMIN variable in the system variables, and assigning its value to the Oracle XE …\network\admin path, and I tried this one, adding, removing, changing, then putting it back to Oracle 9i …\network\admin path, and still, I would either get one or the other, but not both. And still the same, I would usually be having the DW dB working, but not the Oracle XE dB.

I should mention that for each trial, I am either uninstalling Oracle XE, at least, or I would uninstall all of Oracle XE, ODP.NET and Oracle 9i, in sequence. And in reinstalling, it is also in the same order: Oracle XE, then ODP.NET, and finally, Oracle 9i. I believe it is common knowledge for all who have tried it, that for ODP.NET to work with Oracle 9i, the installation should be in that sequence. This is more or less something to do with the final value of Path in the system variables, and even in the default Home selection for Oracle.

Also, after each uninstallation, I would run CCleaner to remove invalid registry items, after a reboot. And after running CCleaner, another reboot, to really be sure that I am starting clean.

After many trials and errors, I made it to work. I did a reboot, and I checked, and it is still working. Another reboot, and it is still working. I run CCleaner, to once again remove invalid registry items, then a reboot, and yes, I confirm that both data warehouse dB using TNS and Oracle XE dB are both working.

Monday morning, 25-Aug, when I came to work, I booted up my PC, tested the 2 dBs, and they are working. I did a reboot, just to be really sure that I am not dreaming, and yes, both are working.

What did I do to make it work?
  1. I removed the TNS_ADMIN setting from the System Variables. So it is now the individual .ora files being looked up and evaluated when I do a connection to each dB. (I think).
  2. After the installation, I did not touch the .ora files, which means that whatever value that was created by the installers, then that was it. No tweaking or whatever.
  3. The ODP.NET …\network\admin folder does not contain any .ora file. That’s a fact. It is just the dll file anyway that is required by .NET applications. Nada mas.
The day is now closing, and I have done quite a number of reboots – I am just very, very curious if the working condition will be sustained throughout the day. And yes, even as the day is already closing, I still get both data warehouse dB using TNS, and the local dB, using Oracle XE. I guess some times, you just keep your hands off things, and they will work on their own. They don’t need our help. 

Sometimes.

Till then!