Wednesday, January 27, 2010

OBIEE: ORA-12154 could not resolve connect identifier

I'm playing around with OBIEE using the SH schema (sample schema). I've imported the tables successfully, created the logical and presentation layer when I decided to change the driver from ODBC 3.5 to the OCI driver. When I went to run a simple report to test the Times dimension, I received this:

Answers ORA-12154

OK, no worries.

I tried to update the row count through the Administration tool and got this:

Administration Tool ORA-12154

Barnacles.

So I begin to troubleshoot. First though, here's the setup.

OS: Windows Vista Ultimate
Database: 10gR2 (local)
OBIEE: 10.1.3.4 (local)

There is no other Oracle software running on the machine.

First I went to Google. Nothing really exciting there, but it did remind me of the OBIEE forums (which I posted to for the first time yesterday). So I did a search there.

Reading through a couple of posts confirmed what I had done.
1. tnsping my local database. Check.
2. Connect to the local database via SQL*Plus. Check.
3. Check environment variables. TNS_ADMIN is setup to point to my one and only tnsnames.ora file on the machine.

Hold up...what about the PATH variable? I've seen before, in my millions of local installations in Windows, where the order of paths will affect how certain things work. Here's what mine looked like (hard returns added for readability):
C:\oracle\bin;
C:\OracleBI\server\Bin;
C:\OracleBI\web\bin;
C:\OracleBI\web\catalogmanager;
C:\OracleBI\SQLAnywhere;
C:\Program Files\Java\jdk1.6.0_17\bin;
%SystemRoot%\system32;
%SystemRoot%;
%SystemRoot%\System32\Wbem
Hmmm...I wonder if having the database path first is causing this? Why would it though? OBIEE doesn't have it's own tnsnames.ora...so it shouldn't, right? I moved c:\oracle\bin behind the final OBIEE path. Guess what? I was able to connect.

I'm not sure exactly why this happened, I wasn't able (or was just too lazy) to find the exact reason in the forums (I'm sure Christian or Robin will leave me a note), but it worked. Yeah for me.

6 comments:

rnm1978 said...

Is it possible something else changed too and the reboot/restart to pick up the changed PATH also picked up the other thing?

The PATH problem normally shows itself in OBIEE through not being able to find the OCI library files (The specified module could not be found / Failed to load the DLL), which makes sense.
But you're getting an ORA error, so it's got to the client it just can't resolve the DSN that you've got in your connection pool with what it's got in its tnsnames.

you've got me stumped :)

Joel Garry said...

How exactly is your TNS_ADMIN set? According to TFM, it looks for a registry setting, not an environment variable first.

The ODBC driver uses the OCI driver internally (see MOS 781054.1, which points at 66403.1, so) for 10.2, some software (like patch sets) assumes the ODBC driver is in the oracle home directory. There are dependencies between the ODBC and the sqlnet dll's, which are very version specific.

So I speculate when you switched drivers, you are now using incompatible dll's with the oracle bin first in the path. Moving the bin to after the other directories allow it to find the correct dll first. So that's why you don't get the library not found error, and instead get the wtf error - sqlnet isn't smart enough to give a version mismatch error.

I suspect TFM and MOS are incomplete.

Gary Myers said...

Are you running 64bit or 32bit Windows ?

I've found it a right pain getting 64-bit Oracle DB on 64-bit Windows to talk to 32-bit applications on the same machine.

The 32-bit apps want a 32-bit Oracle client [yeah for XEClient], but there's also an issue when 32-bit software is installed in the "Program Files (x86)" directory as the brackets cause problems.

I also find it confusing that, in 64 bit Windows, the system32 directory has the 64 bit programs while WOW64 has the 32-bit programs.

oraclenerd said...

@Joel

Setting it via a system environment variable, TNS_ADMIN.

I haven't looked into the registry though...that's a good point, I shall check it out.

I have no other software installed, just 10gR2 and OBIEE (and now OBIA). I know that OBIEE doesn't use SQL*Net, it uses the ODBC Data Sources. Just weird that it happened that way...

oraclenerd said...

@Gary

32 bit Windows all the way...I haven't taken the plunge yet to 64 bit.

sunny said...

hi i'm stuck in calculating the percentage..

I have two colomuns in bmm layer one is student count and another rfstudent count.

student count gives all the students for the year 2010 whose composite score is 3 and rf student count gives count of all the students whose composite score is in 3,2,1 ,,,,ineed to calculate percentage of these two coloumns

soemthing like this student count whose CS is 3/student count whose CS is 3,2,1 and ineed to develop in bmm layer creating another logical colomun says % student count