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 18.104.22.168 – Sample Application (V305)
Local Desktop with : Windows 7 Professional
Oracle Instant Client version: 11.2
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.
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
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
- After Googling on it for some time, I figured out that the issue is due to the way Oracle 11g server accepts incoming connections
- Based on the information from http://appsdbastuff.blogspot.sg/2011/08/ora-12504-when-using-hstname-method.html I changed the LISTENER.ORA on the VM, to include the parameter
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!!