One of my many duties is to keep the database servers running at my work. Fortunately, I have a good staff.
This weekend, we upgraded our Oracle server from 8i to 10g. After many trials with the DUA (Database Upgrade Assistant) to get it working on a test box we built, we decided instead to just EXPort and IMPort the main database server.
While Oracle likes to tell you that you should install the 10g software over the 8i and use the upgrade utility for the migration, they are wrong. The main reason they are wrong is that if you install 10g over 8i, 8i will continue to work, but 10g is messed up.
By the way, this was on a Windows 2000 server.
Therefore, the plan of action was to uninstall the 8i software, remove the files from the Windows server, remove the registry keys, and reinstall the new 10i software.
If there is some question, I recommend this exact same procedure for the clients as well. Do not try to install these two on the same box because weird things will happen. Oracle might know UN*X (they seemed okay on that platform, but they do not know Windows.)
One interesting thing with the client software was that we were installing the basic client on most PC's however, the terminal server we use had a requirement to use the EXP function. So I installed the utilities from the Universal Installer, however, the third directory level had different rights after the install of just these options. Therefore, I uninstalled the client, reinstalled in the Custom mode with these options and the exact same thing happened. I had to tell NT to propagate the rights from the base of the Oracle Home directory to get this fixed. Did I mention that I love Oracle?....not.
Today, I found out that the interconnection we have developed between Oracle and the SQL 2000 servers was not working. DTS was the likely culprit. The error that we were getting was a message about the Oracle Server failing to contact the DTS server to complete the transaction.
The way we have this set up is that the Oracle server also has a copy of Microsoft SQL 2000. We created an "API" database, which houses views. These views link to the Oracle server via a Link Server. This way if the Oracle box develops an issue or the Link Servers hangs (as will happen with flaky destination services); you must stop and restart the service. By having a dedicated server on this box, the resources of connecting to Oracle and minimizes the isolation of any faults.
We found out that the standard Oracle server installation does not install the Oracle for MTS Service, which connects Oracle transactions to the SQL server transactions. We installed this but it still was not working.
We then found the MTxOCI setting in the registry. This is located in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI. In this key, three dlls link the 10g client software for transactions. They are OracleOciLib, OracleSqlLib, OracleAxLib and they need a string value of oci.dll, orasql10.dll, and oraclient10.dll respectively.
After making these changes, we found that we needed to reboot the DTC service and the SQL server. Now they connect.
We also did some tests with Microsoft SQL 2005. We found that the 2005 server does not come with the Microsoft Oracle OLEDB client (MSDAORA). Even though we had the Oracle OLEDB Client on the server (OraOLEDB.Oracle), the server would not connect because the table columns returned an unexpected column property (or some such non-sense). It would be good to see if Oracle is going to support using the Oracle Client to create a linked server in Microsoft SQL but my guess is that they are less interested in their customers than they are to wage war with Microsoft.
This means that we must have SQL 2000 on the Oracle server for the foreseeable future.
After having worked 7 years (has it been that long) with Oracle, I have yet to see the advantages over Microsoft SQL server. Just my personal thoughts.