KNOWLEDGE BASE

Troubleshooting Oracle Connection Errors


Product(s): Tableau Desktop, Tableau Server
Version(s): All
Last Modified Date: 16 Aug 2016

Oracle drivers require very specific connection statements in a unique format. Though the TNSNames.ora file may not always be required. For instance, if you have installed only the Tableau-provided Oracle files and do not have a stand-alone Oracle client. The Oracle error messages refer to the TNSNames.ora file.

Typically, correcting route or naming syntax in the Advanced Oracle Connection dialog box or using your full .WORLD database name resolves most Oracle connection issues. If your connection error requires more troubleshooting, refer to the four common connection errors listed below.

  • ORA-03113: end-of-file on communication channel
  • ORA-12154: TNS: could not resolve the connect identifier specified HOST value incorrect or Global name incorrect or unknown
  • ORA-12514: TNS listener does not currently know of service requested in connect descriptor
    SERVICE value incorrect
  • ORA-12541: TNS: no listener
    PORT value incorrect
  • ORA-00932: inconsistent data types

ORA-03113: end-of-file on communication channel

ORA-03113 is a catch-all type error for any problem interrupting an Oracle session. There can be numerous causes for this error. Please refer to the list below for some troubleshooting guidance.

  • Refer to Oracle documentation specific to this error: My Oracle Support.
  • Oracle recommends that you check for network problems and review the SQL*Net setup.
  • If you're connecting to Oracle 9.2.0.5, in many cases the primary cause of this error is Oracle bug 3010227. Ask your Oracle database administrator to apply Oracle patch 9.2.0.6 or another patch appropriate for your server.
  • Set the Oracle initialization parameter 'STAR_TRANSFORMATION_ENABLED' to FALSE.
  • Alternatively, if you would like to test this issue further follow the optional procedure listed below.

Step 1 

From the Tableau Desktop start page, select Connect to Data.

Step 2 

For Tableau Desktop 8.2 and later, on the Connect page, click Oracle.

For Tableau Desktop 8.1 and earlier, on the Connect to Data page, click Oracle.

Step 3 

For Tableau Desktop 8.2 and later, connect to the Oracle server, and then click OK.

For Tableau Desktop 8.1 and earlier, follow the steps in the Oracle Connection dialog box to complete the connection. For more information about completing the connection steps, refer to the Oracle Database topic in the Desktop Help.

Step 4 

For Tableau Desktop 8.2 and later, do the following:

  1. In the join area, hover over the Custom SQL table until the edit icon displays, and then click the icon.
  2. Copy the query in the Edit Custom SQL dialog box.

For Tableau Desktop 8.1 and earlier, do the following:

  1. Under Step 5, select a table or view from the schema, and then select Custom SQL.
  2. Copy the query that appears in the Step 5 text box.

Step 5 

In a SQL session connected to this database, paste and run the query. The expected response is error ORA-7445: exception encountered: core dump, which confirms that the problem is ORA-3113, as expected.

ORA-12154: TNS: could not resolve the connect identifier specified

ORA-12154 occurs when the transparent network substrate (TNS) cannot resolve the service name. The service name is specified in the TNSNames.ora file, which is located in your %ORACLE_HOME%\network\admin\ folder. Most often, this error occurs when information in the TNSNames.ora file is incorrect. For example:

  • The .world extension is not included on the database name.
  • The SERVICE_NAME or HOST variable is incorrect.

To resolve this issue, try one of the three following troubleshooting options, in the order listed below.

Option 1: Edit TNSNames.ora

 Provide the full database name, including the .world extension in both of the following locations:

  • The TNSNames.ora file.


    And


  • (Tableau Desktop 8.2 and later) The Server text box of the Connect page.
  • (Tableau Desktop 8.1 and earlier) Step 1 text box in the Oracle Connection dialog box when you connect to the data source in Tableau.

Option 2: Ensure that Tableau Server Run As User account has permissions to TNSNames.ora (Tableau Server only)

If you have Tableau Server installed, complete the procedure below to ensure that the Tableau Server Run As user account has permissions to the location of the TNSNames.ora file. If the Run As user account does not have permissions, Tableau Server is unable to access the Oracle Data source details.

Step 1 

Verify the location of the TNSNames.ora file, or the equivalent SQLNET.ora and LDAP.ora files on the machine.

Note: By default, the TNSNames.ora file is located in <oracle-directory>\network\admin directory. For example, C:\Oracle_Client\network\admin.

Step 2 

Confirm that the TNS_ADMIN variable points to the location of the file or files described in step 1.

Note: To check the TNS_ADMIN variable, click the Start button, and select Control Panel > System. Click Advanced system settings, click the Advanced tab, and click Environmental Variables button.

Step 3 

On the Windows Start button, select Tableau Server <version> > Configure Tableau Server.

Step 4 

Under Server Run As User, copy the information in the User text box.

Step 5 

Go to the folder where the TNSNames.ora file is located.

Step 6 

Right-click the folder and select Properties. Click the Security tab and click the Edit button.

Step 7 

Under Group or user names, click the Add button.

Step 8 

In the Enter the object names to select text box, paste the details of the Run As User account you copied in step 6.

Step 9 

When finished, click OK.

Step 10 

In the Permissions area,ensure that the Full control and Modify check boxes are selected.

Step 11 

Click OK to close the dialog boxes.

 

For more information about this error, you can refer to the following external resources:

 

Option 3: Verify that all information in TNSNames.ora is correct

 If the above troubleshooting steps do not resolve the issue, continue reading and complete the procedure to verify the other information in the TNSNames.ora file is provided correctly.

 

An example of a TNSNames.ora file is shown here:

 

QAORCL10.world =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = MY_HOST_NAME)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = MY_SERVICE_NAME)

)

)

 

The three variables of interest in the file are HOST, PORT, and SERVICE_NAME. Copy these variables from the TNSNames.ora file and keep them available. These variables are case sensitive.The following steps describe how to provide these variables for your connection.

Step 1 

From the Tableau Desktop start page, select Connect to Data.

Step 2 

For Tableau Desktop 8.2 and later, on the Connect page, click Oracle.

For Tableau Desktop 8.1 and earlier, on the Connect to Data page, click Oracle.

Step 3(Tableau Desktop 8.1 and earlier only)

In the Oracle Connection dialog box, click Advanced.

Step 4 

For Tableau Desktop 8.2 and later, provide the following information from the TNSNames.ora file:

  • In the Server name text box, type the HOST name.
  • In the Service text box, type the SERVICE_NAME.
  • In the Port text box, type the PORT number.
  • Specify whether to use Windows Authentication or a specific user name and password, and then click Connect.

For Tableau Desktop 8.1 and earlier, in the Advanced Oracle Connection dialog box, provide the following information from the TNSNames.ora file:

  • In the Server Name text box, provide the HOST name
  • In the Service Name text box, provide the SERVICE_NAME.
  • In the Port text box, provide the PORT number.
  • When finished, click OK.

Note: Variables are case sensitive.

Step 5 

 

For Tableau Desktop 8.2 and later, select a schema from the Schema drop-down list, drag a table to the join area, and then click Go to Worksheet.

For Tableau Desktop 8.1 and earlier, follow the remaining steps in the Oracle Connection dialog box to complete the connection.

  • Under Step 5, keep the selection of Single Table, and select a table or view from the schema.
  • Under Step 6, you have the option to edit the default connection name.
  • When finished, click OK.

Step 6 

Complete the steps in the Setting an Oracle Connection to Use TNSNames.ora article.

Important: 

  • Make sure that you save the TNSNames.ora file you use in ASCII encoding. Any other encoding besides ASCII, for example UTF-8 or Unicode, causes the ORA-12154 error message.
  • These steps are usually required even if the Oracle software is already installed on the machine.

Step 7 

Download and install the appropriate Oracle drivers from the Tableau Drivers page. Even if a different Oracle driver is installed on your computer, it could be incompatible with Tableau and will require the version specified on the Drivers page.

ORA-12514: TNS listener does not currently know of service requested in connect descriptor

Typically this error occurs when the SERVICE value is incorrect.

To resolve this issue, find out what the correct SERVICE value is, open the TNSNames.ora file located in your %ORACLE_HOME%\network\admin\ folder. Refer to the steps under ORA_12154 if necessary.

ORA-12541: TNS: no listener

Typically this error occurs when the PORT value is incorrect.

To resolve this issue, replace the PORT value with either 1521 or 1526. Try the value that is currently not in use.

ORA-00932: inconsistent data types

This error occurs when connecting to Oracle or when creating an extract from an Oracle data source. Typically this error is caused by the installation of incorrect Oracle drivers.

To resolve this issue, install the correct Oracle drivers from the Drivers page for the version of Tableau you are using.

Additional troubleshooting suggestions

If you do not have an Oracle Client installed on your machine, be sure to get the necessary files from your database administrator. If the Oracle data connection errors persist, do the following:

  • Check the TNSNames.ora folder path used to create the TNS_ADMIN variable.
  • Restart your machine to ensure that the TNS_ADMIN variable is recognized.
  • Check that the Oracle connection name used in Tableau exactly matches the TNSNames.ora Net Service Name entry. This name is case sensitive.
  • In some cases Windows will need to be restarted before the Oracle driver will pick up the TNS_ADMIN system variable
  • Contact local IT to verify that the TNSNames.ora file is current.
  • If the Oracle connection uses LDAP, make sure to include the SQLNet.ora file as well as the TNSNames.ora file.

 

Did this article resolve the issue?