Configuring ODBC with InstantClient

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
carlino70Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
slightwv (䄆 Netminder) Commented:
Also, can you post a screen show of the sqlplus attempt and failure?
carlino70Author Commented:
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
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.
slightwv (䄆 Netminder) Commented:
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.
carlino70Author Commented:
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

slightwv (䄆 Netminder) Commented:
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
carlino70Author Commented:
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.
slightwv (䄆 Netminder) Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlino70Author Commented:
That is the problem, I didn´t see the real extention of tnsnames and sqlnet
sqlplus and odbc are working now.
Thankyou
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.