FYI: ORACLE_HOME Environment Variable and OBIEE 11g RPD error ORA-12154

Consider the scenario where you've been asked to install a fresh 11g stack on a server. A normal OBIEE 11g stack includes:

• 11g database
• Repository via RCU
• 11g weblogic domain
• Admin Server
• Managed Server scaled out n times for each ManagedServer instance
• OBIEE 11g (managed within the weblogic domain)
• 11g presentation server
• 11g client tools

Due to financial constraints, you've been asked to install the 11g database, 11g weblogic domain and 11g presentation server on a single server. This in its self is not an issue, and is common practice, especially for proof of concepts.

In a standard 11g database install, you'll set your $ORACLE_HOME variable to ..\.\

product\11.2.0\dbhome_1\

This is where you'll store your TNSNames.ora and SQLNet.ora files. These files are critical because they determine the database addresses needed for establishing a collection. Your default tnsnames.ora file will most likely include your localhost.  See below for an example:

Now you've installed the database, created the RCU and you're ready to install & configure Fusion Middleware (formally 10g's Enterprise Manager).

But wait! Fusion Middleware's Configuration Guide says create an ORACLE_HOME environment variable and set it to OBIEE 11g's database path:  app\11g\mw_home

See table below:

 

OBIEE 11g ORACLE_HOME	app\11g\MIDDLEWARE_HOME
Oracle 11g database ORACLE_HOME	app\CookJR\product\11.2.0\dbhome_1

What do you do?

Keep your ORACLE_HOME environment variable set to your DB path and make sure any datasources you add as a physical layer of the RPD are added to the ORACLE_HOME db path and not the 11g ORACLE_HOME

This is critical because during the configuration of your OBIEE system within 11g you'll be required to set the ORACLE_HOME path twice:

1. When you configure your Oracle Identity Store to utilize an external database
2. When you update your tnsnames.ora file to contain the physical data sources of your connection pool within the RPD

If you fail to adhere to this policy, you will encounter the following error when you create a connection pool in your physical layer:

ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

This is actually a known Oracle Bug (Search OTN ID 129637.01) and if you encounter this error , relax the fix is quick:

STEP 1: Edit registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\<Oracle_Home> to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.

STEP 2: Edit the file %MiddleWare_Home%\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd to set %ORACLE_HOME%\bin as first entry in the PATH.
e.g.

set PATH=%OBIEE_HOME%\bin;%OBIEE_HOME%\bifoundation\server\bin;%OBIEE_HOME%\bifoundation\web\bin;C:\Oracle\BIEE_11g\jre\bin;%windir%;%windir%\system32;%PATH%

STEP 3 Please edit the file %MiddleWare_Home%\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\user.cmd to set TNS_ADMIN to your Oracle client tnsnames.ora home directory path (in case exists) or to your OBIEE tnsadmin %OBIEE_Client_Home%\Oracle_BI1\network\admin.
e.g.

set TNS_ADMIN=C:\oracle\product\11.2.0\client_1\network\admin

Oracle Technical Network says you can't test this in online mode, but it is possible by creating a new connection pool and then attempting to 'Import Metadata'. No reboot is required. 

If you pass Step 1:

you have successfully connected to your physical data source using the tnsnames.ora file located in your

app\CookJR\product\11.2.0\dbhome_1 folder. Congrats!

keywords : obiee 11g upgrade, obiee ORACLE_HOME variable, obiee 11g guide, obiee 11g installation, ORA-12154