Hostname | Port | DB Name | |
Oracle Database (Source) | esesslxXXXX.world | 1521 | ORAPROD1 |
SQLSERVER Data base (Target) | ESESSMW9999.world | 1433 | SQLDB_PROD |
Oracle Gateway | esesslxXXXX.world | 1522 | DG4MSQL |
Following steps are involved..
-bash-3.00$ cd /software/11gR2-Gateways/gateways/ -bash-3.00$ ./runInstaller
Select PATH for the Oracle Home. The Path defaults to Oracle Home. No need to change the same
Once it prompts with above screen, open another shell and run the script as root
Click Close to Finish the installation.
esesslxXXXX.world:ORAPROD1:> telnet ESESSMW9999.world 1433 Trying 153.88.xxx.xx... Connected to ESESSMW9999.world.
Make sure that the MS SQL database details are correct in the dg4msql parameter file.
esesslxXXXX.world:ORAPROD1:> cat initdg4msql1.ora # This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Microsoft SQL Server # # HS init parameters # HS_FDS_CONNECT_INFO=[ESESSMW9999.world]:1433//SQLDB_PROD
HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER
LISTENER_DG4MSQL = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= TCP)(Host= esesslxXXXX.world)(Port= 1522)) ) SID_LIST_LISTENER_DG4MSQL = (SID_LIST = (SID_DESC = (SID_NAME = dg4msql) (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql) (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/) (PROGRAM = dg4msql) ) (SID_DESC = (SID_NAME = dg4msql1) (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql) (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/) (PROGRAM = dg4msql) ) )
esesslxXXXX.world:ORAPROD1:> lsnrctl stat LISTENER_dg4msql LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2018 10:49:48 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=esesslxXXXX.world)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER_dg4msql Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 29-JAN-2018 10:41:11 Uptime 0 days 0 hr. 8 min. 36 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/12.2.0.1/db/network/admin/listener.ora Listener Log File /opt/oracle/12.2.0.1/db/network/log/listener_dg4msql1.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=esesslxXXXX.world)(PORT=1522))) Services Summary... Service "dg4msql" has 1 instance(s). Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
dg4msql = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522)) (CONNECT_DATA= (SID=dg4msql)) (HS=OK))
-bash-3.00$ tnsping dg4msql TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-MAR-2018 11:27:09 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /opt/oracle/12.2.0.1/db/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522)) (CONNECT_DATA= (SID=dg4msql)) (HS=OK)) OK (10 msec)
SQL> CREATE PUBLIC DATABASE LINK SQLDB_DBLINK CONNECT TO <username> IDENTIFIED BY <password> using 'dg4msql'; Database link created.
SQL> select count(*) from T_PA_OBJECTS@SQLDB_DBLINK; COUNT(*) -------- 16402
This concludes setup steps to read and write data of Microsoft SQL server tables from Oracle database.