Solved

Configuring ODBC with InstantClient

Posted on 2014-10-07
10
304 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
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

758 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

22 Experts available now in Live!

Get 1:1 Help Now