Solved

Configuring ODBC with InstantClient

Posted on 2014-10-07
10
310 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 76

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 76

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
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now