This discussion is archived
14 Replies Latest reply: Aug 8, 2012 5:05 AM by 752126 RSS

Connecting SQL server from Oracle 11g

Sam.G Newbie
Currently Being Moderated
Hello,
I know there are several threads available to this post but please help me in
*step by step process to follow in connecting 2 servers.
I tried everything in internet but got Connection closed error after tnsping of DNS name. Do i need to run any instance sort of thing in SQL server?
I dont have any access on SQL server.Whatever i have to do that has to be only in Oracle server.I got all those information and created ODBC (using SQL server driver) and tested the connection successfully in Oracle server. Altered INIT*.ORA,TNSNAMES.ORA and LISTENER.ORA but when pinging TNS awfully getting the afore said error.

My environment : Oracle 11g server - SQL server 2008 ( both in windows platform).

My
TNS names entry


GSIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 63455))
(CONNECT_DATA = (SID=GSIS)
(SERVER = DEDICATED)
(HS=OK)
)
)


Listener entry


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = GSIS)
(ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
(PROGRAM = hsodbc)
)
)


and

initGSIS


entry


# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = GSIS
HS_FDS_TRACE_LEVEL = OFF


#
# Environment variables required for the non-Oracle system
#
#set


**************************************************
when tnsping i am getting

TNS connection closed.

I am awfully stuck here.

Please guide..
  • 1. Re: Connecting SQL server from Oracle 11g
    kgronau Guru
    Currently Being Moderated
    There are a couple of issues:

    Listener.ora:
    (SID_DESC =
    (SID_NAME = GSIS)
    (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
    (PROGRAM = hsodbc)

    in 11.2 the program executable is called DG4ODBC
    so it loks like:
    (SID_DESC =
    (SID_NAME = GSIS)
    (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
    (PROGRAM = dg4odbc)


    Once you have changed the listener you should STOP and START it in the command line window using lsnrctl: lsnrctl stop then lsnrctl start and the service summary should then show for GSIS 1 service handler in status unknwon.


    the GSIS tns entry
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 63455))
    does not point to your Oracle listener
    (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
    and the HS key word is not placed coerrectly:
    GSIS =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
    (CONNECT_DATA = (SID=GSIS))
    (HS=OK)
    )

    Best would be to check out this note available on My Oracle Support portal:
    For 32bit Windows:
    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install          [Document 466225.1]     
    and for 64bit Windows:
    Document 1266572.1 How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install
  • 2. Re: Connecting SQL server from Oracle 11g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    There are several problems with your configuration files.

    1. Listener.ora - you have -


    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
    (SID_NAME = GSIS)
    (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
    (PROGRAM = hsodbc)
    )
    )

    but it should be -

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
    (SID_NAME = GSIS)
    (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
    (PROGRAM = dg4odbc)
    )
    )

    For 11g the program is dg4odbc and not hsodbc.

    2. tnsnames - you have -

    GSIS =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 63455))
    (CONNECT_DATA = (SID=GSIS)
    (SERVER = DEDICATED)
    (HS=OK)
    )
    )

    but it should be -

    GSIS =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
    (CONNECT_DATA = (SID=GSIS)
    (SERVER = DEDICATED)
    (HS=OK)
    )
    )


    the host and port are those of the listener not of SQL*Server.

    Make the changes, stop and start the gateway listener and try again.

    Regards,
    Mike
  • 3. Re: Connecting SQL server from Oracle 11g
    kgronau Guru
    Currently Being Moderated
    Mike,
    the HS keyword is also not correct ... see my posting above ...
  • 4. Re: Connecting SQL server from Oracle 11g
    Sam.G Newbie
    Currently Being Moderated
    Hello Mike,
    Thank you for replying.
    The SQL server's IP adress is 10.1.1.1 ( for instance) with port number 63455.
    Oracle database address is COB417147.cob.apac.bosch.com.
    What should i do now?

    Thanks again.

    Regards,
    Sam.
  • 5. Re: Connecting SQL server from Oracle 11g
    kgronau Guru
    Currently Being Moderated
    check out my first posting and correct your listener as the executable is still wrong. The provide the listener start output generated from lsnrctl
  • 6. Re: Connecting SQL server from Oracle 11g
    Sam.G Newbie
    Currently Being Moderated
    Hello ,
    The above said corrections are made and still shows the same error.
    Do i need to do something in SQL server while pinging TNS?

    Where to find the note?

    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install [Document 466225.1]

    Kindly guide.

    Thanks for understanding,
    Regards,
    Sam.
  • 7. Re: Connecting SQL server from Oracle 11g
    kgronau Guru
    Currently Being Moderated
    please provide the lsnrctl status output and also the tnsping output.

    BTW, did you install dg4odbc 32 or 64bit and what is the word size of your operating system?
  • 8. Re: Connecting SQL server from Oracle 11g
    Sam.G Newbie
    Currently Being Moderated
    Status

    C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\BIN>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-MAY-2012 17:48
    :14

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
    ction
    Start Date 11-MAY-2012 17:36:30
    Uptime 0 days 0 hr. 11 min. 48 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\network\
    admin\listener.ora
    Listener Log File c:\oracle\app\amk1cob\diag\tnslsnr\COB417147\listener\
    alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=COB417147.cob.apac.bosch.com)(PORT=1
    522)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=COB417147.cob.apac.bosch.com)(PORT=8
    080))(Presentation=HTTP)(Session=RAW))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "GSIS" has 1 instance(s).
    Instance "GSIS", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl.cob.apac.bosch.com" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB.cob.apac.bosch.com" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully


    TNS ping

    C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\BIN>tnsping GSIS

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-MAY-2
    012 18:21:55

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.
    1.1)(PORT = 63455)) (CONNECT_DATA = (SID=GSIS)) (HS=OK))
    TNS-12537: TNS:connection closed

    C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\BIN>


    BTW it is 32-bit machine.

    Edited by: 933729 on May 11, 2012 5:57 AM
  • 9. Re: Connecting SQL server from Oracle 11g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    You still need to change your tnsnames.ora entry.
    As said earlier it should look like this -

    GSIS =
    <space>(DESCRIPTION =
    <space>(ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
    <space>(CONNECT_DATA = (SID=GSIS))
    <space>(HS=OK)
    <space>)

    it needs to refer to the listener host and port, not the SQL*Server host and port.
    I've also added <space> to represent there should be at least one space at the beginning of each line, as they get lost in the posts.
    You don't need to do anything on the SQL*Server. The gateway communicates with it using the System DSN.

    Please make the change and let us know what happens.

    Regards,
    Mike

    Edited by: mkirtley on May 11, 2012 2:09 PM
  • 10. Re: Connecting SQL server from Oracle 11g
    Sam.G Newbie
    Currently Being Moderated
    Hi,

    Thank you Mike. It is working fine i.e) TNS is pinging finally by altering it to host's address in TNS.
    Please clarify how it refers SQL server without giving any entries anywhere regarding SQL credentials. I have created DSN.Does this do that job?
    I have created DBlink

    create database link GSIS_PQSR
    connect to USER_NAME identified by PASSWORD using 'GSIS';

    select * from t_pmap_test_for_int@GSIS;

    Now i am getting ORA-02019: connection description for remote database not found

    Please guide.

    Thank you,

    Regards,

    Sam.

    Edited by: 933729 on May 11, 2012 6:17 AM
  • 11. Re: Connecting SQL server from Oracle 11g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Sam,
    The connection between DG4ODBC and SQL*Server is handled by the information and driver specified in the system DSN defined as the HS_FDS_CONNECT_INFO parameter.
    When you create the database link you should double quotes round the user and password as SQL*Server is case sensitive. If quotes are not used they are translated to uppercase, so try -

    create database link GSIS_PQSR
    connect to "USER_NAME" identified by "PASSWORD" using 'GSIS';

    in the correct case as defined on SQL*Server.

    For the ora-2019 check that -

    - initGSIS.ora is in the directory C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\hs\admin
    - that the tnsnames.ora entry is in the directory $ORACLE_HOME\network\admin for the RDBMS not the C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\network\admin for the gateway.

    Regards,
    Mike
  • 12. Re: Connecting SQL server from Oracle 11g
    Sam.G Newbie
    Currently Being Moderated
    Hello Mike,

    Got it!
    You are the best in business.
    Hats off to you.
    With out your help nothing could have been done.
    Thank you once again for your precious comments.

    Regards,
    Sam.
  • 13. Re: Connecting SQL server from Oracle 11g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi Sam,
    That's good, glad you got it working and thanks for the feedback.

    Regards,
    Mike
  • 14. Re: Connecting SQL server from Oracle 11g
    752126 Newbie
    Currently Being Moderated
    Hi Mike,

    I am raising my question in this thread becuase through this thread and replies I have sucessfuly established the connectivity between Oracle 11G and SQl Server 2008.

    Thanks a lot for the help, but now I have 1 issue I am not able to call or execute the SP's or function of sql server in oracle 11G.

    Can you please suggest or help me.

    Thanks

    Regards
    Anant.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points