Solved

Configuring ODBC with InstantClient

Posted on 2014-10-07
10
324 Views
Last Modified: 2014-10-08
Hi experts, I need to configure ODBC with InstantClient_11_2 over Windows 7 Professional.
My files of instalation are:
instantclient-basiclite-nt-11.2.0.3.0
instantclient-jdbc-nt-11.2.0.3.0
instantclient-odbc-nt-11.2.0.3.0
instantclient-sqlplus-nt-11.2.0.3.0

Open in new window

The environment variables set:
PATH=...C:\Oracle\instantclient_11.2\;C:\Oracle\instantclient_11.2\network\admin
TNS_ADMIN=C:\Oracle\instantclient_11.2\network\admin

Open in new window

The odbc_install was successfull.
But when I tried to use sqlplus, I have the error:
ORA-12154: TNS:could not resolve the connect identifier specified

Open in new window

Here the "C:\Oracle\instantclient_11.2\network\admin\tnsnames.ora":
XA21_EPE=
  (DESCRIPTION =
  (ENABLE=BROKEN)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XXX.XX )(PORT = 1521))
    (LOAD_BALANCE = no)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xa21bcs)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 5)
      )
    )
  )

Open in new window

Also, when I tried to configure the ODBC Drivers and I tried test the connection, I see:
ORA-12154: TNS:could not resolve the connect identifier specified

Open in new window

I followed the basic steps in:
http://www.interfaceware.com/manual/odbc_oracle.html

Open in new window

Attached the errors capture.
Could you help me to find the error?, I tried so many times with differents options, and nothing works.
Thankyou in advanced
error-driver-odbc-configuration.doc
0
Comment
Question by:carlino70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40367045
Forget ODBC until you get sqlplus working.

Do you have the following file?
If so, what is in it?

C:\Oracle\instantclient_11.2\network\admin\sqlnet.ora
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40367047
Also, can you post a screen show of the sqlplus attempt and failure?
0
 

Author Comment

by:carlino70
ID: 40367080
Something like this:
C:\Oracle\instantclient_11_2>sqlplus sys/xxxxx@xa21 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 7 17:24:21 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name:

Open in new window


sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
TCP.CONNECT_TIMEOUT=5

Open in new window

0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40367087
xa21 with sqlplus is the alias in the tnsnames.ora file.

Try:
sqlplus sys/xxxxx@XA21_EPE as sysdba

If that works, then we'll move on to the ODBC.

I think the problem there is xa21_roserio_4 in the service name box.  Try XA21_EPE there as well.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40367270
To add:
sqlplus doesn't have anything to do with ODBC and any DSN you set up.

From your sqlnet.ora file, sqlplus will look for the tnsnames.ora file first (the TNSNAMES in NAMES.DIRECTORY_PATH).

The ODBC setup image looks good.  Not sure why the Test opened a different window and allowed you to enter different values.
0
 

Author Comment

by:carlino70
ID: 40368184
here the output, with differents options:

set ORACLE_SID=xa21
C:\Oracle\instantclient_11_2>sqlplus xajtdb/neptune@xa21_epe

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 8 08:26:15 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name:
C:\Oracle\instantclient_11_2>sqlplus xajtdb/neptune@xa21_rosario_1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 8 08:27:18 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name:
C:\Oracle\instantclient_11_2>sqlplus xajtdb/neptune@xa21_rosario_2

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 8 08:27:42 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name:
C:\Oracle\instantclient_11_2>sqlplus xajtdb/neptune@xa21_rosario_3

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 8 08:28:08 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name:
C:\Oracle\instantclient_11_2>sqlplus xajtdb/neptune@xa21_rosario_4

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 8 08:28:32 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name:
C:\Oracle\instantclient_11_2>

Open in new window

and all entries in the tnsnames.ora:
xa21_rosario_1 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xx)(PORT = 1521))
        ) (CONNECT_DATA =
            (SID = XA21) (SERVER = DEDICATED)
        )
    )


xa21_rosario_2 =
  (DESCRIPTION =
  (ENABLE=BROKEN)
    (ADDRESS = (PROTOCOL = TCP)( HOST = 192.168.xxx.xx )(PORT = 1521))
    (LOAD_BALANCE = no)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xa21bcs)
    )
  )


xa21_rosario_3 =
  (DESCRIPTION =
  (ENABLE=BROKEN)
    (ADDRESS = (PROTOCOL = TCP)(HOST = xa21bcs-scan)(PORT = 1521))
    (LOAD_BALANCE = no)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xa21bcs)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 5)
      )
    )
  )


xa21_rosario_4 =
  (DESCRIPTION =
  (ENABLE=BROKEN)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xx )(PORT = 1521))
    (LOAD_BALANCE = no)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = xa21)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 5)
      )
    )
  )


XA21_EPE=
  (DESCRIPTION=
    (LOAD_BALANCE=no)
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.xxx.xx)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (FAILOVER_MODE=
        (TYPE=select)
        (METHOD=basic)
        (RETRIES=5)
        (DELAY=5)
      )
      (SERVER=dedicated)
      (SERVICE_NAME=xa21bcs)
    )
  )

Open in new window

The parameters in Oracle Server:
service_name = xa21bcs

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40368217
There are only a few things that will cause the ORA-12154.

Check out the suggested actions here:
http://ora-12154.ora-code.com/

Unset the following environment variables:
ORACLE_SID
TNS_ADMIN

Make sure the tnsnames.ora file is under the network\admin folder.

Post the results of the following from a cmd prompt (the set command is to show your environment variables:
set
type C:\Oracle\instantclient_11.2\network\admin\tnsnames.ora
sqlplus xajtdb/neptune@xa21_epe
0
 

Author Comment

by:carlino70
ID: 40368247
ok.
Variables ORACLE_SID, TNS_ADMIN not configured
tnsnames.ora is in the network/admin path:
C:\Oracle\instantclient_11_2\network\admin>dir
 El volumen de la unidad C es OSDisk
 El número de serie del volumen es: 5CD9-1A75

 Directorio de C:\Oracle\instantclient_11_2\network\admin

07/10/2014  03:37 p.m.    <DIR>          .
07/10/2014  03:37 p.m.    <DIR>          ..
07/10/2014  12:05 p.m.                66 sqlnet.ora.txt
08/10/2014  09:02 a.m.             1.575 tnsnames.ora.txt
07/10/2014  03:02 p.m.             1.429 tnsnames.ora_BKP.txt
               3 archivos          3.070 bytes
               2 dirs  41.312.772.096 bytes libres

Open in new window

and:

C:\Oracle\instantclient_11_2\network\admin>type C:\Oracle\instantclient_11_2\network\admin

Access denied.

Open in new window


I'm seeing that the problems appear to be permissions.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40368250
The problem is you don't have a tnsnames.ora file.  You have a tnsnames.ora.txt file.

Rename them (the sqlnet.ora.txt as well).

>>C:\Oracle\instantclient_11_2\network\admin>type C:\Oracle\instantclient_11_2\network\admin

You tried to 'type' a folder not a file.  Look at what I posted and what you tried.
0
 

Author Closing Comment

by:carlino70
ID: 40368561
That is the problem, I didn´t see the real extention of tnsnames and sqlnet
sqlplus and odbc are working now.
Thankyou
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question