So it is not my knowledge posted her - thank goes to the original investigators!
- wolfgang
RESOLVING COMMON ORACLE CONNECTIVITY ISSUES
(Six Relatively Easy Suggestions)
Oracle Home Selector Tool
This is the first thing you should check because it is usually the easiest.
You may have a tool called the Oracle Home Selector available in:
Start–>Programs–>Oracle Installation Products (or maybe in another Start menu folder)
If available, run this tool to select the “right” middleware home as the primary home. If you have only one set up as far as Oracle is concerned, a message will tell you so. If this is the case, it could be that the older, “wrong” middleware was removed but there is still some residual information that remains to confuse BusinessObjects. Or it could be the OCIW32.dll file…
Multiple OCIW32.dll Files
This is the second-easiest potential fix. The issue fixed by the following resolution may manifest itself in the form of the “ORA-01019: Unable to Allocate Memory on Client Machine” error message.
Use Windows Explorer’s Search function (or go to Start --> Search --> Files or Folders) to look for files by the name of “ociw32.dll” (it is not case-sensitive). If you have multiple copies, rename (safer than deleting) all the copies not found in the \bin directory for the “right” Oracle middleware. For example, your search may yield matching files in both C:\Oracle\Ora81\bin and in C:\Windows\System32. Rename the System32 copy (you can do this right in the Search Results window) to something like “ociw32.dll.old” and see if this helps BusinessObjects to run better. If not, try rebooting the machine to see if that makes a difference.
Client Machine’s PATH Environment Variable
The next potential fix is correcting the order of paths in the client machine’s PATH environment variable. It will contain a string of paths, looking something like:
C:\windows\system32;C:\Oracle\Ora81\bin;C:\blah\blah…
To access your machine’s PATH environment variable in Windows 2000, right-click on the “My Computer” icon on your desktop, go to Properties. Click the Advanced tab. Click the “Environment Variables…” button. The lower pane will contain “System Variables” including the PATH. Scroll down if necessary, and select the PATH environment variable. Click the Edit button to open another box that will allow you to see more of the definition. You may want to copy the entire definition and paste into Notepad to read it more easily, as it can be very long. You may notice more than one Oracle \bin path (such as C:\Oracle\Ora81\bin). One of these will likely be for the “right” middleware path, and the others for “wrong” middleware(s). Make sure that the “right” \bin entry appears first in the PATH definition. You can cut and paste it to shuffle into the right order. Ensure that you maintain the proper semicolon ( delimiter between each path definition in the PATH environment variable definition.
The HOSTS file
If you have a different setup from other users and everyone else can work successfully, but you are left out in the cold, it may help to add entries to your local “hosts” file for all relevant machines (repository database server, data source database servers). Typically you can find your hosts file in a directory location resembling the following:
C:\WINNT\system32\drivers\etc
Open it with a text editor and add a server’s IP address, then at least one space, then the server hostname. There are examples in the file that you can follow, and they look like this:
102.54.94.97 rhino.acme.com # source server
38.25.63.10 x.acme.com # x client host
Keep in mind that anything to the right of a # character is considered a comment and is not read by the computer.
The Windows Registry
This is a slightly more involved potential fix than the others, hence I suggest it to be tried only if the other steps do not resolve the issue.
First, before changing anything in the Windows Registry, it is strongly advised that you make a backup of its settings. You can do this by opening the registry editor, going to the Registry menu and selecting “Export Registry File…” From that dialog, you can select a complete or a partial export (the whole thing or just one branch of it, such as HKEY_CURRENT_USER).
To check the Oracle registry settings:
Start --> Run --> “regedit” This opens the Windows registry editor.
Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
If an ORACLE_HOME definition exists here, ensure that it is defined to the proper path (for example, C:\Oracle\Ora81). You can change the value by double-clicking on the ORACLE_HOME name.
You may find, under the ORACLE folder, folders such as HOME0 and HOME1. If the contents of one of these has paths all pointing to the “wrong” middleware location, and the other folder’s contents all point to the “right” middleware, right-click on the “wrong” middleware folder and delete it.
TNSnames.ora Default Domain Setting
This is a more obscure solution which will only apply to some situations. For example, you have one user who is a developer and running a different Oracle runtime version, and perhaps multiple Oracle middleware and/or applications installed, and no other user has connection issues that the developer does.
Using SQL*Plus this is what a login typically looks like for Oracle.
"user id/user password@database name"
Business Objects builds your login the same way as SQL*Plus.
Oracle Net8 takes the part after the @ as the host name (before the @ is the username/password). It then tacks on the DEFAULT_DOMAIN setting from the SQLNET.ORA file. So the result might it might look like:
“databasename.world”
Net8 then looks in the TNSNAMES.ORA file for a entry which matches.
This means your entry in TNSNAMES.ORA must match your SQLNET.ORA DEFAULT_DOMAIN setting.
If instead you build the login like this when creating the key:
“user id/user password@database name.” (note the tiny dot or period at the end)
The Net8 client then ignores the DEFAULT_DOMAIN setting in SQLNET.ORA and only looks for a host entry in TNSNAMES.ORA of the database name. By doing this every one can have the same TNSNAMES.ORA entry.
So as a result of the presence of the period in the key file for the database name we avoid the issue of the sqlnet.ora file settings.
Wolfgang Bidner (BOB member since 2002-08-30)