0 Replies Latest reply: Apr 5, 2013 5:40 AM by 1001264 RSS

    Oracle11g(64bit) link to SQL Server 2012

    1001264
      Hi,

      I am trying to establish link from my Oracle 11g 64 Bit server to SQL server 2012, following are the steps followed as of yet and its not working.

      Operating system = windows server 2008 R2

      Steps are followed as given in Oracle guide as well as verified across different blog-posts.

      step 1- Created ODBC system DSN from "From the Start menu, choose Administrative Tools> select ODBC." then SQL Server DSN is created and tested successfully. DSN="SQLSRV"


      step 2 - Created file "initSQLSRV.ORA" with below configuration at path = Oracle11g\product\11.2.0\dbhome_1\hs\admin
      HS_FDS_CONNECT_INFO = SQLSRV
      HS_FDS_TRACE_LEVEL = ADMIN
      step 3 - updated listener.ora with below configuration
      MSLISTENER =
         (DESCRIPTION_LIST =
          (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP)(HOST = myserv.myoffice.com)(PORT = 1523))
          )
        )
      
      SID_LIST_MSLISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = SQLSRV)
          (ORACLE_HOME = D:\Oracle11g\product\11.2.0\dbhome_1)
          (PROGRAM = hsodbc)
          (ENVS = "LD_LIBRARY_PATH = D:\Oracle11g\product\11.2.0\dbhome_1\LIB")
        )
      )
      step 4 - updated tnsnames.ora with below configuration
      SQLSRV =
      (DESCRIPTION =
          (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = myserv.myoffice.com)(PORT = 1523))
          )
          (CONNECT_DATA =
                (SID = SQLSRV)
                (SERVER=DEDICATED)
           )
           (HS = OK)
      )
      step 5 - restarted listener with bellow commands

      1. lsnrctl reload mslistener
      2. lsnrctl status mslistener
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserv.myoffice.com)(PORT=1523)))
      STATUS of the LISTENER
      ------------------------
      Alias                     mslistener
      Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
      Start Date                04-APR-2013 18:55:50
      Uptime                    0 days 20 hr. 59 min. 4 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   D:\Oracle11g\product\11.2.0\dbhome_1\network\admin\listener.ora
      Listener Log File         d:\oracle11g\product\11.2.0\dbhome_1\log\diag\tnslsnr\myserv\mslistener\alert\log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserv.myoffice.com)(PORT=1523)))
      Services Summary...
      Service "SQLSRV" has 1 instance(s).
        Instance "SQLSRV", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      3. lsnrctl service mslistener
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserv.myoffice.com)(PORT=1523)))
      Services Summary...
      Service "SQLSRV" has 1 instance(s).
        Instance "SQLSRV", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      The command completed successfully
      step 6 - Now I try ti tnsping the SQLSRV SID and I get below error message
      Used parameter files:
      D:\Oracle11g\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
      
      TNS-03505: Failed to resolve name
      step 7 - when I try to test the service(SQLSRV) via NET Manager utility, I get below error-
      ORA-12518: TNS:listener could not hand off client connection
      step 8 - The detail error in the log file is -
       05-APR-2013 15:58:44 * (CONNECT_DATA=(SID=SQLSRV)(SERVER=DEDICATED)(CID=(PROGRAM=D:\Oracle11g\product\11.2.0\dbhome_1\jdk\jre\bin\java.exe)(HOST=myserv)(USER=152803))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.224.30.144)(PORT=59696)) * establish * SQLSRV * 12518
      TNS-12518: TNS:listener could not hand off client connection
       TNS-12560: TNS:protocol adapter error
        TNS-00530: Protocol adapter error
         64-bit Windows Error: 2: No such file or directory
      Well, From what i have read, there are couple of things that might be the key problem

      1. hsodbc might not work on 64bit (is it?)
      2. there is already one active SID in oracle hence I need to change the SID using oradim utility (please confirm)


      I appreciate any help on this matter.