Troubleshoot OCI Connections

There are several reasons why you might have trouble connecting to an Oracle Database using OCI.

Check to ensure that the following conditions are true:

• The computer running the Oracle BI Server must use Oracle Call Interface (OCI) to connect to the database.

• If you choose not to use the entire connect string in the repository connection pool, you must ensure that a valid tnsnames.ora file is set up in the following location within the environment, so that the Oracle BI Server can locate the entry:

  BI_DOMAIN/bidata/components/core/serviceinstances/ssi/oracledb

• If you choose not to use the entire connect string in the repository connection pool, ensure that the net service name in the tnsnames.ora file matches the Data Source Name used in the connection pool.

  For example, in the following example of a tnsnames.ora entry, the corresponding repository connection pool Data Source Name is ITQA2.

  ITQA2 =
     (DESCRIPTION =
        (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = ITQALAB2)(PORT = 1521))
     (CONNECT_DATA =
        (SERVICE_NAME = ITQALAB2.corp)
        )
     )
  

The following procedure shows how to check repository database and connection pool settings against the Oracle tnsnames.ora settings.

1. Open the repository in the Model Administration Tool.
2. In the Physical layer, double-click the database you want to check to display the Database dialog.
3. On the General tab, in the Data source definition: Database field, ensure that the appropriate Oracle Database version is selected. Then, click OK.
4. Open the Connection Pool dialog for this data source. You might need to expand the database object in the Physical layer to see the connection pool object.
5. In the Connection Pool dialog, check that the following is true:

   • The Call interface field displays the appropriate value for the release of the Oracle Database you're using.

   • The Data source name field displays the Oracle Database net service name that you defined in the tnsnames.ora entry.

   • The User name and password fields contain the correct values.

   Change the values if necessary, then click OK.

6. In the environment, open the tnsnames.ora file located in the following directory:

   BI_DOMAIN/bidata/components/core/serviceinstances/ssi/oracledb

7. Check that a valid net service name exists with the following characteristics:

   • Matches the connection pool settings for the Data Source Name

   • Specifies the targeted Oracle physical database