1 2 Previous Next 26 Replies Latest reply on Jun 21, 2011 10:38 PM by 869193

    beginner connecting to Oracle using ODBC (11.2.0.2.0)

    869193
      Hello-

      I am a new Oracle user and have permission to access to a remote Oracle database, however I am having issues with the initial setup/configuration using ODBC! I have downloaded the Oracle Driver and Instant Client (11.2.0.2.0) and I need to use Microsoft Access for accessing the data (the Access database is already configured to the correct Oracle database). Note: Im working on Windows XP.

      Issues I am having:

      I am still struggling with ODBC in that I get the same SQLState=S1000 Error. ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor.

      My TNSnames.ora file currently looks like this:

      alias= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chrdbprd02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=alias)))

      I need to be linking to operating system chrdbprd02, port 1521. The database name is p016 (but Im not sure where to include that in the TNSnames.ora file if you can't have both a service and SID in the same file...)

      I have been using "Alias" for the "TNS Service name" and the "Data Source Name".

      I would appreciate any feedback. Thanks!
        • 1. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
          AliD
          Replace SERVICE_NAME=alias with SERVICE_NAME=p016. The rest should not change.
          1 person found this helpful
          • 2. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
            869193
            I was able to connect once, but then I went to test the connection again and got the same message that TNS could not resolve the connect identifier specified. I know there is lots of info on this error available, but is there any reason why it would be able to connect one time, then not again?
            • 3. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
              Keith Jamieson
              Start by verifying that the database you need to connect to is up and running.


              Also, try disabling your firewall and seeing if you can connect. If you need to have your firewall enabled you need to open whatever port the database is listening on. The default is 1521 and you should have the port opened in both directions.
              • 4. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                orafad
                MsMonroe wrote:
                test the connection again and got the same message that TNS could not resolve the connect identifier specified.
                That's not the same as "+ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor+".

                What error do you get?
                • 5. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                  617785
                  MsMonroe wrote:
                  I was able to connect once, but then I went to test the connection again and got the same message that TNS could not resolve the connect identifier specified. I know there is lots of info on this error available, but is there any reason why it would be able to connect one time, then not again?
                  For that error, you must have either changed the tnsnames.ora file, or the reference to it in your odbc dsn definition.

                  Go back to the explanation on my blog, particularly http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

                  As you read through that, keep in mind that the odbc driver is doing exactly what sqlplus is doing in the discussion. As far as tns is concerned, sqlplus and odbc are both "just another client". What you enter at the command line in sqlplus is entered as part of the dsn definition for odbc.
                  1 person found this helpful
                  • 6. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                    869193
                    Thanks so much for the feedback, everyone. I was getting the "TNS could not resolve the connect identifier specified" when attempting to connect though ODBc, but then I tried from SQLplus and got: (which I think is a step in the right direction!)


                    Enter user-name: ub55975/ub55975passwd@chrdbprd02:1521/p016
                    ERROR:
                    ORA-12514: TNS:listener does not currently know of service requested in connect
                    descriptor


                    I'm going to keep working on it from here (and read up on forums about how to troubleshoot this error. Any and all resources are welcome. Thanks again!
                    • 7. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                      sb92075
                      MsMonroe wrote:
                      Thanks so much for the feedback, everyone. I was getting the "TNS could not resolve the connect identifier specified" when attempting to connect though ODBc, but then I tried from SQLplus and got: (which I think is a step in the right direction!)


                      Enter user-name: ub55975/ub55975_x@chrdbprd02:1521/p016
                      ERROR:
                      ORA-12514: TNS:listener does not currently know of service requested in connect
                      descriptor


                      I'm going to keep working on it from here (and read up on forums about how to troubleshoot this error. Any and all resources are welcome. Thanks again!
                      try below instead

                      sqlplus ub55975/ub55975_x@'chrdbprd02:1521/p016'
                      1 person found this helpful
                      • 8. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                        869193
                        I used the input you suggested and got: (but I wasn't sure if I was supposed to include the "sqlplus" part)

                        Enter user-name: sqlplus ub55975/ub55975passwd@'chrdbprd02:1521/p016'
                        SP2-0306: Invalid option.
                        Usage: CONN[ECT] [logon [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
                        where <logon> ::= <username>[<password>][@<connect_identifier>]
                        <proxy> ::= <proxyuser>[<username>][<password>][@<connect_identifier>]


                        without the "sqlplus" I got the same error as before:

                        Enter user-name: ub55975/ub55975passwd@'chrdbprd02:1521/p016'
                        ERROR:
                        ORA-12514: TNS:listener does not currently know of service requested in connect
                        descriptor
                        • 9. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                          869193
                          Also:

                          When I attempt to connect from ODBC I get the "TNS: could not resolve the connect identifier specified", however when I use SQLPlus and attempt to connect that way, I get a different error (see below)


                          Enter user-name: ub55975/ub55975passwd@chrdbprd02:1521/p016
                          ERROR:
                          ORA-12514: TNS:listener does not currently know of service requested in connect
                          descriptor

                          Does that make sense to have different errors like that? And which method of connecting should I keep trying? I'm open for anything, since Ive been working on this for days!! Thanks again everyone.
                          • 10. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                            sb92075
                            ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
                            One cause of this problem is when the Oracle database is down & needs to be started.
                            A remote client send a request to the Listener asking to be connected to a specific service.
                            If/when the listener does not know anything about that service, the listener responds with ORA-12514
                            Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.
                            This line contains valuable debugging details. So post this line & surrounding lines.
                            1 person found this helpful
                            • 11. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                              869193
                              Hmmmm Interestingly enough, the digits 12514 don't appear anywhere in the listner.log file, even though I am definitely getting that error over and over! Ill paste the last 10 lines or so of the listener.log file in case you can see any hints as to what's going on! Thanks!

                              15-JUN-2011 13:08:45 * ping * 0

                              15-JUN-2011 13:08:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=XPN-CND015030P.uboc-ad.corp.uboc.com)(PORT=1521)))(VERSION=169869568)) * status * 0

                              15-JUN-2011 13:08:53 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\app\ub55975\product\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=XPN-CND015030P)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.151.128.38)(PORT=4195)) * establish * orcl * 0

                              15-JUN-2011 13:11:08 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=services)(ARGUMENTS=64)(SERVICE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XPN-CND015030P.uboc-ad.corp.uboc.com)(PORT=1521))))(VERSION=169869568)) * services * 0

                              15-JUN-2011 13:11:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=XPN-CND015030P.uboc-ad.corp.uboc.com)(PORT=1521)))(VERSION=169869568)) * status * 0

                              15-JUN-2011 13:11:33 * service_update * orcl * 0

                              15-JUN-2011 13:17:24 * service_update * orcl * 0

                              15-JUN-2011 13:20:29 * service_update * orcl * 0

                              15-JUN-2011 13:20:37 * service_died * orcl * 12537
                              • 12. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                                sb92075
                                the digits 12514 don't appear anywhere in the listner.log file,
                                Different listener? Different system?

                                FWIW - IMO posted connection sting is malformed

                                SQL> username/password@tns_alias

                                without port# and SID included.

                                If you get desperate, utilize wireshark or etheral to packet sniff between client & DB Server
                                • 13. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                                  orafad
                                  To rephrase sb's suggestion:
                                  C:\>sqlplus username@'localhost:1521/xe'
                                  
                                  SQL*Plus: Release 11.2.0.2.0 Beta on MÕ Jun 20 20:04:05 2011
                                  
                                  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                                  
                                  Enter password: <enter password at this prompt>
                                  
                                  Connected to:
                                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
                                  
                                  SQL>
                                  (Replace username, localhost, portno (1521), service name (xe) with the actual connection info for your remote db server.)


                                  About listener.log, you need to look in the log at the "chrdbprd02" host for line/response corresponding to ORA-12514.
                                  1 person found this helpful
                                  • 14. Re: beginner connecting to Oracle using ODBC (11.2.0.2.0)
                                    869193
                                    Okay I tried your suggestion and got a NEW error! :)


                                    Enter user-name: ub55975@chrdbprd02:1521/p016
                                    ERROR:
                                    ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                                    1 2 Previous Next