OBIEE Admin tool connection and the ORA-12504 !!!

Have you guys ever had any problem with the connection between the OBIEE Admin tool on your desktop and the OBIEE VM? If you had not, my post below is all about this …….

When I started off with my blog, the idea was to blog about the most interesting and challenging experiences of mine in the business analytics space. I hit at one recently, which I wanted to share with you guys, and so started up my OBIEE VM to replicate. Here comes the fun part; After having had a successful connection all the time from my local desktop to the VM hosting OBIEE Sample App, I woke up one morning to a shock to notice that it gave up. I spent the next 2 days struggling my way through all the Admin tool settings, rebooting my VM several times, messing around with oracle linux firewall, punching some holes through Virtual box for some ports, downloading oracle 12c client by mistake, reinstalling my Oracle 11g Client altogether, and finally got it working. So here I am writing my interesting tryst with this connection problem now, instead of the one which I started off earlier for..

So, for you guys out there with similar problem, here is a solution.

Infra Set up:

Virtual Machine of : OBIEE 11.1.1.7 – Sample Application (V305)
Local Desktop with : Windows 7 Professional
Oracle Instant Client version: 11.2

Issue:
I could not anymore import any schema from the database on the VM, through my Admin tool (Offline and Online) on the desktop. I could only see the error message “Connection Failed” on the screen, and even through my sleep, after having stared at it for 2 continuous days.

Steps:
Here is what I did to nail down the issue

  • Bid Adieu to the full Oracle Client on my desktop
  • Downloaded the Oracle Instant Client 11.2
  • Unzipped the Oracle Client into C:\Program Files
  • Changed the bi_init.bat file on my desktop, to include Oracle Client in the PATH variable so that it could get picked up by the Admin tool, as follows 

         set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%windir%;%windir%\system32;C:\Program Files\Oracle instantclient_11_2;C:\Oracle\Middleware\EPMSystem11R1\bin;%ESSBASEPATH%\bin;%PATH%

  • Added the environment variable TNS_ADMIN to point to the client folder C:\Program Files\Oracle instantclient_11_2 so that I could enable trace on the connections
  • Created a log directory under C:\Program Files\Oracle instantclient_11_2 to collect the log files
  • Created the SQLNET.ORA file in C:\Program Files\Oracle instantclient_11_2 with the following text to enable trace and the log location
         DIAG_ADR_ENABLED = OFF 

         TRACE_LEVEL_CLIENT = SUPPORT 
         TRACE_DIRECTORY_CLIENT = C:\Program Files\Oracle instantclient_11_2\log 

  • After trying to import the schema again, the following is the error message in the Oracle client’s log
         niotns: Couldn’t connect, returning 12504

         DEFAULT_SERVICE_listener_name= orcl.us.oracle.com 

         This parameter would enable users to connect to the database, without having to specify a service name 

  • Hurray!!!!!! The connection is now established and working fine.

 I would post the original issue I was trying to address later. Till then, Adios!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s