Setting an Oracle Connection to Use TNSNames.ora

• 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

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.

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.

Step 5

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

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.

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.

Please restart Tableau Server after completing the steps above.