Oracle BI EE 10.1.3.3/2 – Identifying Oracle Homes for OCI Drivers
Posted by Venkatakrishnan J on October 3, 2007
I have been trying to figure out how OBI EE finds ORACLE_HOME in Windows machines. I do not have ORACLE_HOME set on my machine. And i have 2 instances of Oracle DB running. But whenever i try to connect to a database using OCI in BI Administrator, it always picks up the one that i have installed as the latest. In Solaris and UNIX we do not have the problem since we explicitly have to specify the ORACLE_HOME in the user.sh or system.sh files. And it looks like, it depends on how your PATH variable has been constructed. For example, let me take a sample PATH variable. My PATH variable looks like this
As you see above, the path variable has the link to both the Oracle Homes. But it looks like BI Administrator would always pick the one that is entered first(or the latest install among the 2 databases). In the case above, the Oracle Home used would be D:\oracle\product\10.2.0\db_1\bin. This can be verified in command prompt using the tnsping utility.
Lets first try to ping ORCL (an entry in my tnsnames.ora file in D:\oracle\product\10.2.0\db_1\network\admin).
As you see above, the sqlnet.ora file used is the one that comes from D:\oracle\product\10.2.0\db_1\network\admin. Now lets change the PATH variable in such a way as to have the 2 database home as the first one.
Now try pinging ORCL(an entry in my tnsnames.ora file in D:\oracle\BISE1\db\network\admin).
As you see above, the sqlnet.ora file used is the one that comes from D:\oracle\BISE1\db\network\admin which means that our OCI path has changed and i can start using my tnsnames entries in this path. So simple but still took me some time to figure out. But the lingering question still remains. Can we effectively use only one Oracle Home instance? What if we have different tnsnames entries in different Oracle Homes. Well, looks like the only way as of now is to have all the entries in the same tnsnames.ora file and use that Oracle Home’s OCI.