- Introduction:
1. Connectivity should be established between source and target environments. Crosscheck with ping or telnet command.
2. MySQL connector odbc package should be installed on Oracle DB server. Crosscheck with
“rpm -qa|grep mysql” command.
mysql-community-client-8.0.35-1.el8.x86_64
mysql-connector-odbc-8.0.26-1.el7.x86_64
| ## | Source | Target |
|---|---|---|
| Database | Oracle 19c | MySQL |
| IP | 192.168.80.33 | 10.192.225.144 |
| DB Name | ORCLPRD | mysql_db |
| Port | 1521 | 5544 |
| Username | sys | datalake |
|
[root@oracle01~]# yum install mysql-connector-odbc-8.0.26-1.el7.x86_64 Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package mysql-connector-odbc.x86_64 0:8.0.26-1.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ==================================================================================== Package Arch Version Repository Size ==================================================================================== Installing: mysql-connector-odbc x86_64 8.0.26-1.el7 mysql-connectors-community 4.1M Transaction Summary ==================================================================================== Install 1 Package Total download size: 4.1 M Installed size: 22 M Is this ok [y/d/N]: y Downloading packages: mysql-connector-odbc-8.0.26-1.el7.x86_64.rpm | 4.1 MB 00:00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql-connector-odbc-8.0.26-1.el7.x86_64 1/1 Success: Usage count is 1 Success: Usage count is 1 Verifying : mysql-connector-odbc-8.0.26-1.el7.x86_64 1/1 Installed: mysql-connector-odbc.x86_64 0:8.0.26-1.el7 Complete! [root@oracle01~]# |
2. Now create odbc.ini file in /etc location with below contents. It will create the data source for MySQL DB. If any existing file is there, then take the backup and add these entries. You may require root user access to edit this file.
|
[oracle@oracle01 admin]$ cat /etc/odbc.ini [mysql_db] Description = MYSQL Driver = /usr/lib64/libmyodbc8a.so Server = 10.192.225.144 User = datalake Password = Admin@#1234 Port = 5544 Database = mysql_db OPTION = 0 TRACE = OFF [oracle@oracle01 admin]$ |
|
[oracle@oracle01 admin]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/hs/admin [oracle@oracle01 admin]$ cat initmysql_db.ora HS_FDS_CONNECT_INFO = mysql_db HS_FDS_TRACE_LEVEL = off HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so HS_FDS_FETCH_ROWS = 1 HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=american_america.we8iso8859P1 HS_NLS_NCHAR=UCS2 |
|
[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=mysql_db) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1) (PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/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) ) ) mysql_db = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) # IP Oracle Database (CONNECT_DATA=(SID=mysql_db)) (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 20-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 20-FEB-2025 11:50:17 Uptime 0 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 "mysql_db" has 1 instance(s). Instance "mysql_db", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@oracle01 admin]$ [oracle@oracle01 admin]$ tnsping "mysql_db" TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-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=mysql_db)) (HS=OK)) OK (0 msec) |
|
[oracle@oracle01 ~]$ isql -v mysql_db +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases +-----------------------------------------------------------------+ | Database | +-----------------------------------------------------------------+ | information_schema | | daily_schedular_db | | mysql_db | | mysql | | performance_schema | | test | +-----------------------------------------------------------------+ SQLRowCount returns 6 6 rows fetched SQL> [oracle@oracle01 ~]$ mysql -udatalake -p -h10.192.225.144 -P5544 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1094140 Server version: 5.6.47 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | daily_schedular_db | | mysql_db | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) |
-p --> Password
-h --> Host
-P --> Port &
-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_MYSQL_DBLINK CONNECT TO "datalake" IDENTIFIED BY "Admin@#1234" USING 'mysql_db'; 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_MYSQL_DBLINK datalake mysql_db 20-FEB-25 SQL> select sysdate from dual@ORACLE_TO_MYSQL_DBLINK; SYSDATE --------- 20-FEB-25 SQL> select * from dual@ORACLE_TO_MYSQL_DBLINK; D - X SQL> |
Thanks for visiting!!
What Next --> Oracle to MSSQL Server DB Link
0 comments:
Post a Comment