Inhaltsverzeichnis
Maria DB 11.6 Datenbank mit einer Oracle 23ai unter Oracle Linux 9 verbinden
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:
- Oracle DB Zugangsdaten ermittlen (DB mit Test Schema bereist installiert)
- Oracle Linux 9 System - Minimale Basis Konfiguration (IP Adressse / Server Name / DNS / NTP konfiguriert)
- Repository für die Maria DB hinterlegen
- Maria DB Instance aufsetzen
- Test Datenbank anlegen
- Test User berechtigen
- Maria DB Connect Libs installieren
- ODBC Libs installieren
- Oracle Instant Client mit ODBC Treibern installieren
- Zugriff auf die Oracle DB über SQL*Plus mit EasyConnect Syntax prüfen - Connect String ermitteln
- ODBC Datenquelle einrichten und mit isql den Zugriff über ODBC auf die DB sicherstellen
- Tabelle / View in Maria DB anlegen über ODBC und prüfen
- Umgebung härten (Wo hin mit dem Password?)
Test Umgebung aufbauen
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.
Oracle DB Umgebung
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 Version
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/
Software installieren
dnf install mariadb-server
Datenbank einrichten und starten
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
Service einrichten
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
DB Umgebung optimieren
# 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
Test Datenbank und Test User bereitstellen
# 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!
MariaDB Connect Engine für Oracle einrichten
root
Connect Plugin installieren und aktivieren
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';
Oracle ODBC Connect aktivieren
Instant Client installieren
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
ODBC Verbindung aktiveren
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
Remote Tabelle ( Quasi DB Link) in der Maria DB anlegen
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/
Remote View anlegen
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';
DML Test
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 prüfen
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/
Password Handling
Nachdem es prinzipell funktioniert, wo kann das Passwort ab besten abgelegt werden?
Passwort nur in der DB hinterlegen
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' ***
File-Based Key Management
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
Quellen
Web:
Doku:
JDBC Treiber