{"id":3443,"date":"2014-01-07T11:29:06","date_gmt":"2014-01-07T11:29:06","guid":{"rendered":"http:\/\/www.oratraining.com\/blog\/?p=3443"},"modified":"2014-01-08T15:08:05","modified_gmt":"2014-01-08T15:08:05","slug":"steps-by-step-connecting-to-microsoft-sql-database-from-oracle-database-using-database-gateway-for-sql-server","status":"publish","type":"post","link":"https:\/\/www.oratraining.com\/blog\/2014\/01\/steps-by-step-connecting-to-microsoft-sql-database-from-oracle-database-using-database-gateway-for-sql-server\/","title":{"rendered":"Steps-by-step: Connecting to Microsoft SQL Server database from Oracle using Database Gateway for SQL Server"},"content":{"rendered":"

Starting with 11g Oracle now provides a Database Gateway for MS SQL Server and various other databases.<\/p>\n

The gateway is certified with Oracle database release 10.1.0.5, 10.2.0.3 after applying the gateway compatibility patch bug or you can directly use it with Oracle RDBMS 10.2.0.4, 10.2.0.5, 11.1 and 11.2<\/p>\n

 <\/p>\n

Following steps are involved. Please note that this steps are shown for Linux\/Unix platform but for Windows also similar steps are to be followed.<\/p>\n

    \n
  1. Download Oracle Database Gateways CD if you have not already installed it<\/li>\n
  2. Install Oracle Database Gateway for Microsoft SQL Server<\/li>\n
  3. Configure Database Gateway for Microsoft SQL Server (DG4MSQL)<\/li>\n<\/ol>\n

     <\/p>\n

    Download the software<\/strong><\/p>\n

    Download Oracle Database Gateways from Oracle eDelivery site.<\/p>\n

    \"dg4msql-1\"<\/a><\/p>\n

     <\/p>\n

    Install Oracle Database Gateway for Microsoft SQL server<\/strong><\/p>\n

    Unzip the downloaded zip file. It will create a directory named “gateways”
    \n<\/strong><\/p>\n

    Change the directory to gateways<\/strong> and start installer.<\/p>\n

     <\/p>\n

    -bash-3.00$ cd \/software\/11gR2-Gateways\/gateways\/
    \n<\/strong><\/p>\n

    -bash-3.00$ .\/runInstaller<\/strong><\/p>\n

     <\/p>\n

     <\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    \"\"<\/p>\n

    Click Next<\/strong><\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    Select name for the Oracle Home. The Path defaults to Oracle Home. No need to change the same.<\/p>\n

     <\/p>\n

    \"\"<\/p>\n

    \"\"<\/p>\n

    Select Oracle Database Gateway for Microsoft SQL Server<\/strong> and click Next<\/strong><\/p>\n

    \"\"<\/p>\n

    Provide any existing Microsoft SQL Server details where you are planning to connect. It actually writes this in a config file and does not really verify.<\/p>\n

    This can be changed later also in the configuration file. Click Next<\/strong><\/p>\n

    \"\"<\/p>\n

    Click Install<\/strong><\/p>\n

     <\/p>\n

    \"\"<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    Once it prompts with above screen, open another shell and run the script as root<\/p>\n

    root@host # \/app\/oracle\/product\/11.2.0\/dbhome_1\/root.sh<\/strong><\/p>\n

    Running Oracle 11g root.sh script…<\/p>\n

     <\/p>\n

    The following environment variables are set as:<\/p>\n

    ORACLE_OWNER= ora11g<\/p>\n

    ORACLE_HOME= \/app\/oracle\/product\/11.2.0\/dbhome_1<\/p>\n

     <\/p>\n

    Enter the full pathname of the local bin directory: [\/usr\/local\/bin]:<\/p>\n

    The file “dbhome” already exists in \/usr\/local\/bin. Overwrite it? (y\/n) [n]: y<\/p>\n

    Copying dbhome to \/usr\/local\/bin …<\/p>\n

    The file “oraenv” already exists in \/usr\/local\/bin. Overwrite it? (y\/n) [n]: y<\/p>\n

    Copying oraenv to \/usr\/local\/bin …<\/p>\n

    The file “coraenv” already exists in \/usr\/local\/bin. Overwrite it? (y\/n) [n]: y<\/p>\n

    Copying coraenv to \/usr\/local\/bin …<\/p>\n

     <\/p>\n

    Entries will be added to the \/var\/opt\/oracle\/oratab file as needed by<\/p>\n

    Database Configuration Assistant when a database is created<\/p>\n

    Finished running generic part of root.sh script.<\/p>\n

    Now product-specific root actions will be performed.<\/p>\n

    Finished product-specific root actions.<\/p>\n

     <\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    Click Exit<\/strong> to Finish the installation.<\/p>\n

     <\/p>\n

    Configure Oracle Database Gateway for Microsoft SQL Server
    \n<\/strong><\/span><\/p>\n

     <\/p>\n

    Make sure that the MS SQL database details are correct in the dg4msql parameter file.<\/p>\n

     <\/p>\n

    -bash-3.00$ more $ORACLE_HOME\/dg4msql\/admin\/initdg4msql.ora
    \n<\/strong><\/p>\n

    # This is a customized agent init file that contains the HS parameters<\/p>\n

    # that are needed for the Database Gateway for Microsoft SQL Server<\/p>\n

     <\/p>\n

    #<\/p>\n

    # HS init parameters<\/p>\n

    #<\/p>\n

    HS_FDS_CONNECT_INFO=[SERVER127]:4076\/\/NewCDP16<\/p>\n

    # alternate connect format is hostname\/serverinstance\/databasename<\/p>\n

    HS_FDS_TRACE_LEVEL=OFF<\/p>\n

    HS_FDS_RECOVERY_ACCOUNT=RECOVER<\/p>\n

    HS_FDS_RECOVERY_PWD=RECOVER<\/p>\n

     <\/p>\n

    -bash-3.00$ cd $ORACLE_HOME\/network\/admin<\/strong><\/p>\n

    Append following in existing listener.ora. <\/strong>Change the name, path and port as required.<\/p>\n

     <\/p>\n

    LISTENER_dg4mssql<\/strong>=<\/p>\n

    (DESCRIPTION_LIST =<\/p>\n

    (DESCRIPTION =<\/p>\n

    (ADDRESS_LIST =<\/p>\n

    (ADDRESS = (PROTOCOL = TCP)(HOST = <oracle host or IP>) (PORT = <any unused port>))<\/p>\n

    )<\/p>\n

    )<\/p>\n

    )<\/p>\n

     <\/p>\n

    SID_LIST_LISTENER_dg4mssql<\/strong>=<\/p>\n

    (SID_LIST=<\/p>\n

    (SID_DESC=<\/p>\n

    (SID_NAME=dg4msql)<\/p>\n

    (ORACLE_HOME=\/app\/oracle\/product\/11.2.0\/dbhome_1)<\/p>\n

    (ENV=”LD_LIBRARY_PATH=\/app\/oracle\/product\/11.2.0\/dbhome_1\/dg4msql\/driver\/lib:\/app\/oracle\/product\/11.2.0\/dbhome_1\/lib”)<\/p>\n

    (PROGRAM=dg4msql)<\/p>\n

    )<\/p>\n

    )<\/p>\n

     <\/p>\n

    Start the newly created listener.
    \n<\/strong><\/p>\n

    -bash-3.00$ lsnrctl start LISTENER_dg4mssql<\/strong><\/p>\n

     <\/p>\n

    Verify that the service is registered with the listener .
    \n<\/strong><\/p>\n

    -bash-3.00$ lsnrctl status LISTENER_dg4mssql<\/strong><\/p>\n

    \u2026<\/p>\n

    Listening Endpoints Summary…<\/p>\n

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<oracle hostname or IP>)(PORT=1551)))<\/p>\n

    Services Summary…<\/p>\n

    Service “dg4msql” has 1 instance(s).<\/p>\n

    Instance “dg4msql”, status UNKNOWN, has 1 handler(s) for this service…<\/p>\n

    The command completed successfully<\/p>\n

     <\/p>\n

    Append following in tnsnames.ora
    \n<\/strong><\/p>\n

     <\/p>\n

    dg4msql =<\/p>\n

    (DESCRIPTION=<\/p>\n

    (ADDRESS=(PROTOCOL=tcp)(HOST=< oracle hostname or IP >)(PORT=1551))<\/p>\n

    (CONNECT_DATA=(SID=dg4msql))<\/p>\n

    (HS=OK)<\/p>\n

    )<\/p>\n

     <\/p>\n

    -bash-3.00$ tnsping dg4msql<\/strong><\/p>\n

     <\/p>\n

    TNS Ping Utility for Solaris: Version 11.2.0.1.0 – Production on 07-JAN-2014 12:30:08<\/p>\n

     <\/p>\n

    Copyright (c) 1997, 2009, Oracle. All rights reserved.<\/p>\n

     <\/p>\n

    Used parameter files:<\/p>\n

    \/app\/oracle\/product\/11.2.0\/dbhome_1\/network\/admin\/sqlnet.ora<\/p>\n

     <\/p>\n

     <\/p>\n

    Used TNSNAMES adapter to resolve the alias<\/p>\n

    Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=1551)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))<\/p>\n

    OK (0 msec)<\/p>\n

     <\/p>\n

    Create a new database link pointing to this TNS using SQL Server login credentials. This username must be already created in the Microsoft SQL Server database.<\/p>\n

     <\/p>\n

    SQL> CREATE DATABASE LINK sqlserver CONNECT TO “SQLUser” IDENTIFIED BY “SQLPass” USING ‘dg4msql’;<\/strong><\/p>\n

     <\/p>\n

    Database link created.<\/p>\n

     <\/p>\n

    SQL> select count(1) from “SQLView”@sqlserver;
    \n<\/strong><\/p>\n

     <\/p>\n

    COUNT(1)<\/p>\n

    ———-<\/p>\n

    24592<\/p>\n

     <\/p>\n

    This concludes setup steps to Read data of Microsoft SQL server table from Oracle database.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Starting with 11g Oracle now provides a Database Gateway for MS SQL Server and various other databases. The gateway is certified with Oracle database release 10.1.0.5, 10.2.0.3 after applying the gateway compatibility patch bug or you can directly use it with Oracle RDBMS 10.2.0.4, 10.2.0.5, 11.1 and 11.2   Following steps are involved. Please note […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3443","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/3443","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/comments?post=3443"}],"version-history":[{"count":7,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/3443\/revisions"}],"predecessor-version":[{"id":3451,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/3443\/revisions\/3451"}],"wp:attachment":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/media?parent=3443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/categories?post=3443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/tags?post=3443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}