Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:mara_db_connect_engine_oracle_23ai

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:

  1. Oracle DB Zugangsdaten ermittlen (DB mit Test Schema bereist installiert)
  2. Oracle Linux 9 System - Minimale Basis Konfiguration (IP Adressse / Server Name / DNS / NTP konfiguriert)
    1. Repository für die Maria DB hinterlegen
    2. Maria DB Instance aufsetzen
    3. Test Datenbank anlegen
    4. Test User berechtigen
  3. Maria DB Connect Libs installieren
  4. ODBC Libs installieren
  5. Oracle Instant Client mit ODBC Treibern installieren
  6. Zugriff auf die Oracle DB über SQL*Plus mit EasyConnect Syntax prüfen - Connect String ermitteln
  7. ODBC Datenquelle einrichten und mit isql den Zugriff über ODBC auf die DB sicherstellen
  8. Tabelle / View in Maria DB anlegen über ODBC und prüfen
  9. Umgebung härten (Wo hin mit dem Password?)
Installation Maria DB muss über das Repository von Maria DB erfolgen, sonst lassen sich die Pluings nicht nachinstallieren!

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

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

Bisher nicht zum laufen bekommen!


Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
dba/mara_db_connect_engine_oracle_23ai.txt · Zuletzt geändert: 2025/01/15 14:38 von gpipperr

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki