Background:
In enterprises most cases their database infrastructures or applications are built on different databases, sometimes data needs to be transferred or synced from one database to another heterogenous database. Oracle supports heterogenous connectivity to popular databases like MySQL, SQL Server, MariaDB, DB2, Sybase and more on the list. Here we will try to establish connection from oracle to MariaDB and perform some DML on the remote MariaDB.
Workaround:
It is assumed that you have a MariaDB database installed and have a database named “hstest” and an user namead as “HSTEST”. Also there are some tables in the hstest database. If there are no existing user and database you can create one as below and insert demo data.
Create MariaDB database and test table:
[oracle@ltdb ~]$ mysql -uroot -p
MariaDB [(none)]> CREATE USER ‘HSTEST’@’%’ IDENTIFIED BY ‘user_password’;
MariaDB [(none)]> CREATE DATABASE HSTEST;
MariaDB [(none)]> USE HSTEST;
MariaDB [hstest]> CREATE TABLE `TEST` (`name` varchar(100) DEFAULT NULL, `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MariaDB [hstest]> INSERT INTO TEST(name, id) VALUES(‘demo user’,101);
MariaDB [hstest]> INSERT INTO TEST(name, id) VALUES(‘demo user 2’,102);
MariaDB [hstest]> INSERT INTO TEST(name, id) VALUES(‘demo user 3’,103);
MariaDB [hstest]> SELECT * FROM TEST;
+————-+——+
| name | id |
+————-+——+
| demo user | 101 |
| demo user 2 | 102 |
| demo user 3 | 103 |
+————-+——+
3 rows in set (0.001 sec)
MariaDB [(none)]> CREATE USER ‘HSTEST’@’%’ IDENTIFIED BY ‘user_password’;
MariaDB [(none)]> CREATE DATABASE HSTEST;
MariaDB [(none)]> USE HSTEST;
MariaDB [hstest]> CREATE TABLE `TEST` (`name` varchar(100) DEFAULT NULL, `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MariaDB [hstest]> INSERT INTO TEST(name, id) VALUES(‘demo user’,101);
MariaDB [hstest]> INSERT INTO TEST(name, id) VALUES(‘demo user 2’,102);
MariaDB [hstest]> INSERT INTO TEST(name, id) VALUES(‘demo user 3’,103);
MariaDB [hstest]> SELECT * FROM TEST;
+————-+——+
| name | id |
+————-+——+
| demo user | 101 |
| demo user 2 | 102 |
| demo user 3 | 103 |
+————-+——+
3 rows in set (0.001 sec)
Configure Heterogenous connectivity and perform DML:
- Install the ODBC driver.
[oracle@ltdb ~]$ sudo yum install unixODBC unixODBC-devel -y
If the oracle user doesn’t present in the sudoers file execute the wheel command under root to make the entry in sudoers.
[root@ltdb ~]# usermod -aG wheel oracle - Download the mariadb odbc connector and install, download the latest one by browsing to the mariadb site “https://downloads.mariadb.com/Connectors/odbc/”, change the below wget command according to the release. The installation of the connector library depends on the system if it is 64 bit then it will be under “/usr/lib64”, if it is 32 bit then “/usr/lib” directory.
[oracle@ltdb ~]$ mkdir odbc_package
[oracle@ltdb ~]$ cd odbc_package
[oracle@ltdb ~]$ wget https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.6/mariadb-connector-odbc-3.1.6-ga-rhel7-x86_64.tar.gz
[oracle@ltdb ~]$ tar -xvzf mariadb-connector-odbc-3.0.8-ga-rhel7-x86_64.tar.gz
[oracle@ltdb ~]$ sudo install lib64/libmaodbc.so /usr/lib64/ - Validate ODBC installed correctly and show the paths.
[oracle@ltdb ~]$ odbcinst -j -
Configuring Database Gateway for ODBC (dg4odbc) to use the Data Source Name(DSN), the below entry should be made under the $ORACLE_HOME/hs/admin/initSID_NAME.ora file as we are using Oracle Heterogenous Connectivity.In this case our SID name is mariadb so the file name should be initmariadb.ora. Make the below entries to the file, save and exit.
[oracle@ltdb admin]$ cd $ORACLE_HOME/hs/admin
[oracle@ltdb admin]$ vi initmariadb.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# directory /u01/app/oracle/product/12.2.0/dbhome_1/hs/admin#
# HS init parameters
#
HS_FDS_CONNECT_INFO = hstest
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmaodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=american_america.we8iso8859P1
HS_NLS_NCHAR=UCS2#
# ODBC specific environment variables
#
set ODBCINI= /home/oracle/.odbc.ini
set LD_LIBRARY_PATH=/usr/lib64#
# Environment variables required for the non-Oracle system
#
set HOME=/home/oracle -
The Data Source Name(DSN) file “odbc.ini” should be created under “/home/oracle/” directory as we are creating user data source, it might already be created. If you like to create system data source then the file should be /etc/odbc.ini under the /etc directory. Make the below entries to the .odbc.ini file.[oracle@ltdb ~]$ cd ~
[oracle@ltdb ~]$ vi .odbc.ini
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package[ODBC Data Sources]
lgms=mariadb LGMS schema data source[lgms]
Description = ODBC for MySQL
Driver = /usr/lib64/libmaodbc.so
Server = 192.168.0.100
Database = hstest
Port = 3306
TRACE = OFF[default]
Driver = /usr/lib64/libmaodbc.so -
Configuring the listener:
[oracle@ltdb admin]$ cd $ORACLE_HOME/network/admin
[oracle@ltdb admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ltdb.hstest.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = mariadb)
(PROGRAM = /u01/app/oracle/product/12.2.0/dbhome_1/bin/dg4odbc)
(ENV =”LD_LIBRARY_PATH=/home/oracle/lib64:/usr/lib64:/u01/app/oracle/product/12.2.0/dbhome_1/bin/lib”)
)
) -
Configure the tnsnames.ora:
[oracle@ltdb admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.MARIADB =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521) )
)
(CONNECT_DATA = (SID = mariadb) )
(HS = OK)
) - Reload the listener:
lsnrctl Reload - Check the data source is working correctly.
[oracle@ltdb ~]$ isql -v mariadb database_name user_password - Create a public database link to connect to MariaDB from Oracle by any user, for security reason create a private DB Link.
[oracle@ltdb ~]$ sqlplus / as sysdba
SQL> CREATE PUBLIC DATABASE LINK “HSTEST.MARIADB” CONNECT TO LGMS IDENTIFIED BY <hstest_user_password> USING ‘mariadb’; -
Test the DB Link working we can access the MariaDB created table TEST.
SQL> SELECT * FROM TEST@HSTEST.MARIADB;
SQL> SELECT * FROM TEST@LGMS.MARIADB;name id
—————————— ———-
demo user 101
demo user 2 102
demo user 3 103 - Do some DML on the MariaDB targeted table created above.
SQL> INSERT INTO TEST@HSTEST.MARIADB (“name”, “id”) VALUES (‘demo user 4’, 104);
SQL> COMMIT; -
Check to see the new data inserted and committed at the destination.
SQL> SELECT * FROM TEST@HSTEST.MARIADB;name id
—————————— ———-
demo user 101
demo user 2 102
demo user 3 103
demo user 4 104 - You can create a table into local Oracle database from the MariaDB table data .
SQL> CREATE TABLE TEST_ORACLE AS SELECT * FROM TEST@HSTEST.MARIADB; -
During inserting multiple records from a source table(oracle) to a destination table(mariadb) general SQL will get error like below:
SQL> INSERT INTO TEST@HSTEST.MARIADB SELECT * FROM TEST_ORACLE;ERROR at line 1:
ORA-02025: all tables in the SQL statement must be at the remote database -
The workaround is to use a PL/SQL block with bind variable like below:
BEGIN
SAVEPOINT SP1;FOR rec IN (SELECT NAME, ID FROM TEST_ORACLE)
LOOP
INSERT INTO “TEST”@HSTEST.MARIADB (“NAME”,”ID”) VALUES (rec.NAME, rec.ID);
END LOOP;
COMMIT;EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO SP1;
RAISE;
END;