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%networkadmin 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
  • The
    Server text box of the Connect page.
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>networkadmin directory. For example,
C:Oracle_Clientnetworkadmin.
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.

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 
On the Connect page, click Oracle.
Step 3 
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.
Note: Variables
are case sensitive.
Step 4 
Select a schema from the Schema
drop-down list, drag a table to the join area, and then click Go to
Worksheet
.
Step 5 
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 6
Download and install the appropriate
Oracle drivers from the Tableau
Drivers
page. Even if an Oracle driver is installed on your computer, it could be
incompatible with Tableau and will require the version specified on the Drivers
page.

  • June 30, 2018 | 23 views
  • Comments