Connect to MariaDB from Oracle and perform DML on remote MariaDB table

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)

Configure Heterogenous connectivity and perform DML:

  1. 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
  2. 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/
  3. Validate ODBC installed correctly and show the paths.
    [oracle@ltdb ~]$ odbcinst -j
  4. 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
  5. 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
  6. 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”)
    )
    )
  7. 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)
    )
  8. Reload the listener:
    lsnrctl Reload
  9. Check the data source is working correctly.
    [oracle@ltdb ~]$ isql -v mariadb database_name user_password
  10. 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’;
  11. 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
  12. 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;
  13. 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
  14. 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;
  15. 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
  16. 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;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.