- Introduction:
1. Connectivity should be established between source and target environments. Crosscheck with ping or telnet command.
2. MSSQL Server ODBC Drivers should be installed on Oracle DB server. Below are the list of packages.
a) msodbcsql17
b) mssql-tools
c) unixODBC-devel
| ## | Source | Target |
|---|---|---|
| Database | Oracle | MSSQL Server |
| IP | 192.168.80.33 | 10.50.19.5 |
| DB Name | ORCLPRD | MSSQLDB |
| Port | 1521 | 1433 |
| Username | sys | ViewSQL |
|
[root@oracle01 ~]# yum install -y msodbcsql17 Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package msodbcsql17.x86_64 0:17.9.1.1-1 will be installed --> Finished Dependency Resolution Dependencies Resolved Do you accept the license terms? (Enter YES or NO) YES Installing : msodbcsql17-17.9.1.1-1.x86_64 1/1 Verifying : msodbcsql17-17.9.1.1-1.x86_64 1/1 Installed: msodbcsql17.x86_64 0:17.9.1.1-1 Complete! [root@oracle01 ~]# [root@oracle01 ~]# yum install -y mssql-tools Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package mssql-tools.x86_64 0:17.9.1.1-1 will be installed --> Finished Dependency Resolution Do you accept the license terms? (Enter YES or NO) YES Installing : mssql-tools-17.9.1.1-1.x86_64 1/1 Verifying : mssql-tools-17.9.1.1-1.x86_64 1/1 Installed: mssql-tools.x86_64 0:17.9.1.1-1 Complete! [root@oracle01 ~]# [root@oracle01 ~]# yum install -y unixODBC-devel Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check Installing : unixODBC-devel-2.3.7-1.rh.x86_64 2/3 Cleanup : unixODBC-2.3.1-14.0.1.el7.x86_64 3/3 Verifying : unixODBC-devel-2.3.7-1.rh.x86_64 1/3 Verifying : unixODBC-2.3.7-1.rh.x86_64 2/3 Verifying : unixODBC-2.3.1-14.0.1.el7.x86_64 3/3 Installed: unixODBC-devel.x86_64 0:2.3.7-1.rh Dependency Updated: unixODBC.x86_64 0:2.3.7-1.rh Complete! [root@oracle01 ~]# |
|
[oracle@oracle01 ~]$ cat /etc/odbc.ini [MSSQL] Description = MSSQL Driver = /usr/lib64/libmsodbcsql-17.so Server = 10.50.19.5 User = ViewSQL Password = T$eT$2025 Port = 1433 Database = MSSQLDB [oracle@oracle01 ~]$ |
|
[oracle@oracle01 admin]$pwd /u01/app/oracle/product/19.0.0/dbhome_1/hs/admin [oracle@oracle01 admin]$ cat initMSSQL.ora #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 = MSSQL HS_FDS_TRACE_LEVEL = user HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log HS_NLS_NCHAR = UCS2 # # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini # # Environment variables required for the non-Oracle system # [oracle@oracle01 admin] |
|
[oracle@oracle01 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCLPRD) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = ORCLPRD) ) (SID_DESC= (SID_NAME=MSSQL) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1) (PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/opt/microsoft/msodbcsql17/lib64:/u01/app/oracle/product/19.0.0/dbhome_1/lib") ) ) [oracle@oracle01 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCLPRD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPRD) ) ) MSSQL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) # IP Oracle Database (CONNECT_DATA=(SID=MSSQL)) (HS=OK) ) [oracle@oracle01 admin]$ |
5. Check Listener status and test tnsping command.
|
[oracle@oracle01 admin]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2025 11:50:17 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle01)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 10-FEB-2025 11:50:17 Uptime 1 days 8 hr. 10 min. 48 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ORCLPRD" has 2 instance(s). Instance "ORCLPRD", status UNKNOWN, has 1 handler(s) for this service... Instance "ORCLPRD", status READY, has 1 handler(s) for this service... Service "ORCLPRDXDB" has 1 instance(s). Instance "ORCLPRD", status READY, has 1 handler(s) for this service... Service "MSSQL" has 1 instance(s). Instance "MSSQL", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@oracle01 admin]$ [oracle@oracle01 admin]$ tnsping "MSSQL" TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2025 11:51:07 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) (CONNECT_DATA=(SID=MSSQL)) (HS=OK)) OK (0 msec) |
|
[oracle@oracle01 lib64]$ /opt/mssql-tools/bin/sqlcmd -D -S MSSQL -U ViewSQL Password: 1> select count(*) from users 2> go ----------- 1083 (1 rows affected) 1> 2> quit [oracle@oracle01 lib64]$ [oracle@oracle01 lib64]$ [oracle@oracle01 lib64]$ isql -v MSSQL ViewSQL T$eT$2025 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select count(*) from users +------------+ | | +------------+ | 1083 | +------------+ SQLRowCount returns 0 1 rows fetched SQL> [oracle@oracle01 lib64]$ |
-D --> Quoted identifiers by default.
-S --> SQL Server instance &
-v --> Verbose
7. Now we will create the DB link. If you want to create a private DB link then you need to login in Oracle database with the user in which you want the DB link. Here we are creating Public DB link.
|
[oracle@oracle01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 12:07:08 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> CREATE PUBLIC DATABASE LINK ORACLE_TO_MSSQL_DBLINK CONNECT TO "ViewSQL" IDENTIFIED BY "T$eT$2025" USING 'MSSQL'; Database link created. SQL> set lines 333 pages 333 col OWNER for a20 col DB_LINK for a80 col USERNAME for a20 col HOST for a30 col CREATED for a10 select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links; SQL> OWNER DB_LINK USERNAME HOST CREATED --------- ------------------------------- --------- ----------- ---------- SYS SYS_HUB SEEDDATA 17-APR-19 PUBLIC ORACLE_TO_MSSQL_DBLINK ViewSQL MSSQL 21-FEB-25 SQL> select sysdate from dual@ORACLE_TO_MSSQL_DBLINK; SYSDATE --------- 21-FEB-25 SQL> select * from dual@ORACLE_TO_MSSQL_DBLINK; D - X SQL> |
Thanks for visiting!!
What Next --> Oracle to MySQL DB Link
0 comments:
Post a Comment