X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

Recent Posts

Autonomous Database

Oracle Database Security: DBSAT und Data Safe

Wie kann man schnell und unkompliziert die Sicherheitseinstellungen der Oracle Datenbank überprüfen? Welche Sicherheitsempfehlungen und Praktiken werden nur teilweise oder gar nicht verwendet? Wie sieht es mit den Privilegien für die User aus? Gibt es "überprivilegierte" User? Führen die User sensitive Operationen aus und wenn ja welche? Gibt es sensitive Daten in der Datenbank? Und wenn ja, welche Tabellen sind in welchem Umfang betroffen? Abhilfe können die Oracle Datenbank Werkzeuge DBSAT (kurz für Oracle Database Security Assessment Tool) und der Oracle Data Safe Service (neu seit 2019) schaffen. Sie unterstützen nicht nur die Umsetzung notwendiger Datensicherheitsmaßnahmen, sondern helfen auch potenzielle Sicherheitsrisiken zu erkennen und sensitive Daten zu ermitteln. Angesichts zunehmender Gefahr durch Datenmissbrauch und gleichzeitig steigenden Anforderungen durch gesetzliche Vorschriften, ist der Schutz sensibler Unternehmensdaten dabei von entscheidender Bedeutung.  Woraus bestehen diese Werkzeuge und wie kann man sie verwenden? DBSAT überprüft Datenbankkonfigurationen, Datenschutzfunktionalitäten und gibt Sicherheitsempfehlungen gemäß Oracle Datenbanksicherheit Best Practices. In der Version 2 wurde es zusätzlich mit der Fähigkeit ausgestattet, auf einfachste Weise, sensitive Daten in Oracle Datenbanken zu suchen. Das Oracle Database Security Assessment Tool ist für Kunden mit gültigem Datenbanksupport kostenfrei. Es dient in erster Linie zur kurzfristigen Identifizierung von Angriffszielen und hilft bei der Minimierung allgemeiner Risiken sowie bei der Umsetzung einer umfassenden Sicherheitsstrategie. Es ist sowohl für On-Premises Datenbanken (ab der Version 10.2.0.5) als auch cloudbasierten Oracle Datenbanken einsetzbar. Für die Datenbank ist es nicht invasiv, d.h. es werden keine Objekte oder Strukturen in der Datenbank angelegt.Das Linemode Tool DBSAT steht seit Juni 2016 zur Verfügung und kann, wie in der Support Note (Doc ID 2138254.1) beschrieben, heruntergeladen werden. Einfach auspacken und starten und Reports in unterschiedlichen Formaten generieren. In den Reports werden die Empfehlungen des Center for Internet Security (CIS), der Allgemeinen Datenschutzverordnung (GDPR) der Europäischen Union und des Security Technical Implementation Guide (STIG) hervorgehoben um empfohlene Sicherheitskontrolle zu identifizieren. Wer mehr über DBSAT erfahren möchte, kann folgende Blogpostings dazu verwenden: Überprüfung der Datensicherheit mit dem Oracle Database Security Assessment Tool Das Oracle Database Security Assessment Tool sucht nun nach sensiblen Daten Oracle Database Security Assessment Tool jetzt in Version 2.1.0 Seit der Oracle Open World 2019 gibt es ein weiteres Werkzeug - der Oracle Data Safe Service. Im Unterschied zu DBSAT ist Oracle Data Safe ein webbasierter Oracle Cloud Service, um Datenbank-Sicherheit zu untersuchen und zu kontrollieren. Data Safe ermöglicht das Überwachen von Oracle Datenbank Sicherheitsrichtlinien und darüberhinaus das Maskieren von sensiblen Daten in der Oracle Datenbank. Aktuell wird Oracle Data Safe ausschließlich in der Oracle Cloud und für cloudbasierte Oracle Datenbanken angeboten, die Nutzung von On-Premises Datenbanken wird bald folgen. Oracle Data Safe Architektur Genaue Informationen zu den unterstützten Datenbanken findet sich im Handbuch über den Link Supported Database Targets.  Die Merkmale von DBSAT und Data Safe sind in folgender Matrix gelistet. DBSAT versus Data Safe Wie man leicht erkennen kann, besitzt Data Safe einen größeren Funktionsumfang als DBSAT. Die Funktionen von Data Safe sind zudem auch intuitiv über die webbasierte Konsole zu verwenden. Zum Verwenden von Data Safe, ist ein Setup der (Datenbank) Targets erforderlich. Informationen dazu finden sich in unserem aktuellen Artikel Oracle Data Safe: Konfiguration und Registrierung von Oracle Datenbanken. Nach dem Registrieren der konfigurierten Targets kann man dann loslegen: Ein Klick auf die Service Konsole reicht dazu aus! Der Eröffnungsbildschirm gibt dann einen ersten Überblick über den aktuellen Status aller registrierter Targets.  Ausschnitt aus Summary Report Das vertikale Menü zeigt an, welche Aufgaben durchgeführt werden können. Es handelt sich um Security Assessments, User Assessments, Data Discovery mit Data Masking und Activity Auditing. Die  Assessments können gleichzeitig für mehrere Targets angestoßen werden. Pro Target kann eine Assessement Baseline definiert werden. Wie man rechts oben erkennen kann, ist auch ein Filtering der Targets möglich. Welche Assessments sind nun möglich? Mit einem Klick auf Security Assessments wird ein DBSAT ähnlicher Report generiert. Folgender Screenshot zeigt einen Ausschnitt aus dem Überblicks Report. Mit einem Klick auf die Kategorie User Accounts werden die Detail Reports mit den entsprechenden Recommendations ausgegeben. Es existieren Kategorien zu den Themen Privileges and Roles, Authorization Control, Fine-Grained Access Control, Auditing, Encryption und Database Configuration. Jede Kategorie ist dabei in mehrere Unterkategorien unterteilt, die ebenfalls farblich nach Risikograd kodiert sind.   Ausschnitt aus einem Security Assessment Report Das User Assessment hilft die Sicherheit der Datenbankuser zu beurteilen und Benutzer mit hohem Risiko zu identifizieren. Die Handhabung ist analog zum Security Assessment. Ein User Assessment Job generiert einen Bericht, der hilft, Benutzer zu identifizieren, die ein Risiko für die Zieldatenbank darstellt. Der Bericht enthält dabei vier Diagramme und eine Tabelle. Die Diagramme geben eine Überblick über das User Risiko (Critical, high, medium, low), User Rollen (DBA, DV ADMIN und AUDIT ADMIN), letzten Passwort Änderungen und die letzten Logins. Die Tabelle enthält Informationen zu den einzelnen Usern wie Username, Target DB, User Typ, Status, Last Login, Passwort Profil usw. Ausschnitt aus einem User Assessment Report Klickt man auf den verlinkten Username erhält man alle Informationen zu den Privilegien des Users. Am Ende der Zeile kann man über den Link View Activity einen Report über die Aktivitäten des Users bekommen. Ausschnitt aus einem Activity Auditing Report Dazu ist vorab eine Setup über den Link Activity Auditing erforderlich. Das Setup gibt die Möglichkeit Audit Policies und Alerts festzulegen. Danach kann man einen Activity Auditing Job für eine oder mehrere Targets starten und bekommt detaillierte Activity Tracking Informationen. Einstellung der Activity Auditing Policies Im letzten Schritt sollen sensitive Daten in der Datenbank gefunden und maskiert werden. Kennt man diese Funktionen schon aus dem Enterprise Manager Cloud Control, wird man bei dem Prozedere einige Parallelen feststellen.   Sensitive Typen, die über reguläre Ausdrücke definiert sind, helfen bei der Suche nach Daten auf der Grundlage von Spaltennamen, Daten und Kommentaren. Data Discovery kann dabei vordefinierte und auch benutzerdefinierte, sensitive Typen verwenden. Nachdem das Datenbank-Schema ausgewählt und die zu verwendeten sensitiven Typen ausgewählt sind, wird ein Discovery Job angestartet, der das Schema auf sensitive Daten überprüft. Auswahl der sensitiven Typen Der Data Discovery Bericht listet dann die Spalten und die dazugehörigen Detail-Informationen auf. Die sensitiven Spalten werden auf der Grundlage ihrer sensitiven Typen kategorisiert. Der Bericht enthält die Gesamtzahl der ermittelten sensiblen Tabellen, Spalten und Werte. In einem Diagramm wird die Menge der sensitiven Daten auf der Ebene der sensitiven Kategorien und des sensitiven Typs ausgegeben. Das Ergebnis ist ein sensitives Datenmodell, das unter einem definierten Namen in einer Bibliothek abgelegt wird. Sensitiver Daten Discovery Report   Zum Schluss können diese sensitiven Spalten noch maskiert werden. Datenmaskierung, auch bekannt als statische Datenmaskierung, ist der Prozess, bei dem sensible Daten dauerhaft durch fiktive, aber realistisch aussehende Daten ersetzt werden. Sie erzeugt konsistente, maskierte Daten mit ähnlichen Eigenschaften wie die der Originaldaten, um sensible oder vertrauliche Informationen zu ersetzen. Ein typisches Szenario wäre beispielsweise einen Klon für Test oder Development Zwecke einer autonomous Datenbank zu erzeugen und darauf die Masking Operationen durchzuführen. Zum Maskieren ist eine Masking Job erforderlich, der entsprechende Policies auf dem ausgewählten Datenmodell ausführt. Eine Masking Policy ordnet dabei den sensitiven Spalten bestimmte Maskierungsformaten zu, die dann zur Maskierung der Daten verwendet werden sollen. Maskierungsformate können von Oracle vorgegebene oder aber auch User definierte Formate sein. Vordefinierte Masking Formate finden sich im Handbuch im Kapitel Predefined Masking Formats. Oracle Data Safe unterstützt mehrere Basis-Maskierungsformate, die als Bausteine beim Erstellen neuer Maskierungsformate verwendet werden können. Beispiel für Maskierungsformate sind: Shuffle, Random Number, Random String, Substring u.v.m. Building Blocks finden sich unter dem Punkt Basic Masking Formats. Data Discovery Resultat Folgendes Beispiel zeigt eine vorgeschlagene Masking Policy, die das Masking Format "Random Name" verwendet. Masking Format Random Name   Abschliessend wird dann noch ein Masking Job geschedult oder direkt ausgeführt. Eine eingeblendete Warnung zeigt noch einmal die Zusammenfassung des ausgewählten (Datenbank) Targets an, auf der die Maskierung ausgeführt werden soll, um Fehler bei der Targetauswahl  zu verhindern. Fazit DBSAT und Data Safe können dabei helfen schnell und unkompliziert einen Überblick über den Security-Status der Datenbanken (hier Targets) zu bekommen. DBSAT ist dabei von OTN downloadbar und im Linemode für On-Premises und cloudbasierte Oracle Datenbanken verwendbar. Oracle Data Safe ist eine Webkonsole, die Security Einstellungen/Konfigurationen, User Privilegien und Aktivitäten überprüft und beim Auffinden und Maskieren von sensitiven Daten helfen kann. Es handelt sich dabei hauptsächlich um folgende 5 Funktionen: Das Security Assessment analysiert Datenbank Targets, identifiziert Konfigurationseinstellungen, die unnötigerweise Risiken mit sich bringen können, und gibt Empfehlungen für Maßnahmen zur Risikominderung gemaß STIG oder CIS Recommendations. Das User Assessment liefert Informationen über die Risiken, die mit bestimmten privilegierten Datenbanksystembenutzern verbunden sind. Data Discovery ermöglicht es, sensible Daten in Ihren Datenbankzielen zu entdecken und zu klassifizieren. Data Masking ermöglicht sensible Daten in Ihren Test- und Entwicklungsdatenbanken zu maskieren. Mit Activity Auditing können Benutzeraktivitäten auf ausgewählten Datenbanken überwacht und Informationen dazu gesammelt werden. Mit Activity Auditing können Benutzeraktivitäten in ausgewählten Datenbanken überwacht und Informationen dazu gesammelt werden. Bei Bedarf können Echtzeit-Warnungen bei ungewöhnlichen oder auf einer schwarzen Liste geführten Verhalten ausgelöst werden. Weitere Funktionen und Beschreibungen zu DBSAT und Data Safe kann man auf folgenden Websites und Handbüchern erhalten. Data Safe Website Handbuch Using Oracle Data Safe Data Safe Architektur DBSAT Website Handbuch zu DBSAT 

Wie kann man schnell und unkompliziert die Sicherheitseinstellungen der Oracle Datenbank überprüfen? Welche Sicherheitsempfehlungen und Praktiken werden nur teilweise oder gar nicht verwendet? Wie...

Autonomous Database

Oracle Data Safe: Konfiguration und Registrierung von Oracle Datenbanken

Data Safe Konfiguration Oracle Data Safe ist ein Cloud basierte Kontrollzentrum für Datenbank-Sicherheit. Data Safe ermöglicht das Überwachen von Oracle Datenbank Sicherheitsrichtlinien und das Maskieren von sensiblen Daten in der Oracle Datenbank. Aktuell wird Oracle Data Safe ausschließlich in der Oracle Cloud angeboten, die zu überwachenden Oracle Datenbanken sind ebenfalls auf die Oracle Cloud Infrastructure Datenbanken beschränkt. Dieser Artikel befasst sich mit dem Setup/Konfiguration der Umgebung, um Data Safe nutzen zu können müssen einige Einstellungen vorgenommen werden. Voraussetzung zur Durchführung ist ein Oracle Cloud Infrastructure Tenant und administrative Zugriffsrechte. Sollten Sie keinen Oracle Cloud Infrastructure Tenant haben, können Sie sich hier einen Free-Trial einrichten. Das Setup unterteilt sich in folgende Punkte: Einrichten der Benutzer Aktivieren von Data Safe Registrierung der Oracle Datenbanken in Data Safe ggf. De-Registrierung der Oracle Datenbank   1. Einrichten des Benutzer Sollte Ihr verwendeter OCI Benutzer Administrationsrechte besitzt, können Sie zum Punkt 2 springen. Der Data Safe Benutzer muss in der Oracle Cloud Infrastructure (OCI) angelegt sein, dies kann ein lokaler oder federated Benutzer sein. Federation heißt: Der Benutzer wird in einem zentralen Identity Management System angelegt und in die OCI synchronisiert. Der IDM Default Service der OCI ist der Oracle Identity Cloud Service (IDCS), kann jedoch auch durch andere Systeme ersetzt werden (Active Directory, etc.). In der Praxis hat es sich bewährt Benutzer in Gruppen zu unterteilen. Diese Gruppen bekommen unterschiedliche, an die Aufgaben angepasste, Rechte zugeordnet. So wird z.B. eine Administrations-Gruppe angelegt, die neue Datenbank-Ziele in Data Safe konfiguriert und eine andere User-Gruppe, die nur lesenden Zugriff auf die Überwachungsergebnisse hat. Wichtig ist, dass der Benutzer und ggf. die Gruppe in OCI vorhanden ist. Hier können Sie nachlesen, wie in der OCI eine neue Gruppe angelegt werden kann.  Bild 1: OCI Synchronisierung mit dem Oracle Identity System In unserer Beispiel-Konfiguration vergeben wir dem Benutzer der Einfachheit halber Administrationsrechte. Für ein produktives System müssen selbstverständlich bestimmte Richtlinien eingehalten werden und Nutzer und Administratoren sind zu trennen. Die Rechte können in der OCI Console unter Menü > Identity > Policies gesetzt werden. Hier erstellen Sie eine neue Policy mit folgendem Eintrag: Allow group <eine existierende Gruppe> to manage data-safe in tenancy 2. Aktivieren von Data Safe Vor der Aktivierung noch ein paar Hintergrundinformationen: Hinter dem Data Safe Cloud Service Instanz steckt eine Web-Applikation und eine Pluggable Database (PDB) die in jeder Region (Cloud Rechenzentrums-Standort) separat vorhanden sind. D.h. bevor Sie diesen Service nutzten können, müssen Sie sich für die Nutzung der Web-Applikation und PDB freischalten. Dies können Sie einfach über das OCI Console Menü > Data Safe > Enable Data Safe. Wenn Sie unter diesem Punk bereits den Schalter „Service Console“ sehen, ist Data Safe bereits aktiviert. Bild 2: Aktivierung von Data Safe in einer Region 3. Registrierung der Oracle Datenbanken in das Data Safe Kontrollzentrum Dieser Schritt ist abhängig von dem zu überwachenden Datenbank-Typ. Folgende Datenbanken (Stand Juni 2020) werden unterstützt:  Bild 3: Liste der unterstützten Datenbanken.  Die Konfiguration unterscheidet sich zwischen VM, Bare Metal oder Exadata Cloud Service basierten Datenbanken und Autonomous Datenbanken. Eine zweite Unterscheidung ist die Fähigkeit die Datenbank über eine Public IP in Data Safe einzubinden oder über eine Private IP. Die Einbindung per Public IP ist für alle unterstütze Datenbank außer Exadata Cloud Service möglich, die Nutzung einer Private IP ist nur bei der VM, Bare Metal oder Exadata Cloud Service Variante möglich.  Für Test bzw. öffentliche Datenbanken kann die Public IP Variante gewählt werden, für Datenbanken mit sensiblen Inhalten oder keiner Public IP ist die Variante der Private IP zu empfehlen.  Bild 4: Public IP vs. Private IP Verbindung Kümmern wir uns zuerst um das Einbinden der VM, Bare Metal bzw. Exadata Cloud Service Variante: Für die Registrierung öffnen wird die Data Safe Console OCI Console Menü > Data Safe > Service Console und klicken auf den Reiter „Targets“. Um eine neue Datenbank zu registrieren klicken Sie auf den Punkt „Register“. Ein Pop-Up-Fenster öffnet sich und Sie müssen einige Konfigurations-Parameter eingeben. Doch bevor Sie dies tun können, muss in der Ziel-Datenbank ein Skript gestartet werden. Dieses Skript kann direkt aus diesem Fenster unter den Punkt „Download Privilege Script“ runtergeladen werden. Das Skript enthält GRANT/REVOKE Statements für den zu verwendenden Datenbank Benutzer. Bild 5: Runterladen des Download Privilege Scripts Hinweis: Das Skript muss in jeder Pluggable Database als Administrator ausgeführt werden, die überwacht werden soll, es kann und soll nicht in der Container PDB ausgeführt werden. Also erst einen Benutzer in der Pluggable Database anlegen, dann das Skript als Datenbank-Administrator laufen lassen! In dem Skript wird der Name des Benutzers abgefragt, der die Grants bekommt, ich habe hierfür in der PDB einen eigenen Benutzer angelegt. Bild 6: Ausführen des dscs_privileges.sql Skripts Im nächsten Schritt werden die Konfigurations-Parameter eingegeben. Nachfolgend werden die (benötigten) Parameter genannt und beschrieben: Data Safe Ziel (Target) Daten Target Name = Frei wählbarer Name für das Data Safe Ziel, hier am besten den Datenbank-Namen verwenden Target Type = Oracle_Database - hier ist bis jetzt nur diese eine Option auswählbar Ressource Group = Gruppierung für Ziele, Typen und Reports - Wird verwendet, um Gruppen für Data Safe Ziele zu erstellen. Man kann einfach einen neuen Namen eintippen oder eine existierende Gruppe verwenden. Verbindungs-Details Database with Private IP? - No OCID - hier wird die OCID des DB Systems eingegeben Connection Type - TCP/TCPS - hier wählen wir TCP aus Hostname/IP Adress - Selbsterklärend, die IP Adresse des Hosts auf den die DB läuft Port Number - 1521 - Wichtig an dieser Stelle!!! Die Datenbank muss aus dem Public Internet erreichbar sein, ggf. muss man die Security List dahingehend ändern, dass der Port 1521 freigeschaltet ist. Database Service Name - Full Qualified Service Name - Die SID reicht an dieser Stelle nicht, auch hier daran denken PDB Service Name! Database User Name - Der Benutzer, der in dem dscs_privileges.sql Skript angegeben wurde Database Password - Das Passwort dieses Benutzers Anschließend testet man die Verbindung und klickt nach erfolgreichem Test „Register Target“.  Hinweis: Es kann vorkommen, dass man anschließend eine Fehlermeldung bekommt, dass ein federated User nicht ausreicht und man einen lokalen Benutzer benötigt. Diesen Fehler bekommt man, wenn man z.B. in einer frühen Version des Data Safes Services eine Datenbank registriert hat. In der Vergangenheit konnte man nur lokale OCI Benutzer für Data Safe nutzen. Sollten Sie diesen Fehler bekommen, einfach den Browser-Cache leeren, anschließend funktioniert alles wie erwartet. Bild 7: Verbindungsparameter Registrierung VM Basierte OCI Datenbank Dies war der letzte Schritt, anschließend sieht man die Datenbank in der Liste der Ziele.  Möchte man eine Autonomous Database in Data Safe einbinden, geht dies in einem Schritt, da die nötigen Privilegien schon vorhanden sind und die Konfigurationsparameter dem System bekannt sind. In dem nachfolgenden Beispiel registriere ich eine Autonomous Data Warehouse DB. Im ersten Schritt geht man auf Menü > Autonomous Data Warehouse, man klickt auf die gewünschte Datenbank und registriert diese durch ein Klick auf Register im Bereich Data Safe. Bild 8: Data Safe Registrierung Autonomous Data Warehouse Anschließend kann man aus der Detailansicht der Autonomous Database direkt in den Data Safe Cloud Service springen und sieht dort unter Targets die neu registrierte Autonomous Database. 4. ggf. De-Registrierung der Oracle Datenbank Für das De-Registrieren geht man einfach auf die Target Seite von Data Safe, wählt das gewünschte Ziel aus und löscht den Eintrag.  Fazit: Diese Beispiele hat einen möglichen Weg aufgezeigt, um eine VM basierte und eine Autonomous Datenbank in Data Safe zu registrieren. Die Autonomous Database geht wesentlich schneller, da die nötigen Privilegien, Benutzer etc. schon vorhanden sind. Die Registrierung kann natürlich auch über die APIs durchgeführt werden, dies ist wichtig, wenn es zum Beispiel darum geht viele Datenbanken einzubinden.  Hilfreiche Links: Allgemeine Data Safe Seite Dokumentations-Einstieg Data Safe  Einführungs-Video Data Safe (7 Minuten)  

Data Safe Konfiguration Oracle Data Safe ist ein Cloud basierte Kontrollzentrum für Datenbank-Sicherheit. Data Safe ermöglicht das Überwachen von Oracle Datenbank Sicherheitsrichtlinien und das...

Advisor

SPA mal anders - SQL-Tuning mit SQL Performance Analyzer (SPA)

Was tun? Die Performance eines Datenbank Workloads ist nicht (mehr) das was sie verspricht. Häufig hat man SQL Statements in Verdacht, die aus irgendwelchen Gründen nicht mehr performant laufen. Die Ursachen dafür können vielfältig sein - eine Änderung an den Zugriffsobjekten wie Indizes, an Statistiken, an der Datenbankumgebung selbst z.B. bei einer Migration/Upgrade u.v.m. Die betroffenen Statements müssen gefunden, analysiert und getunet werden. Mit einem AWR Report lassen sich beispielsweise die TOP n Statements finden. Ein zusätzliches SQL Tracing und die Analyse von Ausführungsplänen und -Statistiken kann anschliessend beim Tunen helfen. Je nach Anzahl der Statements kann der Tuning Aufwand dabei allerdings entsprechend groß werden. Der SQL Performance Analyzer (auch kurz SPA) kann hier Abhilfe schaffen! Wie Database Replay ist SPA eine Komponente von Real Application Testing, die in der Enterprise Edition der Oracle Cloud eingeschlossen ist. Noch einmal zur Erinnerung: Bei Real Application Testing handelt es sich um ein Werkzeug der Datenbank, das einen Workload aufzeichnen und in einer Testumgebung wieder abspielen kann. Keine Installation einer separaten Werkzeug-Software oder spezielles Skripting für Real Application Testing ist dafür nötig. SPA und DB Replay können auch unabhängig voneinander verwendet. Sie sind allerdings auch eine gute Ergänzung. So kann es sinnvoll sein, zuerst die SQL Performance mit SPA zu überprüfen und danach den gesamten Workload mit DB Replay unter Berücksichtigung aller konkurrierenden Zugriffe zu testen. Oder umgekehrt: bei der Aufzeichnung von DB Replay den SQL Workload mitaufzuzeichnen und danach separat mit SPA zu evaluieren und zu tunen. Interessiert an der Database Replay Komponente? Eine Beschreibung und Anleitung mit Linemode Skripten findet sich in unserem Blogbeitrag Testen mit Database Replay. Wie funktioniert der SQL Performance Analyzer? Der Fokus von SQL Performance Analyzer liegt auf der detaillierten Statement Analyse eines definierten SQL Workloads. Ein SQL Workload kann dabei aus SELECT bzw. DML Statements bestehen, die über ein SQL Tuning Set (kurz STS) zur Verfügung gestellt werden. Der Workload wird zweimal abgespielt - einmal VOR der Veränderung und dann NACH einer Veränderung. Das Ergebnis ist eine detaillierte Vergleichsanalyse (vor und nach der Veränderung) der einzelnen Statements, gemessen an verschiedenen Metriken wie beispielsweise elapsed time, cpu_time etc. Optional besteht anschliessend die Möglichkeit den Tuning Prozess zu automatisieren - mit dem SQL Tuning Advisor oder über die Verwendung von SQL Plan Baselines. Zur Ausführung kann ähnlich wie bei Database Replay die graphische Oberfläche über Enterprise Manager Cloud Control oder das PL/SQL Package DBMS_SQLPA sowie die zugehörigen Data Dictionary Views verwendet werden. Die vollständige Funktionalität des Package und der Data Dictionary Views lässt sich im Handbuch in den entsprechenden Kapiteln (siehe unten) nachlesen. Folgendes Beispiel zeigt einen exemplarischen Ablauf unter Verwendung von Linemode Skripten. Möchte man das Ganze selbst ausprobieren, kann man diese Skripte hier herunterladen. (Ein Readme zur Erläuterung des Ablaufs ist im zip File enthalten :)). Zusammengefasst sind folgende Schritten notwendig: Generierung eines SQL Tuning Sets Erste Ausführung VOR der Veränderung Zweite Ausführung NACH der Veränderung Auswertung(en) gemäß der gewählten Metrik Generierung von verschiedenen Berichten   Der Ablauf Voraussetzung für SPA ist die Bereitstellung eines SQL Tuning Sets (auch STS). Dazu stehen mehrere Möglichkeiten zur Verfügung. SQL Tuning Sets können beispielsweise aus AWRs oder aus dem Cache erzeugt werden und gegebenenfalls über Data Pump Export und Import auf eine andere Datenbank verlagert werden. Entweder ist dies graphisch über den Enterprise Manager oder im Linemode mit dem Package DBMS_SQLTUNE möglich. Die SQL Tuning Set Funktionalität haben wir in einem vorangegangenen Tipp ausführlich erläutert. Mehr darüber kann man im Blogposting Oracle SQL Tuning Sets - Grundlage zum SQL Tuning nachlesen. Ein paar nützliche Skripte finden sich hier zum Download. Im folgenden Beispiel generieren wir ein SQL Tuning Set STS1 aus den AWR Reports. -- STS creation with name STS1 execute dbms_sqltune.create_sqlset(sqlset_name =>'STS1'); -- STS1: SELECT statements in parsing schema DWH_DATA DECLARE cur dbms_sqltune.sqlset_cursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM dbms_sqltune.select_workload_repository (begin_snap=>5716, end_snap=>5717, basic_filter => 'upper(sql_text) like ''SELECT%'' and parsing_schema_name = ''DWH_DATA''') P; dbms_sqltune.load_sqlset (sqlset_name => 'STS1', populate_cursor => cur); END; / Monitoren lassen sich die SQL Tunings Sets mit Data Dictionary Views wie DBA_SQLSET oder DBA_SQLSET_STATEMENTS. select name, owner, statement_count from dba_sqlset; NAME OWNER STATEMENT_COUNT -------------------- ---------- --------------- STS1_SUB US 5 STS1A US 5 STSR US 27 STS2 US 283 STS1 US 96 STS3 US 44 RAt3_r_315627 SYS 6 Um SPA zu verwenden ist zuerst eine spezielle TASK erforderlich, die mit CREATE TASK des Package DBMS_SQLPA erzeugt wird. Monitoren lässt sich die Task über USER_ADVISOR_TASKS. -- create analysis task declare tname varchar2(100); begin tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS1'); end; / PL/SQL procedure successfully completed. select task_name, status, advisor_name from user_advisor_tasks; TASK_NAME STATUS ADVISOR_NAME --------------- ----------- ------------------------- TASK_115 INITIAL SQL Performance Analyzer Danach wird mit EXECUTE_ANALYSIS_TASK ein erster Lauf durchgeführt. -- first run execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'TASK_115', - execution_type => 'test execute', - execution_name => 'RUN_BEFORE'); Nun muss die Umgebung entsprechend angepaßt/geändert werden. Beispiele für Änderungen sind Einstellungen der Initialisierungsparameter (OPTIMIZER_FEATURES_ENABLE etc.), Änderung der Speicherung (Komprimierung etc.), Hinzufügen von Indizes usw. Danach erfolgt der zweite Durchlauf. -- Ignore Optimizer Hints alter session set optimizer_ignore_hints=true; Session altered. -- second run execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'TASK_115', - execution_type => 'test execute', - execution_name => 'RUN_AFTER'); Nun werden noch Auswertungen nach unterschiedlichen Metriken wie buffer_gets, cpu_time, disk_reads, optimizer_cost, elapsed_time (default), direct_writes etc. berechnet. Dazu verwendet man wieder die Prozedur EXECUTE_ANALYSIS_TASK - dieses Mal allerdings mit EXECUTION_TYPE compare performance. Pro Aufruf kann dabei jeweils eine Metrik zur Bewertung herangezogen werden, die dann unter einem Execution Name abgespeichert wird. Folgende Beispiele führen Bewertungen zu ELAPSED_TIME und CPU_TIME durch. -- metric elasped_time execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (task_name => 'TASK_115', - execution_type => 'COMPARE PERFORMANCE', - execution_name => 'ELA', - execution_params => dbms_advisor.arglist('comparison_metric', 'ELAPSED_TIME')); --metric cpu_time execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (task_name => 'TASK_115', - execution_type => 'COMPARE PERFORMANCE', - execution_name => 'CPU', - execution_params => dbms_advisor.arglist('comparison_metric', 'CPU_TIME')); Einen Überblick über die Executions kann man mit folgender Abfrage bekommen. select execution_name, task_name, status, advisor_name from user_advisor_executions; EXECUTION_NAME TASK_NAME STATUS ADVISOR_NAME --------------- -------------------- ----------- ------------------------- RUN_AFTER TASK_115 COMPLETED SQL Performance Analyzer RUN_BEFORE TASK_115 COMPLETED SQL Performance Analyzer ELA TASK_115 COMPLETED SQL Performance Analyzer CPU TASK_115 COMPLETED SQL Performance Analyzer 4 rows selected. Nun können die zugehörigen Berichte in TEXT-, HTML- oder XML- Format generiert werden. Mit dem Parameter LEVEL lassen sich zusätzlich Detail-Reports zu den einzelnen Themenbereichen generieren wie Berichte über Statements mit geänderten Plänen (CHANGED_PLANS), verschlechterte (REGRESSED) oder verbesserte (IMPROVED) Statements. Der folgende Aufruf zeigt die Generierung eines Summary Reports. Verwendet wird dabei die Metrik elasped_time, die unter dem Namen ELA abgespeichert ist. --summary report in HTML for elapsed_time spool summary.html set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'TYPICAL','SUMMARY', null, 100, 'ELA') from dual; spool off Im Screenshot ist ein Ausschnitt aus dem Report zu sehen. Möchte man weitere Details erhalten wie zum Beispiel die einzelnen Ausführungspläne und Execution Statistiken vor und nach der Änderung, kann man mit folgendem Aufruf einen detaillierten HTML Report für die Statements generieren. -- detail report for all statements set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off spool detail.html select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'ALL', 'ALL', null, 100, 'ELA') detailed_rep from dual; spool off Im Screenshot ist ein Ausschnitt aus dem Report zu sehen. Folgende Aufrufe zeigen weitere Beispiele für Berichte - einmal für "improved" und dann für "regressed" Statements. set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off -- only improved statements spool improved.html select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'IMPROVED', 'ALL', null, 100, 'ELA') detailed_rep from dual; spool off ---------------------------------------------------------------------------------- set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off -- only regressed statements spool regressed.html select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'REGRESSED', 'ALL', null, 100, 'ELA') detailed_rep from dual; spool off Nun kann man weitere Tests durchführen und das Ergebnis mit den entsprechenden Reports dokumentieren. Tuning mit Advanced Features Nachdem wir im letzten Schritt einen Überblick über die Performance Metriken und Pläne der Statements erhalten haben, heisst es den Umfang der Performance-Unterschiede abzuschätzen. Bei großen Unterschieden und vielen betroffenen Statements sollte die Ursache in der Gesamtkonfiguration gesucht werden. Falls nur einige Statements betroffen sind, gibt es zwei weitere Optionen wie man mit "regressed statements" umgehen kann. Die erste Option wäre den SQL Tuning Advisor zu nutzen und die Empfehlungen - wie Profiles, Statementänderungen, Statistiken, alternative Pläne usw. - zu implementieren. Der andere Weg wäre mit SQL Plan Baselines zu arbeiten. Die Idee ist, die Statements mit ihren originalen "besseren" Ausführungsplänen als SQL Plan Baselines festzulegen. Möchte man auch diese Schritte im Linemode durchführen, bietet sich die Nutzung der Packages DBMS_SQLTUNE und DBMS_SPM an. Folgendes Beispiel zeigt die Implementierung von Plan Baselines für die "regressed" Statements aus dem SQL Tuning Set STS1. Zuerst wird eine Untermenge des SQL Tuning Sets erzeugt, das nur die "regressed" Statements enthält. In unserem Fall handelt es sich um 3 Statements, wie wir bei der Überprüfung der Tuning Sets feststellen können. execute DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STSR', description=> 'Regressed Statements'); -- select STS for regressed statements DECLARE sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN sqlset_cur FOR SELECT value(p) FROM table( DBMS_SQLTUNE.SELECT_SQLPA_TASK('TASK_115', null,'ELA', 'REGRESSED')) p; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STSR', populate_cursor => sqlset_cur); CLOSE sqlset_cur; END; / select elapsed_time, cpu_time, executions, sql_text from dba_sqlset_statements where sqlset_name ='STSR'; ELAPSED_TIME CPU_TIME EXECUTIONS ------------ ---------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 1453756 1433161 1 SELECT 'G' || tt1.PG_FeatureValue_19_1 PG_FeatureValue_19_1, 'B' || 1010 3455568 3314237 1 SELECT 'B' || 1010415 repchannel_1, 'B' || tt1.domain_productgroup_1 dom 40194308 33679037 390 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1)) Jetzt können die entsprechenden Ausführungspläne mit einem einzigen Aufruf als Baselines geladen werden. Fertig! set pause on set serveroutput on DECLARE new_plans PLS_INTEGER; BEGIN new_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'STSR'); dbms_output.put_line('######### number of plans for regressed statements = '||new_plans); END; / ######### number of plans for regressed statements = 3   Fazit Vorteile von SPA sind neben der einfachen und schnellen Handhabung auch die Verfügbarkeit. DML Statements können ausgeführt werden, verändern aber die Testumgebung nicht, da DML Statements automatisch zurückgerollt werden. So ist kein Zurücksetzen nach dem Test erforderlich. Grundsätzlich erfahren auch alle Statements eine Art "Warm up" und werden mehrfach ausgeführt, so dass ein realistischer Vergleich stattfindet. Ausserdem bieten die SPA Berichte eine gute Dokumentation der einzelnen Tuningschritte. Hat man keinen Enterprise Manager zur Verfügung kann man wie wir im Beispiel gezeigt haben auch den Linemode verwenden. Um den Einstieg zu erleichern, kann man sich hier die Skripte downloaden. Weitere Varianten der Skripte lassen sich aus dem Handbuch ableiten. Folgende Links können dazu hilfreich sein. Package DBMS_SQLPA Package DBMS_SQLTUNE für Tuning Advisor Package DBMS_SQLTUNE für SQL Tuning Sets Package DBMS_SPM für SQL Plan Management Data Dictionary View DBA_ADVISOR_TASK Data Dictionary View DBA_ADVISOR_EXECUTIONS Testing Guide: Part I SQL Performance Analyzer Dojo 5: Real Application Testing: Testen mit Database Replay und SQL Performance Analyzer            

Was tun? Die Performance eines Datenbank Workloads ist nicht (mehr) das was sie verspricht. Häufig hat man SQL Statements in Verdacht, die aus irgendwelchen Gründen nicht mehr performant laufen. Die...

Autonomous Database

Datenbank-Tracing übers Hintertürchen oder wie komme ich ohne Betriebssystem-Zugriff an meine Trace-Files?

Viele kennen das Problem: Man kann zwar als Administrator auf die Datenbank zugreifen, jedoch nicht auf das Betriebssystem. Typische Beispiele sind Hosting- und Cloud-Plattform-Umgebungen wie beispielsweise die Oracle Autonomous Database. Doch wie komme ich an weiterführende Informationen, wenn etwas nicht funktioniert? Wie komme ich an die Trace-, Alert- Dateien, etc. wenn kein Zugriff auf das Betriebssystem des Datenbank-Servers möglich ist?  Der nachfolgende Artikel zeigt einige Möglichkeiten an Trace- bzw. Alert-Logfile-Informationen zu kommen, ohne auf das Betriebssystem zugreifen zu müssen. Grundsätzlich existieren zwei Möglichkeiten: Zugriff auf Client-Tracing-Informationen  Zugriff auf Server-Tracing/Alert-Informationen 1. Zugriff auf Client-Tracing-Informationen  Client Tracing wurde in einem vorangegangenen Blog-Eintrag besprochen. Der Link zum Posting findet man unter "Was geht ab? SQL*Net Tracing, erste Schritte bei der Fehlersuche.". Im Artikel wird beispielhaft demonstriert wie Client-Tracing für SQL*Plus aktiviert wird und die zugehörigen Trace-Files generiert werden. Dies hilft vor allem bei Verbindungs-Problemen zur Datenbank. 2. Zugriff auf Server-Tracing/Alert-Informationen Der Zugriff auf Server Tracing Informationen ist durch die Abfrage verschiedener Data Dictionary Tabellen möglich. Folgende Voraussetzungen müssen dafür erfüllt sein: Die Ziel-Datenbank muss geöffnet sein Es müssen ausreichende Zugriffsrechte auf die erforderlichen V$-Views vorhanden sein. Dies ist standardmässig in Oracle Datenbanken über die SYS bzw. SYSTEM Accounts gewährleistet. In der Oracle Autonomous Datenbank erfüllt der ADMIN-Account diese Voraussetzung. Auslesen des Alert Logs Das Alert-Log kann mit folgenden SQL Statement ausgelesen werden: SELECT     originating_timestamp,     message_text,     module_id,     process_id,     filename FROM v$diag_alert_ext ORDER BY originating_timestamp DESC; Bild 1: Anzeige des Alert-Logs im SQL*Developer Die Ausgabe enthält die Alerts in einer absteigenden Reihenfolge (neustes Datum zuerst). Natürlich können je nach Anforderungen zusätzliche Spalten mit Informationen hinzugefügt werden.  Das Statement kann in eine Datei umgeleitet werden, um eine lokale Kopie des Alert-Logs zu erzeugen. Auf die geeignete Formatierung der Spalten wird an dieser Stelle nicht eingegangen. Auslesen von Trace-Files Zum Auslesen der Trace-Informationen kann die View v$diag_trace_file_contents verwendet werden. Die Abfrage ist nur ein Beispiel; natürlich können andere/weitere Spalten ausgegeben werden. SELECT     timestamp,     payload,     trace_filename FROM v$diag_trace_file_contents ORDER BY timestamp DESC; Bild 2: Anzeige der Trace-File-Informationen im SQL*Developer Das Statement liest die gesamte Tabelle aus und stellt die Einträge mit abfallender Zeit/Datumsreihenfolge dar. Es werden alle vorhandenen Trace-Dateien angezeigt. Soll dies eingeschränkt werden, muss im ersten Schritt ermittelt werden, welches Trace-File verwendet werden soll. Der Name/Datum/Uhrzeit der Trace-Files lässt aus der V$-View v$diag_trace_file auslesen, wie folgendes Beispiel zeigt: SELECT     trace_filename,     modify_time FROM v$diag_trace_file ORDER BY modify_time DESC; Bild 3: Anzeige der Trace-File-Informationen im SQL*Developer In dieser Auflistung erscheint das zuletzt geänderte Trace-File an der ersten Stelle. Anschließend kann man mit dem ermittelten Trace-File-Namen das gewünschte Trace-File auslesen (in diesem Beispiel wurde ein Phantasiename als Trace-File-Name angegeben): SELECT     timestamp,     payload,     trace_filename FROM v$diag_trace_file_contents WHERE trace_filename = 'DB0401_m004_10490.trc' ORDER BY timestamp DESC; Fazit Wenn es darum geht mehr Einblicke in mögliche Fehlerursachen zu bekommen, stellt die Abfrage von Trace/Alert-Log-Informationen über die Datenbank ein sehr mächtiges Werkzeug dar. Im Zweifelsfall benötigt man natürlich immer noch Zugriff auf das Betriebssystem, um mögliche Fehlerursachen zu ermitteln. Die hier vorgestellten Möglichkeiten bieten jedoch ein schnelles und unkompliziertes Vorgehen, um mögliche Fehlerursachen schnell und unkompliziert zu ermitteln.

Viele kennen das Problem: Man kann zwar als Administrator auf die Datenbank zugreifen, jedoch nicht auf das Betriebssystem. Typische Beispiele sind Hosting- und Cloud-Plattform-Umgebungen...

Advisor

Oracle SQL Tuning Sets - Grundlage zum SQL Tuning

Die wenigstens Kunden kennen Oracle SQL Tuning Sets! Das war die Aussage meines Kollegen Mike Dietrich (Master Product Manager bei Oracle für Migrationen und Upgrade)  in seinem letzten Webcast  zum Thema Upgrade und Performance Stability. Dabei gibt es einige sehr interessante Anwendungsszenarien - besonders im Tuning Umfeld. Mit SQL Tuning Sets auch kurz STS kann man beispielsweise die relevanten Statements für (wiederkehrende) Tuningaufgaben auflisten. Schnell und einfach lassen sich Aussagen über die Performance von Statements treffen - ohne beispielsweise einen AWR Report zu generieren. Ausserdem liefern sie die Grundlage für den SQL Tuning Advisor und SQL Access Advisor und sind Basis für die Verwendung des SQL Performance Advisors. Möchte man auf einem Remote System testen, ist auch dies mit SQL Tuning Sets möglich, da diese mit Datapump Export bzw. Import auf ein Remote Datenbank System kopiert werden können.  Die SQL Tuning Set Funktionalität steht übrigens in der Enterprise Edition der Oracle Datenbank - On-premises oder in der Cloud - zur Verfügung - ohne zusätzliche Lizenzierung eines Management Packs oder einer Option. Informationen dazu finden sich im Oracle Licensing Guide.  SQL Tuning Sets gibt es schon seit langer Zeit in der Oracle Datenbank. Sie wurden im Zusammenhang mit der Einführung des SQL Tuning Frameworks in der Version 10 der Oracle Datenbank eingeführt. Ein STS ist, wie der Name schon andeutet, eine Sammlung von SQL Statements, die unter einem User definierten Namen in der Datenbank abgespeichert werden. Das Interessante daran ist, dass nicht nur die SQL Kommandos selbst, sondern auch zugehörige Informationen über die Ausführung mitabgespeichert werden.  Ein SQL Tuning Set besteht aus folgenden Komponenten: Ein oder mehrere SQL Statements Kontext zur Ausführung wie Bind Variablen, Parsing Schema usw. Grundlegende Statistiken zur Ausführung wie Elapsed Zeit, CPU Zeit usw. Ausführungspläne Wie lassen sich SQL Tuning Sets erzeugen? Ein STS kann aus verschiedenen Quellen erzeugt werden wie z.B. Automatic Workload Repository (AWR), dem Cursor Cache oder mithilfe von Real Application Testing Database Replay (mehr dazu auch im Tipp Testen mit Oracle Database Replay ). Um beispielsweise möglichst viele Statements einfach und schnell im STS zu speichern, eignet sich das Capture aus dem  Cursor Cache. Sollen nur die Top Statements berücksichtigt werden, kann man sich auf die AWR Methode beschränken. Zur Erzeugung eines STS kann entweder die graphische Oberfläche über Enterprise Manager Cloud Control, die eine sehr einfache und gute Oberfläche dazu anbietet oder der Linemode verwendet werden.  Da nicht jeder die Cloud Control Oberfläche zur Verfügung hat, werden die Beispiele in folgenden Abschnitten im Linemode durchgeführt. Die Infrastruktur für SQL Tuning Sets besteht dabei hauptsächlich aus dem Package DBMS_SQLTUNE sowie den zugehörigen Data Dictionary Views DBA_SQLSET und DBA_SQLSET_STATEMENTS. Die vollständige Funktionalität des Packages und der Data Dictionary Views lässt sich im Handbuch (hier in der Version 19c) in den entsprechenden Kapiteln nachlesen unter: Package DBMS_SQLTUNE Data Dictionary View ALL/USER/DBA_SQLSET Data Dictionary View ALL/USER/DBA_SQLSET_STATEMENTS Folgende Beispiele demonstrieren die Verwendung im Linemode. Skripte zum Löschen, Erzeugen von SQL Tuning Sets und Subsets wie auch zum Monitoren kann man sich hier laden.  Erzeugen eines STS: Cursor Cache, AWR, inkrementell Wichtig zu wissen ist, die Statements nach bestimmten Kriterien gefiltert und ausgegeben werden können, bevor sie in der Datenbank unter einem STS Namen gespeichert werden. Dazu steht intern der Objekttyp SQLSET_ROW zur Verfügung, der beispielsweise folgende Basis-Filterkriterien zulässt: SQL_TEXT für den SQL Text PARSING_SCHEMA_NAME für das User Schema MODULE bei Verwendung von DBMS_APPLICATION_INFO ACTION bei Verwendung von DBMS_APPLICATION_INFO ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS ROWS_PROCESS EXECUTIONS Mit der Funktion SELECT_CURSOR_CACHE kann man die Statements aus dem Cursor Cache selektieren, die bestimmte Kriterien erfüllen. Im folgendem Beispiel werden maximal 10 SELECT Statements aus dem Schema DWH_DATA gefiltert und nach Elapsed Time geordnet ausgegeben. Dazu verwendet man im Parameter BASIC_FILTER die Einstellungen PARSING_SCHEMA_NAME für das User Schema, SQL_TEXT für die Verwendung von SELECT Statements, RESULT_LIMIT für die Begrenzung auf 10 und RANKING_MEASURE1 für das Sortieren nach Elapsed Time. set linesize window SELECT last_exec_start_time, elapsed_time/1000/1000 elapsed_sec, sql_id, sql_text FROM dbms_sqltune.select_cursor_cache ( basic_filter =>'parsing_schema_name = ''DWH_DATA'' and sql_text like ''SELECT%''', result_limit => 100, ranking_measure1 =>'elapsed_time') / LAST_EXEC_START_TIM ELAPSED_SEC SQL_ID ------------------- ----------- ------------- SQL_TEXT -------------------------------------------------------------------------------- 2020-05-13/12:15:35 93.161427 917fgnxfc9du7 SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlv 2020-05-13/12:15:35 10.061682 gghk7k6vt9gzq SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extract(xmlval, ' 2020-05-13/12:15:35 9.018263 f931zc8qhhfaa SELECT ST.* FROM XMLTABLE('/hint_usage/s/h' PASSING XMLTYPE(:B1 ) COLUMNS O VARC 2020-05-13/12:15:35 8.998409 fg4skgcja2cyj SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'), 2020-05-13/12:12:10 5.365492 2s7u2gqad8vm2 SELECT 'B' || 1010280 repchannel_1, 'B' || tt1.domain_productgroup_1 dom 2020-05-13/12:13:42 5.324177 6w3jxyn7373bn SELECT 'B' || tt1.PG_FeatureValue_03_1 PG_FeatureValue_03_1, 'B' || 1010 ... Es lassen sich beliebige Filterkriterien miteinander kombinieren und damit die Ausgabe beeinflussen. So können SQL Tuning Sets erzeugt werden, die bestimmten Performance- oder Umgebungs-Anforderungen wie z.B. Modulenangabe durch DBMS_APPLICATION_INFO genügen. Weitere Möglichkeiten findet man im Handbuch im Bereich DBMS_SQLTUNE.SELECT_CURSOR_CACHE. Wie kann man diese Statements in die Datenbank in einem bestimmten STS speichern? Um ein STS in der Datenbank zu erzeugen, wird im ersten Schritt ein Objekt mit einem bestimmten Namen festgelegt. Dies geschieht mit der Prozedur CREATE_STS. -- Loeschen mit DROP_SQLSET execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS1', sqlset_owner=>'US') / -- Erzeugen mit CREATE_SQLSET execute dbms_sqltune.create_sqlset(sqlset_name=>'STS1', sqlset_owner=>'US') / Möchte man Statements inkrementell über eine bestimmte Zeit aus dem Cursor Cache in einem SQL Tuning Set ansammeln, ist dies mit der Prozedur CAPTURE_CURSOR_CACHE_SQLSET möglich. Das folgende Beispiel pollt über einen Zeitraum von 30 Minuten (1800 Sekunden) alle 5 Minuten die Statements aus dem Cursor Cache. Hier wurde als Einschränkung das Userschema SCOTT verwendet und die Elapsed Zeit (mehr als 5 Sekunden) als Untergrenze festgelegt. execute dbms_sqltune.capture_cursor_cache_sqlset(- sqlset_name => 'STS1',- time_limit => 1800, - repeat_interval => 300, - basic_filter => 'parsing_schema_name = ''DWH_DATA'' AND elapsed_time>5000000'); Hinweis: Dabei dauert die Ausführung der Prozedur CAPTURE_CURSOR_CACHE_SQLSET so lange wie der gegebene Zeitraum vorgibt - in unserem Fall 30 Minuten. Verwendet man noch zusätzlich den DBMS_SCHEDULER um solche Statements inkrementell zu sammeln, lässt sich der Prozess mit DBMS_SCHEDULER.STOP_JOB auch vorzeitig beenden. Sollen nicht inkrementell sondern alle Statements auf einmal aus dem aktuellen Cursor Cache geladen werden, kann man folgendes PL/SQL Programm dazu verwenden. execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS2', sqlset_owner=>'US') / execute dbms_sqltune.create_sqlset(sqlset_name=>'STS2', sqlset_owner=>'US') / DECLARE cur dbms_sqltune.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM dbms_sqltune.select_cursor_cache(basic_filter =>   'parsing_schema_name = ''DWH_DATA'' and sql_text like ''SELECT%''') P; dbms_sqltune.load_sqlset(sqlset_name => 'STS2', populate_cursor => cur); CLOSE cur; END; / Hinweis: Die Funktion VALUE liefert den Wert eines Objekts zurück. Alternativ lassen sich die durch AWR bzw. ASH gesammelten Daten aus dem Workload Repository nutzen. Diese Daten werden ständig gesammelt und per Default 8 Tage (konfigurierbar) in der Datenbank gespeichert. Da dabei auch einmal pro Sekunde die Top SQL-Statements von der Datenbank gesammelt werden, können bei einem Performance-Problem die verursachenden SQL-Statements über die zugehörigen Snapshots gefunden werden. Statt der Funktion SELECT_CURSOR_CACHE wird in diesem Fall die Funktion SELECT_WORKLOAD_REPOSITORY verwendet. Sie beinhaltet die beiden IDs der Snapshots, um den Anfang und das Ende der Periode anzugeben. Zusätzlich ist, wie im vorangegangenen Beispiel schon erklärt, die Angabe von Filterkriterien möglich. Zuerst suchen wir die entsprechenden Snapshots mithilfe der Data Dictionary View DBA_HIST_SNAPSHOT. -- im Root Container ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI'; SELECT snap_id, begin_interval_time begin_snap, end_interval_time end_snap FROM dba_hist_snapshot ORDER BY snap_id; SNAP_ID BEGIN_SNAP END_SNAP ---------- ------------------------------ ------------------------------ 5389 13-MAY-20 06.01.00.650 AM 13-MAY-20 07.00.04.058 AM 5390 13-MAY-20 07.00.04.058 AM 13-MAY-20 08.00.07.496 AM 5391 13-MAY-20 08.00.07.496 AM 13-MAY-20 09.00.10.896 AM 5392 13-MAY-20 09.00.10.896 AM 13-MAY-20 10.00.14.188 AM 5393 13-MAY-20 10.00.14.188 AM 13-MAY-20 11.00.17.593 AM 5394 13-MAY-20 11.00.17.593 AM 13-MAY-20 11.13.08.982 AM 5395 13-MAY-20 11.13.08.982 AM 13-MAY-20 12.00.21.159 PM 5396 13-MAY-20 12.00.21.159 PM 13-MAY-20 01.00.24.978 PM 5397 13-MAY-20 01.00.24.978 PM 13-MAY-20 01.13.03.918 PM Im Beispiel werden die beiden Snapshots 5396 und 5397 verwendet. Zusätzlich können noch weitere Filterkriterien wie in den anderen Beispielen mitangegeben werden. execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS3', sqlset_owner=>'US') / execute dbms_sqltune.create_sqlset(sqlset_name=>'STS3', sqlset_owner=>'US') / DECLARE cur dbms_sqltune.sqlset_cursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM dbms_sqltune.select_workload_repository (begin_snap=>5396, end_snap=> 5397, basic_filter => 'sql_text like ''SELECT%''   and parsing_schema_name = ''DWH_DATA''' ) P; dbms_sqltune.load_sqlset (sqlset_name => 'STS3', populate_cursor => cur); END; / Hinweis: Die im AWR gelisteten Statements sind auf eien eine Zahl begrenzt, da man nur die Top Statements listen möchte. Möchte man die Zahl verändern, kann dies mit der Funktion DBMS_WORKLOAD_REPOSITORY. MODIFY_SNAPSHOT_SETTINGS tun. Ist das STS zu groß bzw. möchte man das STS in mehrere Sets unterteilen, lässt sich dazu die Funktion SELECT_SQLSET in Verbindung mit verschiedenen Filterkriterien verwenden. Folgendes Beispiel erzeugt das Subset STS1A. Es enthält 10 Statements aus dem SQL Tuning Set STS1 geordnet nach der verbrauchten Elapsed Time. execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS1A', sqlset_owner=>'US') / execute dbms_sqltune.create_sqlset(sqlset_name=>'STS1A', sqlset_owner=>'US') / DECLARE cur dbms_sqltune.sqlset_cursor; BEGIN OPEN cur FOR      SELECT VALUE (P)      FROM table(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name =>'STS1',   ranking_measure1 =>'ELAPSED_TIME', result_limit=>&limit)) P;   DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS1A', populate_cursor => cur); CLOSE cur; END; / Enter value for limit: 10 old   7:          sqlset_name =>'STS1', ranking_measure1 =>'ELAPSED_TIME', result_limit=>&limit)) P; new   7:          sqlset_name =>'STS1', ranking_measure1 =>'ELAPSED_TIME', result_limit=>10)) P; PL/SQL procedure successfully completed. Monitoren von STS Möchte man einen Überblick über alle STS des Owners US bekommen, kann man folgende Abfrage verwenden. SELECT name, created, statement_count FROM dba_sqlset WHERE owner='US'; NAME CREATED STATEMENT_COUNT -------------------- ---------------- --------------- STS1 13.05.2020 12:42 15 STS2 13.05.2020 12:44 283 STS3 13.05.2020 13:36 44 STS1A 15.05.2020 15:45 10 Auskunft über den genauen Inhalt der SQL Tuning Sets erhält man dann über die Data Dictionary View DBA_SQLSET_STATEMENTS. Hier hat man nun die Möglichkeit nach unterschiedlichen Kriterien - vergleichbar auch mit V$SQL - zu sortieren und zu filtern. Auf diese Art und Weise bekommt man dann schnell einen Überblick über Statements, die den Anforderungen nicht genügen und muss dazu nicht unbedingt einen AWR Report generieren. SELECT elapsed_time, sql_text FROM DBA_SQLSET_STATEMENTS where sqlset_name='STS1' ORDER BY 1; ELAPSED_TIME ------------ SQL_TEXT --------------------------------------------------------------------------         4002 SELECT  /*# 49435 #*/ DISTINCT     t1.domain_productgroup_id domain_productgroup        25233 select /*+ opt_param('parallel_execution_enabled',        67140 SELECT /*+ USE_HASH(t1) */ /*# 49435 #*/ DISTINCT     'B' || t1.period_id period       344994 ... Transfer von SQL Tuning Sets Abgesehen von Standardeigenschaften wie das Löschen oder nachträgliche Ändern, gibt es auch die Möglichkeit, SQL Tuning Sets in andere Datenbanken zu transportieren. Dies ist besonders dann sinnvoll, wenn z.B. der Tuning Prozess ausgelagert werden soll. Folgender Ablauf beschreibt das Vorgehen. 1) Anlegen einer Staging Tabelle execute dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGING_TABLE'); Hinweis: Die Nutzung von Datapump darf dabei NICHT als SYSDBA erfolgen. 2) Laden der Staging Tabelle mit Daten aus einem oder mehreren SQL Tuning Sets execute dbms_sqltune.pack_stgtab_sqlset(-   sqlset_name => 'STS2', - sqlset_owner => 'US', -   staging_table_name => 'STAGING_TABLE'); 3) Datapump Export und Import der Tabelle STAGING_TABLE in die Test Umgebung 4) Entpacken der SQL Tuning Sets aus der Staging Tabelle execute dbms_sqltune.unpack_stgtab_sqlset(- sqlset_name => '%', - sqlset_owner => 'US',- replace => TRUE, staging_table_name => 'STAGING_TABLE'); Nun kann das SQL Tuning Set bzw. die SQL Tunings Sets in der neuen Umgebung, die z.B. als Testumgebung dient, zum Einsatz kommen. Sogar die Releasestände der Umgebungen können dabei unterschiedlich sein. So kann ein SQL Tuning Set aus einer Datenbank Umgebung mit einem weniger aktuellen Releasestand in eine Testumgebung exportiert bzw. importiert werden, um das Verhalten in der neuen Umgebung zu testen.   Weitere Informationen Package DBMS_SQLTUNE Data Dictionary View ALL/USER/DBA_SQLSET Data Dictionary View ALL/USER/DBA_SQLSET_STATEMENTS SQL Tuning Guide: Managing SQL Tuning Sets  

Die wenigstens Kunden kennen Oracle SQL Tuning Sets! Das war die Aussage meines Kollegen Mike Dietrich (Master Product Manager bei Oracle für Migrationen und Upgrade)  in seinem letzten Webcast  zum...

Advisor

SQL Monitoring Berichte einfach generieren im Linemode

Vor einigen Wochen bekam ich wieder eine Anfrage zur  Beurteilung von SQL Performance. Ein Kollege fragte mich, ob ich ihm beim Tuning von SQL Statements behilflich sein könnte. Die Statements würden so langsam laufen und er wüßte keinen Rat. Er fragte mich, welche Informationen, ich benötigte, da ich keinen Zugriff auf die Umgebung hatte. Das ist nun ein typischer Fall für real-time SQL Monitoring!  Folgende Abschnitte sollen kurz die Funktionsweise demonstrieren, eine umfangreichere Beschreibung findet man im Blogposting von 2019.  Was ist noch einmal real-time Montoring? Wie kann man ohne großes Vorwissen einen Report erzeugen? Welche Voraussetzungen benötigt man dazu? Real-time Monitoring ist ein geeignetes Hilfsmittel um zuverlässig einen detaillierten Überblick über SQL- und PL/SQL-Statistiken von Operationen zu bekommen. Sowohl Cursor-Statistiken (z.B. CPU-Zeiten und IO-Zeiten) als auch Ausführungsplan-Statistiken (z.B. Anzahl der Zeilen, Speicher und belegter Temp Space) werden während der Ausführung der Anweisung nahezu in Echtzeit aktualisiert. Dabei werden nicht nur die gerade aktiven Operationen überwacht, sondern auch Abfragen, die sich in einer Warteschlange befinden oder gar abgebrochen worden sind.  Real-time Monitoring findet sich in den graphischen Konsolen vom Enterprise Manager Cloud Control, im SQL Developer oder aber auch in der Cloud Konsole von Autonomous Database. Dort besteht die Möglichkeit einen Report einfach zu sichern und somit an einen Experten  weiterzugeben. Möchte man allerdings unabhängig von graphischen Konsolen im Linemode arbeiten, reicht in der Regel ein Aufruf einer entsprechenden Funktionen aus dem Package DBMS_SQLTUNE aus, um übersichtliche Reports zu erstellen. Welche Voraussetzungen benötigt man dazu? Real-time Monitoring ist Teil des Tuning Packs.  Die Kontrolle über den Einsatz der Management Packs kann man mit dem Initialisierungsparameter CONTROL_MANAGEMENT_PACK_ACCESS sicher stellen. Der Wert DIAGNOSTIC+TUNING ermöglicht den Einsatz der Tuning Pack-Funktionen. Zusätzlich muss der Parameter STATISTICS_LEVEL den Wert TYPICAL (Defaultwert) haben. Hinweis: In Datenbank Cloud Umgebungen sind Tuning und Diagnostics Pack schon in der Enterprise Edition enthalten. Was ist eigentlich Real-time SQL Monitoring? Ein SQL-Monitoring wird immer automatisch gestartet, falls die Operationen eine der folgenden Voraussetzungen erfüllen: Parallele Ausführung Verbrauch von mehr als 5 Sekunden CPU- bzw. I/O-Zeit Verwendung des MONITOR-Hints Kein umständliches Einschalten beispielsweise durch Setzen eines speziellen Trace Events ist zur Nutzung erforderlich. Möchte man einen Report im Linemode erstellen, gibt es nun die Möglichkeit, mit den Funktionen REPORT_SQL_MONITOR_LIST und REPORT_SQL_MONITOR des Package DBMS_SQLTUNE zu arbeiten. Dabei ist eine Darstellung in unterschiedlichen Formaten möglich wie z.B. Text (das Defaultformat), HTML oder auch ACTIVE für interaktive Reports, die auf der Flash Player-Funktionalität von Adobe basieren. Die Einstellung ACTIVE ist dabei ein "interaktiver" Datenbank Bericht, der die meisten Informationen graphisch zeigt, wie zum Beispiel auch explain plan Darstellung, Metriken usw. Folgendes Beispiel erzeugt eine Liste der langlaufenden Statements. Es ist ein HTML Report und der Detaillierungsgrad des Berichts ist ALL, was so viel bedeutet wie alle Informationen. Hinweis: Seit Oracle Database 19c können sogar User ohne SELECT_ANY_CATALOG Rolle die Funktionen benutzen. In diesem Fall werden nur die User eigenen Statements gelistet. SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON TRIMSPOOL ON SET ECHO OFF FEEDBACK OFF spool monitor_list.html rep SELECT dbms_sqltune.report_sql_monitor_list(type =>'html', report_level => 'ALL') AS report FROM dual; spool off Der zugehörige Report sieht dann folgendermaßen aus. Nur die beiden Ausführungen des Users DEV sind hier gelistet. Eine genauere Detailansicht gibt es über folgenden Aufruf. Möchte man ein bestimmtes Statement anzeigen, kann man dies mit SQL_ID im Aufruf mitangeben. Ohne Angabe einer SQL_ID, wie in unserem Fall, wird nur die zuletzt ausgeführte Operation im Detail angezeigt. SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON TRIMSPOOL ON SET ECHO OFF FEEDBACK OFF spool monitor_sql.html rep select dbms_sqltune.report_sql_monitor(type=>'html') from dual; spool off Der zughörige HTML Report sieht dann wie folgt aus. Die gleiche Abfrage ausgeführt mit dem User SYS zeigt übrigens die Ausführung von allen Usern mit langlaufenden Abfragen. Weitere Informationen Dokumentation DBMS_SQLTUNE 19c New Feature:Real-time SQL Monitoring for Developers (Doc ID 2480461.1)

Vor einigen Wochen bekam ich wieder eine Anfrage zur  Beurteilung von SQL Performance. Ein Kollege fragte mich, ob ich ihm beim Tuning von SQL Statements behilflich sein könnte. Die Statements würden...

Database Management

Was geht ab? SQL*Net Tracing, erste Schritte bei der Fehlersuche.

Man kann es nicht of genug betonen: Wenn der SQL Client sich nicht gegen die Datenbank verbinden kann, ist man oft geneigt den "Trial and Error" Ansatz zu fahren. Es werden willkürlich Parameter verändert, Umgebungsvariablen gesetzt und die tnsnames.ora, sqlnet.ora oder ähnliche Konfigurationsdateien editiert. Die strukturierte Lösung ist dagegen das Verwenden der Trace-Funktionalität des Oracle Server/Clients. Ich selbst habe vor kurzem eine ähnliche Erfahrung machen müssen. Bei einer Kerberos-Authentifizierungs-Konfiguration funktionierte nichts, weder konnte der Client eine Verbindung zur Datenbank aufbauen, noch konnte ich mit den üblichen Methoden eine Fehlerursache finden. Die ausgegebenen Fehlermeldungen brachten mich nicht weiter. Nach Aktivierung des SQL*Net -Tracing auf der Server-Seite, konnte ich zwar Fehlermeldungen sehen, jedoch waren diese nicht hilfreich auch nach längerer Ursachen Recherche. Dann versuchte ich das SQL*Net -Tracing des Clients zu aktivieren, doch leider geht beim schnellen Ausprobieren und das damit verbundene Doku-Lesen wertvolle Zeit verloren, bis ich die richtige und minimale Kombination von Konfigurations-Parametern gefunden habe, die das gewünschte Trace-File an die gewünschte Stelle schreibt. Deshalb stellt dieser Artikel die grundlegenden, minimalen Parameter dar und erklärt, wie man sowohl mit den Instant-Client, wie auch dem Oracle Client das Tracing aktivieren kann. Der erfahrene DBA wird natürlich nur müde lächeln, alle aufgeführten Parameter kann er im Schlaf. Daher richtet sich dieser Artikel an den Oracle DB Nutzer, der diese Funktion eher selten verwendet und nicht etliche Seiten Dokumentation wälzen möchte. Am Ende ermöglichten mir die Tracing-Informationen den Fehler zu beheben und die Ursache klar auszumachen. Denn es muss nicht immer ein Fehler der Oracle Konfigurationen sein, es ist auch möglich das auf Betriebssystem-Ebene nicht ausreichende Rechte vorhanden sind, eine Umgebungsvariable fehlt oder Dateien nicht lesbar sind. Dies alles sieht man nur, wenn man das SQL*Net -Tracing des Clients aktiviert und eine ausreichende Tracing-Tiefe (Tracing Level) einstellt. Die aufgeführten Parameter gelten ab der Oracle DB Version 11g. SQL*Net -Tracing in der Datenbank Dieses Thema dürften den meisten bekannt sein, in der sqlnet.ora werden einige Parameter gesetzt und schon erhält man ein Bild, was beim Datenbank-Server nicht richtig läuft. Allerdings bekommt man wenig Informationen über den SQL Client, selbst wenn er auf der gleichen Maschine läuft, wie der Datenbank-Server. Der Vollständigkeit halber werde ich die Konfiguration des SQL*Net -Tracing für Datenbanken aufführen. In der aktiven sqlnet.ora fügt man folgende Parameter hinzu: TRACE_LEVEL_SERVER = SUPPORT TRACE_DIRECTORY_SERVER = /tmp/db_server_trace Diese beiden Befehle reichen, um ein SQL*Net Server Tracefile zu erzeugen. Allerdings wird es nicht in das Verzeichnis /tmp/db_server_trace geschrieben, dazu benötigt man einen zusätzlichen Parameter: DIAG_ADR_ENABLED = OFF Wenn der Parameter DIAG_ADR_ENABLED auf OFF gesetzt ist, wird ein Nicht-ADR- Tracing verwendet. Für eine schnelle Ursachen-Analyse empfehle ich die Verwendung eines eigens dafür angelegten Verzeichnisses und die Nutzung des ADR_BASE=OFF Parameters. Zusammenfassend wird man mit den folgenden drei Parametern in der sqlnet.ora ein aussagekräftiges Trace File im gewünschten Verzeichnis bekommen: DIAG_ADR_ENABLED = OFF TRACE_LEVEL_SERVER = SUPPORT TRACE_DIRECTORY_SERVER = /tmp/db_server_trace Durch Auskommentieren oder Setzen von TRACE_LEVEL_SERVER = OFF wird das Tracing ausgeschaltet. Allerdings sollte man dann auch den Parameter DIAG_ADR_ENABLED = ON setzen, sonst werden alle ADR Dateien in das TRACE_DIRECTORY_SERVER Verzeichnis geschrieben. Wenn man herausfinden möchte welches Verzeichnis das Default-Verzeichnis für die ADR und Trace Files sind kann man das mit folgender SQL-Query: SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File'; Hinweis: Bei den Oracle Cloud Datenbanken funktioniert das beschriebene Vorgehen natürlich auch. Ausnahme: Alle Oracle Datenbank-Service auf denen man nicht Zugriff auf die Konfigurations-Dateien hat. Zum Beispiel bei der Autonomous Database kann das Server Tracing nicht aktiviert werden. SQL*Net -Tracing im Client Der Client verhält sich im Prinzip nicht anders, ob es sich dabei um einen SQL*Plus Full Client oder den Oracle Instant Client handelt. An dieser Stelle noch kurz ein Anmerkung zum Thema Oracle Instant Client. Wenn man schnell eine Verbindung zu einer Oracle Datenbank aufbauen möchte oder nur bestimmte Funktionen braucht (zum Beispiel SQL*Plus) kann man eigentlich immer den Instant Client verwenden Der Instant Client hat einen wesentlich geringeren Installations-Footprint. Man benötigt keinen Installer, denn der Instant Client wird als ZIP-Datei oder Linux-RPM ausgeliefert. Daher gibt es momentan nur wenige Gründe einen Full Client zu verwenden. Ausserdem benötigt der Instant Client nur die TNS_ADMIN-Umgebungs-Variable (ausser bei Windows) das macht das Handling wesentlich einfacher. Das Aktiveren des Tracing im Instant Client erfolgt ähnlich wie beim Datenbank Server. In der sqlnet.ora werden folgende Parameter gesetzt DIAG_ADR_ENABLED = OFF # ADR_BASE = OFF TRACE_LEVEL_CLIENT = SUPPORT TRACE_DIRECTORY_CLIENT = /tmp/db_client_trace Setzt man den DIAG_ADR_ENABLED = OFF Parameter nicht, werden die Traces in das $TNS_ADMIN Verzeichnis geschrieben bzw. es kann auch vorkommen, das kein Trace-File geschrieben wird. Übrigens: Das funktioniert auch mit der undokumentierten Parameter-Einstellung ADR_BASE = OFF. Bei SQL*Plus im Full Client verhält es sich genauso wie beim Instant Client. Wer sich nun für das Thema Tracing erwärmt hat und mehr erfahren möchte kann sich unter folgenden Link weitere Informationen besorgen. Fazit Das Tracing für Oracle Datenbank Server und Clients stellt eine hervorragende Möglichkeit dar, nach Fehlerursachen zu suchen. Für Nicht-Geübte ist es allerdings schwer die richtigen Parameter zu setzen und zu verstehen, daher werden die Fehler oft nach der "Trial and Error" Methode gesucht. Ich hoffe dieser Artikel ermöglicht den Leser ohne "Doku-Wälzen" schnell den Trace-Modus einzuschalten und sich der drei Parameter stets zu erinnern...    

Man kann es nicht of genug betonen: Wenn der SQL Client sich nicht gegen die Datenbank verbinden kann, ist man oft geneigt den "Trial and Error" Ansatz zu fahren. Es werden willkürlich...

Advisor

Testen mit Oracle Database Replay

In letzter Zeit häufen sich bei mir Anfragen zum Thema Oracle Real Application Testing. Zahlreiche Gründe wie beispielsweise Einführung von Multitenant, die Änderung des zugrundeliegenden Betriebssystems, Wechsel der gesamten Server Plattform wegen Anschaffung neuer Hardware spielen dabei eine Rolle - und nicht zu vergessen der Wechsel in die Cloud. Dabei ist es natürlich immer besser den eigenen realen Workload zu verwenden statt einem synthetischen Workloadgenerator wie zum Beispiel Oracle Swingbench dazu einzusetzen. Generell ist allerdings zu beachten: Zieht man den Einsatz von Real Aplication Testing in Betracht, sollte es dabei immer um eine Einschätzung des Workload Verhaltens innerhalb der Oracle Datenbank gehen. Komponenten wie Application Server, verwendete Middleware oder Client Software können beim Testen mit Real Application Testing nicht berücksichtigt werden. Schon vor Jahren haben wir einige Blogsposts und ein Dojo zu dem Thema verfasst - die Links dazu finden sich am Ende dieses Artikels. Obwohl schon viele Projekte mit Real Application Testing durchgeführt worden sind, gibt es immer noch viele Kunden, die noch nie etwas von Real Application Testing gehört haben. Und als vor einigen Wochen ein Kunde im Zusammenhang mit einem POC bzgl. eines Plattformwechsels zu mir sagte: "Real Application Testing ist ein cooles Oracle Feature, warum haben wir noch nie etwas davon gehört", habe ich mich entschlossen nach den erfolgreichen Tests noch einmal einen Überblicksartikel über die DB Replay Technologie zu geben und den Ablauf beispielhaft zu skizzieren.  Bei Real Application Testing handelt es sich – ganz einfach formuliert – um ein Werkzeug für die Datenbank, das einen Workload aufzeichnen und in einer Testumgebung wieder abspielen kann. Der Ausdruck Werkzeug ist dabei etwas irreführend, da keine zusätzliche Installation einer separaten Werkzeug-Software für Real Application Testing notwendig ist. Die Nutzung erfolgt wie üblich über die Standardwerkzeuge Oracle Enterprise Manager oder PL/SQL beziehungsweise SQL-Aufrufe. Wie bei Partitionierung, Komprimierung, Edition Based Redefinition und andere Techniken in der Oracle Datenbank steht Oracle Real Application Testing out-of-the-Box in jeder Oracle Datenbank Installation zur Verfügung und ist sogar in den Oracle Database Cloud EE Umgebungen kostenfrei verwendbar.  Grundsätzlich gibt es Real Application Testing in den zwei Ausprägungen - Database Replay (kurz DB Replay) und SQL Performance Analyzer (kurz SPA). Beide können unabhängig voneinander verwendet werden, sind allerdings auch eine gute Ergänzung. So kann es sinnvoll sein, zuerst die SQL Performance mit SPA zu überprüfen und danach erst den gesamten Workload mit DB Replay unter Berücksichtigung aller konkurrierenden Zugriffe zu testen. Oder auch umgekehrt: bei der Aufzeichnung von DB Replay den SQL Workload mitaufzuzeichnen und danach separat mit SPA zu evaluieren und zu tunen. Die Durchführung von DB Replay erfolgt dabei grundsätzlich in 3 Schritten: der Aufzeichnung (auch Capture) im Produktivsystem dem einmaligen Processing (auf dem Ziel(Test) Server) und den Replays auf dem Testsystem mit der zugehörigen Auswertung der Reports. Die Idee dabei ist, nicht nur einen einzigen Replay durchzuführen, sondern pro Änderung auf dem Testsystem weitere Replays zu generieren, die dann miteinander verglichen werden können. Capture/Replay Vergleiche sollen im Wesentlichen nur dazu dienen, die generelle Machbarkeit zu verifizieren. Für ein "hartes" Performance Benchmarking ist es erforderlich, Replays auf den zu evaluierenden Konfigurationen/Plattformen zu vergleichen. In unserem aktuellen Fall ging es um eine Machbarkeitsanalyse, um beurteilen zu können, wie ein  Wechsel auf eine andere Plattform wie zum Beispiel Exadata sich auf die kritischen Workloads auswirken würde. Zur Ausführung kann entweder die graphische Oberfläche über Enterprise Manager Cloud Control oder der Linemode mit PL/SQL Packages verwendet werden. Die Infrastruktur für DB Replay besteht dabei hauptsächlich aus den beiden Packages DBMS_WORKLKOAD_CAPTURE und DBMS_WORKLOAD_REPLAY sowie die zugehörigen Data Dictionary Views und einer Client Software WRC (Workload Replay Client) - verfügbar in der Oracle Datenbank Software oder auch separat als Instant Client von OTN downloadbar. Die vollständige Funktionalität der Packages und Data Dictionary Views lässt sich im Handbuch (hier 19c) in den entsprechenden Kapiteln nachlesen unter: Package DBMS_WORKLOAD_CAPTURE Package DBMS_WORKLOAD_REPLAY View DBA_WORKLOAD_CAPTURES View DBA_WORKLOAD_REPLAYS View DBA_WORKLOAD_FILTERS Testing Guide: Part II Database Replay Über die Jahre gab es immer wieder einige interessante Erweiterungen in DB Replay. So ist es beispielsweise möglich einen Capture und Replay in einer Pluggable Database durchzuführen oder die Replay Dateien verschlüsselt abzulegen. In folgenden Abschnitten wird ein exemplarischer Ablauf an einem einfachen Beispiel skizziert um den generellen Umgang mit DB Replay vorzustellen. Weitere Funktionen können in den Beschreibungen nachgelesen werden.    Möchte man das Ganze selbst ausprobieren, kann man die Skripte (bestehend aus SQL und PL/SQL Aufrufen) unter "Alle Skripte zum Download" (in englischer Sprache) laden. Es lohnt sich übrigens auch einen Blick in die einzelnen Listings zu werfen, dort findet man weitere interessante Beschreibungen und Handbuch Links. 1. Schritt: Die Aufzeichnung (Capture) Auf dem Capture-System wird eine Aufzeichnung in ein leeres logisches Verzeichnis (Directory) der Datenbank durchgeführt. Das Capture ist dabei ein Prozess, der nur wenig Overhead auf dem Capture System erzeugt. Das Capture sollte nicht zu lange dauern, ein Workload von circa ein bis zwei Stunden ist eine gute Empfehlung. Prinzipiell besteht die Möglichkeit einen Filter zum Beispiel nach User, Instance Id oder Services zu setzen, damit nicht alle Operationen aufgezeichnet werden und man weniger Capture-Dateien erhält. Typische Filter schliessen beispielsweise Enterprise Manager und RMAN Aktivitäten aus. Capture Filter können dabei ein- oder ausgeschlossen werden. Ob ein Filter als INCLUSION oder EXCLUSION Filter verwendet wird, wird dann beim Capture Start festgelegt. Filtering ist optional und gilt auch nur für die aktuelle Aufzeichnung. Die Überprüfung kann über die View DBA_WORKLOAD_FILTERS erfolgen. Übrigens kann man auch im Nachhinein beim REPLAY spezielle Filter verwenden um nur einen Teil der Aufzeichnung abzuspielen. Typische Beispiele für Filter wären dann ... execute DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname=>'ORACLE MANAGEMENT SERVICE (DEFAULT)', fattribute=>'Program', fvalue=>'OMS'); execute DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname=>'ORACLE MANAGEMENT AGENT (DEFAULT)', fattribute=>'Program', fvalue=>'emagent%'); execute DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname=>'M_RMAN', fattribute=>'Module', fvalue=>'rman%'); Danach kann das Aufzeichnen mit einem einzigen Aufruf initiert werden. Es gibt bestimmte Restriktionen bzgl. der Kommandos, die aufgezeichnet werden. Diese sollte man vorab im Handbuch überprüfen. Die Abhängigkeit von externen Services wie Database Links, Webservices usw. muss ebenfalls berücksichtigt und abgeklärt werden. Starten Sie ausserdem den Capture bei geringer Last um möglichst wenige In Flight-Transaktionen zu erhalten. Am besten wäre natürlich die Möglichkeit die Datenbank herunterzufahren, was in den meisten Umgebungen allerdings keine Option darstellt. Jetzt wird nur noch ein leeres Directory (hier CAPDIR) benötigt und schon kann es losgehen:  -- CREATE OR REPLACE DIRECTORY capdir as '<verzeichnis>'; -- grant read, write on directory capdir; execute DBMS_WORKLOAD_CAPTURE.START_CAPTURE(name=>'&capturename', dir=>'CAPDIR', default_action=>'EXCLUDE'); Das Argument DEFAULT_ACTION (INCLUDE oder EXCLUDE) gibt beim Aufruf an, ob Capture Workloadfilter (falls vorhanden) als INCLUSION oder EXCLUSION Filter verwendet werden. Die Verwendung von EXCLUDE bedeutet in unserem Fall, dass die drei Filter als EXCLUSION Filter angewendet werden d.h. OMS, EMAGENT und RMAN Operation nicht aufgezeichnet werden. Das logische Directory CAPDIR muss leer sein und über ausreichend Platz verfügen, um die aufgezeichneten Dateien zu speichern. Wenn keine Zeitspanne beim Aufruf angegeben wurde- wie in unserem Fall, wird der Capture folgendermassen beendet: execute DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(); Die Capture-Dateien sind dabei binär und unabhängig vom Betriebssystem, so dass der Test auf unterschiedlichen Plattformen möglich ist. Während eines Workload-Captures werden dabei eine Vielzahl von Informationen wie Connections Strings, SQL-Text und Bind Values gespeichert. Möchte man diese Informationen verschlüsselt ablegen, kann dies während des Captures zusätzlich über den Parameter ENCRYPTION angegeben werden. Zur letzten Aktion auf dem Capture-System gehört dann der Export des zugehörigen AWR Snapshots. Damit werden auch noch die Performance Daten im Capture Directory gespeichert. -- Auslesen von Capture Id und AWR Export Status aus der View DBA_WORKLOAD_CAPTURES -- danach Export mit execute DBMS_WORKLOAD_CAPTURE.EXPORT_AWR(capture_id =>'CAPDIR'); Danach werden alle Dateien aus dem Directory CAPDIR auf das Testsystem kopiert. Zur Beurteilung des Capture-Laufs kann man einerseits die View DBA_WORKLOAD_CAPTURES abfragen - übrigens ist dies schon während des Captures möglich - oder einen Report mit der DBMS_WORKLOAD_CAPTURE.REPORT Funktion generieren. Es werden dabei interessante Informationen über die Charakteristiken des Captures wie zum Beispiel Dauer und Größe, Anzahl User Calls und Transaktionen etc. ausgegeben. SQL> SELECT id, start_scn, status, errors, awr_exported, dbtime_total, transactions_total, capture_size/1024/1024 CAPSIZE_MB, user_calls_total, user_calls_unreplayable FROM dba_workload_captures; ID START_SCN STATUS ERRORS AWR_EXPORTED DBTIME_TOTAL TRANSACTIONS_TOTAL ---- -------------- --------- -------- ------------ ------------ ------------------ CAPSIZE_MB USER_CALLS_TOTAL USER_CALLS_UNREPLAYABLE ---------- ---------------- ----------------------- 23 13821838964905 COMPLETED 28833 YES 53929939800 386655 1275.49682 8563239 432574 Ein Capture Report über die Funktion REPORT steht dabei in TEXT- oder HTML-Format zur Verfügung. set pagesize 0 long 30000000 longchunksize 1000 select DBMS_WORKLOAD_CAPTURE.REPORT(capture_id=>&id, format=>'TEXT') from dual; --Ausgabe Database Capture Report For ORCL DB Name DB Id Release RAC Capture Name Status ------------ ----------- ----------- --- -------------------------- ---------- ORCL 1258625022 11.2.0.3.0 NO T_TEST COMPLETED Start time: 15-Jan-19 12:39:26 (SCN = 310491322) End time: 15-Jan-19 13:10:56 (SCN = 310494735) Duration: 31 minutes 30 seconds Capture size: 82.82 KB Directory object: RAT Directory path: /home/oracle/rat_test1 Directory shared in RAC: TRUE Filters used: 4 EXCLUSION filters Captured Workload Statistics DB: ORCL Snaps: 45621-45622 -> 'Value' represents the corresponding statistic aggregated across the entire captured database workload. ... Die Capture Skripte sind hier zum Download. 2. Schritt: Das Processing der Capture Dateien Im zweiten Schritt muss ein Processing der Capture-Dateien in Vorbereitung für das Replay durchgeführt werden. Diese Operation transformiert die Capture Daten in ein abspielbares Format. Sie ist ein einmalig und muss auf dem Zielsystem erfolgen. execute DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir => '&dir'); Das Processing Skript kann hier geladen werden . 3. Schritt: Das Abspielen (Replay) Um einen Replay durchzuführen, ist zuvor die Erstellung eines Testsystems nötig. Wichtig ist dabei, dass die Datenbank auf dem Capture und Replay-System den gleichen Konsistenzzustand haben. Verwenden kann man dazu RMAN, Data Guard, Flashback Database, Snapshot Standby, Data Pump usw. Flashback Database in Kombination mit einem sogenannten "Guaranteed Restore Point" ist beispielsweise eine einfache Möglichkeit die Datenbank wieder schnell zurückzusetzen. Nun kann der Replay initialisiert werden. Dazu wird ein Replay Name vergeben und das Directory, in dem sich die kopierten Daten aus der Aufzeichnung befinden, bekanntgegeben. execute DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name=>'&Replay_name', replay_dir=>'&DIR'); Danach wird mit einem Prepare Kommando, die Charakteristik des Replays festgelegt. Das Replay kann in Oracle Database 19c in den unterschiedlichen Synchronisierungvarianten TIME und SCN ablaufen. Die Synchronisierung TIME (clock based) in Oracle Database 19c basiert dabei auf der Zeit, in der die Aktion während des Captures stattgefunden hat. Die Synchronisierung SCN (Defaulteinstellung) hingegen basiert auf der Commit Zeit während des Captures; die Commit-Reihenfolge bleibt hierbei erhalten. Genaueres kann man dazu im Handbuch erfahren. Hinweis: Die Parameter und die Defaultwerte für die Synchronisierung sind dabei je nach Datenbank Release unterschiedlich. Bitte konsultieren Sie das entsprechende Handbuch für die passende Einstellung. Möchte man gleichzeitig dazu ein SQL Tuning Set mitaufzeichnen, kann dies über den Parameter CAPTURE_STS festgelegt werden. execute DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => 'TIME', capture_sts => TRUE); Wie sieht es mit einer Skalierung des Ablaufs aus? Lässt sich das Timing verändern bzw die Statement Ausführung erhöhen? Weitere Parameter wie CONNECT_TIME_SCALE (Default 100) und THINK_TIME_SCALE (Default 100) sind für die Skalierung von Connection beziehungsweise Think Time zuständig. Über eine Verringerung dieser Parameter kann der aufgezeichnete Workload dann mit unterschiedlicher „Geschwindigkeit“ ablaufen. Ausserdem kann der Parameter SCALE_UP_MULTIPLIER zur Erhöhung der Statement Ausführung (natürlich nur SELECT Kommandos) verwendet werden. Nun sind alle Vorbereitungen getroffen und die Clients(WRC) werden gestartet - entweder auf dem Server selbst oder auf einer zusätzlichen Client Maschine. Die WRC Software findet sich entweder im $ORACLE_HOME/bin oder kann als Instant Client Software von OTN geladen werden. [oracle@by19c ~]$ wrc help=yes Workload Replay Client: Release 19.3.0.0.0 - Production on Tue Apr 21 08:31:52 2020 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. FORMAT: ======= wrc [user/password[@server]] [MODE=mode-value] KEYWORD=value Examples: ========= wrc REPLAYDIR=. wrc scott/tiger@myserver REPLAYDIR=. wrc MODE=calibrate REPLAYDIR=./capture The default privileged user is: system Mode: ===== wrc can work in different modes to provide additional functionalities. The default MODE is REPLAY. Mode Description ---------------------------------------------------------------- REPLAY Default mode that replays the workload in REPLAYDIR VERSION Print the wrc version CALIBRATE Estimate the number of replay clients and CPUs needed to replay the workload in REPLAYDIR ... Um Auskunft über die Anzahl der Clients zu erhalten, bietet sich die Nutzung des WRC Programms selbst oder die Durchführung der Funktion DBMS_WORKLOAD_REPLAY.CALIBRATE an. wrc user/password@ replaydir= mode=calibrate Danach erfolgt der Start der Workload Replay Clients. wrc user/password@ replaydir= mode=REPLAY wrc ... wrc ... Nun kann der Replay durchgeführt werden. Die View DBA_WORKLOAD_REPLAYS gibt dabei zu jeder Zeit Auskunft über den Stand des Replays. execute DBMS_WORKLOAD_REPLAY.START_REPLAY(); Nachdem der Replay beendet ist, kann mit den Auswertungen begonnen werden. Skripte für den Replay finden sich hier zum Download. 4. Schritt: Die Auswertung Erste Informationen zum Replay kann man ähnlich wie beim Capture über eine Data Dictionary View - hier DBA_WORKLOAD_REPLAYS - selektieren, die auch schon während des Laufs verwendet werden kann. Es gibt allerdings auch spezielle DB Replay Berichte und natürlich auch die Möglichkeit über einen zugehörigen AWR Report weitere Informationen zu erhalten. Alle Reports lassen sich im Linemode als TEXT- oder HTML-Format generieren. Besonders hervorzuheben sind dabei der Replay und der Compare Period Report, die bei der Auswertung hilfreich sind. Der Compare Period Report ist häufig sogar ausreichend um Schlüsse über die Machbarkeit und die Gesamtperformance zu ziehen. Eine nachgelagerte Analyse mit einem AWR Report bzw. einem AWR Difference Report bestätigt in der Regel die Ergebnisse aus dem Compare Period Report und kann noch weitere Details zur Performance geben. Normalerweise startet man mit dem Replay Report. Dieser gibt Informationen zu den Einstellungen des Replay Laufs (wie z.B. Synchronisation, Skalierung etc.), Replay Statistiken (wie Anzahl User Calls und DB Time), Top-Events, Workload-Profile und Divergenzen. Eine grosse Anzahl von Divergenzen können auf grobe Verstöße und Probleme beim Abspielen hinweisen und sollten weiter untersucht und beseitigt werden. Ein Replay Report kann folgendermassen erzeugt werden. set pagesize 0 long 30000000 longchunksize 1000 col tt format a120 select DBMS_WORKLOAD_REPLAY.REPORT(replay_id=>&replayid, format=>'HTML') tt from dual; Folgendes Beispiel zeigt einen kurzen Ausschnitt aus einem Replay Report. Eine weitergehende Analyse kann nun mit dem Compare Period Report durchgeführt werden. Voraussetzung ist dabei der Import des Capture AWRs mit der Funktion IMPORT_AWR, die die Capture Id und ein Staging Schema benötigt. DECLARE dbid NUMBER; BEGIN dbid := DBMS_WORKLOAD_REPLAY.IMPORT_AWR(CAPTURE_ID=>&captureid, STAGING_SCHEMA =>'&schema'); END; / Der Compare Period Report liefert nun einen guten Überblick über die Gesamtperformance im Vergleich (z.B. Capture mit Replay1, Replay1 mit Replay2 usw.) und gibt entscheidende Hinweise über die Performance-Unterschiede. Es werden Optimizer- und Memory-Einstellungen, wichtige Initialisierungsparameter, Performance-Statistiken und Top Statements miteinander verglichen und zusätzlich einige Hardware-Statistiken wie I/O- oder CPU-Nutzung ausgegeben. Zudem gibt er eine Bewertung zu den Divergenzen ab - LOW bedeutet dabei zum Beispiel vernachlässigbarer Divergenzanteil. Ähnlich wie beim Replay Report wird das Package DBMS_WORRKLOAD_REPLAY dazu verwendet - hier mit der Funktion COMPARE_PERIOD_REPORT. Notwendig sind dabei die aktuelle Replay Id und die Vergleichs Replay Id. Falls wir den Capture mit dem Replay vergleichen ist die Replay Id NULL. variable ergebnis clob begin DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT( replay_id1 => &replayid, replay_id2 => null, format => 'HTML', result => :ergebnis); end; / set heading off set long 100000 set pagesize 0 spool comparereport.html rep print ergebnis spool off Folgendes Beispiel zeigt einen kleinen Ausschnitt aus einem Compare Period Report. Möchte man darüberhinaus noch weitere detaillierte Informationen über die einzelnen Performance-Metriken erhalten, kann man zum Schluss noch einen AWR Difference Report hinzuziehen oder einzelne AWR Reports generieren. Skripte zur Generierung von DB Replay Reports finden sich hier zum Download. Und alle DB Replay Skripte finden sich hier zum Download. Fazit An dieser Stelle sollen noch einige Erfahrungen mit DB Replay und SPA geschildert werden, die wir in mehreren ausgewählten Projekten gemacht haben. Die Technologie wurde dabei für unterschiedlichste Zwecke wie Plattformwechsel, Migration, Upgrade, Architekturwechsel oder Patch-Test verwendet. Die Zeit zum Erstellen eines Testsystems mit Backup, Upgrade, Migration und die Wahl der Methode zum Zurücksetzen müssen natürlich bei der Planung der Tests immer berücksichtigt werden. Nach einer anfänglichen Lernkurve, um sich mit dem Werkzeug vertraut zu machen, wurde in jedem Projekt schnell deutlich, wie gering der Testaufwand mit Real Application Testing ist und welche Vorteile ein solcher Test hat. So reichte in den meisten Fällen eine 1-2 stündige Einführung mit den hier im Blog zur Verfügung gestellten Skripten für die entsprechenden DBAs aus, um das richtige Verständnis von DB Replay zu bekommen und mit den Tests zu beginnen. Im aktuellen Fall wurden beispielsweise mehrere Capture Workloads auf der Produktionsseite durchgeführt, um auch die unterschiedlichsten Workloads auszutesten. Die Replays wurden dann auf der zugehörigen Zielplattform, die natürlich mit dem entspechenden Backup verfügbar war, abgespielt. Die Ergebnisse wurden dann von den Datenbank Performance Fachleuten evaluiert. Replay Report und Compare Period Report haben dann sogar für die Analyse ausgereicht. Der Replay Report hilft dabei zuerst die Güte des Laufs zu evaluieren. Sind Laufzeiten, Divergenzen und Anzahl Calls vergleichbar und realistisch, kann schon mit einem Compare Period Report die Gesamtperformance sehr gut beurteilt werden, da die Hauptperformance Merkmale dort schon verzeichnet sind.  Ein AWR Difference Report kann dann falls erforderlich noch abschliessend zur Bestätigung hinzugezogen werden.     Unserer Erfahrung nach kann Database Replay Sicherheit und Vertrauen für Migrationen/ Upgrade bringen, für ein besseres Verständnis der eigenen Applikationen sorgen oder auch die entscheidenden Argumente für einen Plattform- oder Architekturwechsel liefern. Und nicht zu vergessen: Real Application Testing kann damit auch ein äusserst wertvolles Mittel sein, wenn ein Wechsel in die Cloud ansteht. Der Ablauf hier im Blog zeigt beispielhaft wie der rote Faden eines DB Replay Prozedere aussehen kann. Weitere Varianten der Skripte lassen sich aus dem Handbuch ableiten. Folgende Links können dazu hilfreich sein. Package DBMS_WORKLOAD_CAPTURE Package DBMS_WORKLOAD_REPLAY View DBA_WORKLOAD_CAPTURES View DBA_WORKLOAD_REPLAYS View DBA_WORKLOAD_FILTERS Testing Guide: Part II Database Replay Dojo 5: Real Application Testing: Testen mit Database Replay und SQL Performance Analyzer Oracle Instant Client Software Download Swingbench Alle Skripte zum Download

In letzter Zeit häufen sich bei mir Anfragen zum Thema Oracle Real Application Testing. Zahlreiche Gründe wie beispielsweise Einführung von Multitenant, die Änderung des zugrundeliegenden...

Database Management

Hybrid Partitioned Tables - Lifecycle Management leicht gemacht

Mit Oracle External Table kann man seit jeher auf Datenmengen, die in Dateien ausserhalb der Datenbank gespeichert sind, lesend zugreifen. In der Datenbank werden dabei nur die erforderlichen Metadaten der External Tables gespeichert. Voraussetzung ist immer das Vorhandensein eines logischen Directories und die Verwendung eines entsprechenden Access Treibers wie z.B. ORACLE_LOADER oder ORACLE_DATAPUMP. Die Dateien können in unterschiedlichen Formaten im Dateisystem, im HDFS oder aber auch mit Autonomous Database im Oracle Cloud Object Storage vorliegen. Bei Letzterem ist die Verwendung des Package DBMS_CLOUD erforderlich. Seit geraumer Zeit gibt es zusätzlich die Möglichkeit External Tables auch partitioniert abzulegen. Damit war es nur noch eine Frage der Zeit, dass man auch eine gemischte - also eine hybride Speicherung - mit der External Table Technik erlaubt. Mit Oracle Database 19c ist es soweit: nun besteht die Möglichkeit hybride partitionierte Tabellen (engl. hybrid partitioned tables) anzulegen. Die Partitionen von hybriden, partitionierten Tabellen können dann sowohl in der Oracle Datenbank in Oracle Tablespaces als auch in externen Quellen vorliegen, wie z.B. Linux-Dateien mit CSV-Einträgen oder Dateien auf dem Hadoop Distributed File System (HDFS). So lassen sich beispielsweise Partitionen, die nicht mehr so häufig abgefragt werden, einfach in externe Dateien verschieben, um so eine kostengünstigere Speicherlösung zu verwenden. Hybrid Partitioned Tables unterstützen dabei alle existierenden Tabellentyptreiber für externe Partitionen wie ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS und ORACLE_HIVE. Um hybrid partitioned Tables anzulegen, wird eine erweiterte External Table Syntax verwendet. Hybrid Partitioned Tables können mit CREATE TABLE oder auch mit ALTER TABLE erzeugt werden. Mit dem ALTER TABLE Kommando können dann beispielsweise "normal" partitionierte Tabellen nachträglich mit externen Partitionen angereichert werden. Eine gute Einführung findet sich im Handbuch "VLDB and Partitioning Guide" im Kapitel Hybrid Partitioned Tables. Hier werden u.a. auch die Einschränkungen beschrieben. So sind die Operationen wie SPLIT, MERGE und MOVE nicht erlaubt und als Partitionierungstyp bisher nur Single Level Partitionierung mit LIST und RANGE möglich. Darüberhinaus gibt es im Moment auch keine Unterstützung für LOB, LONG und ADT Datentypen. Hybrid Partitioned Tables mit CREATE TABLE erzeugen In Szenario 1 wird eine hybrid partitioned Table mit CREATE TABLE angelegt. Die Partition DEPTNO_10 ist eine interne Partition, DEPTNO_20 eine externe Partition. Die Klausel EXTERNAL PARTITION ATTRIBUTES gibt den Treiber (hier: ORACLE_LOADER) und das Directory (hier: HOME) für die externe(n) Partition(en) an. Die CSV Datei emp_dept_20.csv im Directory HOME speichert die Informationen zur Partition DEPTNO_20. Tipp: Mit dem SQL*Plus Kommando SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}] lassen sich die Daten aus Tabellen einfach im CSV Format ausgeben und speichern. Ein Beispiel dazu findet sich im Tipp 12.2: Ausgewählte Basisfeatures in SQL und PL/SQL . Das Directory mit Namen HOME ist vorhanden, wie wir zuerst überprüfen. SQL> SELECT directory_name, directory_path FROM all_directories; DIRECTORY_NAME DIRECTORY_PATH --------------- ------------------------------------------------------------ LOBHOME /home/oracle/LOB TEST /home/oracle/test HOME /home/oracle ... Nun wird eine hybrid partitioned Table EXT_EMP_DEPT_HYBRID_1 erzeugt. Sie ist LIST partitioniert nach der Spalte DEPTNO. DROP TABLE scott.ext_emp_dept_hybrid_1; CREATE TABLE scott.ext_emp_dept_hybrid_1 ( "ENAME" varchar2(10), "DNAME" varchar2(14), DEPTNO number) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY home REJECT LIMIT UNLIMITED ) PARTITION BY LIST (deptno) ( PARTITION deptno_10 VALUES (10) , -- intern PARTITION deptno_20 VALUES (20) EXTERNAL LOCATION ('emp_dept_20.csv')) -- extern / -- Befüllen der internen Partition INSERT INTO "SCOTT".EXT_EMP_DEPT_HYBRID_1 SELECT e.ename, d.dname, d.deptno FROM scott.emp e JOIN scott.dept d ON d.deptno=e.deptno WHERE d.deptno=10; -- Das Resultat abfragen SQL> SELECT * FROM scott.ext_emp_dept_hybrid_1; ENAME DNAME DEPTNO ---------- -------------- ---------- CLARK ACCOUNTING 10 KING ACCOUNTING 10 MILLER ACCOUNTING 10 "SMITH" "RESEARCH" 20 "JONES" "RESEARCH" 20 "SCOTT" "RESEARCH" 20 "ADAMS" "RESEARCH" 20 "FORD" "RESEARCH" 20 8 rows selected. Weitere Partitionen lassen sich dann mit dem ALTER TABLE Kommando wie folgt hinzufügen. SQL> ALTER TABLE scott.ext_emp_dept_hybrid_1 ADD PARTITION deptno_30 VALUES (30) EXTERNAL DEFAULT DIRECTORY home LOCATION ('emp_dept_30.csv'); Das Ganze kann auch mit dem Treiber ORACLE_DATAPUMP verwendet werden. Dazu ist es zuerst notwendig die externen Dateien, die später als externe Partitionen verwendet werden sollen, mit einer Hilfstabelle zu erzeugen. Die Dateien haben in unserem Beispiel dann den Namen emp_dept_20.exp bzw. emp_dept_30.exp. Folgendes Beispiel erzeugt die Datei emp_dept_20.exp. DROP TABLE ext_emp_dept_help; CREATE TABLE ext_emp_dept_help ORGANIZATION EXTERNAL ( TYPE oracle_datapump DEFAULT DIRECTORY home LOCATION ('emp_dept_20.exp') ) REJECT LIMIT UNLIMITED AS SELECT e.ename, d.dname, deptno FROM scott.dept d JOIN scott.emp e USING (deptno) WHERE deptno=20 / --DROP TABLE ext_emp_dept_help; Danach wird die Datei emp_dept_30.exp nach dem gleichen Verfahren erstellt. Nun sind wir in der Lage im nächsten Schritt die hybrid partitioned Tabelle anzulegen. Die externe Partition DEPTNO_20_30 greift dann auf die externen Dateien emp_dept_20.exp und emp_dept_30.exp zu, die wir vorher mit der Hilfstabelle erzeugt und im Directory HOME zur Verfügung gestellt haben. DROP TABLE scott.ext_emp_dept_hybrid_2; CREATE TABLE scott.ext_emp_dept_hybrid_2 ( "ENAME" varchar2(10) , "DNAME" varchar2(14), DEPTNO number) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_datapump DEFAULT DIRECTORY home ) PARTITION BY LIST (deptno) ( PARTITION deptno_10 VALUES (10), -- intern PARTITION deptno_20_30 VALUES (20,30) EXTERNAL LOCATION ('emp_dept_20.exp','emp_dept_30.exp') -- extern ) / Partitioned Tables in Hybrid Partitioned Tables umwandeln In Szenario 2 wird eine list partitioned Table in eine hybrid partitioned Table umgewandelt. Wir verwenden dazu eine "normale" list partitioned Table mit der internen Partition DEPTNO_10. DROP TABLE scott.ext_emp_dept_hybrid_3; CREATE TABLE scott.ext_emp_dept_hybrid_3 ( "ENAME" varchar2(10), "DNAME" varchar2(14), DEPTNO number) PARTITION BY LIST (deptno) ( PARTITION deptno_10 values (10)); -- Befüllen der Partition INSERT INTO scott.ext_emp_dept_hybrid_3 SELECT e.ename, d.dname, d.deptno FROM scott.emp e JOIN scott.dept d ON d.deptno=e.deptno WHERE d.deptno=10; Um externe Partitionen einbinden zu können, muss zuerst die Klausel EXTERNAL PARTITION ATTRIBUTES hinzugefügt werden. Damit wird dann der Treiber (hier ORACLE_LOADER) und das Default Directory (hier HOME) bekanntgegeben. ALTER TABLE scott.ext_emp_dept_hybrid_3 ADD EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY home REJECT LIMIT UNLIMITED ); Im nächsten Schritt können dann externe Partitionen hinzugefügt werden. Hier wird die externe Partition DEPTNO_20_30 angefügt, die ihre Informationen aus den Dateien emp_dept_20.csv und emp_dept_30.csv erhält. ALTER TABLE scott.ext_emp_dept_hybrid_3 ADD PARTITION deptno_20_30 VALUES (20,30) EXTERNAL LOCATION ('emp_dept_20.csv', 'emp_dept_30.csv'); Hybrid Partitioned Tables und Oracle Object Storage In Autonomous Database mit der Version 19c können ebenfalls hybrid partitioned Tables verwendet werden. Wie im Falle von External Tables oder Partitioned External Tables wird dazu das Package DBMS_CLOUD verwendet. Ein Beispiel zum Anlegen einer External Table mit DBMS_CLOUD findet sich im Community Tipp Autonomous Database: External Tables. Im Fall von hybrid partitioned Tables sieht die Syntax dann folgendermassen aus: DBMS_CLOUD.CREATE_HYBRID_PART_TABLE ( table_name IN VARCHAR2, credential_name IN VARCHAR2, partitioning_clause IN CLOB, column_list IN CLOB, field_list IN CLOB DEFAULT, format IN CLOB DEFAULT); Im Handbuch "Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure" im Appendix A "Autonomous Database Supplied Package Reference" findet sich dazu die Beschreibung der einzelnen Argumente. Im Szenario 3 liegen die externen Informationen im Oracle Object Storage in den Dateien emp_dept_20.csv und emp_dept_30.csv, die zu Beginn schon erzeugt worden sind. Wie beim Laden von Daten mit Data Pump muss auch in diesem Fall gewährleistet sein, dass die Kommunikation mit der Autonomous Database abgesichert ist und nur autorisierte Daten geladen werden. Dazu sind die Datenbank Credentials mit dem Package DBMS_CLOUD zuvor angelegt und als Default festgelegt worden. Wir überprüfen die Datenbank Credentials. Falls noch nicht vohanden, werden sie mit ALTER DATABASE PROPERTY festgelegt. SQL> SELECT credential_name, username, enabled, comments   FROM dba_credentials; CREDENTIAL_NAME USERNAME ENABL COMMENTS --------------- ---------------------------------------- ----- ---------------------------------------- CREDENTIAL_US1 oracleidentitycloudservice/ulrike.schwin TRUE {"comments":"Created via DBMS_CLOUD.crea n@oracle.com te_credential"} -- falls nicht enabled -- alter database property set default_credential = 'ADMIN.CREDENTIAL_US1'; -- falls nicht vorhanden, dann zuerst mit dbms_cloud.create_credential erzeugen Das Vorhandensein der Dateien im Object Storage lässt sich vorab auch mit DBMS_CLOUD übeprüfen. Hinweis: DBMS_CLOUD.CREATE_HYBRID_PART_TABLE unterstützt nicht nur Oracle Cloud Infrastructure Object Storage, sondern auch Microsoft Azure und AWS S3. SQL> SELECT object_name, bytes FROM dbms_cloud.list_objects('CREDENTIAL_US1','https://objectstorage.eu-frankfurt 1.oraclecloud.com/ n/oraseemeadesandbox/b/USBUCKET/') WHERE object_name LIKE 'emp%'; OBJECT_NAME BYTES -------------------------------------------------- ---------- emp_dept_20.csv 114 emp_dept_20.exp 12288 emp_dept_30.csv 121 emp_dept_30.exp 12288 Wir erzeugen nun eine hybrid partitioned Table mit der Prozedur DBMS_CLOUD.CREATE_HYBRID_PART_TABLE. Für eine erfolgreiche Anwendung ist dabei die Angabe von Tabellenname, der Credential Name, das Argument FORMAT, das Argument COLUMN_LIST und die Klausel für die externen und internen Partitionen erforderlich. Zur Erinnerung: der Parameter FORMAT muss als ein JSON Objekt angegeben. Dazu gibt es zwei Möglichkeiten. format => '{"format_option" : "format_value" }' oder format => json_object('format_option' value 'format_value')) Folgendes Beispiel zeigt die Verwendung. Im Argument FORMAT können wir den Typ CSV nutzen. DROP TABLE ext_emp_dept_hybrid_auto; BEGIN DBMS_CLOUD.CREATE_HYBRID_PART_TABLE( table_name => 'EXT_EMP_DEPT_HYBRID_AUTO', credential_name => 'CREDENTIAL_US1', format => json_object('type' VALUE 'CSV'), column_list => 'ename varchar2(10), dname varchar2(14), deptno number', partitioning_clause => 'partition by list (deptno) ( partition deptno_30 values (30) external location -- extern (''https://objectstorage.eu-frankfurt 1.oraclecloud.com/n/oraseemeadesandbox/b/ USBUCKET/o/emp_dept_30.csv''), partition deptno_20 values (20) external location -- extern (''https://objectstorage.eu-frankfurt 1.oraclecloud.com/n/oraseemeadesandbox/b/ USBUCKET/o/emp_dept_20.csv''), partition deptno_10 values (10) )' -- intern ); END; / Um sicher zu gehen, dass der Zugriff mit External Table erfolgreich sein wird, empfiehlt es sich vorab mit VALIDATE_EXTERNAL_TABLE eine Prüfung durchzuführen. Gibt es eine Fehlermeldung, sollte man noch einmal das Setup prüfen. In folgendem Beispiel ist offensichtlich die URI nicht korrekt. Die Fehlermeldung gibt dabei den richtigen Hinweis darauf. execute DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO'); Error starting at line : 24 in command - BEGIN DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO'); END; Error report - ORA-20011: Invalid object uri - https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/emp_dept_30.csv ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 844 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1105 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1389 ORA-06512: at line 1 Ist das Problem behoben, kann man erfolgreich auf die Daten zugreifen. SQL> SELECT * FROM ext_emp_dept_hybrid_auto; ENAME DNAME DEPTNO ---------- -------------- ---------- ALLEN SALES 30 WARD SALES 30 MARTIN SALES 30 BLAKE SALES 30 TURNER SALES 30 JAMES SALES 30 SMITH RESEARCH 20 JONES RESEARCH 20 SCOTT RESEARCH 20 ADAMS RESEARCH 20 FORD RESEARCH 20 11 rows selected. Möchte man den Treiber ORACLE_DATAPUMP verwenden, muss im Argument FORMAT der Treiber mit der Konstante DBMS_CLOUD.FORMAT_TYPE_DATAPUMP verwendet werden. Die beiden externen Dateien emp_dept_30.exp und emp_dept_20.exp aus dem ersten Beispiel sind dazu vorab in den Object Storage geladen worden. DROP TABLE ext_emp_dept_hybrid_dp; BEGIN DBMS_CLOUD.CREATE_HYBRID_PART_TABLE ( table_name => 'EXT_EMP_DEPT_HYBRID_DP', credential_name => 'CREDENTIAL_US1', format => json_object('type' VALUE DBMS_CLOUD.FORMAT_TYPE_DATAPUMP), column_list => 'ename varchar2(10), dname varchar2(14), deptno number', partitioning_clause => 'partition by list (deptno) ( partition deptno_30 values (30) external location ( ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/emp_dept_30.exp''), partition deptno_20 values (20) external location ( ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/emp_dept_20.exp''), partition deptno_10 values (10) )' ); END; / Nach erfolgreicher Validierung, kann man auch hier wie gewohnt auf die Informationen mit SQL Kommandos zugreifen. SQL> execute DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO_DP'); PL/SQL procedure successfully completed. Monitoring Möchte man herausfinden, ob eine Tabelle hybrid partitioned ist, kann man dazu im Data Dictionary nachsehen. External Tables - egal ob hybrid oder nicht - lassen sich generell mit der View USER/ALL/DBA_EXTERNAL_TABLES auflisten. SQL> SELECT table_name, owner, type_name FROM all_external_tables; TABLE_NAME OWNER TYPE_NAME ------------------------------ ---------- -------------------- OPATCH_XML_INV SYS ORACLE_LOADER EXT_EMP_DEPT_HYBRID_LIST SCOTT ORACLE_DATAPUMP EXT_EMP_DEPT_HYBRID_3 SCOTT ORACLE_LOADER EXT_EMP_DEPT_HYBRID_2 SCOTT ORACLE_LOADER EXT_EMP_DEPT_HYBRID_RANGE SCOTT ORACLE_DATAPUMP EXT_EMP_DEPT_HYBRID_2 SCOTT ORACLE_DATAPUMP EXT_EMP_DEPT_HYBRID_1 SCOTT ORACLE_LOADER ORDERS_H OT ORACLE_LOADER HYPT_TO_INT_TABLE US ORACLE_LOADER EXT_EMP_DEPT_HELP US ORACLE_DATAPUMP 9 rows selected. Sucht man speziell nach der Eigenschaft "hybrid", sollte man die View USER/ALL/DBA_TABLES mit der neuen Spalte HYBRID verwenden. SQL> SELECT table_name, owner, hybrid FROM all_tables where hybrid='YES'; TABLE_NAME OWNER HYB ------------------------------ ---------- --- EXT_EMP_DEPT_HYBRID_RANGE SCOTT YES EXT_EMP_DEPT_HYBRID_2 SCOTT YES EXT_EMP_DEPT_HYBRID_LIST SCOTT YES EXT_EMP_DEPT_HYBRID_1 SCOTT YES EXT_EMP_DEPT_HYBRID_2 SCOTT YES EXT_EMP_DEPT_HYBRID_3 SCOTT YES ORDERS_H OT YES HYPT_TO_INT_TABLE US YES 8 rows selected. Hybrid partitioned Tables werden natürlich auch in den Views, die mit external partitioned Tables eingeführt wurden, gelistet wie zum Beispiel: USER/ALL/DBA_XTERNAL_PART_TABLES USER/ALL/DBA_XTERNAL_TAB_PARTITIONS USER/ALL/DBA_XTERNAL_TAB_SUBPARTITIONS USER/ALL/DBA_XTERNAL_LOC_PARTITIONS Folgende Abfrage mit USER_XTERNAL_PART_TABLES listet alle partitionierten external Tables auf. SQL> SELECT table_name, type_name, default_directory_name DIRECTORY,   reject_limit, access_parameters FROM user_xternal_part_tables; TABLE_NAME TYPE_NAME DIRECTORY ------------------------------ -------------------- -------------------- REJECT_LIMIT ---------------------------------------- ACCESS_PARAMETERS -------------------------------------------------------------------------------- HYPT_TO_INT_TABLE ORACLE_LOADER HOME UNLIMITED FIELDS TERMINATED BY ',' (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',chan ... Fazit Ab Oracle Database 19c gibt es die Möglichkeit partitionierte Tabellen mit Partitionen auszustatten, deren Daten innerhalb und ausserhalb der Datenbank liegen. So kann Speicherplatz für Daten nicht nur in der Datenbank selbst sondern auch außerhalb verwendet werden. Die Daten können dabei als Dateien im Dateisystem, im Hadoop Distributed File System (HDFS) oder wie im Fall vom Autonomous Database auch im Cloud Object Storage liegen. Ein aktuelles Beispiel liefert CERN, die damit die Möglichkeit nutzten 1 PB Daten mit Oracle Autonomous Database zu nutzen. Wer mehr dazu erfahren möchte, kann sich den Oracle Global Leaders Webcast: Managing 1 PB of data with Oracle Autonomous DW ansehen. Weitere Informationen und Artikel zum Thema External Tables Folgende Links listen unsere deutschsprachigen Blogeinträge zum Thema External Tables, die wir über die Jahre veröffentlicht haben - angefangen von den Grundlagen zu External Tables bis zum aktuellen Tipp in 19c. Grundlagen zu External Tables External Table in 12c: Partitionierung, Constraints, XML, Parameter zur Laufzeit usw. Inline External Table mit 18c In-Memory External Tables Autonomous Database: External Tables Hybrid Partitioned Tables: Lifecycle Management leicht gemacht Handbucheinträge und weitere Informationen VLDB and Partitioning Guide: Hybrid Partitioned Tables Appendix A "Autonomous Database Supplied Package Reference": DBMS_CLOUD Partitioning Guide (Überblick von Herrman Bär - ca 350 Slides) Kundenerfahrung CERN im Webcast Oracle Global Leaders Webcast: Managing 1 PB of data with Oracle Autonomous DW  

Mit Oracle External Table kann man seit jeher auf Datenmengen, die in Dateien ausserhalb der Datenbank gespeichert sind, lesend zugreifen. In der Datenbank werden dabei nur die...

Database Management

Blockchain inside Oracle Database 20c

Nur das Einfügen von Daten in Tabellen (also INSERT-Only) zu erlauben und keine weiteren Manipulationen zuzulassen sind wichtige Anforderungen - auch an die Oracle Datenbank. In Oracle Database 20c ist es nun soweit: Mit der interessanten Erweiterung Datenbanktabellen vom Typ Blockchain kann diese Anforderung erfüllt werden. Zusätzliche Security-Eigenschaften dieses Tabellentyps ermöglichen darüberhinaus Blockchain-Anwendungen nun auch zentral in der Datenbank zu verwalten. Typische Anwendungsfälle sind beispielsweise zentrale unveränderbare Daten wie z.B. Messwerte von IoT-Geräten, Compliance-Daten aus Revisionsgründen usw. innerhalb der Datenbank zu speichern. Einen guten Überblick über die Anwendungsmöglichkeiten und das darunterliegende Konzept kann man im Blogposting "Native Blockchain Tables Extend Oracle Database’s Multi-model Converged Architecture" von Mark Rakhmilevich nachlesen. Dort findet sich auch eine Gegenüberstellung, wann es sinnvoll ist, Blockchain Anwendungen in der Datenbank oder in einer Blockchain Plattform zu betreiben. Aber wie kommt man an eine Oracle Database in der Version 20c? Seit Februar 2020 steht Oracle Database 20c als Oracle Cloud Database Service Virtual Machine im  sogenannten Preview Mode zur Verfügung. Oracle Cloud Datenbanksysteme im Preview Mode werden dabei wie alle anderen Datenbanksysteme in der Cloud erzeugt. Wichtig zu wissen ist nur, dass beim Anlegen die Option "Logical Volume Manager" als Storage Management Software gewählt werden muss. Mehr dazu kann man in unserem Blogposting Oracle Database 20c Preview Mode dazu nachlesen.    Aber zurück zu Thema Blockchain: Was sind noch einmal Blockchains? In einem Satz ausgedrückt: Blockchains sind per Definition kontinuierlich erweiterbare Listen von Datensätzen, die mittels kryptographischer Verfahren miteinander verkettet sind. Jeder Datensatz (Block) enthält dabei typischerweise einen kryptographisch sicheren Hash des vorangegangenen Blocks, einen Zeitstempel und Transaktionsdaten (Quelle Wikipedia). Oracle Blockchain Tabellen erfüllen diese Voraussetzungen: Sie sind INSERT-Only und speichern zusätzlich zu den aktuellen Spaltenwerten kryptographische Hashwerte von früher eingefügten Blöcken (auch Chaining) in sogenannten hidden Spalten ab um Manipulationssicherheit zu gewährleisten. Wenn überhaupt können gewisse Zeilen oder die gesamte Tabelle nur nach einer gewissen (user-definierten) Zeit gelöscht werden. Da es sich um Datenbanktabellen handelt, ist der Zugriff über SQL oder PL/SQL ohne Änderung am SQL Code transparent wie üblich möglich. Wie erzeugt man eine Blockchain Tabelle? Eine Blockchain Tabelle wird mit CREATE TABLE mit einer erweiterten Syntax erzeugt. Folgende Klauseln sind dabei mandatory: - NO DROP [UNTIL n DAYS IDLE]   - NO DELETE [LOCKED] [UNTIL n DAYS AFTER INSERT]  - HASHING USING "SHA2_512" VERSION v1. Im ersten Release sind dabei Datentypen wie NUMBER, VARCHAR2, RAW, JSON, BLOB, CLOB, DATE und weitere skalare Datentypen möglich; andere Datentypen wie ADT, BFILE etc. sind noch nicht erlaubt. Im folgenden Beispiel legen wir eine Tabelle mit Namen LEDGER_TAB1 an, die erst nach frühestens 25 Tagen Idle Time (der Default ist 16) gelöscht werden kann. Das Löschen einzelner Zeilen ist erst nach 31 Tagen möglich. Das genaue Kommando sieht dann folgendermassen aus. SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER) NO DROP UNTIL 25 DAYS IDLE NO DELETE UNTIL 31 DAYS AFTER INSERT HASHING USING "SHA2_512" VERSION v1; Table created. Die Data Dictionary Tabelle USER(ALL|DBA|CDB)_BLOCKCHAIN_TABLES gibt einen Überblick über die Tabellen vom Typ Blockchain. SQL> SELECT * FROM user_blockchain_tables; TABLE_NAME ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG --------------- ------------- --- -------------------------- -------- LEDGER_TAB1 25 NO 16 SHA2_512 LEDGER_TAB2 16 NO 16 SHA2_512 ORDERS_BL 31 NO 31 SHA2_512 Die Tabelle besteht dabei aus den 3 user-definierten Spalten BANK, D_DATE und D_AMOUNT. Zusätzlich werden 10 weitere hidden Spalten angelegt, um die zusätzlichen Informationen für die Blockchain abzuspeichern. Hidden Spalten lassen sich entweder aus der Tabellen USER_TAB_COLS oder mit der SQL*PLUS Variablen SET COLINVISIBLE ON sichtbar machen. SQL> desc ledger_tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- BANK VARCHAR2(128) D_DATE DATE D_AMOUNT NUMBER SQL> SELECT column_name, hidden_column FROM user_tab_cols WHERE table_name='LEDGER_TAB1'; COLUMN_NAME HID --------------------------------------------- --- BANK NO D_DATE NO D_AMOUNT NO ORABCTAB_INST_ID$ YES ORABCTAB_CHAIN_ID$ YES ORABCTAB_SEQ_NUM$ YES ORABCTAB_CREATION_TIME$ YES ORABCTAB_USER_NUMBER$ YES ORABCTAB_HASH$ YES ORABCTAB_SIGNATURE$ YES ORABCTAB_SIGNATURE_ALG$ YES ORABCTAB_SIGNATURE_CERT$ YES ORABCTAB_SPARE$ YES set colinvisible on SQL> desc ledger_tab1 Name Null? Type ------------------------------------ ----- --------------------------- D_DATE DATE D_AMOUNT NUMBER ORABCTAB_SPARE$ (INVISIBLE) RAW(2000 BYTE) ORABCTAB_USER_NUMBER$ (INVISIBLE) NUMBER ORABCTAB_HASH$ (INVISIBLE) RAW(2000 BYTE) ORABCTAB_SIGNATURE$ (INVISIBLE) RAW(2000 BYTE) ORABCTAB_SIGNATURE_ALG$ (INVISIBLE) NUMBER ORABCTAB_SIGNATURE_CERT$ (INVISIBLE) RAW(16 BYTE) ORABCTAB_SEQ_NUM$ (INVISIBLE) NUMBER ORABCTAB_CHAIN_ID$ (INVISIBLE) NUMBER ORABCTAB_INST_ID$ (INVISIBLE) NUMBER ORABCTAB_CREATION_TIME$ (INVISIBLE) TIMESTAMP(6) WITH TIME ZONE Wenn Zeilen eingefügt werden, werden krypthographische Hashwerte aus den vorangegangenen Blöcken gemeinsam mit den aktuellen Daten gespeichert - ein sogenanntes Chaining wird durchgeführt. Dieser Hashwert ist eine (SHA2-512)-Berechnung aus dem aktuellem Zeileninhalt des Users inklusive hidden Spalten Informationen (ohne Signatur Informationen) und der hidden Hashwert aus der vorangegangenen Zeile. Jede Änderung (Manipulation) der vorangegangenen Daten würden dann dazu führen, dass sich der Hashwert ändert und nicht mehr mit dem gespeicherten Hashwert der aktuellen Daten übereinstimmt. Wie kann man nun mit diesen Tabellen arbeiten? Die Tabellen können natürlich wie immer mit SELECT-Operationen abgefragt werden, keinerlei Änderungen am SQL Code ist erforderlich. Möchte man die Informationen aus den "hidden" Spalten anzeigen, muss man diese explizit in der SELECT-Klausel angeben. Zum Befüllen der Tabelle - hier mit Einzelsatz INSERT - ist wie üblich das Objektprivileg INSERT erforderlich. Operationen wie DROP TABLE, DELETE, MERGE und UPDATE, Änderung an den Spalten, Bulk INSERTs usw. sind nicht möglich. Die genauen Details dazu kann man im Administrator Guide unter Guidelines for Managing Blockchain Tables nachlesen: In folgendem Ausschnitt werden exemplarisch einige SELECT Operationen - auch gemischte - ausgeführt. SQL> SELECT bank, d_date, d_amount FROM ledger_tab1 WHERE rownum=1; BANK D_DATE D_AMOUNT ---------- --------- ---------- DB 17-NOV-19 1000 SQL> SELECT o.order_id, c.name, o.status, o.order_date FROM orders_bl o JOIN ot.customers c USING (customer_id) ORDER_ID NAME STATUS ORDER_DAT ---------- ------------------------------ -------------------- --------- 26 Supervalu Shipped 16-AUG-16 71 Supervalu Shipped 21-FEB-17 96 Supervalu Shipped 14-SEP-16 81 NextEra Energy Shipped 13-DEC-16 74 NextEra Energy Shipped 10-FEB-17 ... SQL> SELECT bank, d_date, d_amount, ORABCTAB_SPARE$,ORABCTAB_USER_NUMBER$, ORABCTAB_HASH$, ORABCTAB_SIGNATURE$, ORABCTAB_SIGNATURE_ALG$, ORABCTAB_SIGNATURE_CERT$, ORABCTAB_SEQ_NUM$, ORABCTAB_CHAIN_ID$, ORABCTAB_INST_ID$, ORABCTAB_CREATION_TIME$ FROM us1.ledger_tab1 WHERE rownum<2; BANK D_DATE D_AMOUNT ---------- --------- ---------- ORABCTAB_SPARE$ ---------------------------------------------------------------------------------------------------- ORABCTAB_USER_NUMBER$ --------------------- ORABCTAB_HASH$ ---------------------------------------------------------------------------------------------------- ORABCTAB_SIGNATURE$ ---------------------------------------------------------------------------------------------------- ORABCTAB_SIGNATURE_ALG$ ORABCTAB_SIGNATURE_CERT$ ORABCTAB_SEQ_NUM$ ORABCTAB_CHAIN_ID$ ----------------------- -------------------------------- ----------------- ------------------ ORABCTAB_INST_ID$ ORABCTAB_CREATION_TIME$ ----------------- --------------------------------------------------------------------------- DB 17-NOV-19 1000 110 739EBB6DFE29DED923318614487C812DD922A13658E00A176ABF19DE8BFFF001DBB8E0270BF54E193B74E2915EBFD1260978 75184DC6CBBB729DEE7D94B1E0FA 1 23 1 25-FEB-20 11.05.47.115998 AM +00:00 Standard SQL-Operationen wie DELETE und UPDATE werden wie alle nicht erlaubten Operationen mit folgender Fehlermeldung abgewiesen. SQL> delete from ledger_tab1 where rownum=1; delete from ledger_tab1 where rownum=1 * ERROR at line 1: ORA-05715: operation not allowed on the blockchain table SQL> update ledger_tab1 set bank='X'; update ledger_tab1 set bank='X' * ERROR at line 1: ORA-05715: operation not allowed on the blockchain table Tabellen bzw. Datenbankschemas, die Blockchain Tabellen besitzen, können erst nach Ablauf der IDLE Time mit DROP-Operationen gelöscht werden. Zeilen ausserhalb der Retention Zeit lassen sich unabhängig davon mit der neuen Funktionalität DBMS_BLOCKCHAIN_TABLE.DELETE_ROWS löschen. Es gibt darüberhinaus auch einige neue Funktionen, die über die PL/SQL Schnittstelle mit neuen Packages zur Verfügung gestellt werden. So kann man beispielsweise das neue Package DBMS_BLOCKCHAIN_TABLE verwenden um Zeilen zu löschen Zeilen zu verifizieren oder eine Signatur hinzuzufügen. Hinzufügen einer Zeilen-Signatur (optional) Eine Signatur zu einer Tabellenzeile hinzuzufügen ist optional. Da dies aber zu mehr Sicherheit führt, soll in den folgenden Abschnitten, die Vorgehensweise exemplarisch demonstriert werden. Die Zeile mit dem Spalteneintrag DB1 (für die Spalte BANK) soll eine Zeilen Signatur erhalten, die dann in den dazu vorgesehenen hidden Spalten gespeichert wird. Diese Spalten sind im Moment noch leer. Insgesamt sind drei Schritte dazu erforderlich. Zuerst wird ein Zertifikat zur Datenbank hinzugefügt, um damit die Signatur der Zeile zu berechnen und zu verifizieren. Dazu wird die neue Funktion DBMS_USER_CERTS.ADD_SIGNATURE verwendet, die eine Zertifikat GUID als Resultat liefert. Mit OPENSSL wird dazu ein digitales Zertifikat example.com.pem zum Testen wie folgt erzeugt. openssl req -x509 -sha256 -nodes -newkey rsa:4096 -keyout example.com.key -days 730 -out example.com.pem Jetzt kann das Zertifikat example.com.pem aus dem logischen Directory BLOCK hinzugefügt werden. set serveroutput on DECLARE file BFILE; buffer BLOB; amount NUMBER := 32767; cert_guid RAW(16); BEGIN file := BFILENAME('BLOCK', 'example.com.pem'); DBMS_LOB.FILEOPEN(file); DBMS_LOB.READ(file, amount, 1, buffer); DBMS_LOB.FILECLOSE(file); DBMS_USER_CERTS.ADD_CERTIFICATE(buffer, cert_guid); DBMS_OUTPUT.PUT_LINE('Certificate GUID = ' || cert_guid); END; / Certificate GUID = 9F7B7815464B7E48E0530200000ACE49 Die Zertifikat GUID lässt sich danach aus der Data Dictionary View DBA_CERTIFICATES wieder auslesen. SQL> SELECT user_name, distinguished_name, certificate_guid FROM dba_certificates; USER_NAME DISTINGUISHED_NAME CERTIFICATE_GUID --------------- --------------------------------------------- -------------------------------- US1 CN=OR,OU=SL,O=OR,L=MUC,ST=BY,C=DE 9F7B7815464B7E48E0530200000ACE49 Jetzt wird der Hash-Wert der Zeile berechnet und die Bytes in die Datei signature1.dat geschrieben. DECLARE row_data BLOB; buffer RAW(4000); inst_id BINARY_INTEGER; chain_id BINARY_INTEGER; sequence_no BINARY_INTEGER; row_len BINARY_INTEGER; l_file UTL_FILE.FILE_TYPE; BEGIN SELECT ORABCTAB_INST_ID$, ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$ INTO inst_id, chain_id, sequence_no FROM US1.ledger_tab1 where bank='DB1'; DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE('US1','LEDGER_TAB1',inst_id, chain_id, sequence_no, 1, row_data); row_len := DBMS_LOB.GETLENGTH(row_data); DBMS_LOB.READ(row_data, row_len, 1, buffer); l_file := UTL_FILE.fopen('BLOCK','signature1.dat','wb', 32767); UTL_FILE.put_raw(l_file, buffer, TRUE); UTL_FILE.fclose(l_file); END; / Die Signatur wird dann außerhalb der Datenbank mit Hilfe von OPENSSL unter Verwendung des Zertifikats berechnet und in Binärformat in der Datei sign1.final gespeichert. openssl dgst -sha512 -sign example.com.key -out sign1.final signature1.dat Danach wird die Signatur sign1.final zu der Zeile (mit Wert DB1 für die Spalte Bank) mit DBMS_BLOCKCHAIN_TABLE.SIGN_ROW eingefügt. DECLARE inst_id binary_integer; chain_id binary_integer; sequence_no binary_integer; signature RAW(2000); cert_guid RAW (16) := HEXTORAW('9F7B7815464B7E48E0530200000ACE49'); file bfile; amount number:=2000; buffer RAW(4000); BEGIN SELECT ORABCTAB_INST_ID$, ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$ INTO inst_id, chain_id, sequence_no FROM us1.ledger_tab1 WHERE bank='DB1'; file := bfilename('BLOCK', 'sign1.final'); DBMS_LOB.FILEOPEN(file); dbms_lob.READ(file, amount, 1, signature); dbms_lob.FILECLOSE(file); DBMS_BLOCKCHAIN_TABLE.SIGN_ROW('US1','LEDGER_TAB1', inst_id, chain_id, sequence_no, NULL, signature, cert_guid, DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512); END; / Selektiert man erneut die Tabellenzeile, findet man die entsprechenden Signatur Informationen in den Spalten ORABCTAB_SIGNATURE$, ORABCTAB_SIGNATURE_ALG$ und ORABCTAB_SIGNATURE_CERT$ vor. SQL> select BANK,D_DATE, D_AMOUNT, ORABCTAB_SPARE$,ORABCTAB_USER_NUMBER$, ORABCTAB_HASH$, ORABCTAB_SIGNATURE$, ORABCTAB_SIGNATURE_ALG$, ORABCTAB_SIGNATURE_CERT$, ORABCTAB_SEQ_NUM$, ORABCTAB_CHAIN_ID$, ORABCTAB_INST_ID$, ORABCTAB_CREATION_TIME$ from us1.ledger_tab1 where rownum<2; BANK -------------------------------------------------------------------------------- D_DATE D_AMOUNT --------- ---------- ORABCTAB_SPARE$ -------------------------------------------------------------------------------- ORABCTAB_USER_NUMBER$ --------------------- ORABCTAB_HASH$ -------------------------------------------------------------------------------- ORABCTAB_SIGNATURE$ -------------------------------------------------------------------------------- ORABCTAB_SIGNATURE_ALG$ ORABCTAB_SIGNATURE_CERT$ ORABCTAB_SEQ_NUM$ ----------------------- -------------------------------- ----------------- ORABCTAB_CHAIN_ID$ ORABCTAB_INST_ID$ ------------------ ----------------- ORABCTAB_CREATION_TIME$ --------------------------------------------------------------------------- DB 17-NOV-19 1000 110 739EBB6DFE29DED923318614487C812DD922A13658E00A176ABF19DE8BFFF001DBB8E0270BF54E19 3B74E2915EBFD126097875184DC6CBBB729DEE7D94B1E0FA 5E17CB909442868A5B4A999E18A78FF695C366E6B1815DE26DDAB5151AE87E962475C3316D634668 A7ADD2F99B17686A86A7ECD64F9AED0FE03900AE295A0366C958212A5DBD0520FE186A646495CC90 B98C844BAB233667C4950502B12024D055158A209A4E6FBB4FDEEF5AE5E7E73587171BEB7B5F4079 5C7E92E0D6C0C57 3 9F7B7815464B7E48E0530200000ACE49 1 23 1 25-FEB-20 11.05.47.115998 AM +00:00 Fazit Die Eigenschaften des neuen Tabellentyps Blockchain wie INSERT-Only und Manipulationssicherheit führen dazu, dass ganz neue Anwendungen in der Datenbank gespeichert werden können (siehe auch Converged Database). Zum Erzeugen der Tabelle ist nur eine Erweiterung des CREATE TABLE Kommandos notwendig. SQL und PL/SQL Kommandos können wie gewohnt gemeinsam mit Standard Oracle Technologien wie Partitionierung, RAC usw. verwendet werden. Falls Einschränkungen existieren, sind diese im Administration Guide zu finden. Zusätzliche Funktionen wie Löschen abgelaufener Zeilen, Hinzufügen von Zertifikaten, Validieren von Zeilen usw. werden über die neuen PL/SQL Packages DBMS_BLOCKCHAIN_TABLE und DBMS_USER_CERTS bereitgestellt.   Der gesamte Beitrag findet sich auch unter folgendem Link "Blockchain inside Oracle Database 20c" Weitere Links und Informationen Blogposting "Native Blockchain Tables Extend Oracle Database’s Multi-model Converged Architecture" von Mark Rakhmilevich Handbuch Database Administrator's Guide "Managing Blockchain Tables" Handbuch DBMS_BLOCKCHAIN_TABLE  Blogposting "Oracle Database 20c Preview Mode"  

Nur das Einfügen von Daten in Tabellen (also INSERT-Only) zu erlauben und keine weiteren Manipulationen zuzulassen sind wichtige Anforderungen - auch an die Oracle Datenbank. In Oracle Database 20c ist...

Cloud

Oracle Database 20c Preview Mode

Seit kurzer Zeit  (Stand Februar 2020) steht Oracle Database 20c in der Oracle Cloud im sogenannten Preview Mode zur Verfügung.  Was ist eigentlich der 20c Preview Mode in der Oracle Cloud und wie kann man damit arbeiten? Oracle Cloud Datenbanksysteme (genauer: Oracle Cloud Database Service Virtual Machine) im Preview Mode werden wie alle anderen Datenbanksysteme in der Cloud bereitgestellt/erzeugt. Im Selektor im Dialogfeld "Create DB System" für virtuelle Maschinen wählt man dabei einfach die Version "20c (Preview)" aus (siehe Screenshot).    Wichtiger Hinweis: Beim Anlegen muss "Logical Volume Manager" als Storage Management Software gewählt werden, ansonsten erhält man in der Liste der Database Versionen nicht Möglichkeit "20c (Preview)" auszuwählen. Wichtig zu wissen ist, dass 20c im Preview Mode als Single Instanz (Non RAC) verwendbar ist; Data Guard oder ASM sind nicht im Preview Mode verfügbar. Möchte man mehr Details zum Preview Mode erfahren, kann man im Handbuch im Abschnitt Bare Metal and Virtual Machine DB Systems mehr dazu finden. Ein Preview Mode ist also für alle diejenigen interessant, die ihre Anwendungen vor der allgemeinen Verfügbarkeit der Software testen möchten oder sich vorab mit den Core Features des neuen Release vertraut machen wollen. Das Preview Release bleibt dabei solange verfügbar bis man selbst das System beendet. Parallel zum Preview Mode wurde auch das Handbuch "What's New" zur Verfügung gestellt. Dort sind die neuen Features kategorisiert  in Sparten wie Big Data and Data Warehousing Solutions, Security Solutions, Performance and High-Availability Options, Tools and Languages und Database Upgrade and Utilities. Möchte man etwas zu den Grundlagen erfahren, ist auch der Abschnitt Changes in Oracle Database Release 20c des SQL Reference Guide empfehlenswert. Beispielsweise lassen sich nun JSON Daten in einem speziellen SQL-Datentyp JSON Datentyp speichern. Der Datentyp repräsentiert dabei JSON unter Verwendung eines nativen Binärformats, der ein optimiertes Format für schnelle Abfragen und Aktualisierungen sowohl im Oracle-Datenbankserver als auch in Oracle-Datenbank-Clients liefert. Ausserdem gibt es sogenannte SQL Makros für skalare Ausdrücke, die sich innerhalb der WHERE und HAVING-Klausel vom SELECT verwenden lassen. Ein ausführliches (Code) Beispiel findet sich im Blogeintrag Oracle 20c SQL Macros: a scalar example to join agility and performance von Franck Pachot. Ein weiteres Feature ist der neue Tabellentyp Blockchain, der eine manipulationssichere Persistenzoption direkt in der Oracle Datenbank bietet. Einen guten Einblick zu Blockchain Tabellen erhält man übrigens im Oracle Blog "Native Blockchain Tables Extend Oracle Database’s Multi-model Converged Architecture". Dies sind nur einige Beispiele für neue Features. Einen Überblick über 20c Features kann man mittlerweile auch über die Database Features and Licensing App  unter https://apex.oracle.com/database-features/ bekommen.   Übrigens Informationen zur produktiven Verfügbarkeit des Oracle Datenbank Release finden sich wie immer in der My Oracle Support Note "Release Schedule of Current Database Releases (Doc ID 742060.1)".            

Seit kurzer Zeit  (Stand Februar 2020) steht Oracle Database 20c in der Oracle Cloud im sogenannten Preview Mode zur Verfügung.  Was ist eigentlich der 20c Preview Mode in der Oracle Cloud und wie kann...

Autonomous Database

Database Vault für Autonomous Database

Ab sofort steht eine weitere Sicherheitsmaßnahme für die Autonomous Database zur Verfügung. Neben den bisher eingesetzten Sicherheitstechnologien wie Database Service Firewall, Transparent Data Encryption, Lockdown Profiles, Unified Auditing und 2-Faktor Authentifizierung (Zertifikat + Passwort) besteht nun die Möglichkeit, Database Vault zum Schutz gegen hochprivilegierte Datenbankbenutzer zu verwenden. Gerichtet ist diese Schutzmaßnahme gegen die Möglichkeit eines unbefugten Zugriffs durch Infrastruktur-Administratoren auf Ihre Daten. Um den störungsfreien Betrieb einer Autonomous Database zu gewährleisten, müssen jedoch hochprivilegierte Zugriffe auf Ihre Autonomous Database möglich sein. Diese Zugriffe werden über sogenannte – Common User – durchgeführt. Common User können auf jede Autonomous Databases (PDB) innerhalb einer Container-Datenbank zugreifen. Hierunter fallen unter anderem die Datenbankbenutzer SYS und SYSTEM. Im Kontext von Autonomous Database wird ein weiterer hochprivilegierter Common User mit dem Namen - C##CLOUD$SERVICE - verwendet. Dieser Benutzer wird lediglich über eine API angesprochen und dient als Schnittstelle zwischen der Cloud Control Plane und der Autonomous Database. Der Betrieb selbst wird durch zahlreiche technische und organisatorische Maßnahmen gesichert, welche auch fortlaufend durch externe Audits überprüft und überwacht werden. Mit Database Vault können Sie nun in Ihrer Autonomous Database eine zusätzliche Schutzebene einziehen um zu verhindern, dass trotz aller umgesetzten Sicherheitsmaßnahmen kein unbefugter Zugriff auf Ihre Daten, zum Beispiel durch einen dieser Common User, erfolgen kann. Das Ganze lässt sich mit wenigen Kommandos umsetzen, wie das folgende Beispiel zeigt. Die Voraussetzung ist eine laufende Autonomous Database, zum Beispiel auch eine AlwaysFree. Diese kann eine Transaction Processing oder Data Warehouse Datenbank auf einer Shared oder Dedicated Umgebung. Dedicated ist bei AlwaysFree nicht möglich. Zusätzlich wird das Schema (HR_DATA) benötigt, welches wir vor dem Zugriff durch Infrastruktur-Administratoren - wie zum Beispiel SYS und SYSTEM - schützen möchten. Nach dem Anlegen einer Autonomous Database melden wir uns mit dem Database Administrator ADMIN  an. Bei Bedarf vorab das Schema HR_DATA inklusive Objekte anlegen.  Zuerst überprüfen wir den Status von Database Vault. set linesize window col STATUS format a20 col PDB_NAME format a25 col DV_FEATURE format a20 SELECT B.NAME as PDB_NAME, A.NAME as DV_FEATURE, A.STATUS FROM CDB_DV_STATUS A, V$CONTAINERS B where A.CON_ID=B.CON_ID; PDB_NAME                                       DV_FEATURE                     STATUS -------------------------                            --------------------                      -------------------- RPLKBZT5W7YLXAQ_ADBDV        DV_APP_PROTECTION      NOT CONFIGURED RPLKBZT5W7YLXAQ_ADBDV        DV_CONFIGURE_STATUS  FALSE RPLKBZT5W7YLXAQ_ADBDV        DV_ENABLE_STATUS         FALSE In dieser Abfrage sehen wir, dass Database Vault weder konfiguriert noch aktiviert ist. Dies ist auch der Auslieferungszustand der Autonomous Database. Um Database Vault zu konfigurieren und zu aktivieren, muss ein PL/SQL Package namens DBMS_CLOUD_MACADM verwendet werden. Dies unterscheidet sich zwischen einer Nicht- Autonomous Database von einer Autonomous Database. Bei einer Nicht- Autonomous Database wird die PL/SQL Prozedur DVSYS.CONFIGURE_DV dazu verwendet. Diese Prozedur kann in einer Autonomous Database nicht verwendet werden, da sie nur durch einem Datenbankbenutzer mit SYSDBA Rechten ausgeführt werden kann und dieses Recht in einer Autonomous Database dem ADMIN Benutzer nicht zur Verfügung steht. Deswegen verwenden wir jetzt das PL/SQL Package DBMS_CLOUD_MACADM. In diesem PL/SQL Package befinden sich die Prozeduren CONFIGURE_DATABASE_VAULT, ENABLE_DATABASE_VAULT und DISABLE_DATABASE_VAULT. Zum Konfigurieren von Database Vault verwenden wir die Prozedur CONFIGURE_DATABASE_VAULT. Diese Prozedur erfordert die Angabe von zwei Datenbankbenutzern, welche im Nachgang die Rollen des Database Vault Owners und des Database Account Managers übernehmen. Diese Benutzer müssen vorab erstellt werden. Wir verwenden hier DVO als Database Vault Owner und DVAM als Database Account Manager. Der Benutzer ADMIN kann dafür nicht verwendet werden. GRANT CREATE SESSION TO dvo IDENTIFIED BY "Feuerwehr#112"; Grant succeeded. GRANT CREATE SESSION TO dvam IDENTIFIED BY "DiePolizei#110"; Grant succeeded. Jetzt kann Database Vault in der Autonomous Database konfiguriert werden. Dies muss mit dem Benutzer ADMIN durchgeführt werden. EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT(dvowner_uname     => 'DVO', dvacctmgr_uname   => 'DVAM'); PL/SQL procedure successfully completed. Nachdem die Konfiguration erfolgreich durchgeführt wurde, können wir Database Vault aktivieren. Dies wird mit der Prozedur ENABLE_DATABASE_VAULT gemacht. Der Prozeduraufruf erfordert keine weiteren Angaben. EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT; PL/SQL procedure successfully completed. Nachdem auch diese Prozedur erfolgreich durchgeführt wurde, muss die Autonomous Database über die Cloud Konsole neu gestartet werden. Sobald die Datenbank wieder verfügbar ist, melden wir uns mit dem ADMIN Benutzer an und überprüfen den Database Vault Status erneut. set linesize window col STATUS format a20 col PDB_NAME format a25 col DV_FEATURE format a20 SELECT B.NAME as PDB_NAME, A.NAME as DV_FEATURE, A.STATUS FROM CDB_DV_STATUS A, V$CONTAINERS B where A.CON_ID=B.CON_ID; PDB_NAME                                      DV_FEATURE                       STATUS -------------------------                            --------------------                       -------------------- RPLKBZT5W7YLXAQ_ADBDV        DV_APP_PROTECTION       NOT CONFIGURED RPLKBZT5W7YLXAQ_ADBDV        DV_CONFIGURE_STATUS  TRUE RPLKBZT5W7YLXAQ_ADBDV        DV_ENABLE_STATUS         TRUE Wie hier zu sehen ist, ist Database Vault nun in der Autonomous Database aktiviert und einsatzbereit. Das Verwalten der Database Vault Sicherheitsrichtlinien kann nun über Oracle Cloud Control 13.4 durchgeführt werden oder mittels des PL/SQL Package DVSYS.DBMS_MACADM über SQL*Plus durchgeführt werden. Weitere Informationen über das PL/SQL Package DVSYS.DBMS_MACADM finden Sie hier. Im folgendem Beispiel werden wir das Schema HR_DATA, welches vorab angelegt wurde, vor dem Zugriff durch SYS beziehungsweise SYSTEM schützen. Als Erstes überprüfen wir, ob ein Benutzer mit DBA Rechten auf die Daten zugreifen kann. Da wir SYS und SYSTEM nicht verwenden können, führen wir diesen Test mit dem ADMIN Benutzer durch. Die Wirkung ist dabei dieselbe, da der ADMIN Benutzer wie SYS und SYSTEM auch über ein SYSTEM Privileg – SELECT ANY TABLE – zugreift.  select count(*) from hr_data.demo_hr_employees;  COUNT(*)  -----------           62 Jetzt legen wir einen Database Vault REALM mit dem Namen HR_DATA an. exec DVSYS.DBMS_MACADM.CREATE_REALM(realm_name => 'HR_DATA', description => 'PII Data', enabled => 'Y', audit_options => '1'); PL/SQL procedure successfully completed. Wurde das erfolgreich durchgeführt, beschreiben wir welche Objekte mittels dieses Realms geschützt werden sollen. Im Beispiel werden alle Objekte innerhalb des Schemas HR_DATA geschützt. exec DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'HR_DATA', object_owner => 'HR_DATA', object_name => '%', object_type => '%'); PL/SQL procedure successfully completed. Damit sind wir auch im Prinzip bereits fertig. Von jetzt an, kann kein Datenbankbenutzer mittels eines System-Privileges mehr auf Objekte innerhalb des Schemas HR_DATA zugreifen.           select count(*) from hr_data.demo_hr_employees; ERROR at line 1: ORA-01031: insufficient privileges In diesem Beispiel wurde gezeigt, wie einfach sich Daten vor dem Zugriff hochprivilegierter Datenbankbenutzer in der Autonomous Database schützen lassen. Alle Eigenschaften von Database Vault stehen kostenfrei in der Autonomous Database zur Verfügung.   Um den Schütz des Schemas wieder zu entfernen einfach folgenden Befehl verwenden. exec DVSYS.DBMS_MACADM.DELETE_REALM(realm_name => 'HR_DATA'); Um Database Vault zu deaktivieren einfach diesen Befehl als ADMIN beziehungsweise DVO ausführen.           exec DBMS_CLOUD_MACADM.DISABLE_DATABASE_VAULT; Die Autonomous Database durchstarten und alles ist wieder wie vorher. Database Vault kann aber wesentlich mehr. Wenn Sie zum Beispiel die Vergabe von Rollen schützen wollen, müssen Sie einfach einen weiteren Realm anlegen und dort entsprechende Rollen bekannte geben. Kleiner Hinweis: Alle Rollen gehören dem SYS Benutzer.  Weitere Informationen Die Dokumentation und gute Beispiele dazu finden Sie im Oracle Database Vault Administrator's Guide. Weitere Informationen zum Thema Autonomous Database und Database Vault finden Sie hier: Using Oracle Database Vault with Autonomous Database Lizenzen Für die Verwendung von Database Vault in der Autonomous Database ist keine zusätzliche Lizenz notwendig. Genauso wie bei den Oracle Database Cloud Services ab Enterprise Edition - High Performance   Viel Erfolg beim Testen.  

Ab sofort steht eine weitere Sicherheitsmaßnahme für die Autonomous Database zur Verfügung. Neben den bisher eingesetzten Sicherheitstechnologien wie Database Service Firewall, Transparent Data...

Cloud

Oracle Cloud Infrastructure Shell - Das Browser-Terminal

Seit kurzem ist die Oracle Cloud Infrastructure Cloud Shell verfügbar. Der folgende Artikel gibt Anwendungsbeispiele und Hintergrundinformationen zur Cloud Shell. Anhand von drei einfachen Beispielen wird das schnelle und unkomplizierte Arbeiten mit der Cloud Shell verdeutlicht. Was ist die Cloud Shell? Die Oracle Cloud Infrastructure (OCI) Shell ist ein browser-basierendes Terminal-Fenster, das Zugriff auf eine Linux-Shell gewährt. Die Shell wird unten im OCI-Fenster angezeigt und man kann trotz geöffneter Shell in der OCI-Konsole navigieren.  Vorteil an dieser Stelle: Man bekommt eine Shell in der man als aktueller Benutzer eingeloggt ist und auf die OCI-Umgebung zugreifen kann. Dieser Benutzer besitzt die gleichen Rechte und Policies wie der Benutzer, mit dem man sich im Browserfenster angemeldet hat. Zentrale Administrative Tätigkeiten können so auf der Shell durchgeführt werden, ohne eine aufwendige Konfiguration/Port-Freischaltung bei Desktop-Terminals.  Hinweis: Die Cloud-Shell wird immer gegen die Region ausgeführt, in der die Cloud-Shell gestartet worden ist. Bei einem Wechsel zwischen den Regionen, muss die Cloud-Shell beendet und neu gestartet werden, wenn man auch mit der Shell in die neue Region wechseln möchte. Limits und Kosten Die Basis-Service-Limits der Cloud Shell liegen bei einem Pay-as-you-Go Account bei 50 Benutzern und einer Gesamtnutzung von 240 Stunden. Bei Monthly Flex liegen die Limits bei 75 Benutzern und 400 Stunden Nutzung/Monat. D.h. innerhalb dieser Limits entstehen keine weiteren Kosten.  Wichtig! Die Cloud Shell ist in den Always-Free-Accounts nicht vorhanden! Hintergrund ist die Nutzung zusätzlicher Compute-Ressourcen, die durch die Cloud Shell Instanz entstehen. Daher ist die Cloud Shell nur den Bezahl-Accounts vorbehalten.  Implementierung Die Cloud Shell ist als Docker-Image implementiert. Wenn Sie die Cloud Shell im Browser-Fenster aktivieren, wird das Docker-Image gestartet und steht nach kurzer Zeit zur Verfügung. Das Docker-Image hat ein 5GB großes Volume im Home-Verzeichnis. Wenn Skripte/Dateien etc. im Home-Verzeichnis gespeichert werden, sind diese auch nach Beenden beim nächsten Start verfügbar.   Das Docker-Image liegt nicht innerhalb des Benutzer-Tenants, d.h. die Tenant-Limits etc. werden durch die Nutzung der Cloud-Shell nicht beeinflusst. Bevor eine Gruppe von Benutzern auf die Cloud Shell zugreifen kann, muss die entsprechende Policy in der OCI angelegt werden. Die Policy lautet:  allow group <GRUPPEN-NAME> to use cloud-shell in tenancy Das Time-Out der Cloud Shell liegt bei 20 Minuten Inaktivität und das maximale Session-Time-Out liegt bei 24 Stunden. Dies sollte man berücksichtigen, bevor zum Beispiel Jobs angelegt werden und man sich anschließend wundert, warum diese nicht ausgeführt wurden. Tools Die Cloud Shell unterstützt folgende Tools: OCI CLI Git Java Python (2 und 3) SQL*Plus kubectl helm maven gradle terraform Dies bedeutet, es kann alles aus der Cloud Shell gemacht werden, was auch aus einem Terminal-Fenster bzw. aus einer Development-Instanz innerhalb der OCI gemacht werden kann.  Beispiele:  Hochfahren und Anhalten von einer/mehreren OCI Ressourcen (Datenbanken, Compute-Instanzen, etc.) Ausführen von Terraform-Skripten in dem OCI-Tenant Aufrufen von Datenbank-Verbindungen und Ausführen von SQL*Plus Befehlen/Skripten Ausführen von Python-Skripte, Java-Programmen, etc. Steuern der Kubernetes-Cluster mittels kubectl Erstellen und Bereitstellen von Maven-Projekten Bereitstellen von Builds aus einem Git-Repository uvm. Verbindungen Mit der Cloud Shell kann auf das Internet zugreifen Der umgekehrte Zugriff - vom Internet auf die Cloud Shell - ist allerdings nicht möglich. Die Cloud Shell besitzt keine privaten Zugriffrechte auf den Tenant: Private Ressourcen innerhalb des Tenants sind damit nicht mit der Cloud Shell bedienbar. D.h die privaten Ressourcen innerhalb der OCI bleiben privat und können mit der Cloud Shell nicht "umgangen" werden.  Beispiel für die Anwendung der Cloud Shell 1.) Öffnen der Cloud Shell Wenn ausreichend Rechte vorhanden sind, kann man durch Klicken auf das Cloud Shell Icon oben rechts die Shell öffnen.  Bild: Öffnen der Cloud Shell innerhalb des Browser-Fensters. Das Öffnen kann ein paar Sekunden dauern, da bei längerer Benutzung die Docker-Instanz neu gestartet werden muss. Hat man die Cloud Shell vor kurzem gestartet, geht es viel schneller. Das Cloud Shell Fenster kann man maximieren und minimieren, Schrift-art und -Größe und den Hintergrund verändern.  2.) Abfragen des aktuellen Tenants Um herauszufinden wie der aktuelle Tenant bezeichnet wird, an dem man angemeldet ist, reicht es ohne weitere Konfiguration die OCI CLI zu verwenden. Mit dem Befehl: oci os ns get bekommt man den aktuellen Tenant angezeigt.  Bild: Abfrage des aktuellen Tenants.  3.) Verbindung zu einer Datenbank mittels SQL*Plus Für diese Verbindung wird der bereits installierte Instant-Client verwendet. Um es etwas einfacher zu machen erzeugen wir eine tnsnames.ora Datei und legen diese im Home-Verzeichnis-Pfad des aktuellen Benutzers ab. Anschließend geben wir den Pfad mittels export TNS_ADMIN=/Pfad_zur_tnsnames_ora bekannt. Wichtig an dieser Stelle ist, dass die tnsnames.ora im Verzeichnis-Pfad des Benutzers liegt, da sie nur an dieser Stelle persistent abgelegt werden kann. Anschließend rufen wir mit sqlplus /nolog und einem connect username/password@NetServiceName die Verbindung auf. Natürlich können nur Verbindungen geöffnet werden, die auch über das Internet erreichbar sind, da die Cloud Shell nicht auf OCI Ressourcen zugreifen kann, die in einem privaten Netzwerk liegen.  Bild: Aufruf einer SQL*Plus Verbindung  Fazit Die OCI Cloud Shell ist ein sehr effizientes Hilfsmittel, wenn es um die zeilenorientierte Administration der OCI Ressourcen geht. Die Vielzahl von vorhandenen Tools ermöglicht es Administratoren und Entwicklern eine Reihe von Aufgaben schnell und effizient durchzuführen. Man könnte sich natürlich auch alle Tools lokal auf den eigenen Rechner installieren oder den Oracle Cloud Infrastruktur Development Client aus dem OCI Marketplace installieren. Dies bedeutet jedoch ein größerer Aufwand und setzt voraus, dass man entweder immer Zugriff auf den Client hat oder eine zusätzliche Ressource in dem Cloud Tenant betreibt, der natürlich Kosten verursacht.  Weiterführendes Material: Cloud Shell Handbuch     

Seit kurzem ist die Oracle Cloud Infrastructure Cloud Shell verfügbar. Der folgende Artikel gibt Anwendungsbeispiele und Hintergrundinformationen zur Cloud Shell. Anhand von drei einfachen Beispielen...

Autonomous Database

DBMS_CLOUD REST APIs

Möchte man programmatisch - beispielsweise mit PL/SQL - im Oracle Cloud Object Storage zum Beispiel einen Bucket anlegen, löschen oder Compartments auflisten, kann man dies jetzt auch mit der REST-API Erweiterung des Package DBMS_CLOUD bewerkstelligen. Dabei liefert die DBMS_CLOUD REST-API nicht nur eine Schnittstelle für die Oracle Cloud Infrastruktur (OCI), sondern auch für Amazon Web Services oder Azure Cloud. Übrigens für diejenigen, die APEX nutzen: Mit APEX Version 19.2 gibt es eine Integration für Oracle Cloud Infrastructure Web Credentials, um mit Oracle Cloud Object Storage zu arbeiten. Möchte man mehr dazu erfahren, kann man dazu folgenden Artikel von Adrian Png im Oracle Magazine finden: Better File Storage in Oracle Cloud. Dort wird ausführlich beschrieben, wie man einfach mit den REST-APIs des Oracle Cloud Object Storage in APEX interagieren kann. Aber zurück zu DBMS_CLOUD - wozu hat man DBMS_CLOUD eigentlich bisher verwendet? Die Packages DBMS_CLOUD und DBMS_CLOUD_ADMIN bieten wichtige Schnittstellen und Erweiterungen für das Arbeiten mit Autonomous Database. Mit wachsender Funktionalität der Autonomous Database werden auch diese Packages erweitert. Im Handbuch im Abschnitt Autonomous Database Supplied Package Reference kann man Details zu den einzelnen Funktionen und Prozeduren nachlesen. Folgende Liste zeigt einige Beispiele für Funktionen und Prozeduren von DBMS_CLOUD: - COPY_DATA zum Kopieren von Dateien aus dem Object Storage in Autonomous Database  - CREATE_CREDENTIAL, DROP_CREDENTIAL, UPDATE_CREDENTIAL   zum Speichern, Löschen und Ändern von Cloud Object Storage Credentials - CREATE_EXTERNAL_TABLE (CREATE_ETXERNAL_PART_TABLE, CREATE_HYBRID_PART_TABLE)   zum Erzeugen von External Tables (auch partitioniert bzw. hybrid) für Dateien in der Cloud - VALIDATE_EXTERNAL_TABLE (VALIDATE_EXTERNAL_PART_TABLE, VALIDATE_HYBRID_PART_TABLE)   Validieren der External Table - DELETE_FILE, LIST_FILES Löscht bzw. listet Dateien in dem angegebenen Verzeichnis - DELETE_OBJECT,  LIST_OBJECTS Löschen und Auflisten von Objekten aus dem Cloud Objekt Storage - GET_OBJECT Listet Objekte aus dem Object Storage und kopiert sie in Autonomous Database - PUT_OBJECT Kopiert eine Datei aus der Autonomous Database in den Cloud Object Storage  Wie kann man nun programmatisch mit DBMS_CLOUD einen Bucket im Objektstore anlegen? Das Ganze funktioniert in zwei Schritten: Authentifizierung gegenüber dem Cloud Objekt Store mit DBMS_CLOUD.CREATE_CREDENTIALS Verwendung der REST API mit DBMS_CLOUD.SEND_REQUEST  Da es sich hierbei um "native" Oracle Cloud Infrastructure Operationen handelt, müssen die Credentials OCI spezifische Parameter wie User OCID, Tenancy OCID, privater Key und Fingerprint enthalten. Folgendes Beispiel zeigt eine Verwendung. Hinweis: Bitte unbedingt beachten, dass beim Kopieren und Einfügen des Private Keys keine zusätzlichen Umbrüche oder Sonderzeichen entstehen. -- Löschen des Credentials execute dbms_cloud.drop_credential('CRED_USEXTEND'); -- Anlegen des Credentials begin DBMS_CLOUD.CREATE_CREDENTIAL ( credential_name => 'CRED_USEXTEND',  user_ocid       => 'ocid1.user.oc1..aaaaaaaajjo6ll3nsokpwdxxxxxxxxxxxxxxxxxxrijhbjxq', tenancy_ocid    => 'ocid1.tenancy.oc1..aaaaaaaap7sgxxxxxxxxxx343ztr6ydz6fscx6nuka', private_key     => '-----BEGIN RSA PRIVATE KEMIIEpAIBAAKCA..-----END RSA PRIVATE KEY-----', fingerprint    => '9c:3f:d2:5b:49:1b:20:92:4e:4a:5d:cc:d7:d4:c1:66'); end; / Hinweis: Informationen zur Generierung eines Private/Public Key Paares und Fingerprints erhält man im Abschnitt How to Generate an API Signing Key aus der OCI Dokumentation. Nach der Erzeugung kann man die Credentials wie üblich mit ALTER DATABASE in der Datenbank bekanntgeben. alter database property set default_credential = 'ADMIN.CRED_USEXTEND'; Alle Credentials lassen sich mit DBA_CREDENTIALS auflisten. select credential_name, enabled, comments from dba_credentials; CREDENTIAL_NAME ENABLED COMMENTS --------------- ------- ------------------------------------------------------------------------- CREDENTIAL_US1  TRUE    {"comments":"Created via DBMS_CLOUD.create_credential"} CREDENTIAL_US2  TRUE    {"comments":"Created via DBMS_CLOUD.create_credential"} CRED_US         TRUE    {"comments":"Created native credential via DBMS_CLOUD.create_credential"} CRED_USEXTEND   TRUE    {"comments":"Created native credential via DBMS_CLOUD.create_credential"} Im nächsten Schritt wird die Funktion SEND_REQUEST des Package DBMS_CLOUD mit folgenden Parametern verwendet. DBMS_CLOUD.SEND_REQUEST ( credential_name IN VARCHAR2, -- hier CRED_USEXTEND uri             IN VARCHAR2, -- passende URI, die zum Request passt method          IN VARCHAR2, -- HTTP Methode GET, PUT, POST, HEAD, DELETE mit DBMS_CLOUD headers         IN CLOB DEFAULT NULL, -- Headers der Cloud native API in JSON Format body            IN BLOB DEFAULT NULL) -- Body des PUT oder POST Request in JSON Format RETURN DBMS_CLOUD_TYPES.resp; Die Informationen zur verwendeten URI findet man in der OCI Dokumentation im Kapitel APIs and Endpoints - in unserem Fall unter Objectstorage im Abschnitt CREATE BUCKET. Es handelt sich; um den Endpoint https://objectstorage.eu-frankfurt-1.oraclecloud.com und die Methode POST /n/{namespaceName}/b/. Der Body erfordert die Übergabe des neuen Bucket Namens 'name' und der Compartment ID 'compartmentId'. Somit sieht das Anlegen eines Buckets mit Namen TESTUS folgendermaßen aus: DECLARE   resp DBMS_CLOUD_TYPES.resp; BEGIN   resp := DBMS_CLOUD.send_request(   credential_name => 'CRED_USEXTEND',   uri             => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/',   method          => DBMS_CLOUD.METHOD_POST,   body            => UTL_RAW.cast_to_raw(             JSON_OBJECT('name' value 'TESTUS',                       'compartmentId' value 'ocid1.compartment.oc1..aaaaaaaaz77ryqv6xwxxxxa')));  END; / Den Erfolg kann man danach einfach in der graphischen Cloud Konsole im Bereich Object Storage überprüfen. Um den Bucket wieder zu löschen, reicht folgender Aufruf aus. Die DELETE Methode erfordert außer dem Credentialnamen und der URI keine weiteren Informationen. Das folgende Beispiel löscht den Bucket TESTUS wieder. DECLARE   resp DBMS_CLOUD_TYPES.resp; BEGIN   resp := DBMS_CLOUD.send_request(   credential_name => 'CRED_USEXTEND',   uri    => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/TESTUS',   method => DBMS_CLOUD.METHOD_DELETE); END; / Auch hier kann man das Ergebnis natürlich wieder in der Cloud Konsole überprüfen. Im letzten Beispiel listen wir alle Compartments auf. Dazu wird die GET Methode mit DBMS_CLOUD.METHOD_GET verwendet. Die Endpoints, URIs und die entsprechenden Parameter findet sich in der Identity and Access Management Service API. Der Endpoint lautet hier https://identity.eu-frankfurt-1.oraclecloud.com/ und die Methode ist GET /20160918/compartments/. Sie erfordert die Information über die Root Compartment ID. Die Header Informationen bestehen aus einem JSON Objekt mit Wert 'list-compartments' für die 'opc-request-id'. Der vollständige Aufruf sieht dann folgendermaßen aus. SET SERVEROUTPUT ON DECLARE   DBMS_CLOUD_TYPES.resp;   root_compartment_ocid VARCHAR2(512) := 'ocid1.tenancy.oc1..aaaaaaaap7sg74i4ibhxz7ymmvxxxxxka'; BEGIN   resp := DBMS_CLOUD.send_request(   credential_name => 'CRED_USEXTEND',   uri     => 'https://identity.eu-frankfurt-1.oraclecloud.com/20160918/compartments?compartmentId='||   root_compartment_ocid,   method  => DBMS_CLOUD.METHOD_GET,   headers => JSON_OBJECT('opc-request-id' value 'list-compartments'));   dbms_output.put_line('Body: ' || DBMS_CLOUD.get_response_text(resp)); END; / Die Ausgabe ist im JSON Format und gibt ausführliche Informationen über die Inhalte der Compartments. Ein Ausschnitt aus dem Ergebnis sieht dann so aus: body: [ {   "id" : "ocid1.compartment.oc1..aaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxhfv2a",   "compartmentId" : "ocid1.tenancxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxa",   "name" : "AutonomousPlatform",   "description" : "AutonomousPlatform ",   "timeCreated" : "2019-07-24T11:17:24.350Z",   "freeformTags" : { },   "definedTags" : {     "Operation" : {       "CreationDate" : "2019-07-24T11:17:24.289Z",       "CreatedBy" : "ocid1.saml2idp.oc1..aaaaaaaaq7g335nqsjimza3xpib67lribjdzadesisqjaywawvkww3tlpuvq/marcus.schroeder@oracle.com"     }   },   "lifecycleState" : "ACTIVE" }, {   "id" : "ocid1.compartment.oc1..aaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxg5qkrdbix56q",   "compartmentId" : "ocid1.tenancxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxa",   "name" : "BI",   "description" : "Compartment for the BI Group",   "timeCreated" : "2018-12-07T11:16:32.630Z",   "freeformTags" : { },   "definedTags" : { },   "lifecycleState" : "ACTIVE" }, { ... Detaillierte Informationen und Beispiele zur DBMS_CLOUD REST API finden sich entweder im Oracle Autonomous Data Warehouse Handbuch im Kapitel DBMS_CLOUD REST APIs oder im Oracle Autonomous Transaction Processing Handbuch im Kapitel DBMS_CLOUD REST APIs.

Möchte man programmatisch - beispielsweise mit PL/SQL - im Oracle Cloud Object Storage zum Beispiel einen Bucket anlegen, löschen oder Compartments auflisten, kann man dies jetzt auch mit der REST-API...

Advisor

Oracle Database Advisors - damit geht es leichter

Fragen zum Tunen einer Oracle Datenbank oder generell zum Testen von Oracle Datenbank Workloads werden immer wieder gestellt. Häufig kann dabei das Oracle Database Advisor Framework gute Unterstützung geben. Nehmen wir beispielsweise die Überprüfung von Oracle Datenbank Performance über AWR (Automatic Workload Repository) Reports. Eine gute Hilfestellung bei der Interpretation liefert Automatic Database Diagnostic Monitor (kurz ADDM), dessen Informationen am Ende des Reports zu finden sind. Was sind eigentlich Oracle Database Advisors? Oracle Database Advisors analysieren die Datenbank in den unterschiedlichsten Bereichen, nicht nur beim Tunen der Datenbank. So können Advisors auch bei der Einschätzung von Zugriffstrukuren, Security Konfigurationen, Segment Komprimierung usw. helfen. Sie liefern damit einerseits die Grundlage für Automatisierungen innerhalb von Oracle Datenbanken beispielsweise für Autonomous Datenbank und können auf der anderen Seite dem DBA und Datenbank Entwickler eine gute Unterstützung beim Arbeiten mit der Oracle Datenbank bieten. Woraus bestehen Oracle Database Advisors? Advisors sind in der Regel über unterschiedliche Methoden nutzbar - über graphischen Werkzeuge wie der Enterprise Manager oder SQL Developer, skriptgesteuert über PL/SQL Packages, Initialisierungsparameter oder auch über die entsprechenden V$-Views. Die meisten von ihnen stehen schon bei der Installation zur Verfügung und können sofort genutzt werden. Andere kann man separat von My Oracle Support laden oder sind über die Cloud Console aktivierbar. Im Unterschied zu Alerts sind Advisors Ressource intensiver, da ihre Analyse und Lösungsvorschläge einen größeren Detailgrad aufweisen. Wichtig zu wissen ist, wie die Advisors zu verwenden sind und welche Ratschläge man erwarten kann. Mit der Weiterentwicklung der Oracle Datenbank und des Datenbank Offerings werden immer wieder neue Advisors zur Verfügung gestellt. Möchte man einen aktuellen Überblick über die gängigsten Advisors (Stand Januar 2020)  bekommen, kann folgende Tabelle dazu verwenden. Sie sind geordnet nach Anwendungsbereichen und geben erste Hinweise zur Verwendung. Ein kurze Beschreibung dieser Advisors mit zugehörigen aktuellen Handbuch- und My Oracle Support Note Einträgen findet man in unserem aktuellen Blogposting.  

Fragen zum Tunen einer Oracle Datenbank oder generell zum Testen von Oracle Datenbank Workloads werden immer wieder gestellt. Häufig kann dabei das Oracle Database Advisor Framework gute...

JSON

GeoJSON: Oracle Spatial im JSON Format

Kann man Oracle Spatial Geometrien in einem offenen leichter lesbaren Format wie JSON ausgeben? Das Format GeoJSON gibt die Antwort darauf. Für alle diejeniegen, die das Format nicht kennen: Eine Definition und Beschreibung findet sich im  RFC 7946 der Geographic JSON Working Group vom August 2016. Dort heißt es:   "GeoJSON ist ein Geodatenaustauschformat, das auf der JavaScriptObject-Notation (JSON) basiert. Es definiert verschiedene Typen von JSON-Objekten und die Art und Weise, in der sie kombiniert werden, um Daten über geografische Merkmale, ihre Eigenschaften und ihre räumliche Ausdehnung darzustellen. Das Koordinatenbezugssystem für alle GeoJSON-Koordinaten ist ein geographisches Koordinatensystem, unter Verwendung von WGS 84 [WGS84] und mit Längen- und Breiteneinheiten von Dezimalgraden." Ein Beispiel im Format GeoJSON sieht dann folgendermassen aus.  { "type": "FeatureCollection", "features": [ { "type": "Feature", "geometry": {"type": "Point", "coordinates": [102.0, 0.5]}, "properties": {"prop0": "value0"} }, { "type": "Feature", "geometry": { "type": "LineString", "coordinates": [ [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0] ] }, "properties": { "prop0": "value0", "prop1": 0.0 } } Wie man erkennen kann, gibt es ein vorgegebenes Format, das mit einem Eintrag zum GeoJSON Typ (hier FeatureCollection) beginnt. Die Informationen zu den Geometrien tauchen dann nach dem Eintrag geometry type (im obigem Code in Zeile 4) auf. Dabei können übrigens neun verschiedene Eingaben wie "Point", "MultiPoint", "LineString", "MultiLineString", "Polygon", "MultiPolygon" und "GeometryCollection" gemacht werden. (Bitte unbedingt die Schreibweise beachten!) Mehr dazu kann man im RFC Dokument nachlesen.   Was bedeutet das für Oracle JSON und die Oracle Spatial Geometrien? Beide Techniken können ganz einfach kombiniert bzw. in das unterschiedliche Format "umgewandelt" werden: also von Spatial (also SDO_GEOMETRY) nach GeoJSON bzw. umgekehrt. Besitzt man Daten im GeoJSON Format lässt sich darauf auch ein Spatial Index anlegen. Dazu wird die SQL/JSON Funktion JSON_VALUE verwendet. Auch Abfragen mit SQL/JSON Funktionen wie JSON_TABLE, JSON_VALUE, JSON_ARRAYAGG usw. können wie gewohnt durchgeführt werden. Einen guten Überblick über diese Möglichkeiten - sogar mit Beispielcode - findet man in der Präsentation "GeoJSON and the Oracle Database" von Albert Godfrind.  Besonders interessant für Oracle Spatial Nutzer ist sicherlich die Möglichkeit aus gespeicherten Geometrien, das Format GeoJSON zu generieren und somit zur Speicherung ein leicht lesbares offenes Format zur Verfügung zu haben. Dazu wurde in Oracle Database 12.2 der Datentyp SDO_GEOMTRY um die Methode GET_GEOJSON erweitert. Folgendes Beispiel zeigt eine Verwendung: Die Tabelle DUS_RIDE_AND_BIKE_STATIONEN speichert dabei die Punkt-Geometrien in der Spalte LOCATION. SQL> select name, c.location.get_geojson() as json from dus_ride_and_bike_stationen c; NAME -------------------------------------------------------------------------------- JSON -------------------------------------------------------------------------------- Bike+Ride Aachener Straße - Aachener Platz { "type": "Point", "coordinates": [6.768617, 51.195687] } Bike+Ride Am Seestern U { "type": "Point", "coordinates": [6.73752, 51.241592] } Bike+Ride An den Kämpen { "type": "Point", "coordinates": [6.775968, 51.326056] } Eine zweite Möglichkeit besteht darin die Funktion TO_GEOJSON des Utility Package SDO_UTIL zu verwenden. Folgendes Beispiel zeigt auch hier die einfache Verwendung. SQL> select name, sdo_util.to_geojson(location) as json from dus_ride_and_bike_stationen; NAME -------------------------------------------------------------------------------- JSON -------------------------------------------------------------------------------- Bike+Ride Aachener Straße - Aachener Platz { "type": "Point", "coordinates": [6.768617, 51.195687] } Bike+Ride Am Seestern U { "type": "Point", "coordinates": [6.73752, 51.241592] } Bike+Ride An den Kämpen { "type": "Point", "coordinates": [6.775968, 51.326056] } So lassen sich auf einfache Art und Weise gespeicherte Spatial Geometrien in JSON Format ausgeben.  Ausführlicher und vollständig beschrieben ist das verwendete Beispiel in folgendem Tipp. Neugierig geworden, dann einfach einmal ausprobieren!

Kann man Oracle Spatial Geometrien in einem offenen leichter lesbaren Format wie JSON ausgeben? Das Format GeoJSON gibt die Antwort darauf. Für alle diejeniegen, die das Format nicht kennen: Eine...

Advisor

Gewusst wie - Oracle Database Advisor Pre-Check für Autonomous Database

Immer mehr Oracle Database User entscheiden sich Database Workloads in der Oracle Autonomous Database zu betreiben. Was ist dabei zu beachten? Welche Überprüfungen kann man vornehmen um sich gegebenenfalls auf die Migration nach Autonomous Database vorzubereiten? Die Antwort liefert der sogenannte Autonomous Database Schema Advisor, der seit Mitte letzten Jahres zur Verfügung steht und im Dezember noch einmal erweitert wurde. Damit können Datenbanken der Version 10.2 bis einschliesslich 19c untersucht werden - On-Premises oder in der Cloud. Das Ergebnis ist ein Text Report, der folgende Informationen ausgibt:  Liste der Objekte, die nicht migriert werden, weil die Autonomous Database bestimmte Einschränkungen vorsieht   Liste der Objekte, die mit Änderungen migriert werden können (Diese Änderungen werden dann automatisch während des Imports oder bei der Ausführung von DDL Kommandos zur Objekterstellung vorgenommen.) Bestimmte Best-Practice-Empfehlungen und -Anleitungen Woraus besteht der Schema Advisor? Wie lässt er sich installieren und welches Resultat kann man erwarten?  Der ADB (kurz auch für Autonomous Database) Schema Advisor besteht aus einigen Hilfstabellen und dem PL/SQL Package ADB_ADVISOR, die über ein kurzes SQL Skript installiert werden. Folgendes einfache Vorgehen beschreibt die Installation: Download des Installationsskripts "install_adb_advisor.sql"  von My Oracle Support mit DOC ID 2462677.1. SYS Connection in SQL*PLUS und Start des Skripts in einer PDB. Wichtig zu wissen ist auch: Der ADB Schema Advisor ist ein Oracle Datenbank Advisor, der wie die meisten Oracle Advisors "nur" Empfehlungen gibt und nichts an der bestehenden Datenbank verändert. Folgendes Beispiel zeigt einen Ausschnitt aus einer 19c Installation mit einer Pluggable Database mit Namen US1. Unter anderem wird ein neuer Datenbank User (hier ADB) erzeugt, der dann der Owner des Package ADB_ADVISOR ist.   SQL> sho user USER is "SYS" SQL> sho pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          3 US1                            READ WRITE NO SQL> start install_adb_advisor.sql ADB <Passwort> SQL> -------------------------------------------------------- SQL> -- Create ADB Advisor schema owner SQL> -------------------------------------------------------- SQL> CREATE USER &&1 identified by "&&2"; old   1: CREATE USER &&1 identified by "&&2" new   1: CREATE USER adb identified by "Passwort" User created. SQL> SQL> -------------------------------------------------------- SQL> -- Grant privs to schema onwer SQL> -------------------------------------------------------- SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL> SQL> GRANT CREATE TABLE, CREATE SESSION, CREATE PROCEDURE TO &&1; old   1: GRANT CREATE TABLE, CREATE SESSION, CREATE PROCEDURE TO &&1 new   1: GRANT CREATE TABLE, CREATE SESSION, CREATE PROCEDURE TO adb Grant succeeded. SQL> GRANT SELECT ON V_$VERSION TO &&1; ... Möchte man den Advisor de-installieren, ist ein einfaches DROP USER CASCADE ausreichend. Das Dokument "ADB_Advisor_intro.pdf", das ebenfalls zum Download zur Verfügung, beschreibt die Installation und die Verwendung.  Das neuinstallierte Package ADB_ADVISOR besteht nun aus den drei Prozeduren REPORT, SETMAXROWS und SETMAXSCHEMAS. SQL> desc ADB.ADB_ADVISOR PROCEDURE REPORT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  SCHEMAS                        VARCHAR2                IN  ADB_TYPE                       VARCHAR2                IN     DEFAULT PROCEDURE SETMAXROWS  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  ROWS                           NUMBER                  IN PROCEDURE SETMAXSCHEMAS  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  SCHEMAS                        NUMBER                  IN Nun kann man loslegen! Dazu ist eine Connection mit dem Owner des Package (hier ADB) erforderlich. Die Prozedur REPORT, die die zwei Übergabeparameter SCHEMAS und ADB_TYPE besitzt, führt dabei die Analyse durch. Für den Parameter SCHEMAS kann man bis zu 30 Schemas einzeln angeben oder das Schlüsselwort 'ALL' verwenden, wenn man alle Schemas mit einem Aufruf überprüfen möchte.  Da es vier verschiedene Arten von Autonomous Database gibt, kann der Parameter ADB_TYPE folgende Werte haben:  'ATP' für die autonome Transaktionsverarbeitung (shared) 'ADW' für Autonomes Data Warehouse (shared) 'ATPD' für die autonome Transaktionsverarbeitung (dediziert) 'ADWD' für Autonomes Data Warehouse (dediziert) Ein Aufruf, um das Schema SH und SPATIALUS auf eine Migration nach ADW (shared) zu überprüfen, sieht dann beispielsweise folgendermassen aus: execute adb_advisor.report(SCHEMAS=>'SH, SPATIALUS', ADB_TYPE=>'ADW'); Eine SQL*Plus Formatierung zu Beginn sorgt für eine gut lesbare Ausgabe.  SQL> SET SERVEROUTPUT ON FORMAT WRAPPED SQL> SET LINESIZE WINDOW execute adb_advisor.report(SCHEMAS=>'SH, SPATIALUS', ADB_TYPE=>'ADW'); Hinweis: Mit der Prozedur SETMAXROWS kann man vor der Generierung des Reports die Anzahl der Zeilen erhöhen, wenn ein Eintrag [Output Truncated] im Bericht auftaucht. Folgendes Beispiel zeigt eine Verwendung für alle Schemas (ALL) in Verbindung mit der Autonomous Database vom Typ ATP.  Die Ausführung erfolgt dann mit folgenden Parametern und die Ausgabe startet dann mit folgendem Abschnitt: SQL> execute ADB_ADVISOR.REPORT(SCHEMAS=>'ALL', ADB_TYPE=>'ATP'); ========================================================================================== == ATP SCHEMA MIGRATION REPORT FOR AUTO_ADV,PDBUSER,OT,SPATIALUS,DEV1,SCOTT,DATASAFE_ADMIN,TEST ========================================================================================== ADB Advisor Version   : 19.3.0.0.1 Instance Name         : DB19c Database Name         : DB19C Host Name             :  by19c Database Version      : 19.0.0.0.0 Pluggable Database    : US1 Schemas Analyzed      : AUTO_ADV,PDBUSER,OT,SPATIALUS,DEV1,SCOTT,DATASAFE_ADMIN,TEST Analyzing for         : Autonomous Transaction Processing (Serverless) Report Start date/time: 02-JAN-2020 15:45 ... Der Report besteht aus vier Teilen. In Teil 1 wird zuerst eine Zusammenfassung aller Objekte gegeben. In Teil 2 werden die Objekte aufgelistet, die nicht migriert werden können. Im vorliegenden Fall handelt es sich um Objekte, die im Schema SYS liegen und daher nicht für eine Migration in Frage kommen können. ... In Teil 3 werden die Änderungen, die an den Objekten während der Migration durchgeführt werden, aufgelistet. Im letzten Teil werden noch einige allgemeine Hinweise zur Migration, zu Initialisierungsparametern, Tablespaces, User Attributen usw. gegeben. Der folgende Screenshot zeigt einen Ausschnitt.   Der Schema Advisor ist schnell installiert und kann auf einfache und schnelle Weise Oracle Datenbanken analysieren. Ein Vorwissen ist dazu nicht erforderlich. Möchte man mehr über eventuelle Einschränkungen erfahren, kann man je nach Autonomous Database Typ folgende Kapitel im Handbuch verwenden: Oracle Autonomous Data Warehouse (Shared) Oracle Autonomous Transaction Processing (Shared) Oracle Autonomous Data Warehouse (Dedicated) Oracle Autonomous Transaction Processing (Dedicated)

Immer mehr Oracle Database User entscheiden sich Database Workloads in der Oracle Autonomous Database zu betreiben. Was ist dabei zu beachten? Welche Überprüfungen kann man vornehmen um sich...

SQL, PL/SQL und mehr

Oracle Database und Temporal Validity

Temporal Validity (wörtlich übersetzt auch zeitliche Gültigkeit) ist eine Standardfunktion der Oracle Datenbank, die in jeder Edition der Datenbank und in jedem Offering - ob Cloud oder On-Premises Installationen - zur Verfügung steht. Vielen Oracle Datenbank Usern und Entwicklern ist diese Funktion allerdings nicht bekannt, obwohl sie seit 12.1 in der Oracle Datenbank enthalten ist. Was genau versteht man nun unter Temporal Validity in der Oracle Datenbank? Temporal Validity ermöglicht es, einer Tabelle eine gültige Zeitdimension zuzuordnen und Daten in Abhängigkeit von ihrer zeitlichen Gültigkeit als valide einzustufen. Die gültige Zeitdimension wird dabei durch das Beginn- und Endedatum oder den Zeitstempel des Zeitraums bestimmt. Einsatzszenarien könnten beispielsweise das Gültigkeitsdatum der Deckung für eine Versicherungspolice, Kündigungsdaten eines Mitarbeiters in einer Personalanwendung, oder auch das Gültigkeitsdatum einer Adressänderung für einen Kunden oder Klienten sein. Quasi jede Anwendung, bei der es wichtig ist zu wissen, wann bestimmte Daten gültig wurden (aus der Sicht der Anwendung) und wann sie ungültig wurden. Ein weiteres Beispiel wäre Datenkorrekturen, bei denen falsche Daten aufbewahrt werden müssen und mit dem Zeitraum gekennzeichnet werden, in dem sie als gültig erachtet wurden, und bei der die richtigen Daten als aktuell gültig angesehen werden müssen. Temporal Validity Abfragen werden typischerweise im Zusammenhang mit der Oracle Flashback-Technologie verwendet. So gibt es AS OF und VERSIONS BETWEEN Abfragen, die den gültigen Zeitraum angeben wie zum Beispiel wie folgt: SQL> select first_name, to_char(valid_time_start,'dd-mon-yyyy') "Start", to_char(valid_time_end,'dd-mon-yyyy') "End" from ot.emp_valid VERSIONS PERIOD FOR VALID_TIME BETWEEN to_date('01-SEP-1995') and to_date('01-SEP-1996') order by 2; Darüberhinaus gibt es die Möglichkeit ohne Änderung am SQL Statement mit DBMS_FLASHBACK_ARCHIVE auf Session Ebene die Sichtbarkeit von Tabellendaten zu einem bestimmten Zeitpunkt anzugeben. Die Implementierung ist dabei ganz einfach. Die Datenbanktabelle muss beim CREATE TABLE oder ALTER TABLE mit einem speziellen Attribut ausgestattet werden. Damit werden zusätzliche Spalten hinzugefügt, damit man sich das entsprechende Anfangs- und Endedatum einfügen lässt. Die Spalten sind dabei auf "invisible" (unsichtbar) gesetzt. Danach kann man sofort mit den Abfragen loslegen.  Was zur Aktivierung zu tun ist und wie die Abfragen aussehen, wird in folgendem Tipp an einem kleinen Beispiel demonstriert.  

Temporal Validity (wörtlich übersetzt auch zeitliche Gültigkeit) ist eine Standardfunktion der Oracle Datenbank, die in jeder Edition der Datenbank und in jedem Offering - ob Cloud oder On-Premises...

JSON

Daten in JSON Format: wie geht das?

Wie kann man Daten (gespeichert in der Oracle Datenbank) in JSON Format umwandeln? Mittlerweile gibt es dazu in der Oracle Datenbank eine Reihe von Möglichkeiten. Je nachdem wie man sich die Ausgabe vorstellt, kann man beispielsweise in einer SELECT Anweisung die Funktionen LISTAGG, CONCAT oder UNPIVOT verwenden. Bei einer komplexen JSON Ausgabe bieten sich allerdings die speziellen SQL/JSON Funktionen an, die seit Oracle 12.2 in der Datenbank enthalten sind und genau für solche Anforderungen entwickelt worden sind. Im ersten Beispiel werden die Werte aus den Spalteninhalten mithilfe der Funktion JSON_OBJECT generiert. Die Ausgabe wird in Form eines JSON Objekts augegeben. Wie immer werden die Tabellen EMP und DEPT verwendet. SQL> select JSON_OBJECT(e.*, d.*) ausgabe           from scott.dept d join scott.emp e on d.deptno=e.deptno; AUSGABE ------------------------------------------------------------------------------------------------------------ {"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09T00:00:00","SAL":2450,"COMM": null,"DEPTNO":10,"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"} {"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"1981-11-17T00:00:00","SAL":5000,"COMM" :null,"DEPTNO":10,"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"} {"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1300,"COMM":n ull,"DEPTNO":10,"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"} ... Natürlich kann man das Ganze auch genauer spezifizieren. In folgendem Beispiel soll nur die EMPNO als EMP_ID und der zugehörige Department Name ausgegeben werden.  SQL> select JSON_OBJECT('emp_id' is e.empno, 'department' is d.dname) ausgabe            from scott.dept d join scott.emp e on d.deptno=e.deptno; AUSGABE -------------------------------------------------------------------------------- {"emp_id":7782,"department":"ACCOUNTING"} {"emp_id":7839,"department":"ACCOUNTING"} {"emp_id":7934,"department":"ACCOUNTING"} {"emp_id":7566,"department":"RESEARCH"} {"emp_id":7902,"department":"RESEARCH"} {"emp_id":7876,"department":"RESEARCH"} {"emp_id":7369,"department":"RESEARCH"} {"emp_id":7788,"department":"RESEARCH"} {"emp_id":7521,"department":"SALES"} {"emp_id":7844,"department":"SALES"} {"emp_id":7499,"department":"SALES"} {"emp_id":7900,"department":"SALES"} {"emp_id":7698,"department":"SALES"} {"emp_id":7654,"department":"SALES"} 14 rows selected. Die vollständige Syntax zu JSON_OBJECT findet sich hier.   Im nächsten Beispiel sollen die Angestellten aus der EMP Tabelle als JSON Aggregat zur Verfügung gestellt werden. Hierzu wird die Funktion JSON_OBJECTAGG verwendet. SQL> select d.deptno,           JSON_OBJECTAGG('emp_name' is ename) ename_per_deptno           from scott.dept d join scott.emp e on d.deptno=e.deptno group by d.deptno;  DEPTNO ------------ ENAME_PER_DEPTNO ------------------------------------------------------------------------------------------------------------         10 {"emp_name":"CLARK","emp_name":"MILLER","emp_name":"KING"}         20 {"emp_name":"SMITH","emp_name":"FORD","emp_name":"ADAMS","emp_name":"SCOTT","emp_name":"JONES"}         30 {"emp_name":"ALLEN","emp_name":"JAMES","emp_name":"TURNER","emp_name":"BLAKE","emp_name":"MARTIN","emp_name" :"WARD"} Der Link zur detaillierten Syntax findet sich hier. Im letzten Beispiel wird mithilfe von JSON_ARRAYAGG ein JSON Array erzeugt. Pro Abteilung sollen die entsprechenden Angestellten in einem JSON Array ausgegeben werden. SQL> set pagesize 1000 SQL> select JSN_OBJECT('dname' is d1.dname,                                              'empinfo' is                             (select JSON_ARRAYAGG(json_object('ename' is ename,'sal' is sal, 'comm' is comm absent on null) absent on null)                              from scott.dept d join scott.emp e on d.deptno=e.deptno) absent on null) as ct1           from scott.dept d1; CT1 ------------------------------------------------------------------------------------------------------------ {"dname":"ACCOUNTING","empinfo":[{"ename":"SMITH","sal":800},{"ename":"ALLEN","sal":1600,"comm":300},{"ename ":"WARD","sal":1250,"comm":500},{"ename":"JONES","sal":2975},{"ename":"MARTIN","sal":1250,"comm":1400},{"ena me":"BLAKE","sal":2850},{"ename":"CLARK","sal":2450},{"ename":"SCOTT","sal":3000},{"ename":"KING","sal":5000 },{"ename":"TURNER","sal":1500,"comm":0},{"ename":"ADAMS","sal":1100},{"ename":"JAMES","sal":950},{"ename":" FORD","sal":3000},{"ename":"MILLER","sal":1300}]} {"dname":"RESEARCH","empinfo":[{"ename":"SMITH","sal":800},{"ename":"ALLEN","sal":1600,"comm":300},{"ename": "WARD","sal":1250,"comm":500},{"ename":"JONES","sal":2975},{"ename":"MARTIN","sal":1250,"comm":1400},{"ename ":"BLAKE","sal":2850},{"ename":"CLARK","sal":2450},{"ename":"SCOTT","sal":3000},{"ename":"KING","sal":5000}, {"ename":"TURNER","sal":1500,"comm":0},{"ename":"ADAMS","sal":1100},{"ename":"JAMES","sal":950},{"ename":"FO RD","sal":3000},{"ename":"MILLER","sal":1300}]} ... Der Link zur detaillierten JSON_ARRAYAGG Syntax findet sich hier.  Hat man keinen großen Ansprüche bzgl der JSON Ausgabe, könnte man auch ganz einfach das Werkzeug Oracle SQL Developer Command Line (kurz SQLcl). SQLcl vereinigt dabei die Vorteile eines Linemode Werkzeugs mit den Features von SQL Developer und kann genau wie der SQL Developer separat von OTN geladen und installiert werden. Folgendes Beispiel zeigt die Syntax des SQLcl Kommandos SET SQLFORMAT, mit dem man ganz einfach eine JSON Ausgabe erzielen kann.   [oracle@by19c ~]$ sql /nolog SQLcl: Release 19.1 Production on Tue Nov 26 14:33:33 2019 Copyright (c) 1982, 2019, Oracle.  All rights reserved. SQL> help set sqlformat SET SQLFORMAT   SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default} SQL> select * from scott.emp; {"results":[{"columns":[{"name":"EMPNO","type":"NUMBER"},{"name":"ENAME","type":"VARCHAR2"},{"name":"JOB","type":"VARCHAR2"},{"name":"MGR","type":"NUMBER"},{"name":"HIREDATE","type":"DATE"},{"name":"SAL","type":"NUMBER"},{"name":"COMM","type":"NUMBER"},{"name":"DEPTNO","type":"NUMBER"}],"items": [ {"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"17-DEC-80","sal":800,"deptno":20} ,{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"20-FEB-81","sal":1600,"comm":300,"deptno":30} ,{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"22-FEB-81","sal":1250,"comm":500,"deptno":30} ,{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"02-APR-81","sal":2975,"deptno":20} ,{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"28-SEP-81","sal":1250,"comm":1400,"deptno":30} ,{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"01-MAY-81","sal":2850,"deptno":30}... 14 rows selected.   Liegt das SQL im XML Format vor, gibt es auch seit einiger Zeit die Funktion JSONTOXML, die XML in JSON umwandeln kann. SQL>  select XMLTOJSON(xmldoc) from scott.x_tab where rownum=1; XMLTOJSON(XMLDOC) ---------------------------------------------------------------------------------------------------------------------------------------- {"Abteilungen":{"Abteilung":[{"@nr":1,"Deptname":"Sales Consulting","Mitarbeiter":[{"Addresse":{"City":"Berl in","Street":"Riesstr"},"Age":30,"Name":"Ulrike Mayer"},{"Addresse":{"City":"Berlin","Street":"Riesstr"},"Ag e":40,"Name":"Martin Scholz"}]},{"@nr":2,"Deptname":"Consulting","Mitarbeiter":[{"Addresse":{"City":"Muenche n","Street":"Riesstr"},"Age":30,"Name":"Martina Schwinn"},{"Addresse":{"City":"Muenchen","Street":"Riesstr"} ,"Age":25,"Name":"Peter Schulte"}]},{"@nr":3,"Deptname":"Support","Mitarbeiter":{"Addresse":{"City":"Berlin" ,"Street":"Riesstr"},"Age":22,"Name":"Peter schulte"}}]}} Übrigens der umgekehrte Weg kann mit XMLTOJSON errreicht werden :) Weitere Informationen zu den SQL/JSON Funktionen kann man im SQL Language Guide oder im JSON Developer's Guide nachlesen.

Wie kann man Daten (gespeichert in der Oracle Datenbank) in JSON Format umwandeln? Mittlerweile gibt es dazu in der Oracle Datenbank eine Reihe von Möglichkeiten. Je nachdem wie man sich die Ausgabe...

Autonomous Database

Oracle Cloud Always Free Services, was bedeutet das im Detail?

Was ist der Oracle Cloud Always Free Services? Auf der Oracle Open World 2019 wurden die Oracle Cloud Always Free Services vorgestellt, dieser Artikel soll verdeutlichen, was in diesem Angebot im Detail enthalten ist. Grundsätzlich bedeutet es, dass in jedem Oracle Cloud Infrastructure Account unabhängig ob es sich hierbei um ein Free-Trial, Monthly Commit, Bring-your-own-License oder Pay-as-you-go handelt, immer eine Reihe von freien Services vorhanden sind. Frei bedeutet ohne zeitliche Begrenzung, andere Cloud-Anbieter bieten auch freie Umgebungen an, diese werden allerdings oft nach Ablauf eines Jahres kostenpflichtig. Folgende Services sind frei (Anzahl und Größe) 1x Autonomous Data Warehouse (1 OCPU/Core + 8 GB RAM + 0,02 TB Storage) 1x Autonomous Transaction Processing (1 OCPU/Core + 0,02 TB Storage) 2 x Compute (1/8 OCPU/Core + 1 GB RAM) inklusive 1 Public IP Adresse und 480 Mbps Netzwerkbandbreite  OS: Oracle Linux, Canonical Ubuntu Linux oder CentOS Linux Block Volume (100 GB) Object Storage (10 GiB) Archive Storage (10 GiB) Load Balancing (10 Mbps) Monitoring Notifications Outbound Data Transfer Mehr Details gibt es unter folgenden Link. Der Oracle Cloud Always Free Services ermöglicht durch diese Zusammenstellung das Hosten einer kleinen Web-Anwendungen ohne laufende Kosten. Der Productsupport ist in diesem Angebot natürlich nicht eingeschlossen. Es besteht jedoch die Möglichkeit den Oracle Cloud Always Free Service jederzeit mit Bezahl-Services anzureichern bzw. zu erweitern.  Wie kann ich die Always Free Services am einfachsten nutzen? Die einfachste Möglichkeit eine Always Free Service Tier einzusetzen, ist einen Free Trial zu beantragen und aus diesem Trial nur die Always Free Services zu nutzen. Beim Anlegen eines Trial Accounts ist normalerweise einen Kreditkarte erforderlich, diese wird aber primär zur eindeutigen Identifizierung verwendet. Unter folgenden Link kann man einen Free Trial beantragen. Wenn ich bereits einen Oracle Cloud Account verwende, benötige ich keine weiteren Schritte um die Always Free Services zu nutzen. Wo finde ich die Always Free Services in meinem Oracle Cloud Infrastructure Account? Die beiden Autonomous Database Services finde ich unter der normalen "Create Autonomous Database" Seite im unteren Bereich, dort ist ein Schalter "Show only Always Free configuration options", wenn ich diesen aktiviere, bekomme ich sofort die Free Autonomous Database Service Optionen angezeigt. Bild 1: Always Free Autonomous Database Services Für die Always Free Compute Services muss man "Create Instance" auswählen und in der korrekten Availablity Domain sein in meinem Fall ist die AD3, anschließend kann man die "" Größe auswählen.  Bild 2: Auswahl der Availaiblity Domain Bild 3: Auswahl der Compute Größe  Für den Oracle Free Load Balancer Service navigiert man auf die "Create Load Balancer" Seite und wählt den freien Micro Load Balancer Services aus. Bild 4: Auswahl des Oracle Free Load Balancer Service Die Größe des Object Storage orientiert sich an der Storage Größe der Compute Shapes, die automatisch mit der vorgegebenen Free Service Größe konfiguriert werden.  Fazit: Der Oracle Cloud Always Free Services ist eine kostenfreie Methode, um kleinere Anwendungen, wie zum Beispiel Web- oder APEX-Anwendungen, dauerhaft in der Oracle Cloud zu betreiben oder bestimmte Service kostenfrei zu testen. Im Gegensatz zu anderen Cloud-Anbietern sind diese Services Always-Free, d.h. ohne zeitliche Begrenzungen. Weitere Informationen: Einstiegs-Seite Dokumentation Erste Schritte:  APEX  SQL Developer und Autonomous Datenbank  

Was ist der Oracle Cloud Always Free Services? Auf der Oracle Open World 2019 wurden die Oracle Cloud Always Free Services vorgestellt, dieser Artikel soll verdeutlichen, was in diesem Angebot im...

Database Management

Flashback in Oracle Database

Immer häufiger werde ich auf das Thema Flashback in der Oracle Datenbank angesprochen. Was gibt es überhaupt für Technologienund wie sind diese implementiert? Oder viel weniger konkret: Was kann ich tun oder kann ich überhaupt etwas tun, wenn ich eine Tabelle aus Versehen gelöscht habe? Wie kann man versehentlich veränderte Daten schnell wiederherstellen? Und wie funktioniert das Zurücksetzen von einzelnen Tabellenzeilen?  Um diese Fragen zu beantworten, stellt die Oracle Datenbank Technologien wie Flashback Query, Flashback Drop, Temporal Validity, Flashback Database, Flashback Transaction, Flashback Data Archive oder Flashback Version Query zur Verfügung. Wie funktionieren diese? Was muss man tun um die einzelnen Technologien zu verwenden? Wie sind diese lizenziert?  Gleich vorab alle Technologien stehen ohne zusätzliche Installation zur Verfügung. Und die für Oracle Datenbankentwickler interessanten Technologien wie Flashback Query, Flashback Data Archive und Flashback Versions Query sind in allen Editionen enthalten. Um mehr über die Funktionsweise ausgewählter Flashback Technologien zu erfahren, habe ich einen alten Tipp aufbereitet und gegen die aktuelle Datenbankversion getestet. In folgendem Beitrag findet man den aktuell überarbeiteten Tipp. Viel Spass beim Testen und Ausprobieren! 

Immer häufiger werde ich auf das Thema Flashback in der Oracle Datenbank angesprochen. Was gibt es überhaupt für Technologienund wie sind diese implementiert? Oder viel weniger konkret: Was kann ich...

Autonomous Database

Python mit Oracle Database: So einfach geht das!

Immer häufiger tritt die Frage auf, kann man Python in Verbindung mit der Oracle Datenbank verwenden. Was muss man dafür tun? Um zu zeigen wie einfach man mit Python und der Oracle Datenbank arbeiten kann, zeige ich in den folgenden Abschnitten die ersten Schritte, die dazu erforderlich sind. In diesem Zusammenhang ein Hinweis und Tipp von mir: Bevor man mit einem komplexen Python Programm eine Lösung implementiert, sollte man vorab prüfen, ob es keine Funktion oder Technologie in der Oracle Datenbank gibt, die das Programmieren stark vereinfacht. Die Oracle Datenbank stellt sehr viele Funktionen zur Verfügung um zum Beispiel JSON oder XML zu verarbeiten, Konvertierungen von XML nach JSON durchzuführen oder gar eine HTTP Adresse auszulesen. Beispiele dazu finden Sie auch in unserer Kategorie SQL und PL/SQL Tipps. Wie startet man nun mit Python? Das Oracle Python Developer Center ist ein guter Einstieg in das Thema. Hier findet man auch ein kurzes 5 minütiges Starter Video um Python für Oracle Database unter Linux zu verwenden.  Folgendes Bild zeigt wie Python mit der Oracle Datenbank verwendet wird. Python liest die .py-Skripte und ruft cx_Oracle-Methoden auf. Der Import bindet dann die cx_Oracle-Schnittstelle ein, die Bestandteil des Oracle-Client ist. Die Client Bibliotheken stellen dann die Verbindung mit der Datenbank her, die sich sowohl in der Cloud als auch On-Premises befinden kann. Wie stelle ich nun in meiner Python Umgebung diese Oracle Schnittstelle zur Verfügung?   3 Schritten sind dazu erforderlich. Am Beispiel Windows schaut die Installation folgendermaßen aus. Installation von Python Version 2.7 oder Version 3.5-3.7  über python.org PS ...> python -V Python 3.7.4 Installation von cx_Oracle (z.B. Version 7.2) unter https://oracle.github.io/python-cx_Oracle/ ....>python -m pip install cx_Oracle Collecting cx_Oracle Downloading https://files.pythonhosted.org/packages/27/d3/95d3db458abd27701afc7... Oracle-7.2.2-cp37-cp37m-win_amd64.whl (187kB)     100% |████████████████████████████████| 194kB 4.4MB/s Installing collected packages: cx-Oracle Successfully installed cx-Oracle-7.2.2 You are using pip version 19.0.3, however version 19.2.3 is available. You should consider upgrading via the 'python -m pip install --upgrade pip' command. Installation von Oracle Instant Client Dazu ist nur ein Download des Basic Package und ein Unzip erforderlich. Und schon kann es losgehen! Bevor wir einen einfachen Connect mit einer Datenbank herstellen, müssen wir sicherstellen dass die Instant Client Libraries in der PATH Variable enthalten ist. In der Powershell sieht das Kommando dann folgendermassen aus. $env:PATH+=";c:\users\uschwinn\instantclient_19_3" Im ersten Beispiel verbinde ich mich mit einer Datenbank on-Premise über einen Easy Connect String. Dazu sind Angaben zu Host, Port und Servicenamen erforderlich. Es wird die Datenbankversion ausgegeben. import cx_Oracle db_connection_string = 'username/passwort@hostname.de.oracle.com:1521/servicename' con = cx_Oracle.connect(db_connection_string) print("Database version:", con.version) con.close() Das Ergebnis sieht dann in meinem Fall folgendermaßen aus: PS ...> python c:/users/uschwinn/downloads/version.py Database version: 19.4.0.0.0 Hinweis: Bitte vergessen Sie nicht die Connection mit close() zu schließen. Möchte man sich mit einer Autonomous Datenbank in der Oracle Cloud verbinden, benötigt man die passende Wallet Datei- im Zip- Format, die einen Zugriff auf die Datenbank erlaubt. Die Zip Datei enthält unter anderem eine tnsnames.ora Datei.  Die tnsnames.ora Datei wird dann über die TNS_ADMIN Variable bekannt gegeben: $env:TNS_ADMIN="C:\data\keys\wallet_dbjun" Dann sieht das Programm version.py folgendermaßen aus: import cx_Oracle db_connection_string = 'username/passwort@servicename' con = cx_Oracle.connect(db_connection_string) print("Database version:", con.version) con.close() PS ...> python c:/users/uschwinn/downloads/version.py Database version: 18.4.0.0.0 Zum Abschluss soll ein kurzes Beispiel demonstrieren, wie man in der Oracle Datenbank eine Tabelle anlegen und eine Zeile einfügen kann. import cx_Oracle # create and populate Oracle objects db_connection_string = 'username/passwort@servicename' con = cx_Oracle.connect(db_connection_string) # create table, if necessary cursor = con.cursor() cursor.execute("""         select count(*)         from user_tables         where table_name = 'TEST'""") count, = cursor.fetchone() if count == 0:     print("Creating table...")     cursor.execute("""             create table TEST (                 i number not null,                 j varchar2(200)             )""") # remove all existing rows and then add a new one print("Removing any existing rows...") cursor.execute("delete from test") print("Adding row to table...") cursor.execute("insert into test values (1, 'test')") con.commit() print("Success!") Möchte man mehr dazu erfahren, kann man die Dokumentation von cx_oracle auf Github https://oracle.github.io/python-cx_Oracle/index.html finden.Tutorials und eine große Anzahl von Sample Skripts sind unter https://github.com/oracle/python-cx_Oracle/tree/master/samples verfügbar.  Viel Spaß beim Ausprobieren!    

Immer häufiger tritt die Frage auf, kann man Python in Verbindung mit der Oracle Datenbank verwenden. Was muss man dafür tun? Um zu zeigen wie einfach man mit Python und der Oracle Datenbank arbeiten...

Autonomous Database

Oracle Application Express (APEX) und Autonomous Database

Oracle APEX gibt es jetzt auch auf (serverless) Autonomous Database - Grund genug das Thema auch in unserem Blog zu thematisieren.   Für alle diejenigen, die sich noch nicht mit APEX beschäftigt haben, und sich fragen, was ist eigentlich Oracle Application Express (kurz APEX) und welche Anwendungsbereiche gibt es, eine kurze Erklärung vorab. APEX, das übrigens seit ca. 2006 zur Verfügung steht und sich bis zur aktuellen Version 19 stetig weiterentwickelt hat, ist eine browserbasierte Entwicklungsumgebung zur Erstellung datenbankzentrischer Webapplikationen. APEX steht automatisch auf allen gängigen Plattformen und in allen Editionen der Oracle Datenbank zur Verfügung und ist somit besonders gut für Entwickler mit SQL und PL/SQL Kenntnissen geeignet. Ein wichtiges Merkmal von APEX ist dabei, dass kein ausführbarer Code erzeugt wird, sondern die Anfragen im Browser direkt in PL/SQL-Aufrufe umgesetzt werden. Dabei werden die Webapplikationen, die in Datenbanktabellen gespeichert sind, in Echtzeit aus Metadaten "ge-rendert".  Was kann man nun mit APEX machen? Mit APEX lassen sich Daten laden, Tabellenkalkulationen importieren, REST-Schnittstellen entwickeln, Datenvisualisierungen erstellen sowie für Desktop-Anwendungen als auch für mobile Geräte, um nur einige Beispiele zu nennen.  Es gibt eine Menge ausgezeichneter Blogeinträge zum Thema und die Community ist groß. Wer mit APEX starten möchte, kann beispielsweise folgende Tutorials verwenden:  https://apex.oracle.com/en/learn/tutorials/ https://apex.oracle.com/en/learn/resources/ Und nun gibt es APEX auch mit Autonomous Database! Was bedeutet dies für den APEX Entwickler und für den DBA, der APEX bereitstellt? "Oracle APEX on Autonomous Database" ist eine installierte, vorkonfigurierte, vollständig verwaltete und gesicherte Umgebung und stellt bis auf wenige Ausnahmen die gleichen Funktionen wie auf On-Premises Installationen zur Verfügung. APEX steht dabei sowohl für ADW (Autonomous Data Warehouse) Instanzen als auch für ATP (Autonomous Transaction Processing) Instanzen zur Verfügung. Hat man also eine (serverless) Autonomous Database - egal welchen Workload Typs - provisioniert, kann man fast sofort beginnen, mit APEX zu entwickeln. Keine weitere Installation oder Konfiguration ist nötig. Alles Wichtige für den Einstieg findet sich im Autonomous Handbuch im Kapitel 7 Creating Applications with Oracle Application Express in Autonomous Database oder auch im Blogeintrag von Joel Kallman The Quick Guide for Getting Started with APEX on Autonomous Database Serverless  Die wenigen Schritte sind auch hier kurz beschrieben: Da die APEX Instanz einer autonomous Datenbank über keine vordefinierten Workspaces für Oracle Application Express verfügt, muss zuerst ein neuer Workspace erstellt werden. Hinweis für alle Neulinge mit APEX: Ein Workspace ist ein Bereich in einer APEX Installation, die mehreren Usern erlaubt, ihre Applikation und Daten in einem gemeinsamen privaten Bereich - dem Workspace - zusammenzufassen. So können sich mehrere APEX User einen Workspace teilen oder auch einem dedizierten Workspace angehören. Datenbankseitig ist der Workspace ein logischer Bereich, der aus Tabellen, Views, Stored Procedures usw. besteht. Ein einzelnes Datenbankschema kann dabei mit einem oder mehreren Workspaces verknüpft sein. Um einen neuen Workspace anzulegen, navigiert man im Cloud Menü zum Bereich Tools. Dann klickt man auf den Button "Oracle Application Express" und gelangt von dort aus in den Bereich "Administration Services". Die Administration einer APEX Instanz erfolgt in der Regel über diese "Administration Services" und nicht über SQL*PLUS oder dem Werkzeug Enterprise Manager.   Nach erfolgreicher Eingabe des Passworts des Users ADMIN, befindet man sich im "Instance Administration" Bereich. Hier lassen sich dann Workspaces anlegen und auch monitoren. Dazu kann man entweder neue User anlegen oder auch den Workspace mit einem bestehenden Schema verbinden. Dieser User ist dann ein Workspace Administrator und kann alle administrativen Tätigkeiten bzgl. des Workspaces durchführen. Und nun kann es schon losgehen: Wir navigieren entweder über die Service Console auf die Application Express Login Site und loggen uns mit dem Workspace und Username/Passwort ein. Am Besten bookmarkt man sich gleich die entsprechende URL. In meinem Fall sieht die URL ungefähr so aus: https://xxxxxx-dbjun.adb.eu-frankfurt-1.oraclecloudapps.com/ords/apex So kann man ganz einfach und schnell in wenigen Minuten und ohne detaillierte Kenntnisse von APEX Daten laden und eine Applikation entwickeln. Damit man erkennen kann, wie einfach das Handling mit APEX ist, zeige ich im Folgenden ein paar interessante Anwendungen.  Wie funktioniert zum Beispiel ein Upload einer CSV Datei in APEX? Man navigiert vom "SQL Workshop" über "Utilities" zum "Data Workshop" Button. Von hier aus lassen sich dann Daten in unterschiedlichen Formaten wie zum Beispiel CSV, XLSX, XML und JSON laden. Generell lassen sich mit dem "Create Application" Wizard, Applikationen entweder auf schon bestehenden Daten oder nach dem Neuladen von Daten erstellen.  Oder man installiert schnell eine der vielen Sample Apps (hier Live Poll) und probiert sie gleich nach der Installation aus. Was steckt eigentlich hinter APEX und wie ist das Zusammenspiel mit (serverless) Autonomous Database? Oracle APEX verwendet eine einfache 3-Tier-Architektur, bei der Anfragen vom Browser über einen Webserver an die Datenbank gesendet werden. Wie man an der URL erkennen kann, wird Oracle REST Data Services (ORDS) verwendet um die Anfragen an die Datenbank zur Bearbeitung zu senden. Innerhalb der Datenbank wird der Request dann von Oracle APEX verarbeitet. Nach Abschluss der Verarbeitung wird das Ergebnis über ORDS an den Browser zurückgegeben. Da wir es mit einer managed Plattform zu tun haben, das bedeutet Konfiguration, Patching, Monitoring und Upgrades von APEX Komponenten wird von Oracle durchgeführt, erübrigt sich eigentlich die Frage nach der APEX Version oder auch nach dem Monitoring von SQL Performance. Trotzdem lassen sich natürlich die Aktivitäten wie SQL Statements wie immer im Activity Monitor in der Autonomous Database Service Konsole monitoren, wie man aus folgendem Screenshot ersehen kann.   Möchte man wissen, welche APEX Version installiert ist, kann man sich wie in allen Tools mit dem Help Button behelfen. APEX steht, wie zu erwarten war, in der aktuellen Version 19.1 zur Verfügung.  Bestehende Applikationen lassen sich natürlich importieren. Beachten sollte man allerdings ein paar Einschränkungen beispielsweise bzgl. Administration Service, Printing Service oder im  Authentication Bereich. Nachlesen kann man diese im Kapitel zu "Restrictions and Limitations for Oracle Application Express with Autonomous Transaction Processing". Möchte man mehr zu APEX wissen, kann man unter der URL https://apex.oracle.com/en/learn/documentation/ das aktuelle Handbuch finden. Für Neulinge eignen sich die zu Beginn des Blogs erwähnten Tutorials / Handson Labs, die in ca 1-1.5 Stunden durchgearbeitet werden können. Folgende Themen stehen aktuell zum Ausprobieren zur Verfügung: Spreadsheet Lab Existing Tables Lab Proof-of-Concept Lab REST Lab Remote Tables Am Besten also einfach ausprobieren! In wenigen Minuten lässt sich damit schnell eine Anwendung auf "Oracle APEX on Autonomous Database" erstellen.   

Oracle APEX gibt es jetzt auch auf (serverless) Autonomous Database - Grund genug das Thema auch in unserem Blog zu thematisieren.  Für alle diejenigen, die sich noch nicht mit APEX beschäftigt haben,...

SQL, PL/SQL und mehr

SQL ganz einfach mit Quick SQL

Ist das SQL Statementschreiben mit Syntaxnachschlagen zu aufwändig? Benötigt man schnell ein paar generierte Testdaten in Datenbanktabellen? Möchte man ein Datenmodell schnell in der Datenbank realisieren? Dann kann vielleicht Quick SQL gute Dienste leisten.  Quick SQL ist eine Packaged Oracle APEX Applikation, die man entweder in Oracle APEX selbst, in Live SQL (siehe https://livesql.oracle.com)  oder aber direkt über die URL https://apex.oracle.com/quicksql/ nutzen kann. Einzige Voraussetzung ist dann ein OTN Account.  Wie funktioniert Quick SQL? Man verwendet sehr einfache Shortcuts und automatisch werden SQL Statements wie CREATE TABLE, CREATE VIEW aber auch CREATE TRIGGER, SELECT, DROP und auch INSERTS mit Zufallsdaten generiert. Das Erlernen der Abkürzungen (Shortcuts) ist dabei ganz einfach: Entweder in der APEX Dokumentation nachschlagen oder oben den Button "Syntax" ausprobieren. Noch einfacher geht es mithilfe der Samples, die an einfachen Beispielen die Funktionsweise demonstrieren. Im linken Fenster erfolgt das Eingeben der Abkürzungen und rechts wird das Ergebnis mit korrekten SQL Statements ausgegeben. Und so sieht das Ganze dann beispielsweise aus: Hier  handelt es sich um das Beispiel "Departments und Employees", das natürlich beliebig weiterverarbeitet werden kann. Kurz zur Erläuterung: Links werden Tabellen- oder Viewnamen plaziert wie DEPARTMENTS und EMP_V, danach folgen eingerückt die Spaltennamen. Die Tabelle EMPLOYEES steht rechts von DEPARTMENTS, um das Master-Detail Verhältnis anzuzeigen. Auf der rechten Seite kann man das Ergebnis erkennen: es werden 2 Tabellen - DEPARTMENTS und EMPLOYEES - angelegt. Automatisch wurden Primary Key Spalten mit Namen ID eingefügt, die mit Informationen aus Triggern befüllt werden. Möchte man dies Verhalten ändern und keine Trigger verwenden, ist dies wie auch andere Grundeinstellungen über den Button "Settings" möglich. Zusätzlich wurde in der Spalte EMPLOYEES der Foreign Key über die Spalte DEPARTMENT_ID angelegt. Die Datentypen für Spalten können mit Abkürzungen wie NUM für NUMBER, VC für VARCHAR2 usw. angegeben werden. NN steht für die NOT NULL Bedingung. Tabellendirektive - also Informationen, die für die Ganze Tabelle gelten -  werden immer mit Slash gekennzeichnet. Das können weitere Statements sein wie INSERT, SELECT oder auch ein Oracle "Rest enabling" sein. In unserem Beispiel handelt es sich um  /INSERT 14 oder /INSERT4. /INSERT 4 bedeutet dann beispielsweise das 4 INSERT Statements mit Zufallsdaten generiert werden. Folgender Screenshot zeigt ein Beispiel für ein INSERT Statement und die CREATE VIEW Definition. Die SQL Skripte lassen sich dann als Datei herunterladen - siehe Button "SAVE" - so dass man diese dann ganz einfach in anderen Tools ablaufen lassen kann. Zum Schluss noch ein Ausschnitt aus dem "Settings" Menüpunkt: Es lohnt sich die Settings vor der ersten Nutzung durchzulesen und gegebenenfalls anzupassen, um beispielsweise automatisch DROP Kommandos oder einen Schemanamen einzufügen oder das Standardverhalten beim Befüllen des Primary Keys über Trigger abzuändern. Und das wären auch schon die wichtigsten Informationen. Weitere Funktionen lassen sich leicht aus den Samples ableiten und schnell erlernen. Also einfach einmal ausprobieren!!             

Ist das SQL Statementschreiben mit Syntaxnachschlagen zu aufwändig? Benötigt man schnell ein paar generierte Testdaten in Datenbanktabellen? Möchte man ein Datenmodell schnell in der Datenbank...

Autonomous Database

Interaktive Karten, Geokodieren und ortsbezogene Analysen direkt in Oracle Autonomous Database mit Oracle Spatial Studio

Erst im Mai hatte ich hier einen Beitrag über „Location Intelligence“ unserer Autonomous Databases veröffentlicht. Aber wie schon von Ulrike Schwinn in Ihrem Post angekündigt, gibt es nun noch mehr mit dem gerade herausgekommenen Oracle Spatial Studio. Dieses ist eine „Self-service“ Anwendung zum einfachen Erstellen von interaktiven Karten und zur unkomplizierten, schnellen Analyse Ihrer ortsbezogenen Daten. Die Anwendung ist für alle diejenigen Anwenderinnen und Anwender gedacht und hilfreich, die keine spezifischen Kenntnisse im Bereich von GIS („Geographic Information System“) und den Oracle Spatial Technologies haben. Die Anwendung steht ganz ohne zusätzliche Kosten allen Nutzerinnen und Nutzern mit Oracle Cloud Subscriptions für die Autonomous Databases (Autonomous Data Warehouse, Autonomous Transactional Processing) bzw. die Oracle Database Services mit Oracle Spatial and Graph (Oracle Database Cloud Service High Performance Edition, Oracle Database Cloud Service Extreme Performance Edition, Oracle Database Exadata Cloud, Oracle Database Exadata Cloud at Customer) zur Verfügung. Auch gültige „on-premises“ Oracle Spatial and Graph Lizenzen berechtigen zur zusätzlichen Nutzung von Oracle Spatial Studio. Wie können ortsbezogene Daten auf Karten visualisiert werden? Ortsbezogene Informationen, die sehr häufig in Anwendungen mit interaktiven Karten zum Einsatz kommen, sind z.B. Adressdaten oder Datensets mit standard-konformen Beschreibungen punkt-, linien- oder flächenhafter Objekte/Abbildungen unserer realen Welt. Um eine typischerweise in Textform vorliegende Adresse auf einer Karte darstellen zu können, bedarf es der Umrechnung dieser in ein ortsbezogenes Objekt. Ein solches Objekt ist attributiv beschrieben durch Geokoordinaten für ein spezifisches Koordinatenbezugssystem („Spatial Reference System“) und kann auf einer Karte wie in Abb. 1 dargestellt werden. Adresse in Textform Umrechnung in Koordinaten (Dezimaldarstellung) Koordinaten-bezugssystem Räumliches Objekt (in der Oracle DB) Alexanderplatz1, 10178 Berlin, DE 13.4129, 52.52115 4326 (WGS84) MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(13.4129, 52.52115, NULL), NULL, NULL)   Abb. 1: Ortsbezogenes Objekt (hier Punkt) auf Karte anzeigen Mit Oracle Spatial Studio können beliebig große Sets an Adressdaten (z.B. aus Excel-Tabellen) in eine Oracle Datenbank geladen und direkt dort geokodiert werden. Die im Hintergrund verwendeten Referenzdaten für die Umrechnung kommen von der Firma HERE und werden der Anwendung über eine Geocoding Service URL zur Nutzung bereitgestellt. Wo finden Sie Oracle Spatial Studio? Die Anwendung können Sie hier in 2 Ausführungen herunterladen: Als Quickstart Kit (.zip), welches Sie unter Windows oder Linux einfach nur auspacken. Als Java EE Enterprise Application Archive Datei (.ear) für das Deployment auf Oracle WebLogic Server. Bitte lesen Sie die jeweilige README Datei zuerst. Dort finden Sie hilfreiche Hinweise, um die Anwendung zu deployen, Anpassungen vorzunehmen bzw. diese zu starten. Beim ersten Start der Anwendung (voreingestellt ist https://localhost:4040/spatialstudio) muß ein DB Repository angegeben werden, welches die Metadaten aufnimmt. Dafür können Sie die Verbindungsdaten einer Oracle DB Instanz Ihrer Wahl angeben. Einstellungen prüfen und ggf. anpassen In Spatial Studio gibt es nur wenige Einstellungen. Prüfen Sie die Anzahl der gleichzeitig zu geokodierenden Adressen („Geocoding Batch Size“). Der Wert ist aktuell auf 500 voreingestellt. Er kann nach Bedarf angepaßt werden, muß aber in jedem Fall größer als 0 sein. Sofern die zu ladenden Daten die Größe von 50 MB überschreiten, ist auch der externe Konfigurationsparameter „dataset_max_size“ anzupassen. Dieser hat den Standardwert 50 und steht in der Datei sgtech_config.json. Einen Speicherort für die Datensets festlegen Um Datensets zu speichern bzw. auf in einer Oracle DB Instanz vorhandene Tabellen zugreifen zu können, müssen zuerst die Verbindungen („Connections“) angelegt in Spatial Studio werden. In Abb. 2 sehen Sie die bereits eingerichtete Standardverbindung (es ist in der verwendeten Testumgebung die gleiche, in welcher auch das Repository liegt) und 2 weitere Verbindungen: Eine davon zeigt auf eine Autonomous Data Warehouse Instanz, die andere auf eine Autonomous Transactional Processing Instanz. Abb. 2: Verbindung einrichten Datensets laden und geokodieren Ist der Ablageort für die Daten über die eingerichteten Verbindungen bekannt, können Sie daran gehen, Datensets hochzuladen. Derzeit unterstützt werden die Formate „Spreadsheet“ (.xlsx), „Shapefile“ (.shp) oder bereits in Oracle DB-Tabellen vorhandene Daten. Die nächsten Schritte (sehen Sie dazu auch die nachfolgenden Abbildungen) zeigen das Hochladen eines Datensets vom OpenData Portal DE, welches Adressen für E-Bike Ladestationen in Wesel bereitstellt. Verwendbare Adressinformationen sind in den ersten beiden Spalten enthalten. Die 1. Spalte zeigt auf den Ort, die 2. Spalte enthält Straße und Hausnummer. Vor dem Hochladen wurden direkt in der .xslx Datei noch manuell Spaltennamen für das spätere Mapping der Spalten eingefügt. Abb. 3: Datenset anlegen Abb. 4: Drag & Drop der Excel Tabelle in den "Spreadsheet" Bereich Abb. 5: Anpassen der Verbindung, des Tabellennamens und der Spaltenbezeichner Abb. 6: Starten der Geokodierung für die geladenen Daten Abb. 7: Mapping der für die Geokodierung verwendeten Spalten Abb. 8: Ergebnisse der Geokodierung überprüfen Das in Abb. 8 angegebene SQL Statement kann in den SQL Developer übertragen und dort ausgeführt werden. Es zeigt die Ergebnisse der Geokodierung detailliert an. Abb. 9: Überprüfung mit Hilfe des SQL Developer Detailinformationen sind u.a. der Matchcode. Dieser gibt wichtige Hinweise auf die Treffgenauigkeit beim Geokodieren. Matchcode 1: Exakter Match Matchcode 3: Alle Adressdetails bis auf die Hausnummer konnten zugeordnet werden. Matchcode 4: Nur der Ort wurde gefunden, nicht aber Straße und Hausnummer. Drei der E-Bikestationen in Wesel konnten also nicht geokodiert werden. Für sechs weitere gab es keine exakte Zuordnung, aber eine mögliche, welche übernommen wurde. Typischerweise würde man jetzt hergehen und die Daten so bereinigen, so daß in einem 2. Lauf möglichst 100 Prozent der Daten geokodiert werden können. Hilfestellung bei der Bereinigung geben dabei die Informationen, welche sich in der Spalte GC_CANDIDATES befinden. Die Kartendarstellung für die geokodierten E-Bike-Stationen sieht als Projekt in Spatial Studio so (oder ähnlich) aus: Abb. 10: Kartenansicht zu E-Bike-Stationen in Wesel Eine erste räumliche Analyse Die geokodierten Daten lassen sich nun direkt in einem Projekt interaktiv und sehr einfach räumlich analysieren. Als Beispiel für eine solche Analyse können Sie z.B. herausfinden, in welchem Bundesland (oder Bundesländern) die E-Bike-Stationen (und somit Wesel) liegen. Dafür benötigen Sie ein weiteres Datenset mit den Umrissen der Bundesländer in gleichen Projekt. Ein solches Datenset können Sie hier herunterladen und anschließend in der zuvor für Excel Tabellen beschriebenen Weise dieses Mal als Shapefile laden. Die einzelnen Schritte der Analyse können wieder über die nachfolgende Abbildungen verfolgt werden. Abb. 11: Datenset mit Bundesländern dem Projekt zur Verfügung stellen Abb. 12: Auswahl des Datensets Abb. 13: Räumliche Analyse initiieren Abb. 14: Passende Komponente für die gewünschte räumliche Analyse auswählen Abb. 15: Parameter für die gewählte räumliche Analyse setzen Abb. 16: Visuelles Ergebnis der Analyse Das Ergebnis einer "Any Interaction" Analyse der E-Bike-Stationen in Bezug auf Bundesländer ergibt demnach "Nordrhein-Westfalen". Fazit Dieser Beitrag über das Oracle Spatial Studio umfaßt einen kleinen Ausschnitt der Möglichkeiten, ortsbezogene Informationen auf Karten darzustellen und räumliche Analysen durchzuführen, ohne vertiefte Kenntnisse von Geografischen Informationssystemen (GIS) oder Datenbanken zu haben. Mehr Informationen Infos zu Oracle Spatial Studio Demo Video zu Oracle Spatial Studio Oracle Spatial and Graph: Geocoder Developer´s Guide Deutschsprachiger Oracle Spatial Blog  

Erst im Mai hatte ich hier einen Beitrag über „Location Intelligence“ unserer Autonomous Databases veröffentlicht. Aber wie schon von Ulrike Schwinn in Ihrem Post angekündigt, gibt es nun noch mehr...

Analytics

Das neue Spatial Studio und die Oracle Datenbank

Seit Kurzem steht Oracle Spatial Studio 19.1 zum Download von OTN zur Verfügung. Oracle Spatial Studio ist ein Self-Service-Tool zur Visualisierung und Analyse von Geodaten auf der Grundlage der Oracle Spatial-Datenbanktechnologie.  Die Java Applikation ist schnell deployed und einfach zu bedienen.  Im einfachsten Fall kann man wie folgt vorgehen: Einfach den Zip File von OTN laden, auspacken und deployen und schon kann es losgehen. Voraussetzung ist eine Verbindung zu einer Datenbank (mindestens mit Version 12.2), eine Public Internet Verbindung um OpenStreetMap Styles zu verwenden und JDK 8 (>=Update 181). Dabei sollte man nicht vergessen das JAVA_HOME und den PATH zum Beispiel wie folgt zu setzen. (Ich verwende den JDK von SQL Developer 19.)  set JAVA_HOME=C:\data\sqldeveloper19\jdk\jre\ set PATH=C:\data\sqldeveloper19\jdk\jre\bin Mit start.bat lässt sich dann das Studio starten. Nach dem Start gelangt man über die URL https://localhost:4040/spatialstudio auf die Spatial Studio Anwendung; dabei ist es erforderlich eine Security Exception im Browser hinzuzufügen. Und schon kann man anfangen und man gelangt zum Loginscreen. Dort kann man sich mit dem Standarduser admin verbinden und danach Verbindungen zur Datenbank herstellen. Eine genaue Beschreibung der Vorgehensweise findet sich  im Get Started Dokument. Spezielles GIS Know How oder Spatial Know How ist zum Arbeiten nicht erforderlich.  Folgendes Beispiel zeigt dann eine einfache Visualisierung.  Als Datasets können kann dabei aus folgenden Quellen stammen: Excel Spreadsheets Shapefiles Datenbank Tabellen REST-Endpunkte werden automatisch für Datensätze und Ergebnisse der räumlichen Analyse generiert. Die Ergebnisse können als CSV und GeoJSON exportiert werden und dann beispielsweise mit Geschäftsdaten kombiniert werden. Lust das Ganze jetzt einmal selbst auszuprobieren? Gute Tutorials finden sich auf https://blogs.oracle.com/oraclespatial/. Darüberhinaus werden wir in den nächsten Wochen einen weiteren Post dazu veröffentlichen.  Übrigens: Spatial Studio kann mit Autonomous Datenbank Subscriptions, Oracle Database Cloud Services und On-Premises Datenbanken verwendet werden. Frei verfügbar ist es zudem in Oracle Database 18c Express Edition. Genaueres kann man im FAQ zu Oracle Spatial Studio nachlesen. Weitere Informationen und Anleitungen zum Ausprobieren finden sich auch in folgenden Beiträgen: Spatial Studio - Hello World! Oracle Spatial Studio Video        

Seit Kurzem steht Oracle Spatial Studio 19.1 zum Download von OTN zur Verfügung. Oracle Spatial Studio ist ein Self-Service-Tool zur Visualisierung und Analyse von Geodaten auf der Grundlage der...

Autonomous Database

SQL Developer Web und Autonomous DB

SQL Developer Web steht seit Ende Juni zusammen mit APEX für Autonomous Database zur Verfügung. Grund genug einen kurzen Überblick über die einzelnen Komponenten zu geben. Der Start macht SQL Developer Web.  Sucht man ausführliche Beschreibungen zur Handhabung, sollte man in jedem Fall auch folgende Quellen heranziehen: Handbücher zu Autonomous Database  Blogeintrag von Jeff Smith Was ist überhaupt SQL Developer Web? Wie startet man mit SQL Developer Web? Welche Funktionen werden von SQL Developer Web abgedeckt und welche nicht?  Eines gleich vorab: SQL Developer Web kann nur wenige Teile der Desktop Version von SQL Developer abdecken. SQL Developer Web ist wie der Name schon ausdrückt die Web Version von SQL Developer - also quasi eine "abgespeckte" Version. Im Wesentlichen ist die Idee dahinter, dem Datenbank Anwender eine einfache Möglichkeit zu geben, ohne Installation und Konfiguration schnell SQL Kommandos und/oder PL/SQL Skripte über die Weboberfläche abzusetzen. Übrigens wenn ich von Autonomous Database spreche, ist die Ausprägung ADW wie auch ATP damit gemeint. Oracle SQL Developer Web ist dabei eine Oracle REST Data Service Anwendung; der Zugriff erfolgt über eine schemabasierte Authentifizierung. Beim Anlegen der Autonomous Database ist dies schon für den User ADMIN durchgeführt worden, so dass man sofort zugreifen kann. Dazu navigiert man in der Service Console unter "Development".  Dort findet sich neben APEX auch das der Bereich SQL Developer Web. Ein Klick auf diesen Bereich führt dann direkt zum Login Screen. Jetzt ist ein Einloggen mit User ADMIN möglich.  Danach steht SQL Developer Web in vollem "Web"-Funktionsumfang zur Verfügung. Geöffnet ist ein Worksheet, das die Ausführung von SQL Statements und PL/SQL Skripts, die Anzeige von Ausführungsplänen und Autotracing ermöglicht - vergelichbar mit dem Woksheet von SQL Developer Desktop. Auf der linken Seite im Bereich Navigator werden die existierenden Datenbank Objekte des eingeloggten Users (hier ADMIN) angezeigt. Ein rechter Mausklick auf ein Objekt stellt das Objektmenü zur Verfügung. Dort kann man das zugehörige DDL Generieren oder Objekt Eigenschaften anpassen und auch neue Objekte anlegen. Als User ADMIN ist es natürlich möglich auch Objekte anderer User zu verwalten - die Drop-Down Liste zeigt dann die verfügbaren User an.  Interessant ist, dass der Data Modeler integriert ist. Data Modeler ist sehr hilfreich um schnell einen Überblick über das Datenmodell der Objekte zu erhalten. Ein einfaches Drag und Drop in den Data Modeler Bereich ist schon ausreichend, um einen Eindruck zu bekommen, in welcher Beziehung die Objekte zueinander stehen. Folgendes Beispiel zeigt einen Ausschnitt aus dem SH Schema.  Klickt man sich durch das Menü, kann man einige hilfreiche Funktionen entdecken. So ist es möglich sich das zugehörige DDL zu generieren, das Diagramm als SVG abzuspeichern oder auch einfach auszudrucken. Der Schema Report (das Icon ganz rechts) beispielsweise listet alle Tabellen im Diagramm, mit Spalten, Indizes und Constraints auf. Navigiert man zur Home Page erhält man weitere Informationen zu den Objekten wie zum Beispiel über die Aktualität der Statistiken, Validität oder Änderung an den Objekten selbst, wie man aus folgendem Screenshot ersehen kann.  Um mit einem weiteren User arbeiten zu können, muss zuerst ein neuer Datenbank User angelegt werden und mit entsprechenden Datenbankrechten ausgestattet werden. Folgende Statements zeigen ein Beispiel: create user test identified by "<passwort>"; grant connect, resource, dwrole to test; Im nächsten Schritt muss das Schema noch "REST-enabled" werden.  Dies wird über folgenden Aufruf gewährleistet. Mit dem Argument p_url_mapping_pattern wird ein Schema Alias vergeben, damit in der zugehörigen URL der Schemaname nicht publik wird. BEGIN    ORDS_ADMIN.ENABLE_SCHEMA(      p_enabled => TRUE,      p_schema => 'TEST',      p_url_mapping_type => 'BASE_PATH',      p_url_mapping_pattern => 'T1',      p_auto_rest_auth => TRUE    );    COMMIT; END; / Der SQL Developer Web Aufruf für TEST erfolgt dann über die entsprechende abgewandelte URL. Dabei wird die URL, die für den ADMIN User verwendet wird, einfach etwas angepasst - nämlich  "admin" wird durch "T1" ersetzt. Also statt https://xxxxb.eu-frankfurt-1.oraclecloudapps.com/ords/ADMIN/sign-in/?r=_sdw%2F%3Fnav%3Dworksheet wird jetzt folgende URL verwendet: https://xxxx.eu-frankfurt-1.oraclecloudapps.com/ords/T1/sign-in/?r=_sdw%2F%3Fnav%3Dworksheet Und schon bekommen wir den Login-Screen für den TEST User. Fazit Im Wesentlichen ermöglicht SQL Developer Web das unkomplizierte Ausführen von SQL und Pl/SQL über eine Web Oberfläche. Dabei bietet das Worksheet sogar die Möglichkeit wie beim SQL Developer in der Desktop Version Ausführungspläne zu generieren. Möchte man weitere Datenbank Operationen ausführen, ist die Desktop Version das Mittel der Wahl. Benötigt man detaillierte Aussagen über die Performance und/oder die Systemauslastung kann man die Webschnittstellen Performance Hub in der Cloud Konsole oder den Bereich Activity in der Service Konsole benutzen.             

SQL Developer Web steht seit Ende Juni zusammen mit APEX für Autonomous Database zur Verfügung.Grund genug einen kurzen Überblick über die einzelnen Komponenten zu geben. Der Start macht SQL Developer...

JSON

JSON Update in 19c mit JSON_MERGEPATCH

Seit Oracle 12.1 wird JSON in der Datenbank unterstützt d.h. es gibt die Möglichkeit auf JSON-Daten mit Standard Datenbankmitteln zuzugreifen. Die Idee dahinter ist, nicht nur einen einfachen Textstring zu speichern und auf diesen zuzugreifen, sondern auch spezielle JSON Pfad Zugriffe oder JSON Validierungen zu ermöglichen, um nur einige Features zu nennen. Zusätzlich stehen alle relationalen Datenbank Features bei der Nutzung von JSON zur Verfügung, wie z.B. Indizes, Transaktionshandling, gemischte Abfragen, relationale Views, External Tables usw. Zusätzlich dazu gibt es Funktionen zum Extrahieren von JSON, aber auch zum Generieren von JSON Daten, zum Suchen im JSON-Dokument und zum Laden von Daten.   Benötigt man eine Einführung in das Thema, kann man folgende deutsche Textbeiträge lesen: JSON und die Oracle Datenbank JSON in 12.2: JSON Generierung, neues Data Guide Konzept, neue Objekttypen 18c Basics: Architektur, SQL*Plus, SQL für JSON   Auch in 19c sind einige interessante Erweiterungen im Bereich JSON ergänzt worden, wie zum Beispiel die Möglichkeit JSON-Dokumente mit einem Kommando deklarativ zu aktualisieren. Dazu ist die neue SQL-Funktion JSON_MERGEPATCH eingeführt worden. Welche Funktionen besitzt nun die neue SQL-Funktion JSON_MERGEPATCH? Mit JSON_MERGEPATCH lassen sich gemäß IETF-Standard bestimmte Teile eines JSON-Dokuments aktualisieren. Wie der Name schon andeutet, legt man in einer Art Patch-Dokument genau fest, wie die Änderungen im Source-Dokument aussehen sollen. JSON_MERGEPATCH führt dann anschließend die Informationen aus dem Patch- und dem Source-Dokument zusammmen.  Im aktuellen Tipp wird die Funktionalität an Beispielen demonstriert. 

Seit Oracle 12.1 wird JSON in der Datenbank unterstützt d.h. es gibt die Möglichkeit auf JSON-Daten mit Standard Datenbankmitteln zuzugreifen. Die Idee dahinter ist, nicht nur einen...

Advisor

19c mit Real-Time Monitoring für Database Developer

Datenbankentwickler sollten nicht nur SQL Statements schreiben, sondern auch die Möglichkeit haben, ihre eigenen SQL Abfragen zu monitoren und gegebenenfalls zu optimieren. Liegt es an gewissen Statements, der Parallelisierung, den Ausführungsplänen, oder vielleicht an Ressourcen, die zu knapp bemessen sind, falls Datenbankabfragen zu langsam laufen? Hilfestellung zur Beantwortung dieser Fragen liefert dabei die Datenbank selbst. Sie stellt ein eigenes Framework zur Verfügung, um ohne Verwendung von externen Werkzeugen und ohne zusätzliche Installation ein genaues Monitoring zu erlauben. Dieses Datenbank Framework besteht dabei aus speziellen Datenbank Views, PL/SQL-Packages und ein spezielles Workload Repository, um nur einige Beispiele zu nennen.  Das Real-Time Monitoring beispielsweise, das schon seit Oracle 11g in der Oracle Datenbank zur Verfügung steht, ist ein geeignetes Hilfsmittel, um zuverlässig einen detaillierten Überblick über globale SQL- und PL/SQL-Statistiken von Operationen zu erhalten. Sowohl Cursor-Statistiken (z.B. CPU-Zeiten und IO-Zeiten) als auch Ausführungsplan-Statistiken (z.B. Anzahl der Zeilen, Speicher und belegter Temp Space) werden während der Ausführung der Anweisung nahezu in Echtzeit aktualisiert. Dabei werden nicht nur die gerade aktive Operationen monitort, sondern auch Abfragen, die sich in einer Warteschlange befinden oder gar abgebrochen worden sind. Grundlage um die relevanten Statistiken anzuzeigen, sind die Views V$SQL_MONITOR und V$SQL_PLAN_MONITOR. Darüber hinaus stellt DBMS_SQLTUNE die Funktionen REPORT_SQL_MONITOR und REPORT_SQL_MONITOR_LIST bereit, um übersichtliche Reports zu erstellen.  Ein Hinderungsgrund für Database Developer diese Funktion zu verwenden, war bisher immer, dass Datenbankadministrationsrechte wie SELECT_ANY_CATALOG Rolle erforderlich waren. Mit 19c ist diese Einschränkung nun aufgehoben worden! Somit können Datenbank Developer mit weniger Privilegien beispielsweise nur mit der CONNECT und RESOURCE Rolle das Real-Time Monitoring für das Monitoren ihrer eigenen Statements verwenden.  Ein Beispiel wie Datenbank Developer das Real-Time Monitoring verwenden können, wird im folgenden Tipp beschrieben und demonstriert

Datenbankentwickler sollten nicht nur SQL Statements schreiben, sondern auch die Möglichkeit haben, ihre eigenen SQL Abfragen zu monitoren und gegebenenfalls zu optimieren. Liegt es an...

Autonomous Database

Autonomous Database jetzt auch mit Location Intelligence

Seit Ende Mai ist die Unterstützung für die Arbeit mit Geodaten auch in den beiden Autonomous Datenbanken verfügbar. In der Oracle Datenbank lange etabliert und mit jeder Version seit Oracle DB 8i immer weiter vervollständigt und verbessert, hat die Prozessierung, Analyse und Nutzung von Geodaten jeglicher Art (Vektor, Raster, Netzwerke, Punktwolken, etc.) längst Einzug gehalten in moderne Datenbank-zentrische Applikationen und ist aus diesen derzeit nicht wegzudenken. Autonomous Data Warehouse und Autonomous Transaction Processing ziehen nach und ermöglichen nunmehr grundlegende Funktionen zur Speicherung, Indexierung und Analyse von 2-dimensionalen Vektordaten. Letzteres inkludiert sowohl typische "Point-in-Polygon" Abfragen, Umgebungssuchen, räumliche Verschneidungen, Distanz- und Flächenberechnungen, Linear Referencing für die Analyse von Ereignissen entlang von linearen Netzen, als auch "Geofencing" Abfragen, bei denen sich bewegende Objekte dahingehend überwacht werden können, ob sie sich in einen definierten Raum hinein- oder wieder herausbewegen. Mehr zum Thema ist (in englischer Sprache) in diesen beiden Blog Posts zu lesen: Autonomous Data Warehouse - Now with Spatial Intelligence Autonomous Transaction Processing - Now with Spatial Intelligence Probieren Sie es aus. Wir sind gespannt auf Ihr Feedback.        

Seit Ende Mai ist die Unterstützung für die Arbeit mit Geodaten auch in den beiden Autonomous Datenbanken verfügbar. In der Oracle Datenbank lange etabliert und mit jeder Version seit Oracle DB 8i...

Analytics

Analytics Cloud: Zugriff auf das BI Repository mit dem BI Admintool

Die Oracle Analytics Cloud ist grundsätzlich betrachtet die Weiterentwicklung der Oracle BI Enterprise Edition (OBIEE) für die Oracle Cloud als Platform Service. Wie auch in der sogenannten "on-premise" Version von BI 12c kommt hier zum Erstellen und Bearbeiten des BI Datenmodells (auch als "Common Enterprise Information Model" bezeichnet) das Business Intelligence Developer Client Tool (BI Admintool) zum Einsatz. Der BI Administration Tool-Client ist eine Windows-Anwendung, mit der Sie Ihr Oracle BI-Repository erstellen und bearbeiten können. Das Administration Tool kann im Offline-Modus oder im Online Modus betrieben werden. Das BI Admintool wird regelmäßig aktualisiert und in zwei Varianten angeboten: Oracle Business Intelligence Developer Client Tool: Die aktuelle Version lautet 12.2.1.4.0 und trägt damit dieselbe Versionsnummer wie die aktuelle BI 12c on-premise Version. Oracle Analytics Developer Client Tool: Die aktuelle Version lautet 105.2.0-307 und ist damit an die Versionsnummer der Analytics Cloud Instanzen angelehnt. Es können übrigens beide Versionen parallel installiert werden - hierzu muss lediglich jeweils ein separates Installationsverzeichnis ausgewählt werden (z.B. C:\ORCL\BI12cAdmin und C:\ORCL\OACAdmin). Der wesentlichste funktionale und sichtbare Unterschied zwischen den beiden Versionen ist im Menü bzw. in der Menüleiste zu finden: In der On-Premise Version des BI Admintools ist die Option "Open in the Cloud" ausgegraut bzw. deaktiviert: In der Analytics Cloud Variante ist die Option "Open in the Cloud" verfügbar. Wie kann ich nun die Analytics Cloud Variante des BI Admintools nutzen, um das BI Repository quasi im "Cloud Online" Modus zu öffnen? In diesem Blogeintrag möchten wir diese Frage beantworten. BI Admintool: Die Option "Open in the Cloud" Wir gehen im folgenden davon aus, dass die beiden folgenden Schritte bereits durchgeführt sind: Installation von Analytics Developer Client Tool Zugriff auf eine Analytics Cloud Instanz mit dem Feature Set "Business Intelligence - Enterprise Data Models". Benutzer mit der Berechtigung "BI Service Administrator". Nach der Auswahl der Option "File/Open in the Cloud" müssen Sie die nachfolgenden Parameter eintragen. Einige davon sind als nicht änderbarer Standardwert zu betrachten - wir haben das in der nachfolgenden Tabelle vermerkt. Die Eingabefelder User: Der Anmeldename Ihres Oracle Cloud Accounts Password: Ihr Passwort des Oracle Cloud Accounts Cloud: bootstrap (Standardwert) Host name: Der Hostname wird abgeleitet aus der Service Instanz URL Port number: 443 (Standardwert) SSL - Trust Store: Der Pfad ergibt sich automatisch aus dem Installationsverzeichnis des BI Admintools. SSL - Password: changeit (Standardwert) Erläuterungen Der Hostname kann aus der "Oracle Analytics Cloud URL" abgeleitet werden. Beispiel: Analytics Cloud URL: https://myoacinstance-mycloudaccountname.analytics.ocp.oraclecloud.com/dv/ui?pageid=home Hostname: myoacinstance-mycloudaccountname.analytics.ocp.oraclecloud.com     Das BI Repository wird nun aus der Cloud geladen und kann bearbeitet werden. Nach dem Abschluß der Bearbeitung müssen noch zwei Schritte durchgeführt werden, um das geänderte Repository freizugeben und in der Analytics Cloud Instanz anzuwenden: Sichern der Änderungen: File / Save. Der globale Consistency Check sollte auf jeden Fall durchgeführt werden. Publizieren der Änderungen in die Cloud Instanz: File / Cloud / Publish. Mit diesen abschließenden Schritten wird das Repository in die Cloudinstanz hochgeladen und freigegeben. Die Änderungen im BI-Repository sind dann direkt sichtbar - ggf. melden Sie sich mit Ihrem Account neu an der OAC-Instanz an. Weiterführende Links OAC: How to Connect to the Oracle Analytics Cloud (OAC) RPD using Client Admintool (Doc ID 2432176.1)

Die Oracle Analytics Cloud ist grundsätzlich betrachtet die Weiterentwicklung der Oracle BI Enterprise Edition (OBIEE) für die Oracle Cloud als Platform Service. Wie auch in der...

Oracle Database 19c jetzt

Nachdem Oracle Database 19c seit Februar diesen Jahres auf Oracle Engineered System verfügbar ist, ist es nun auch für weitere Plattformen soweit: Seit ein paar Tagen kann Oracle Database 19c (19.3) auch für on-premise Installationen für Linux X86-64 und Oracle Solaris (SPARC systems, 64-bit) zum Download von Oracle Software Delivery Cloud und Oracle Technology Network geladen werden. Parallel dazu sind auch die entsprechenden Docker Buildfiles unter https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/19.3.0 verfügbar.  Wichtig zu wissen ist, dass 19c (aka 12.2.0.3) das letzte Release der Oracle Database 12c Produktfamilie (einschließlich Oracle Datenbank 18c) ist und als „Long Term“ Release oder auch  als "Terminal Patch Release" bezeichnet wird. Alle Informationen zum Release Schedule finden sich übrigens wie immer in der My Oracle Support Note "Release Schedule of Current Database Releases (Doc ID 742060.1)". Obwohl bei der Entwicklung die Release Stabilität eine wichtige Rolle spielte - Oracle Database 19c wird sicherlich das Zielrelease für die meisten Datenbank Upgrades sein - gibt es auch einige sehr interessante Features wie zum Beispiel im Bereich Automatisierung, Datenmanagement, Performance, Security usw. Einen guten Überblick über alle 19c Features kann man über den New Features Guide oder ganz einfach über die Database Features Applikation (siehe Screenshot) erhalten.   Mehr dazu werden wir in den nächsten Wochen und Monaten dazu veröffentlichen. Informationen zur Lizenzierung findet sich wie immer im Database Licensing Information User Manual.  Hinweis: Steht (noch) keine 19c Instanz zur Verfügung, kann man sich - speziell für das Ausprobieren von SQL und PL/SQL Features - mit Oracle Live SQL behelfen. Live SQL (http://livesql.oracle.com) ist mit der aktuellen 19c Version ausgestattet und hilft mit Tutorials und einer umfangreichen Code Library bei einem schnellen Einstieg. 

Nachdem Oracle Database 19c seit Februar diesen Jahres auf Oracle Engineered System verfügbar ist, ist es nun auch für weitere Plattformen soweit: Seit ein paar Tagen kann Oracle Database 19c (19.3)...

Autonomous Database

Oracle Autonomous Database mit Terraform bereitstellen

Die Oracle Autonomous Database oder kurz Autonomous DB ist eine "Cloud-Only"-Implementierung, die es ermöglicht eine Datenbank schnell, sicher und ohne großen Administrationsaufwand zu erstellen und zu betreiben. Die Nutzung von Hashicorps-Terraform unterstützt das sichere und skalierbare Skripten der Bereitstellung einer oder mehreren Autonomous DBs. Der folgende Artikel gibt einen Überblick über die notwendigen Schritte und zeigt anhand eines Praxis-Beispiels wie die Bereitstellung durchzuführen ist. Warum Terraform? Agilität ist eines der Hauptargumente für Cloud-Computing. Das schnelle Hoch- und Runter-Skalieren von Applikations-Umgebungen funktioniert am Effektivsten mittels Skripten zur automatischer Bereitstellung, Änderung und Löschung. Zusätzlich werden in modernen Cloud-Anwendungen häufig DevOps-Ansätze verfolgt. Das heißt, neben der Bereitstellung des Programm-Codes werden auch die benötigten Laufzeitumgebungen, wie Web-Server, Datenbanken etc. im Code erzeugt. Um Agilität und DevOps im Enterprise Cloud Umfeld verwenden zu können, gibt es weitere Anforderungen wie Source-Code-Verwaltung, Erweiterbarkeit, Einhaltung von Richtlinien und ähnliches. Alle diese Anforderungen können mit Terraform der Firma Hashicorp abgedeckt werden. Hashicorp ist ein unabhängiges Unternehmen und unterstützt alle gängigen Cloud-und Automatisierungs-Anbieter. Typische Anwendungsfälle für Terraform sind: Infrastruktur as Code, Multi-Cloud und Self-Service-Infrastructure.  Infrastructure as Code ist ein typisches DevOps-Cloud-Szenario, in dem Programmcode inklusive Infrastruktur bereitgestellt wird. Der Multi-Cloud-Ansatz ermöglicht es aus einer zentralen Bereitstellungs-Umgebung, Applikations-Komponenten bei verschiedenen Cloud-Anbietern automatisiert bereit zu stellen. In einer Self-Service-Infrastructure werden Cloud-Komponenten aus einem Bestellportal durch den Verbraucher selbst angefordert und erstellt. Übersicht der Bereitstellung Autonomous Database Im ersten Schritt wird die Terraform-Laufzeitumgebung installiert und konfiguriert. Anschließend wird die gewünschte Ressourcen-Konfiguration beschrieben. In unserem Fall ist es Oracle Autonomous Database, das mit wenigen Parametern definiert wird. Die Bereitstellung erfolgt durch Starten des Terraform-Skripts, das die Cloud API der Oracle Cloud verwendet, um alle Einzelkomponenten schnell und in der richtigen Reihenfolge bereitzustellen. Bei der Erstellung kommuniziert der Client auf dem Terraform installiert wurde direkt mit der Oracle Cloud Infrastructure API. Die Terraform-Client-Software kann innerhalb oder außerhalb der Oracle Cloud installiert werden. Hinweis: Sie können beim Erstellen einer neuen Compute-Instanz in der Oracle Cloud den Image-Typen "Oracle Cloud Developer Image" auswählen. Auf diesem Image ist Terraform bereits vorinstalliert.  Die Bereitstellung im Detail Im ersten Schritt wird die Laufzeitumgebung für Terraform installiert und konfiguriert. Diese Schritte sind in der Dokumentation oder in einem meiner früheren Blog-Einträge "Terraform - Automatische Erstellung einer DBaaS-Instanz in der Oracle Cloud Infrastructure" ausführlich beschrieben.  In einem Verzeichnis erzeugt man im nächsten Schritt eine Datei mit der Bezeichnung autonomousDatabase.tf (kann man natürlich auch anders bezeichnen, allerdings muss die Endung .tf lauten). Anschließend kopiert man sich folgendes Programm-Listing in die Datei.  resource "oci_database_autonomous_database" "test_autonomous_database" {     admin_password = "<hierDasAdminPasswort"     compartment_id = "<hierDieCompartmentID>"     cpu_core_count = "1"     data_storage_size_in_tbs = "1"     db_name = "BUADB" } Zu den Parametern: admin_password - Das Passwort des Datenbank-Adminstrators compartment_id - Die eindeutige Oracle Cloud Infrastruktur ID des gewünschten Compartments (hier ist eine Beschreibung, wie diese ermittelt wird) cpu_core_count - Anzahl der gewünschten Cores data_storage_size_in_tbs - Größe des Datenbank-Storage db_name - Der Name der Datenbank Ohne Angabe des Workload-Typs wird als Standard Transaction Processing ausgewählt. Der Workload-Typ ist ein optionaler Parameter mit der Bezeichnung db_workload="OLTP" für Autonomous Transaction Processing oder db_workload="DW" für Autonomous Data Warehouse. Das gilt auch für den Lizenz-Typ, ohne Angabe wird Bring Your Own License verwendet, bei License Included muss der optionale Parameter folgendermaßen aussehen: license_model = "LICENSE_INCLUDED" Diese eine Datei reicht jedoch nicht aus. Es fehlt noch die Authentifizierung und Autorisierung, dass die Datenbank angelegt werden darf. Dafür legen wir uns eine Datei namens provider.tf an (auch diese Datei kann umbenannt werden, benötigt jedoch die .tf Endung). Nach Anlegen der Datei kopieren wir folgendes Programm-Listing: provider "oci" {   region           = "eu-frankfurt-1"   tenancy_ocid     = "<hierDieTenancyID>"   user_ocid        = "<hierDieBenutzerID>"   fingerprint      = "<hierDenFingerprintWert>"   private_key_path = "<hierPfadZumPrivateKey>" } Zu den Parametern: region - Die gewünschte Region in der die Bereitstellung erfolgen soll, in unserem Fall Frankfurt. tenancy_ocid - Die eindeutige Tenancy/Account Oracle Cloud Infrastructure ID oder kurz OCID. Hier ist eine Beschreibung, wie sie ermittelt wird. user_ocid - Die eindeutige Benutzer OCID. Hier ist eine Beschreibung, wie sie ermittelt wird. private_key_path - Der Pfad zu dem Private-Key-File im PEM-Format. Hier ist eine Beschreibung, wie der Schlüssel erzeugt wird. fingerprint - Der Fingerprint des Public-Key-Files, dass in die Oracle Cloud hochgeladen werden muss. Hier ist eine Beschreibung, wie sie ermittelt wird. Wenn man alle Parameter in den beiden Dateien eingetragen hat initialisiert man im ersten Schritt Terraform mit dem Befehl terraform init. Anschliessend wird mit dem Befehl terraform apply die Erstellung der Autonomous Database Instanz initiiert. Nach ca. 5 Minuten ist die Instanz erstellt.  Hinweis: Wenn es zu Fehlermeldungen kommt, sind diese recht sprechend und haben in den meisten Fällen mit der Authentifizierung zu tun! In diesem Fall prüfen Sie bitte noch einmal die Parameter in der provider.tf Datei auf ihre Richtigkeit. Auch Änderungen sind in Terraform kein Problem, wenn Sie zum Beispiel die Anzahl der Cores hoch setzen möchten, ändern Sie diesen Parameter in der autonomousDatabase.tf Datei und initiieren Sie die Änderung mit dem erneuten Aufruf terraform apply. Das Löschen der Umgebung erfolgt ebenfalls sehr einfach, rufen Sie terraform destroy auf, nach Bestätigung wird die Instanz gelöscht. Was als nächstes? Dieses Beispiel ist bewusst sehr einfach gehalten und es gibt eine Reihe von möglichen Erweiterungen.  Zum Beispiel können die Parameter, die wir in den Dateien angegeben haben auch als Umgebungsvariablen in einer Datei definiert werden. Die in dem Beispiel verwendeten Parameter stellen eine Minimalkonfiguration dar, es können auch optionale Parameter hinzugefügt werden, um z.B. den Standard-Anzeigenamen zu ändern.  Es können weitere Komponenten hinzugefügt und Konfigurationen innerhalb der Umgebungen direkt nach Erstellung der Ressourcen durchgeführt werden. Die Kombination von Terraform-Beschreibungen und dem Ressourcen Manager in der Oracle Cloud Infrastructure ermöglicht Ihnen die Planung der Ausführungszeit und das Hinterlegen der Skripte in Ihrem Oracle Cloud-Account.  Eine Beschreibung, wie diese Erweiterungen implementiert werden, finden Sie auf https://www.terraform.io/docs/providers/oci/index.html . Dort finden Sie eine sehr ausführliche Dokumentation der Funktionalitäten. Es gibt auch eine Reihe von fertigen Beispielen auf GitHub unter https://github.com/terraform-providers/terraform-provider-oci Fazit Dieser Artikel umfasst einen kleinen Ausschnitt der Möglichkeiten, welche mit der Kombination Terraform und Oracle Cloud Infrastructure möglich ist. Wenn es um DevOps, Multi-Cloud oder Self-Service-Applikationen geht, ermöglicht Terraform die bis dato beste Umsetzung der Anforderungen.  

Die Oracle Autonomous Database oder kurz Autonomous DB ist eine "Cloud-Only"-Implementierung, die es ermöglicht eine Datenbank schnell, sicher und ohne großen Administrationsaufwand zu erstellen und...

Advisor

Oracle Database Security Assessment Tool jetzt in Version 2.1.0

Letztes Jahr haben 18.000 Kunden das Oracle Database Security Assessment Tool (DBSAT) heruntergeladen. Damit gehört DBSAT zu den beliebtesten Oracle Tools der letzten Zeit. Die erste Version (1.0.1) erschien im Juni 2016. Seitdem wird DBSAT stetig weiterentwickelt, mit Security Best-Practices angereichert und entsprechend neuer Oracle Datenbank-Versionen aktualisiert. Angesichts zunehmender Gefahr durch Datenmissbrauch und gleichzeitig steigenden Anforderungen durch gesetzliche Vorschriften, ist der Schutz sensibler Unternehmensdaten von entscheidender Bedeutung. Mit DBSAT 2.1.0 veröffentlicht Oracle nun eine neue aktualisierte Version des Oracle Database Security Assessment Tools. Was ist neu im DBSAT 2.1.0? Unterstützung von Oracle Database 18c, 19c und Autonomous Databases Berichte mit Verweisen auf STIG-Regeln (Security Technical Implementation Guides) Suchmuster für das Auffinden sensibler Daten für die Sprachen: Niederländisch, Französisch, Deutsch, Italienisch, Portugiesisch und Spanisch Neue Security Best-Practices zu Password-Files, Global Names, Instance Names, RMAN Backups und mehr Aktualisierte Sicherheits-Hinweise und Empfehlungen Empfohlene Sicherheitsmaßnahmen für gefundene sensible Daten Aktualisierte Daten-Kategorien Weitere Informationen zum DBSAT auf der DBSAT OTN Homepage  https://www.oracle.com/database/technologies/security/dbsat.html DBSAT Download siehe MOS Note 2138254.1 https://support.oracle.com/epmos/faces/DocumentDisplay?id=2138254.1 Weitere DBSAT Tipps: Überprüfung der Datensicherheit mit dem Oracle Database Security Assessment Tool https://apex.oracle.com/pls/apex/germancommunities/dbacommunity/tipp/6302/index.html Das Oracle Database Security Assessment Tool sucht nun nach sensiblen Daten https://apex.oracle.com/pls/apex/germancommunities/dbacommunity/tipp/6481/index.html

Letztes Jahr haben 18.000 Kunden das Oracle Database Security Assessment Tool (DBSAT) heruntergeladen. Damit gehört DBSAT zu den beliebtesten Oracle Tools der letzten Zeit. Die erste Version (1.0.1)...

Database Management

Did you already know: Oracle Architektur im "interactive diagram"

Oracle Database 18c ist kein Major Release, trotzdem finden sich einige interessante Features und Neuigkeiten. Hat man schon länger nicht mehr mit Oracle gearbeitet, oder möchte man schnell den neuen Kolleginnen und Kollegen die Oracle Datenbank erklären, eignet sich sicherlich das Oracle Database Concepts Handbuch als Lektüre. Angefangen bei den Oracle Begrifflichkeiten bis zu Process und Memory Management bietet dieses Handbuch einen guten Überblick und Einstieg. Zusätzlich dazu ist mit der Version 18c ein neues Architekturdiagramm zur Verfügung gestellt worden - das interaktive Architekturdiagramm.  Auf 29 Slides wird die 18c Datenbank mit Datenbankdateien, Prozessen, Memory usw. oder auch die Multitenant Architektur erklärt. Mit einem einfachen Mausklick kann man beispielsweise von der Datenbank Instance bis zur Detaildarstellung des Large Pools gelangen. Flankiert wird das Ganze durch eine zusätzliche kurze Textbeschreibung unterhalb der (Powperpoint) Grafik, die weitere Informationen zu der entsprechenden Ansicht liefert.  Möchte man noch weitere Details erfahren, gelangt man mit einem Klick der linken Maustaste auf die entsprechende Seite im Database Concepts Guide. Was will man mehr? Am Besten ausprobieren und den Link bookmarken :) - http://www.oracle.com/webfolder/technetwork/tutorials/architecture-diagrams/18/technical-architecture/database-technical-architecture.html    Hinweis: Steht (noch) keine 18c Instanz zur Verfügung, kann man sich - speziell für Features im SQL Bereich - mit Oracle Live SQL behelfen. Live SQL (http://livesql.oracle.com) ist mit der aktuellen Datenbank Version ausgestattet und hilft mit Tutorials und einer umfangreichen Code Library bei einem schnellen Einstieg.  

Oracle Database 18c ist kein Major Release, trotzdem finden sich einige interessante Features und Neuigkeiten. Hat man schon länger nicht mehr mit Oracle gearbeitet, oder möchte man schnell den neuen...

SQL, PL/SQL und mehr

Oracle Database 18c Express Edition ist verfügbar

Oracle Database 18c XE (18.4) für Linux x64 steht seit 19. Oktober als Download von OTN zur Verfügung. Das Neue an dieser Version ist nicht nur das Release, sondern auch die Ausstattung:  es handelt sich nämlich um eine Datenbank Edition mit Enterprise Features und Optionen wie In-Memory, Partitionierung, Advanced Compression, Oracle Spatial, Advanced Analytics, Advanced Security usw. Mehr zur Lizenzierung und den verfügbaren Features findet sich im Oracle Database 18c Express Edition Licensing Information Guide. Wie bei allen vorangegangenen XE Editionen handelt es sich auch bei 18c XE um eine durch die Community unterstützte Edition. Bitte verwenden Sie das Oracle Database XE Community Support Forum für Hilfe, Feedback und Erweiterungsanfragen. Mit 18c XE können Datenbank Entwickler nun den Funktionsumfang der Datenbank ausschöpfen, Enterprise Features testen und ihre Applikationen entwickeln. Natürlich gibt es auch einige Einschränkungen, die in der folgende Liste zu finden sind:  Bis zu 12 GB User Daten  Bis zu 2 GB Datenbank Memory Bis zu 2 CPU Threads Bis zu 3 Pluggable Datenbanken Am besten gleich herunterladen und testen. Übrigens steht XE jetzt auch auf Oracle Cloud Infrastructure (OCI) Yum-Servern zur Verfügung. Folgender Blogpost beschreibt wie man XE OCI installiert, konfiguriert und sich damit verbinden kann: https://blogs.oracle.com/developers/oracle-database-18c-xe-on-oracle-cloud-infrastructure:-a-mere-yum-install-away Anmerkung: Weitere Informationen zu 18x XE findet sich im Oracle Database XE FAQ und im Handbuch.

Oracle Database 18c XE (18.4) für Linux x64 steht seit 19. Oktober als Download von OTN zur Verfügung. Das Neue an dieser Version ist nicht nur das Release, sondern auch die Ausstattung:  es handelt...

Cloud

Oracle Database 18.1 für Cloud und Exadata

Dominic Giles, Master Product Manager Oracle Database, hat am Freitag den 16. Februar im Oracle Database Insider Blog die Verfügbarkeit von Oracle Database 18c für Cloud und Oracle Engineered Systems angekündigt. Das genaue Release Schedule lässt sich wie immer in My Oracle Support mit Doc ID 742060.1 nachlesen. Im Moment ist das 18c Release für Exadata Software auf On-Premise Installationen verfügbar. Die Cloud Freigaben werden in Kürze folgen. Dort findet man auch die Information, dass die On-Premise Software im Sommer freigegeben werden soll. Die offiziellen Handbücher sind schon auf OTN im Documentation Bereich zu finden. Darüberhinaus sind auf der OTN 18c Einstiegsseite erste Videos und White Paper zu 18c abgelegt. Möchte man selbst erste 18c Features - speziell für den SQL Bereich - einfach und schnell ausprobieren, kann man auch Oracle Live SQL verwenden (siehe auch unseren Blogeintrag zum Thema). Live SQL (http://livesql.oracle.com) ist jetzt nämlich mit der Datenbank Version 18c ausgestattet. Durchsucht man die Code Library nach 18c wird man auch schnell fündig und kann die ersten Skripte zum Thema JSON in 18c, polymorphic table function, private temporary tables u.v.m. ausprobieren.  Auch wir werden in nächster Zeit einige Posts zum Thema 18c hier im Blog veröffentlichen - Stay tuned!

Dominic Giles, Master Product Manager Oracle Database, hat am Freitag den 16. Februar im Oracle Database Insider Blog die Verfügbarkeit von Oracle Database 18c für Cloud und Oracle Engineered Systems ...

Advisor

DBSAT jetzt mit Sensitive Data Discovery

Das Database Security Assessment Tool (DBSAT) hat seit dem 20. Januar einen Nachfolger bekommen. Die neue Version wurde unter anderem mit einer Discovery-Funktionalität ausgestattet, welche sensitive Daten in Oracle Datenbanken suchen kann. Diese Funktionalität kann Sie bei der Umsetzung diverser Gesetze und Richtlinien unterstützen, wie zum Beispiel bei der EU-Datenschutzgrundverordnung (EU-DSGVO), welche im Mai 2018 in Kraft tritt. Highlights dieser Version sind: Aufspüren sensibler und persönliche Daten in Oracle Datenbanken wie Gesundheitsdaten, Mitarbeiter-Daten, IT-Daten, Religion, politische Zugehörigkeit, usw. Aufzeigen von Risiken, welche sich auf die DSGVO-Artikel 25 und 32 beziehen Unterstützung bei der Datenschutzfolgenabschätzung (Data Protection Impact Assessments, DPIA) durch Bewertung der Risiken Empfehlungen von Sicherheitsmaßnahmen zur Unterstützung von Compliance-Anforderungen DBSAT ist natürlich wie sein Vorgänger für Oracle Kunden kostenfrei. Jeder Kunde mit einem aktiven Supportvertrag kann dieses Werkzeug unter Oracle Support Document 2138254.1 beziehen. Einen Einblick zum Thema "Sensitive Data Discovery" mit dem Oracle Database Security Assessment Tool finden Sie hier in diesem Tipp. Eine kurze Präsentation des Tools finden Sie hier auf YouTube. Hier finden Sie ein deutschprachiges Whitepaper: Database Security Assessment Tool

Das Database Security Assessment Tool (DBSAT) hat seit dem 20. Januar einen Nachfolger bekommen. Die neue Version wurde unter anderem mit einer Discovery-Funktionalität ausgestattet, welche sensitive...

Oracle und Docker: Tipps und News

Im Rahmen eines Vortrags, den ich kürzlich auf einer IT-Konferenz gehalten habe, ist mir folgendes aufgefallen: Obwohl der Einsatz von Oracle Software (im speziellen die Oracle Datenbank) in Docker Containern bereits seit einiger Zeit supported ist und es dazu auch verschiedene von Oracle vorbereitete Möglichkeiten gibt, ist diese Tatsache noch nicht allen bekannt. Es tauchen immer wieder verschiedene Fragen dazu auf. Ich möchte daher an dieser Stelle kurz die wichtigsten Informationen und einige News zum Thema "Oracle und Docker" zusammenfassen: Oracle Datenbanken können in Docker Containern betrieben werden, was unter Berücksichtung weniger Einschränkungen - z.B. derzeit noch kein Einsatz als Real Application Cluster - von Oracle offiziell supported ist (siehe MOS Note 2216342.1). Sinngemäß das Gleiche gilt für den Oracle WebLogic Server, dessen Betrieb in Docker Containern ebenfalls supported ist (siehe MOS Note 2017945.1). Die Bereitstellung einer Oracle Datenbank als Docker Container dauert nur wenige Minuten und kann prinzipiell auf zwei Arten erfolgen: in Form vorgefertigter Docker Images (z.B. aus dem Docker Store unter https://store.docker.com ) die nach einmaligem Herunterladen ("docker pull") beliebig oft verwendet werden können. durch das Erstellen von an die eigenen Bedürfnissen angepassten Images mithilfe von Oracle bereitgestellter Build-Skripte von GitHub (https://github.com/oracle/docker-images/tree/master/OracleDatabase). Diese Vorgehensweise dauert zwar initial etwas länger als die erst genannte, erlaubt dafür im Gegensatz dazu einen größeren Einfluss auf den Inhalt des Images wie z.B. die Auswahl konkreter Datenbank-Editionen und -Versionen. Die genannten Methoden werden kontinuierlich weiterentwickelt und erweitert. So ist z.B. geplant auch Dockerfiles für die Verwendung von Oracle RAC Datenbanken bereit zu stellen. Praxisnahe Tipps und konkrete Hinweise zur Nutzung von Oracle Datenbanken in Docker Containern finden sich u.a. in folgendem Blog Artikel meines Kollegen Ralf Durben: https://blogs.oracle.com/coretec/nutzung-von-oracle-datenbanken-in-docker-containern Ergänzend dazu finden sich hier die Folien meines o.g. Vortrags mit weiteren Hinweisen zu Oracle und Docker. Viele Informationen rund um das Thema "Container und Oracle" - sowohl in On-Premises Software als auch in der Cloud - finden sich außerdem auf https://developer.oracle.com/containers.

Im Rahmen eines Vortrags, den ich kürzlich auf einer IT-Konferenz gehalten habe, ist mir folgendes aufgefallen: Obwohl der Einsatz von Oracle Software (im speziellen die Oracle Datenbank) in Docker...

Database Management

Anonymisierung von Daten in der Oracle Datenbank: wie funktioniert das?

Wir werden immer wieder danach gefragt wie Anonymisierung in der Datenbank funktioniert. Je nach Anforderung bietet die Oracle Datenbank dazu verschiedene Möglichkeiten wie Virtual Private Database, Label Security, Data Redaction und Data Masking. Was sind die Unterschiede? Welche Konzepte stecken dahinter? Virtual Private Database (VPD) Die existierenden Objektprivilegien, die Anwendern das Lesen, Einfügen, Ändern und Löschen von Daten erlauben, zielen immer auf alle Zeilen einer Tabelle. Soll der Zugriff auf Zeilenebene gesteuert werden, weicht man entweder auf die Steuerung des Zugriffs über Anwendungen aus oder verwendet Views. Oracle bietet schon seit der Version Oracle8i eine weitere Lösung für dieses Problem: Die Lösung ist unter den Namen Fine Grained Access Control (FGAC) oder auch Virtual Private Database (VPD) bekannt. Es handelt sich dabei um ein Feature der Enterprise Edition. VPD implementiert die Kontrolle für den Zugriff auf einzelne Zeilen auf der Ebene der Tabelle: Mit dem Paket DBMS_RLS werden die Befehle INSERT, UPDATE, DELETE und SELECT, die auf eine mit VPD geschützte Tabelle zugreifen, um eine zusätzliche WHERE-Bedingung erweitert. Diese WHERE-Bedingung wird flexibel durch eine Funktion erstellt, die der Datenbankadministrator oder Anwendungsentwickler schreiben muß. Enthält das SELECT, UPDATE oder DELETE bereits eine WHERE-Bedingung, wird die von der Funktion erzeugte WHERE-Bedingung einfach mit AND angehängt.  Label Security Oracle Label Security (OLS) ist eine Option der Enterprise Edition der Datenbank. Was ist die Idee dahinter? Will man den Zugriff nur auf bestimmte Zeilen erlauben, hat man die Möglichkeit entweder Views anlegen, VPD zu programmieren oder einfach Label Security zu verwenden. Kurz beschrieben handelt es sich bei Oracle Label Security um eine fertige Anwendung, die auf Oracle Virtual Private Database aufgebaut ist.  Wie funktioniert OLS? Zunächst werden Labels definiert. Offizielle Labels sind zum Beispiel "streng geheim", "geheim", "Verschlusssache - vertraulich" und "Verschlusssache - nur für den Dienstgebrauch". Dann werden die definierten Labels einzelnen Tabellenzeilen zugewiesen und dabei in einer separaten Spalte gespeichert. Die Spalte wird automatisch in jeder Tabelle angelegt, die für das Arbeiten mit OLS vorbereitet wird. Schliesslich erhalten auch die Benutzer Labels. Nach jedem Einloggen ist deren Labelinformation Teil ihres session context. Greift nun ein Benutzer auf Datensätze in einer für OLS vorbereiteten Tabelle zu, wird wie immer zunächst überprüft, ob dieser Benutzer überhaupt über die notwendigen Privilegien verfügt, auf die Tabelle zuzugreifen. Ist das der Fall, wird über einen Abgleich des Benutzerlabels und des Zeilenlabels festgestellt, auf welche Sätze genau der Benutzer zugreifen darf.  Oracle Data Redaction Seit dem Datenbank Release Oracle Database 12c gibt es im Rahmen der Advanced Security Option (ASO) das Feature Data Redaction, welches auch für 11.2.0.4 nachträglich verfügbar ist (Backport). Das Ziel ist dabei ein dynamisches Data Masking, also eine Unkenntlichmachung von Teilen der Ausgabe direkt beim Zugriff auf die Daten. Data Redaction wird über das Package DBMS_REDACT eingesetzt. Data Redaction verändert zwar ebenfalls Daten, aber ausschliesslich für die Ausgabe und damit der Darstellung von Produktivdaten, die in Berichten oder Anzeigemasken sichtbar werden. Die ursprünglichen, also gespeicherten, Daten bleiben dabei unverändert. Die mit Data Redaction bearbeiteten Daten können sogar nach wie vor für WHERE Bedingungen, in INSERTs, UPDATEs und DELETEs, für Berechnungen und für das Anlegen von Indizes verwendet werden. Data Masking Für Entwicklungs- und / oder Testumgebungen dürfen in der Regel keine Daten aus einer Produktionsumgebung verwendet werden. Mit dem Oracle Data Masking Pack können sensible Produktionsdaten irreversibel mit fiktiven Daten ersetzt werden. Kopien von Produktionsdatenbanken mit anonymisierten Daten können so einfach und schnell für Test- und Entwicklungsumgebungen bereitgestellt werden und/oder zusätzlich ein Subset der Daten erzeugt werden. Voraussetzung für Data Masking und Subsetting ist der Oracle Enterprise Manager. Desweiteren muss ein Application Data Model (ADM) oder Anwendungsdatenmodell vorhanden sein beziehungsweise erstellt werden. Im Anwendungsdatenmodell wird die Liste der Anwendungen, Tabellen und Beziehungen zwischen Tabellenspalten gespeichert, die entweder im Data Dictionary deklariert sind, aus Anwendungsmetadaten importiert oder vom Benutzer angegeben werden. Das Anwendungsdatenmodell verwaltet vertrauliche Datentypen und die zugehörigen Spalten. Data Masking setzt ein solches Anwendungsdatenmodell voraus. um eine konsistente Maskierung sensibler Spalten und aller abhängigen Spalten sicherzustellen. Mitunter wird nach dem Unterschied zwischen Data Masking und Data Redaction, dem ASO Feature, gefragt. Diese Frage ist naheliegend, denn es geht in beiden Fällen um die Veränderung von Daten. Allerdings verändert Data Masking Daten permanent. Data Masking zielt darauf, in Entwicklungs- und Testumgebungen mit realistischen Daten arbeiten zu können, ohne diese mit dem gleichen Aufwand schützen zu müssen, der für die Originaldaten im Produktivsystem vorgeschrieben ist. Links aus älteren Posts: Oracle Data Redaction Wer kennt Label Security? Erzeugen einer Teilmenge von Daten (Data Subsetting)  Erzeugen eines Application Data Model (ADM) Sensible Spalten (sensitive columns) definieren und nutzen Reversibles Data Masking (encrypt/decrypt) Handbucheinträge Using Oracle Virtual Private Database to Control Data Access Transparent Sensitive Data Protection Policies with Data Redaction Data Masking and Subsetting OTN Database Security Start Page  

Wir werden immer wieder danach gefragt wie Anonymisierung in der Datenbank funktioniert. Je nach Anforderung bietet die Oracle Datenbank dazu verschiedene Möglichkeiten wie Virtual Private Database,...

SQL, PL/SQL und mehr

Oracle Text: 12.2 News, Tipps und Tricks auch für Einsteiger

Wie immer nach einem Release Upgrade stellt sich auch für Oracle Text Nutzer folgende Fragen: Was gibt es an Neuigkeiten in Oracle Text? Wo finde ich Informationen dazu? Wie immer gibt es die Möglichkeit einen Blick in die Handbücher zu werfen. In den Abschnitten "Changes in this Release for..." findet man im ​Text Application Developer's Guide und im Text References Guide Neuigkeiten und auch Information zu "deprecated features" als Linkverweis und als kurze Beschreibung. Neues in Oracle Text 12.2. gibt es übrigens in vielen Bereichen wie zum Beispiel SDATA, Wildcard Abfragen, NEAR Operator, Sentiment Analyse, Storage Präferenzen für Indexed Lookups um nur einige Erweiterungen zu nennen. Darüberhinaus gibt es seit April 2017 ein neues White Paper zum Thema "Oracle Text 12.2 New Features Adding more value to your textual assets", das sich auf einige wichtige 12.2. Features konzentriert. Im deutschsprachigen Textblog haben wir darüberhinaus begonnen einige 12.2 Neuigkeiten zu erläutern, die in den Blogposts "Suche in JSON Dokumenten" und "Mehr Performance für Wildcard Abfragen mit Reverse Token Index" nachzulesen sind. Weitere Posts zum Thema 12.2 werden folgen. Für alle diejenigen, die sich bisher noch nicht mit Oracle Text beschäftigt haben, noch einmal eine kurze Beschreibung: Oracle TEXT ist eine in die Datenbank integrierte Volltextrecherche, die in allen Datenbank- und Cloudeditionen enthalten ist und normalerweise ohne weitere Installation direkt zur Verfügung steht. (Hinweis: Einige wenige architekturbedingte Einschränkungen gibt es beim Exadata Express Cloud Service zu beachten. Informationen dazu finden sich hier.) Man kann in einem "normalen" Datenbankschema also sofort starten.  Möchte man sich über Oracle Text informieren, stehen folgende Informationen zur Verfügung OTN Seite: Oracle Text Oracle Textblog vom Product Manager Roger Ford: Oracle Search - Technical Tips  Tutorials auf Oracle Live SQL   Deutschsprachiger Oracle Textblog von Oracle Mitarbeitern: Oracle Tipps, Tricks und Best Practices Der deutschsprachige Textblog bietet sogar zusätzlich eine Art Inhaltsverzeichnis (Liste aller Posts), das nach verschiedenen Kategorien geordnet ist und einen guten Einstieg und Überblick geben kann.

Wie immer nach einem Release Upgrade stellt sich auch für Oracle Text Nutzer folgende Fragen: Was gibt es an Neuigkeiten in Oracle Text? Wo finde ich Informationen dazu? Wie immer gibt es die...

Advisor

Überprüfung der Datensicherheit mit Database Security Assessment Tool

Datenbanken sollten so konfiguriert werden, dass sie ausschließlich nur Funktionen zulassen, die für den Betrieb der Anwendungen beziehungsweise der Verfahren notwendig sind. Für jedes Datenbanksystem, welches personenbezogene Daten verarbeitet, muss ein Grundschutz implementiert werden. Insbesondere das im Mai 2018 in Kraft tretende EU-Datenschutzgesetz (englisch GDPR - general data protection regulation) stellt höhere Anforderungen an die Sicherheit bei der Verarbeitung personenbezogener Daten. Es ist für alle Unternehmen die in Geschäftsbeziehungen zu EU Bürgern stehen verpflichtend, unabhängig von ihrem Firmensitz. Um bei der Umsetzung notwendiger Datensicherheitsmaßnahmen zu helfen, gibt es ein Werkzeug zur Erkennung potenzieller Sicherheitsrisiken für Oracle Datenbanken -  das Oracle Database Security Assessment Tool (DBSAT). Es überprüft bis zu 71 Datenbankkonfigurationen und Sicherheitsempfehlungen gemäß Oracle Datenbanksicherheit Best Practices. Die hiermit aufgezeigten potenziellen Sicherheitsrisiken werden dokumentiert und können bei Bedarf und Notwendigkeit manuell behoben werden. Kurzfristige Erkennung und Behebung allgemeiner Risiken wird damit einfach möglich. Wo kann man DBSAT downloaden? Welche Ergebnisse gibt es? Antworten auf diese Fragen und weitere Informationen gibt es in unserem aktuellen Community Tipp - verfasst von unserem Kollegen Norman Sibbing.   

Datenbanken sollten so konfiguriert werden, dass sie ausschließlich nur Funktionen zulassen, die für den Betrieb der Anwendungen beziehungsweise der Verfahren notwendig sind. Für...