Aufgabe
Aus einer Maria DB soll auf die Tabellen in einer Oracle Datenbank zugegriffen werden.
Um in der Oracle Sprache zu bleiben, benötigen wir einen DB Link von der Maria DB in die Oracle Datenbank.
Das dazu notwendige Feature der Maria DB heißt „MariaDB Connect Engine“.
Soll anders herum zugegriffen werden, kann das nach folgenden Muster erfolgen ⇒ Datenbank über einen Datenbank Link mit einer MS SQL 2017 Datenbank verbinden
Ablauf:
Die Testumgebung besteht aus einem Oracle 23ai Free Edition DB Server unter Oracle Linux 9 und einem Oracle Linux 9 Server mit der Maria DB.
Linux 9 Basis Installation, Update mit „dnf update“ auf die aktuellste Version von Oracle Linux 9
root
Repostitory für die Maria DB hinterlegen, die mit dem Oracle Linux ausgeliefert wird ist relativ alt (Nur Version (10.5.22):
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s
siehe https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/
dnf install mariadb-server
DB Konfigurieren:
vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Enable binary Log log_bin=bin-log binlog_format=row max_binlog_size=100M expire_logs_days=7 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid
systemctl enable mariadb.service systemctl start mariadb.service systemctl status mariadb.service ps auxwf | grep mariadb netstat -tulpen | grep 3306 tail /var/log/mariadb/mariadb.log
# Sicherheit einstellen und root Passwort setzen /usr/bin/mysql_secure_installation # mit dem Passwort anmelden mysql -h localhost -u root -p #version select VERSION(); #Datenbanken SHOW DATABASES; #User SELECT Host,User FROM mysql.user; #Unötige entfernen DELETE FROM mysql.user WHERE Host='::1' AND User='root' quit #LOG-Rotate Konfiguration vi /etc/logrotate.d/mariadb
eine sehr gute Anleitung für den Start findet sich auch hier ⇒ https://dokuwiki.tachtler.net/doku.php?id=tachtler:mariadb_centos_7
# mit dem Passwort anmelden mysql -h localhost -u root -p CREATE DATABASE IF NOT EXISTS ora23aiConnect DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; SHOW DATABASES LIKE 'ora23aiConnect'; SELECT PASSWORD('Zugang2024oracle23ai'); # *D12F37D789691DBAB454AC69A825BFEB1CE42288 -- Use database. USE mysql; -- Create user. CREATE USER 'oracle23ai_user'@'10.10.10.116' IDENTIFIED BY PASSWORD '*D12F37D789691DBAB454AC69A825BFEB1CE42288'; -- Grant privileges for database ora23aiConnect to new users. GRANT USAGE ON *.* TO 'oracle23ai_user'@'10.10.10.116' IDENTIFIED BY PASSWORD '*D12F37D789691DBAB454AC69A825BFEB1CE42288'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ,DROP ON `ora23aiConnect`.* TO 'oracle23ai_user'@'10.10.10.116'; -- Make sure that priviliges are reloaded. FLUSH PRIVILEGES; SELECT * FROM mysql.user WHERE USER = 'oracle23ai_user'; SHOW GRANTS FOR 'oracle23ai_user'@'10.10.10.116'; quit
Zugriff testen mit:
mariadb -h 10.10.10.116 -u oracle23ai_user -p ora23aiConnect
Soll über das Netzwerk zugegriffen werden den User mit „'oracle23ai_user'@'%'“ anlegen!
root
dnf install MariaDB-connect-engine unixODBC
siehe https://mariadb.com/kb/en/installing-the-connect-storage-engine/
Pürfen ob die Engine aktiv ist:
mariadb -h localhost -u root -p show engines; +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+------------------------------------------------------------------------------------ *********** | CONNECT | YES | Management of External Data (SQL/NOSQL/MED), including Rest query results | NO | NO | NO | ********
Falls nicht aktiv, laden bzw. in die Konfiguration aufnehmen:
vi /etc/my.cnf [mariadb] plugin_load_add = ha_connect
Alterantiv im laufenden Betreib:
mariadb -h localhost -u root -p INSTALL SONAME 'ha_connect';
Ist die ODBC Lib schon installiert?
dnf install unixODBC
Instant Client Repo und Instant client installieren
dnf install oracle-instantclient-release-23ai-el9 # minmal dnf install oracle-instantclient-odbc.x86_64 # alles, mit SQL*Plus zum debuggen der Verbindung! dnf install oracle-instantclient-basic oracle-instantclient-odbc oracle-instantclient-sqlplus oracle-instantclient-tools # wo landet das ganze? rpm -ql oracle-instantclient-odbc.x86_64 *** /usr/lib/oracle/23/client64 ***
Für Ubuntu siehe https://csiandal.medium.com/install-oracle-instant-client-on-ubuntu-4ffc8fdfda08
Treiber hinterlegen:
vi /etc/odbcinst.ini [ORACLE] Description = ODBC für die Oracle Datenbank Driver = /usr/lib/oracle/23/client64/lib/libsqora.so.23.1
Verbindung per SQL*Plus im ersten Schritt testen um den Connect String zu ermitteln
cd /usr/lib/oracle/23/client64/bin ./sqlplus GPI@//10.10.10.114:1521/freepdb1
Verbindung konfigurieren:
vi /etc/odbc.ini [ODBC Data Sources] GPI="Oracle GPI schema" [GPI] Application Attributes = T Attributes = W BatchAutocommitMode = IfAllSuccessful CloseCursor = F DisableDPM = F DisableMTS = T Driver = Oracle EXECSchemaOpt = EXECSyntax = T Failover = T FailoverDelay = 10 FailoverRetryCount = 10 FetchBufferSize = 1024000 ForceWCHAR = F Lobs = T Longs = T MetadataIdDefault = F QueryTimeout = T ResultSets = T ServerName = //10.10.10.114:1521/freepdb1 SQLGetData extensions = F Translation DLL = Translation Option = 0 UserID = gpi Password = gpi
Bzgl. der Werte siehe auch https://docs.oracle.com/en/database/oracle/oracle-database/23/odbcd/basic-programming-oracle-odbc.html#GUID-634470F3-DAEE-481D-9EB4-16BF1FC6ABA2
Testen
isql gpi select object_name from user_objects order by 1; select count(*) from DEPARTMENTS; quit
mariadb -h 10.10.10.116 -u oracle23ai_user -p ora23aiConnect #db setzen create table DEPARTMENTS engine=connect table_type=ODBC tabname='DEPARTMENTS' dbschema='GPI' connection='dsn=GPI'; select * from DEPARTMENTS ; +---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | **************
siehe auch https://mariadb.com/kb/en/connect-odbc-table-type-accessing-tables-from-another-dbms/
Mit „SRCDEF“ eine art View auf die Tabelle anlegen:
CREATE TABLE DEPARTMENTS_270 engine=connect table_type=ODBC tabname='DEPARTMENTS' dbschema='GPI' connection='dsn=GPI' SRCDEF='select * from DEPARTMENTS where DEPARTMENT_ID=270'; # Falls Fehler #bzw falles es die Tabelle in dem Zielschema nicht gibt CREATE TABLE DEPARTMENTS_300 engine=connect table_type=ODBC dbschema='GPI' connection='dsn=GPI' SRCDEF='select * from GPI.DEPARTMENTS where DEPARTMENT_ID=300';
Update test:
MariaDB [ora23aiConnect]> update DEPARTMENTS set MANAGER_ID=200 where DEPARTMENT_ID = 270; Query OK, 0 rows affected, 1 warning (0.063 sec) Rows matched: 0 Changed: 0 Warnings: 1 MariaDB [ora23aiConnect]> show warnings; +-------+------+------------------------------+ | Level | Code | Message | +-------+------+------------------------------+ | Note | 1105 | DEPARTMENTS: 1 affected rows | +-------+------+------------------------------+ 1 row in set (0.000 sec)
Umlaute werden nicht dargestellt?
MariaDB [ora23aiConnect]> update DEPARTMENTS set DEPARTMENT_NAME='äöüßtester' where DEPARTMENT_ID = 270; Query OK, 0 rows affected, 1 warning (0.065 sec) Rows matched: 0 Changed: 0 Warnings: 1 MariaDB [ora23aiConnect]> select * from DEPARTMENTS where DEPARTMENT_ID = 270; +---------------+-----------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+-----------------+------------+-------------+ | 270 | aou?tester | 200 | 1700 | +---------------+-----------------+------------+-------------+ 1 row in set (0.056 sec) # UTF8DB? MariaDB [ora23aiConnect]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; +--------------+--------------------+----------------------------+------------------------+----------+----------------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT | +--------------+--------------------+----------------------------+------------------------+----------+----------------+ | def | information_schema | utf8mb3 | utf8mb3_general_ci | NULL | | | def | ora23aiConnect | utf8mb3 | utf8mb3_general_ci | NULL | | +--------------+--------------------+----------------------------+------------------------+----------+----------------+ 2 rows in set (0.001 sec) # Alles auf UTF8 einstellen: SET character_set_client=utf8; SET character_set_connection=utf8; SET character_set_results=utf8; # keine Verbesserung! # Was ist eingestellt: MariaDB [ora23aiConnect]> show variables like 'char%'; show variables like 'collation%'; #alles auf UTF8? # Test mit umkopieren der Tabelle USE ora23aiConnect ; create table department_local as select * from DEPARTMENTS; select hex(DEPARTMENT_NAME) from DEPARTMENTS where DEPARTMENT_ID=270; 616F753F746573746572 select hex(DEPARTMENT_NAME) from department_local where DEPARTMENT_ID=270; 616F753F746573746572 #hex ist gleich, also sind die Wert auch angekommen, werden aber nicht angezeigt.
In der Datenbank landet aber ein „äöüßtester“! D.h. es kann auch ein Problem mit dem „mariadb“ Tool sein!
Test über SQL*Plus:
cd /usr/lib/oracle/23/client64/bin export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 ./sqlplus GPI@//10.10.10.114:1521/freepdb1 select * from DEPARTMENTS where DEPARTMENT_ID = 270; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 270 äöüßtester 200 1700
Alternativ über den SQL*Developer mit graphischer Umgebung das Ganze testen.
Notwendiger Treiber findet sich unter https://dev.mysql.com/downloads/connector/j/
Nachdem es prinzipell funktioniert, wo kann das Passwort ab besten abgelegt werden?
Pwd und user aus der Datei „/etc/odbc.ini“ zuvor entfernen!
drop table DEPARTMENTS; create table DEPARTMENTS engine=connect table_type=ODBC tabname='DEPARTMENTS' dbschema='GPI' connection='dsn=GPI;UID=GPI;PWD=gpi';
Leider wird das Passwort dann immer noch in dem DDL Statement angezeigt:
show create table DEPARTMENTS_PWD; *** CONNECTION='dsn=GPI;UID=GPI;PWD=gpi' `TABLE_TYPE`='ODBC' `TABNAME`='DEPARTMENTS' ***
siehe https://mariadb.com/kb/en/file-key-management-encryption-plugin/
Plugin konfigurieren:
vi /etc/my.cnf [mariadb] #KeyFile Management plugin_load_add = file_key_management loose_file_key_management_filename = /srv/mariadb/keyfile
Keyfile mit Plain Werten anlegen:
mkdir /srv/mariadb cd /srv/mariadb vi keyfile 1;gpi chown mysql: keyfile chmod u=r,go-rw keyfile
create table DEPARTMENTS_KF engine=connect table_type=ODBC tabname='DEPARTMENTS' dbschema='GPI' connection='dsn=GPI;UID=GPI;PWD=${KEY:1}';
Probleme: SQLDriverConnect: [Oracle][ODBC][Ora]ORA-01017: invalid credential or not authorized; logon denied
Web:
Doku:
JDBC Treiber