Recently while working in MariaDB I've got several more chances to deal with some new CONNECT use cases (and some related problems, that ended up mostly minor or caused by ignorance). This engine can be (and is, widely) used to access tables from virtually any other database management system or data source, as long as there is an ODBC or JDBC driver for it. I had to set up a testing environment to reproduce some of the problems noted while accessing remote tables in Oracle RDBMS, and eventually decided to document setup steps, mistakes and findings for myself and my readers.
I think the topic may require a series of posts, and in this first one I plan to concentrate on creating a basic testing environment to access Oracle RDBMS via ODBC driver and CONNECT engine on my main testing box running Fedora 25, and resolving simplest access issues, like those I described in MDEV-12355 (ended as not a bug thanks to quick help and clarifications from engine creator, Olivier Bertrand).
For pure testing purposes I had not planned to spend time installing and setting up Oracle RDBMS on that Fedora 25 box directly (later I tried and failed), but decided to rely on some existing Docker image. So, I started with installing Docker and dependencies (see this article for some hints, if needed):
[openxs@fc23 ~]$ sudo dnf install dockerI am going to skip details on images that had not worked well (not a topic for this blog). Eventually I ended up working with https://hub.docker.com/r/sath89/oracle-12c/:
...
Installed:
container-selinux.noarch 2:2.10-1.fc25
docker.x86_64 2:1.12.6-6.gitae7d637.fc25
docker-common.x86_64 2:1.12.6-6.gitae7d637.fc25
oci-register-machine.x86_64 0-2.7.gitbb20b00.fc25
oci-systemd-hook.x86_64 1:0.1.6-1.gitfe22236.fc25
skopeo-containers.x86_64 0.1.17-1.dev.git2b3af4a.fc25
Complete!
[openxs@fc23 ~]$ sudo systemctl start docker
[openxs@fc23 server]$ sudo docker run -d -p 8080:8080 -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true sath89/oracle-12c:latestThis image initializes new database by default (unless you pass a volume on host, then it is initialized for the first time if the database is not there), so you have to wait (maybe for many minutes) and check the logs before trying to access the database. Note that container's port 1521 is mapped to host's port 1521 (and my Fedora 25 IP address, 192.168.1.85, is then used later below to access "remote" Oracle server on this port).
4515bc4a6fb4804ac8d714115de12e47a9e8f9baabb2300cde672aedb4843c2b
[openxs@fc23 server]$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
4515bc4a6fb4 sath89/oracle-12c:latest "/entrypoint.sh " 14 seconds ago Up 8 seconds 0.0.0.0:1521->1521/tcp, 0.0.0.0:8080->8080/tcp adoring_turing
[openxs@fc23 server]$ sudo docker logs -f 4515bc4a6fb4
ls: cannot access /u01/app/oracle/oradata: No such file or directory
Database not initialized. Initializing database.
Starting tnslsnr
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/xe/xe.log" for further details.
Configuring Apex console
Database initialized. Please visit http://#containeer:8080/em http://#containeer:8080/apex for extra configuration if needed
Starting web management console
PL/SQL procedure successfully completed.
Starting import from '/docker-entrypoint-initdb.d':
found file /docker-entrypoint-initdb.d//docker-entrypoint-initdb.d/*
[IMPORT] /entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
Import finished
Database ready to use. Enjoy! ;)
^C
So, my "Oracle server" is ready (probably). Now, my Fedora 25 host will work as a client, so I had downloaded and installed basic Oracle client software RPMs (including ODBC driver and SQL*Plus) from OTN:
[openxs@fc23 maria10.1]$ rpm -q -a | grep oracleI also installed unixODBC (including -devel, as I built MariaDB from source and one needs unixODBC-devel to have CONNECT engine build properly) as fine manual stated at the beginning:
oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64
oracle-xe-11.2.0-1.0.x86_64
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64
[openxs@fc23 maria10.1]$ rpm -q -a | grep unixODBCOne has to set up tnsnames.ora for Oracle client to work with (remote) instance, and /etc/odbcinst.ini + /etc/odbc.ini to inform unixODBC about the known drivers and system data sources (this discussion had some useful hints):
unixODBC-2.3.4-3.fc25.x86_64
unixODBC-devel-2.3.4-3.fc25.x86_64
[openxs@fc23 maria10.1]$ cat /etc/oracle/tnsnames.oraSurely, I tried to access my Oracle server in Docker container via sqlplus command first:
XE =
( DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 192.168.1.85)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = XE)
)
)
[openxs@fc23 maria10.1]$ cat /etc/odbcinst.ini
...
[OracleODBC]
Description = Oracle ODBC driver for Oracle 11g
Driver64 = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
FileUsage = 1
Driver Logging = 7
[openxs@fc23 maria10.1]$ cat /etc/odbc.ini
[oracle]
Driver = OracleODBCDSN = OracleODBC
ServerName = XE
UserID = system
Password = oracle
[openxs@fc23 server]$ /usr/lib/oracle/11.2/client64/bin/sqlplus system/oracle@localhost:1521/xe.oracle.docker/usr/lib/oracle/11.2/client64/bin/sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directoryThis is what happens when one does not care to read all the details and forgets to set some environment variables and inform dynamic loader about libraries location. So, I did the following to resolve the problem:
[openxs@fc23 server]$ ldd /usr/lib/oracle/11.2/client64/bin/sqlplus
linux-vdso.so.1 (0x00007ffe8813b000)
libsqlplus.so => not found
libclntsh.so.11.1 => not found
libnnz11.so => not found
libdl.so.2 => /lib64/libdl.so.2 (0x00007f0aadc93000)
libm.so.6 => /lib64/libm.so.6 (0x00007f0aad98a000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0aad76a000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f0aad551000)
libc.so.6 => /lib64/libc.so.6 (0x00007f0aad18b000)
/lib64/ld-linux-x86-64.so.2 (0x0000563b14bfb000)
[openxs@fc23 server]$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64So, SQL*Plus (that I still miss sometimes, after using it maybe only 10 times over last 12 years) client works. Let's try to access Oracle via ODBC and isql now:
[openxs@fc23 server]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
[openxs@fc23 server]$ export PATH=$PATH:$ORACLE_HOME/bin
[openxs@fc23 server]$ sudo ldconfig
[openxs@fc23 server]$ ldd /usr/lib/oracle/11.2/client64/bin/sqlplus
linux-vdso.so.1 (0x00007ffd31b50000)
libsqlplus.so => /usr/lib/oracle/11.2/client64/lib/libsqlplus.so (0x00007f88f680f000)
libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f88f3ea0000)
libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f88f3ad3000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f88f38b2000)
libm.so.6 => /lib64/libm.so.6 (0x00007f88f35a9000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f88f3389000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f88f3170000)
libc.so.6 => /lib64/libc.so.6 (0x00007f88f2daa000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f88f2ba8000)
/lib64/ld-linux-x86-64.so.2 (0x000056123639a000)
[openxs@fc23 server]$ sqlplus system/oracle@localhost:1521/xe.oracle.docker
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 24 11:00:21 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
SQL> create table scott.t1(c1 number(30));
Table created.
SQL> insert into scott.t1 values (12345);
1 row created.
SQL> desc scott.t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER(30)
SQL>
SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
[openxs@fc23 server]$ isql -v oracleSurely, one more environment variable is not set, the one pointing out the location of tnsnames.ora file:
[08004][unixODBC][Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified
[ISQL]ERROR: Could not SQLConnect
[openxs@fc23 server]$ export TNS_ADMIN=/etc/oracle
[openxs@fc23 server]$ isql -v oracle
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from scott.t1;
+---------------------------------+
| C1 |
+---------------------------------+
| 12345 |
+---------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> select count(*) from scott.emp;+-----------------------------------------+
| COUNT(*) |
+-----------------------------------------+
| 14 |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
[openxs@fc23 server]$
Looks like now we should be able to use ODBC table type of CONNECT storage engine of MariaDB (that was already loaded with INSTALL SONAME 'ha_connect.so'; check the details here) to access remote Oracle table:
MariaDB [test]> create table oracle_t1 engine=connect table_type=ODBC tabname='t1' dbschema='scott' connection='dsn=oracle';Do not be like me and do not report bugs when you see these messages! Go read the fine manual that explicitly mentions Oracle as being case sensitive and converting identifiers to upper case unless they are quoted.
ERROR 1105 (HY000): Cannot get columns from t1
MariaDB [test]> show warnings\G
*************************** 1. row ***************************
Level: Error
Code: 1105
Message: Cannot get columns from t1
*************************** 2. row ***************************
Level: Error
Code: 1030
Message: Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT
2 rows in set (0.00 sec)
As soon as you use proper case in tabname (and dbname) settings, everything works as expected, at least for my primitive use case:
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='t1' dbname='scott' connection='dsn=oracle';To summarize, with some initial setup efforts (that should include reading the official CONNECT manual) it is easy to access Oracle tables from MariaDB server via ODBC driver. It may be very useful if you plan to migrate or use some data from Oracle RDBMS.
ERROR 1105 (HY000): Cannot get columns from t1
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='T1' dbname='scott' connection='dsn=oracle';ERROR 1105 (HY000): Cannot get columns from T1
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='T1' dbname='SCOTT' connection='dsn=oracle';Query OK, 0 rows affected (1.29 sec)
MariaDB [test]> select * from t1_oracle;
+-------+
| C1 |
+-------+
| 12345 |
+-------+
1 row in set (0.07 sec)
MariaDB [test]> create table t2_oracle engine=connect table_type=ODBC tabname='SCOTT.T1' connection='dsn=oracle';
Query OK, 0 rows affected (3.54 sec)
MariaDB [test]> select * from t2_oracle;
+-------+
| C1 |
+-------+
| 12345 |
+-------+
1 row in set (0.06 sec)
As a side note, Docker really rocks sometimes. I spent maybe 15+ years installing Oracle RDBMS (starting from version 7.0.16 or so) on all kinds of OSes dozens of times, and I do not miss this experience and do not mind to have it up and running with one simple command any time (rarely) I need it.
See also https://jira.mariadb.org/browse/MDEV-24493 with some details on how to set up these things in 2020 and what bugs you may hit.
ReplyDelete