Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Configuring ODBC with InstantClient

Posted on 2014-10-07
10
Medium Priority
?
351 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
  • 6
  • 4
10 Comments
 
LVL 78

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 78

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 78

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 78

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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Technology Partners: 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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

572 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