- 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