Knowledge Base

Setting an Oracle Connection to Use TNSNames.ora

Product(s): Professional Edition, Tableau Server
Version(s): All
Last Modified Date: 18 Jul 2014

Setting an Oracle Connection to Use TNSNames.ora

Oracle environments offer a diverse array of connection methods and naming conventions. There are several common errors received when the Oracle drivers do not have the necessary route or naming syntax. The common Oracle errors seen with Tableau data connections are:

  • ORA-12154: TNS: could not resolve the connect identifier specified
  • ORA-12514: TNS listener does not currently know of service requested in connect descriptor
  • ORA-12541: TNS: no listener
  • ORA-12170: TNS:Connect timeout occurred
  • ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA

The best way to correct or avoid these errors is to set up a connection to use TNSNames.ora. If after setting up this connection, you are still having connection problems, see the Troubleshooting Oracle Connection Errors article.

This article applies to the Windows platform. For an equivalent article for the Mac platform, see Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora on a Mac.

Set the TNS_Admin environment variable

Before you begin, verify that a copy of TNSNames.ora exists on the machine. The TNSNames.ora file contains the specific information required to connect to the Oracle instance. By default, the TNSNames.ora file resides in {oracle home directory}\network\admin. For example, C:\Oracle_Client\network\admin.

Create a Windows Environment variable that directs Oracle connections to use the information in the TNSNames.ora file. Then you can use the Oracle Net Service Names in Tableau data connections without needing to know the port, service, or SID specifics for each Oracle connection.

Note: If you do not have an Oracle client installed on your computer, get the necessary files from your database administrator.

Step 1

Determine the folder path for TNSNames.

Step 2

Select Start > Control Panel > System.

Step 3

In Windows 7, in the System window, select Advanced system settings.

Or

In Windows XP, in Classic View, double-click System.

Or

In Windows XP, in the default view, under Pick a category, select Performance and Maintenance, and then under or pick a Control Panel icon, click System.

Step 4

In the System Properties dialog box, on the Advanced tab, select Environment Variables.

OracleErrors7.png

Step 5

In the Environment Variables dialog box, under System variables, click New.

OracleErrors7.png

Step 6

In the New System Variable dialog box, in the Variable name text box, type TNS_ADMIN.

Step 7

In the text box, type the location of the TNSNames.ora file.

Step 8

Click OK to add this variable.

The new variable now appears in the list in the dialog box.

Step 9

Click OK in the Environment Variables dialog box and the System Properties dialog box.

Step 10

Restart your machine to ensure that the new environmental variable is recognized.

Use the Oracle net service name in Tableau

After setting the TNS_ADMIN environmental variable, you can connect to Oracle using the Oracle Net Service Names.

Below is a potential template for an entry. Elements in brackets must be replaced by values obtained from your database administrator.

 

[net_service_name]=
(DESCRIPTION=
  (ADDRESS=[protocol_address_information])
  (CONNECT_DATA=
    (SERVICE_NAME=[service_name])
     )
   )

 

Below is an example entry that uses the template above:

Production =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = prod.corp.com)(PORT = 1521)
  )
 (CONNECT_DATA =
  (SERVICE_NAME= orcl)
  )
 )

Step 1

Close all Tableau workbooks and then open a new instance of Tableau.

Step 2

In Tableau, select Connect to Data and open an Oracle data connection.

Step 3

Use only the appropriate Oracle New Service Name.

The details are picked up in the TNSNames.ora file through the TNS_ADMIN system variable.

Additional Steps for Tableau Server

Please restart Tableau Server after completing the steps above.



 

Alternate Search Terms:data sources, 12154, 12514, 12541, ora-12154, ora-12514, ora-12541, sid, sql.ora, tns, tnsnames, tns_admin, unable to connect to oracle, can't connect to oracle
Did this article resolve the issue?

Attachments

Search Knowledge Base