Starting OBIEE 11g Server Or Running Requests After Upgrade From OBIEE 10g Fails With Error: “ORA-12504 or ORA-12154″

Applies to Business Intelligence Suite Enterprise Edition – Version: 11.1.1.3.0 [1905] to 11.1.1.5.0 [1308]   [Release: 11g to 11g]

Symptoms

After upgrading to Oracle Business Intelligence Enterprise Edition (OBIEE) 10g to OBIEE 11g, when viewing a dashboard or creating a new analysis in Answers, the following error occurs:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 12504, message: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)
Or you might get the error:

[2010-11-16T06:02:38.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 004_ILDmfHpFw000jzwkno0002Xk0000qx] [tid: 4f353960] [nQSError: 17014] Could not connect to Oracle database. [[
[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified
at OCI call OCIServerAttach.
[nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement.
[nQSError: 43113] Message returned from OBIS.
It looks like all the database connections have been broken.

Another symptom that may occur is that the BI Server does not start up and you get the following error in the nqserver.log:

[ecid: 00hxwa3cUNZ4aq_S9Q2FSx0001SO000000] [tid: 814] [36007] Loading repository E:\obiee11g\instances\instance2\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository\11rpd.rpd.
[2011-01-06T20:20:52.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 00hxwa3cUNZ4aq_S9Q2FSx0001SO000000] [tid: 19b4] [nQSError: 17014] Could not connect to Oracle database. [[[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specifiedat OCI call OCILogon.

Changes

Upgraded from OBIEE 10g to OBIEE 11g.

Cause

In OBIEE versions prior to 11g, it uses the database client home tnsnames.ora file to resolve the tnsname. But OBIEE 11g uses the tnsnames.ora file installed within its installation home. The path is
[middleware home]/[oracle home]/Oracle_BI1/network/admin
By default, there are no database tns alias descriptors configured.

Solution

There are three ways to solve this problem:

1. If you just need one TNS alias, then copy the database connection descriptor from database tnsnames.ora file to the tnsnames.ora file under the BIEE directory:

[middleware home]/[oracle home]/Oracle_BI1/network/admin

2. You  may also copy the entire tnsnames.ora file

3. Another alternative is to set the TNS_ADMIN environment varaible which would point to the directory where a tnsnames.ora file is located. This would need to be set as a System Environment variable in Windows or in the command shell if starting from a command-line. For Unix/Linux, it would need to configured in the user profile that owns the software or set explicitly in the shell if starting from the command-line.