Sommerparty Banner

Das war die DBConcepts Sommer-Party 2021

Zum vierten Mal fand heuer wieder unsere legendäre DBConcepts Sommerparty in der La Creperie an der Alten Donau statt. Nach einem langen Jahr der Zwangspausierung haben wir eine Rekordzahl an Anmeldungen für unsere Sommer Party entgegengenommen und über 70 TeilnehmerInnen begrüßen können.

Doch diese Party war nicht nur besonders da es unsere erste nach dem Corona Jahr sein sollte. Gemeinsam mit unseren Kunden, Partnern und Kollegen haben wir das 20jährige Jubiläum der DBConcepts gefeiert, zwar nachgefeiert.. aber zumindest gebührend.

Gemeinsam haben wir einen entspannten Abend mit einigen Highlights verbracht. Als Willkommensgeschenk und gab es heuer nicht nur unsere sehr beliebten Sonnenbrillen, sondern auch ein großartiges Strandtuch mit Unterstützung der Arrow.

Nach Meteorologischer Ansage sollte unsere Tretbootchallenge auch dieses Jahr eher ins Wasser fallen, doch dieses Mal war auch für eine Alternative gesorgt – das erste DBConcepts PubQuiz.

Insgesamt 13 Teams haben es sich zur Aufgabe gemacht, die fünf verschiedenen Themenblöcke in kürzester Zeit zu beantworten. Die Themen rangierten von DBConcepts Wissen über Popkultur bis hin zu geografischen Kenntnissen. Die glorreichen Sieger durften sich über tolle Preise freuen!

Sehr besonders freut uns das begeisterte Feedback aller Gäste, die uns zur gelungen Sommer-Party gratuliert haben.

Nach der Sommer-Party ist immer vor der Sommer-Party.

In diesem Sinne freuen wir uns schon auf nächstes Jahr!

Oracle Lizenzierung News

Aktuelle Neuigkeiten in der Oracle Lizenzierung

Es gibt gute Neuigkeiten im Bereich Migration von Oracle Lizenzen!

Laut aktuellen Informationen von Oracle ist es seit 02. Juli 2021 nun nicht mehr notwendig bestehende, alte, Oracle Standard Edition (SE) bzw. Standard Edition One (SEO) Lizenzen (bis 12.1.0.1) bei Oracle auf aktuelle Standard Edition 2 (SE2) (ab 12.1.0.2) Lizenzen migrieren zu lassen. Damit entfällt für SEO Lizenzen auch die Supportpreiserhöhung und es wird somit einfacher und kostengünstiger.

Von nun an reicht es, wenn Sie beim Software Download aus der Oracle Delivery Cloud den aktuellen Lizenzbedingungen der SE2 einfach zustimmen.
Wollen Sie dennoch Ihre bestehenden und unter aufrechtem Support stehenden Oracle SE/SEO Lizenzen auf Oracle SE2 Lizenzen migrieren lassen, so können Sie das weiterhin beantragen. Auch in diesem Fall wird es für alle SE Editionen eine 1:1 Migration ohne Migrationskosten und Supportpreiserhöhung.

Bei Migrationen, die Änderungen der generellen Metriken oder ein Ändern der Lizenzstufe benötigen, wenden Sie sich bitte weiterhin an unsere Lizenzexperten.

Zusammenfassend kann man sagen, dass diese Nachrichten für Anwender sehr erfreulich sind.

Eine aktive Migration von SE/SEO auf SE2 ist nun nicht mehr erforderlich.

Weiters entfällt die, vorher obligatorische, Supportpreiserhöhung für SEO Lizenzen im Rahmen der Migration auf SE2.

Falls Sie noch Fragen zu Ihrer Oracle Lizenz oder einer Migration haben, kontaktieren Sie uns gerne direkt!

Sommerparty Banner

Anmeldung zur DBConcepts Sommer-Party 2021

Endlich ist es wieder soweit! Nach langem Warten, werden wir wieder unser DBConcepts Sommerfest veranstalten. Feiern Sie mit uns den Sommer ganz relaxed an der Alten Donau bei einem fantastischen Grill-Buffet und kühlen Getränken! Für besonders gute Laune sorgt auch heuer wieder die beliebte DBConcepts Tretboot-Challenge und als zusätzliches Schmankerl werden wir am Abend ein […]

Header Oracle SQL Model Clause

Oracle SQL Model Clause

Theorie und Aufbau

Die Oracle Model Clause ist seit Version 10g verfügbar, ihre teils hohe Komplexität und die dadurch bedingte anfangs recht steile Lernkurve macht sie aber trotzdem zu einem Nischen-Feature. Im Prinzip kann man mit diesem Feature einzelne Zellen direkt ansprechen und sehr gezielt differenzierte Berechnungen anstellen. Da man mit diesem Feature allerdings auch ein enorm mächtiges Werkzeug zur Verfügung hat, soll hier im Folgenden ein kurzer Einblick gegeben werden, wie man sich langsam an die ersten Anwendungsfälle wagen kann, ohne von dem schieren Umfang and Möglichkeiten erschlagen zu werden. Dabei wird Information aus der offiziellen Oracle Dokumentation zusammengefasst und mittels anschaulicher Beispiele in praktischer Umsetzung verdeutlicht.

Lt. Doku (https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6) ist der grundlegende Aufbau der gesamten Model Clause wie folgt:

Abbildung 1: Grundlegender Aufbau SELECT

Abbildung 1: Grundlegender Aufbau SELECT

Hier sieht man nach der Group By Clause die Model Clause. Diese ist wiederum wie folgt aufgebaut:

Abbildung 2: model_clause

Abbildung 2: model_clause

Hieran sieht man bereits, dass lediglich die Komponente main_model verpflichtend ist. Um den Einstieg so einfach wie möglich zu halten, betrachten wir in diesem Artikel auch nur das main_model:

Abbildung 3: main_model

Abbildung 3: main_model

Wir betrachten auch hier nur die verpflichtenden Aspekte, die model_column_clauses und die model_rules_clause:

Abbildung 4: model_column_clauses

Abbildung 4: model_column_clauses

Die Model Column Clause definiert die genutzten Spalten und wie die Spalten verwendet werden. Dabei gibt es 3 Gruppen:

PARTITION BY:

Wie auch bei analytischen Funktionen kann man die Datenmenge anhand von beliebigen Spalten (und theoretisch auch Ausdrücken wie Funktionen) in Gruppen aufteilen (=partitionieren). Im Beispiel am Ende wird darauf noch genauer eingegangen. Dieser Aspekt ist optional.

DIMENSION BY:

Die Spalten der Dimension identifizieren eindeutig eine Zeile innerhalb einer Parition (falls vorhanden). Man sieht an den Keywords bereits, dass das ganze Konzept aus dem Analytics Bereich kommt.

MEASURES:

Hier werden die tatsächlichen Spalten definiert auf denen Berechnungen durchgeführt werden.

Abbildung 5: model_rules_clause

Abbildung 5: model_rules_clause

In der Model Rules Clause werden die tatsächlichen Berechnungen definiert, die im Prinzip Zuweisungen mit einer linken Seite (Ziel der Zuweisung) und einer rechten Seite (Wert der zugewiesen wird) bestehen. Der obere Bereich der Abbildung 5 ist komplett optional, daher betrachten wir erneut lediglich den unteren Bereich. Der wichtige Punkt hierbei ist cell_assignement.

Abbildung 6: cell_assignement

Abbildung 6: cell_assignement

Wir werden uns vorerst mal dem einfachsten Fall widmen, der direkten Identifizierung einer oder mehrerer Zellen. Wie oben bereits beschrieben, definiert die Gesamtheit der Spalten welche als Dimensionen definiert wurden eindeutig eine Zeile (innerhalb einer Partition falls vorhanden). Damit wird eine Zelle oder eine Menge an Zellen eindeutig definiert, indem die Measure Spalte definiert wird und für die Dimensionsspalten Werte angegeben werden (mehrere Zellen können angegeben werden indem beispielsweise Wildcards genutzt werden). Zur Veranschaulichung beginnen wird direkt mit einem praktischen Beispiel.

Praktisches Beispiel

Die Basis für unser praktisches Beispiel ist die Tabelle Schulnoten. Erstellskript und Testdaten können am Ende des Blogeintrags heruntergeladen werden. Der Aufbau ist wie folgt:

create table SCHULNOTEN
(
  schulstufe     NUMBER,
  schueler       VARCHAR2(255),
  schuelernummer NUMBER,
  klasse         VARCHAR2(1),
  jahr           NUMBER,
  fach           VARCHAR2(30),
  note           NUMBER
)

Die Tabelle beinhaltet Schulnoten für Schüler in einer sehr vereinfachten Form. Die Testdaten beinhalten die Daten von jeweils 16 Schülern einer 3. Klasse für 3 Schulstufen sowie zwei 2. Klassen mit je 2 Schulstufen. Auf Basis dieser Daten sollen nun diverse Auswertungen durchgeführt werden. Starten wir mit einer simplen Abfrage: Für den Schüler mit der Nummer 1, für das Fach „Deutsch“, für die 4. Schulstufe im Jahr 2021 soll die voraussichtliche Note berechnet werden als gerundeter Durchschnittswert der Vorjahre. Natürlich kann man diese Berechnung auch über Aggregatsfunktionen berechnen, aber es dient als simpler Einstieg:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0)); 

Hier sehen wir uns die einzelnen Komponenten an:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'

Das ist die grundlegende Abfrage. Wenn die Model Clause verwendet wird, müssen ALLE Spalten, die hier abgefragt werden als PARTITION, DIMENSION oder MEASURE deklariert werden. Weiters müssen hier bei Verwendung von Alias-Bezeichnungen diese angegeben werden. Das bedeutet, dass z.B. folgende Statements nicht funktionieren werden (der fehlerhafte Teil ist jeweils fett markiert):

select schulstufe, klasse, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Die Spalte Klasse wird hier in keiner der drei Kategorien angeführt und führt daher zu einem ORA-32614: unzulässiger MODEL SELECT Ausdruck.

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler name, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Hier wird in den Dimensionen der Spalte SCHUELER der Alias NAME gegeben, daher müsste auch im Select-Teil die Bezeichnung NAME verwendet werden.
Ansonsten folgt der Select-Teil dem üblichen Standard. Wir schränken die Datenmenge hier per Where Clause bereits stark ein. Damit kommen wir zum nächsten Punkt:

MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)

Das Keyword MODEL leitet die Model Clause ein. Da PARTITION BY optional ist und wir ohnehin auf genau einen Schüler und ein Fach einschränken erübrigt sich ihr Nutzen hier. Die Abschnitte für DIMENSION BY und MEASURES sind klar ersichtlich, wir haben also 3 Dimensionen und 1 Measure (Faktum wäre das häufig benutzte deutsche Wort dafür). Hier können für die einzelnen Spalten Alias-Bezeichnungen vergeben werden, wie im Fehlerbeispiel oben ersichtlich war. Diese Alias-Bezeichnungen sind dann durchgehend zu nutzen, auch beim nächsten und in diesem Fall letzten Abschnitt:

(note [ 4, 'Anton Anger', 2021 ] =
round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Zur besseren Lesbarkeit wurden die linke und rechte Seite getrennt. Hier sieht man eine exakte Zuweisung zu einer Zelle auf der linken Seite (in diesem Fall, würde es mehrere Schüler mit dem exakt gleichen Namen geben würde die Zuweisung mehrere Zellen befüllen) sowie mehrere exakte Zuweisungen (oder besser Abfragen) auf der rechten. In einfachen Worten steht hier:
Addiere die Noten des angegebenen Schülers aus Stufe 3 im Jahr 2020 bzw. aus Stufe 2 im Jahr 2019 und Stufe 1 im Jahr 2018 und dividiere die Summe durch 3 – eine manuelle Durchschnittsberechnung. Da wir in diesem Select Daten erhalten die es in der Tabelle gar nicht gibt, wäre eine Umsetzung ohne Model Clause nur über Analytic Functions oder Mengenoperationen wie UNION möglich. Beides würde mehr Code benötigen.
Nun kann man den Sinn einer manuellen Durchschnittsberechnung hinterfragen, wenn Oracle dafür praktische Aggregationsfunktionen anbietet und das durchaus zu Recht. Würden wir das Statement anpassen (weil z.B. nicht von 3 sondern von mehreren 100 Zeilen der Durchschnitt berechnet werden soll) würde es wie folgt aussehen:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round(avg(note) [ any, 'Anton Anger', jahr between 2018 and 2020 ],0));

Das wirkt gleich deutlich sauberer und einfacher. Vorsicht bei der Setzung der Klammern, die Aggregationsfunktion wird NUR um die Measure Spaltenbezeichnung gemacht, das Dimensions-Array in eckigen Klammern steht danach (wird das nicht gemacht gibt die Datenbank einen Fehler zurück: ORA-00934: Gruppenfunktion hier nicht zulässig).

Ein weiterer Aspekt hier sind der Wildcard Operator und eine Range Angabe, beides Mittel, um mehrere Zeilen auf einmal anzusprechen (was hier durch Verwendung der Aggregationsfunktion Sinn macht).

Der Wildcard Operator any bewirkt genau das was das Keyword vermuten lässt: Diese Spalte wird nicht betrachtet bei der Berechnung. Die Range-Angabe between 2018 and 2020 wiederum funktioniert exakt gleich wie eine entsprechende Where Clause.

Bevor wir die Model Clause gewinnbringender einsetzen, noch eine kurze Erklärung zur Zuweisung der Werte der Dimensionen in den Regeln. Die Beispiele bisher nutzten fast ausschließlich eine positionelle Referenz, das bedeutet die Werte stehen an der Stelle im Array an welcher in der Dimensionsdefinition die jeweilige Spalte steht.

Abbildung 7: Zuweisung der Werte der Dimensionen

Abbildung 7: Zuweisung der Werte der Dimensionen

Alternativ dazu kann man auch wie beim Aufruf einer PL/SQL Prozedur eine symbolische Referenz verwenden. Bei der Range-Angabe wie oben MUSS das gemacht werden. Wenn wir das nun für alle Stellen so umsetzen würde es wie folgt aussehen:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ schulstufe=4, schueler='Anton Anger', jahr=2021 ] = round(avg(note) [ schulstufe is any, schueler='Anton Anger', jahr between 2018 and 2020 ],0));

VORSICHT: Wenn symbolische Referenzen verwendet werden, dann können keine neuen Zeilen eingefügt werden. Das oben angeführte Statement ergibt also keine neue Zeile für 2021, das gilt allerdings nur für die linke Seite, folgendes Statement funktioniert also dann wieder korrekt:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round(avg(note) [ schulstufe is any, schueler='Anton Anger', jahr between 2018 and 2020 ],0));

So weit war alles sehr simpel und eigentlich nicht des Aufwands einer Model Clause wert. Dehnen wir das Thema aus und lassen uns für den Schüler mit Nummer 1 für alle Fächer die geschätzten Noten für das Jahr 2021 ausgeben.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
 where schuelernummer = 1
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr, fach)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ any, 'Anton Anger', jahr between 2018 and 2020 , cv(fach)],0))
   order by 1,4;

Um alle Fächer zu berechnen, muss eine Schleife genutzt werden, das ist dieser Teil:

for fach in (select distinct fach from schulnoten)

Grundsätzlich könnte auch der Any Operator genutzt werden, aber dann gibt die Abfrage nur Zeilen zurück, welche bereits existieren, ich könnte also nur die Noten aus den Jahren 2018 – 2020 berechnen lassen aber keine neuen Zeilen für 2021. Aus diesem Grund muss das ganze über eine Schleife gemacht werden. In diesem Zusammenhang ist dann auch noch folgender Operator wichtig:

cv(fach)

Der CV() Operator gibt den Current-Value, den aktuellen Wert zurück. Im Rahmen dieser Schleife gibt er also bei jedem Durchlauf den Wert des Durchlaufs zurück. Mit diesen beiden Informationen können wir nun für alle Schüler und Fächer der Klasse die neuen Noten berechnen.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
  where schuelernummer in (select schuelernummer from schulnoten where schulstufe = 3)
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr, fach)
   MEASURES(note)
   (note [ 4, for schueler in (select distinct schueler from schulnoten), 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ any, cv(schueler), jahr between 2018 and 2020 , cv(fach)],0))
   order by 1,2,4;

An diesem Beispiel erkennt man auch gut, dass die Model Clause nur auf Daten zugreifen kann, welche auf Basis der Where-Bedingung existieren. Würde man die Where Clause statt mit einer IN Clause mit einem direkten Filter auf die Schulstufe machen, würde man nur Daten für Schulstufe 3 und 4 erhalten und der Durchschnittswert basiert nur auf den Werten aus Schulstufe 3.
Soweit können wir nun für alle Fächer für jeden Schüler einer bestimmten Klasse/Schulstufe eine simple Voraussage der Noten in der nächsten Schulstufe abfragen. Idealerweise können wir das allerdings für jeden Schüler, unabhängig von der Schulstufe für jedes Fach. Das wäre der gewünschte Endzustand. Ein mögliches Statement dafür könnte wie folgt aussehen:

select schuelernummer,schulstufe, schueler, klasse, jahr, fach, note
  from schulnoten
 MODEL
   PARTITION BY(schuelernummer, schueler, klasse) 
   DIMENSION BY(jahr, fach)
   MEASURES(schulstufe,note)
   (note [ 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ jahr between 2018 and 2020 , cv(fach)],0),
    schulstufe [ 2021, for fach in (select distinct fach from schulnoten) ] = max(schulstufe) [ jahr between 2018 and 2020 , cv(fach)] + 1)
   order by 2,5,3,6;

Da sinnvollerweise auch Daten zu den Schulstufen der jeweiligen Jahre vorhanden sein sollen und diese aber nicht mehr hartcodiert übergeben werden können ist die Schulstufe in die Measures verschoben würden. Zusätzlich werden die Daten mittels PARTITION BY nach Schüler aufgeteilt, das hat den simplen Grund, dass die Noten immer nur in Abhängigkeit der Noten des jeweiligen Schülers berechnet werden, das erspart auch mühsame Arbeit bei der Dimensionsdefinition. Die Klasse ist kein echtes Partitionierungskriterium, da sie aber ausgegeben werden soll kann sie problemlos hier mit eingetragen werden. Für die Schulstufe wird eine neue Regel erfasst, welche den Maximalwert des jeweiligen Schülers für den jeweiligen Zeitraum berechnet und um eins erhöht. Da wird genau genommen auch jedes Fach für sich betrachten könnten wir hier aber auch einfach das Fach von den Dimensionen in die Partition Clause schieben und sparen uns damit die For Schleife, das würde dann so aussehen:

select schuelernummer, schulstufe, schueler, klasse, jahr, fach, note
  from schulnoten
 MODEL
   PARTITION BY(schuelernummer, schueler, klasse, fach) 
   DIMENSION BY(jahr)
   MEASURES(schulstufe,note)
   (note [ 2021 ] = round(avg(note) [ jahr between 2018 and 2020 ],0),
    schulstufe [ 2021 ] = max(schulstufe) [ jahr between 2018 and 2020 ] + 1)
   order by 2,5,3,6;

Nochmal etwas simpler, denn genau genommen ist das Jahr die einzige Variable die wir hartcodiert setzen.
Einen letzten Fall sehen wir uns noch an. Die Kinder bekommen in der 4. Schulstufe statt Sachunterricht die beiden Fächer Biologie und Physik. Die Note für Biologie soll sich aus zu je 50% aus den Noten der Fächer Deutsch und Sachunterricht der 3. Schulstufe berechnen, die Note für Physik zu je 50% aus den Noten der Fächer Mathematik und Sachunterricht der 3. Schulstufe. Wir betrachten also nur die Schüler der 3. Schulstufe in diesem Fall. Das Statement dafür könnte man zum Beispiel wie folgt strukturieren.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
  where schuelernummer in (select schuelernummer from schulnoten where schulstufe = 3)
 MODEL
   PARTITION BY(schueler)
   DIMENSION BY(schulstufe, jahr, fach)
   MEASURES(note)
   (note [ 4, 2021, for fach in (select distinct fach from schulnoten where fach <> 'Sachunterricht') ] = round(avg(note) [ any, jahr between 2018 and 2020 , cv(fach)],0),
    note [ 4, 2021, 'Biologie' ] = round(( note[ 3, 2020, 'Sachunterricht' ] + note[ 3, 2020, 'Deutsch' ] ) / 2,0),
    note [ 4, 2021, 'Physik' ] = round(( note[ 3, 2020, 'Sachunterricht' ] + note[ 3, 2020, 'Mathematik' ] ) / 2,0))
   order by 1,2,4;

Eine Kombination des vorigen mit diesem Statement ist nicht möglich, da die Schulstufe einmal als Dimension herangezogen wird und einmal als Measure berechnet wird. In diesem Fall kann ich also nur einen von beiden Fällen innerhalb einer Model Clause abdecken.
Wie bereits eingangs erwähnt, wurden in diesem Artikel lediglich die grundlegenden Funktionen der Model Clause abgedeckt. Weitere Artikel, welche die zusätzlichen Möglichkeiten erklären, werden folgen.

Vereinfachung, Automatisierung und schnelle Disaster-Recovery für Oracle Datenbanken

Commvault Oracle LiveSync

Disaster Recovery heißt, für den Fall der Fälle vorzusorgen. Ihr Unternehmen kann jederzeit einer Katastrophe zum Opfer fallen – ob durch Menschenhand oder Naturgewalten. Diese möglichen Katastrophen lasten auch stark auf Ihrer internen IT, denn Sie sind in der Regel dafür verantwortlich.

Im Katastrophenfall kommt Ihr Unternehmen nur wieder auf die Beine, wenn es über einen sorgfältig zusammengestellten und einsatzbereiten Disaster-Recovery-Plan verfügt – und das sollte kein beliebiger Plan sein.

Einsatz von CommVault Live Sync Replication für Oracle

 

  • Für eine schnelle Wiederherstellung kann ein Disaster Recovery-Standort mithilfe des grundlegenden Live Sync-Ablaufs lokal verwaltet werden.
  • Für Wiederherstellung Szenarien, in denen der primäre Standort nicht verfügbar ist, kann ein Disaster Recovery-Standort auf Basis einer DASH Copy verwendet werden.
  • Bei beiden Ansätzen kann Live Sync unmittelbar nach Sicherungen oder nach Zeitplan (täglich, wöchentlich, monatlich oder jährlich) ausgeführt werden.

Basic Live Sync Flow

Die grundlegende Basic Live Sync-Konfiguration dupliziert Oracle Datenbanken von Sicherungen auf den Disaster Recovery-Standort fortlaufend. Live Sync repliziert auch Änderungen Oracle Datenbanken, die während der Sicherung der Transaktionslogs erfasst werden und stellt diese auf dem Disaster Recovery-Standort zur Verfügung.

Commvault

Live Sync Flow mit DASH Copy

Bei Verwendung einer DASH Copy mit De-duplizierung können laufende Änderungen an den Remote Disaster Standort effizienter übertragen werden, da nur die geänderten Blöcke an den Remote Media Agent gesendet werden. Eine DASH Kopie reduziert den Datenverkehr und ist so ideal für Standorte die über ein WAN (Wide Area Netzwerk) angebunden sind. Das Verfahren kann auch verwendet werden, um mehrere Disaster Standorte zu betreiben.

Commvault 2

Erreicht wird dies durch eine einfache Konfiguration, welche definiert welche Storage Policy Kopie für den Live Sync herangezogen werden soll.

Der Sync Prozess im Detail

CommVault verwendet für den eigentlich Sync Prozess das Oracle RMAN Interface.

Schritt 1: Herunterfahren der Datenbank und Katalogisieren des Datenbank Controlfiles.

Schritt 2: Die Datenbank wird auf die SCN vorgerollt, welche CommVault durch das Backup der Primären Datenbank erkannt hat.

Schritt 3: Transaktionslogs werden von der DR Kopie gelöscht, so wird ein Überlaufen eines Filesystem verhindert.

Schritt 4: Die Datenbank wird wieder ReadOnly geöffnet und steht für Abfragen bereit.

Rman Script:
[ shutdown immediate;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' 
{…}
startup force mount;
run{
catalog device type 'sbt_tape' backuppiece '2992_PROD_atvv6fuf_2397_1_1','c-483704462-20210518-57';
}
exit;
]
{…]
recover database until scn 3467578 
 delete archivelog ;
 sql "alter database open read only";
}
exit;

Monitoring

Das Monitoring kann bequem aus der CommVault Admin Konsole erfolgen. Hier kann mit einem Blick der aktuelle Status oder zum Beispiel der Zeitpunkt der letzten Synchronisation eingesehen werden.

Bild5 Monitoring

Die einzelnen Replikationsjobs sind zentral im CommVault einsehbar. Die Standardalarmierungen welche CommVault bereitstellt können hier wiederverwendet werden.

Das Replikationsfenster

Das Replikationsfenster kann sehr einfach aus der CommVault Admin Console definiert werden.

Nutzen des Disaster Standorts für Abfragen

Während sich die Datenbank außerhalb des Replikationsfenster befindet, steht die Datenbank am Disaster Standort „ReadOnly“ zu Verfügung.
Innerhalb des Replikationsfenster wird der Status der Datenbank am Remote Standort durch CommVault auf mount geändert, bevor die Transaktionslogs eingespielt werden. Ist der Vorgang abgeschlossen wird die Datenbank durch CommVault wieder „ReadOnly“ geöffnet. Sobald ein weiters Transaktionslog Backup erstellt wird und auf der für den Live Sync bestimmten Storage Copy bereit stehen wird seitens CommVault automatisch der nächste Replikations Vorgang durchgeführt.

Failover und Failback

Ein Failover und ein Failback wird derzeit von CommVault nicht unterstützt. Trotzdem ist ein Failover schnell und einfach durchzuführen.

Schritte:

  • Prüfen ob die Replikation synchron ist.
  • Stoppen und löschen der Replikationsgruppe in CommVault
  • Öffnen der Datenbank mit der resetlogs option

Alternativ können diese Schritte auch in einem CommVault Workflow realisiert werden. Dadurch kann das Failover in sehr kurzer Zeit auch in größeren Umgebungen durchgeführt werden. Das Failback wiederum ist ein Neuaufbau der ehemaligen Primären Seite. Dieser Vorgang wird aber durch CommVault stark automatisiert und vereinfacht.

Warum nicht Oracle Dataguard?

Oracle Dataguard ist eine Funktion der Oracle Enterprise Edition. Lizenznutzer, die sich in diesem Lizenzsegment bewegen sollten, auch auf Dataguard setzten. Der wahre Vorteil liegt hier bei den Besitzern einer Oracle Standard Edition. Hier stellt das Feature eine echte Alternative bereit. Da Oracle Dataguard ein Enterprise Edition Feature ist, müssen Oracle SE Kunden auf eine Scripting Lösung oder eine Disaster Recovery Software Lösung eines weiteren Herstellers zurückgreifen. CommVault Live Sync fehlen derzeit noch Funktionen wie z.B. ein Datenbank Failover aus der CommVault GUI, ist aber in vielen Fällen eine Kostengünstiger Variante. Viele Unternehmen welche heute schon CommVault als Backup Lösung einsetzten, wissen oft nicht, dass sie dieses Feature einsetzten, können um ihre Datenbank Lösung damit Disaster Tolerant auszulegen.

Frequently Asked Questions (FAQ)

Q: Welche Oracle Editionen werden unterstützt.

A: Es werden sowohl Oracle Standard Edition und Oracle Enterprise Edition unterstützt.

Q: Welche Oracle Real Application Cluster unterstützt.

A: Ja, Oracle RAC wird unterstützt.

Q: Wird eine Oracle Lizenz am Disaster Standort benötigt.

A: Ja, der remote Standort muss lizenziert werden.

Q: Werden auch andere Datenbank Hersteller unterstützt.

A: Ja es werden unterschiedliche Hersteller unterstützt. Darunter fallen unter anderem DB2, Microsoft SQL oder PostgreSQL.

Q: Werden auch andere Produkte unterstützt.

A: Ja es wird eine Vielzahl von Produkten unterstützt. Darunter fallen unter anderem VMware oder Cloud Plattformen wie Amazon, Oracle Cloud Infrastructure oder Microsoft Azure. In vielen Fällen wird hier auch ein Failover oder Failback unterstützt. Diese Option macht CommVault besonders für Hybird Cloud Implementierungen interessant.

 

Blogpost ODI.jpg

ODI // Getting an OdiInstance

Bisher musste man, wenn man bei der Ausführung einer ODI-Procedure der Technologie „Groovy“ am Agent die SDK ansprechen wollte, manuell im Sourcecode eine Connection zum Master- und Workrepository herstellen. Das macht es notwendig, dass die Connect-Info in einer Konfiguration hinterlegt werden musste.
Seit dem ODI der Version 12.2 gibt es aber eine odiRef-Funktion, die all diesen Aufwand obsolet macht und eine Instanz der Klasse OdiInstance direkt verfügbar macht.
Im Sourcecode schaut der Unterschied folgendermaßen aus:

pre ODI 12.2:

// Master Respository
String masterRepositoryJdbcDriver = "oracle.jdbc.OracleDriver";
masterRepositoryJndiName = null; // if agent is running on WLS you could use a JDNI data source for repo connection
MasterRepositoryDbInfo mRepDbInfo = null 
if (masterRepositoryJndiName)
  mRepDbInfo = new MasterRepositoryDbInfo(masterRepositoryJndiName, new PoolingAttributes()); 
else {
  masterRepositoryJdbcUrl      = "jdbc:oracle:thin:@host:port/service";
  masterRepositoryJdbcUser     = "ODI_REPO_SCHEMA";
  masterRepositoryJdbcPassword = "*****";
  mRepDbInfo = new MasterRepositoryDbInfo(masterRepositoryJdbcUrl, masterRepositoryJdbcDriver, masterRepositoryJdbcUser, masterRepositoryJdbcPassword.toCharArray(), new PoolingAttributes());
}

// Work Repository
workRepositoryName = "WORKREP";
WorkRepositoryDbInfo wRepDbInfo= new WorkRepositoryDbInfo(workRepositoryName, new PoolingAttributes());

// ODI Instance
OdiInstance odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(mRepDbInfo, wRepDbInfo));

// Authentication
odiUser = "SUPERVISOR";
odiPassword = "*****";
Authentication auth = odiInstance.getSecurityManager().createAuthentication(odiSupervisorUser, odiSupervisorPassword.toCharArray());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);

// Transaction
ITransactionDefinition txnDef = new DefaultTransactionDefinition();
ITransactionManager tm = odiInstance.getTransactionManager();
tme = odiInstance.getTransactionalEntityManager();
ITransactionStatus txnStatus = tm.getTransaction(txnDef);

//
// ... place your SDK code here ...
//

tm.commit(txnStatus); // or tm.rollback(txnStatus);
auth.close();
odiInstance.close();

ab ODI 12.2:

OdiInstance odiInstance = odiRef.getOdiInstance();

// Transaction
ITransactionDefinition txnDef = new DefaultTransactionDefinition();
ITransactionManager tm = odiInstance.getTransactionManager();
tme = odiInstance.getTransactionalEntityManager();
ITransactionStatus txnStatus = tm.getTransaction(txnDef);

//
// ... place your SDK code here ...
//

tm.commit(txnStatus); // or tm.rollback(txnStatus);
odiInstance.close();

 

How to copy LOBs

The Issue

Assume we must copy a table that contains a LOB. There can be different reasons for this. For example, if the table is too fragmented or if there is no more space in the current tablespace.

The straightforward approach with Insert / * + append parallel (n) enable_parallel_dml * / simply to copy the data fails, however. It takes a long time at first. You can see that the time is spent inserting and not reading. How you copy the LOBS is crucial. The rest of the time is comparatively negligible.

The Insert .. Select approach has another problem: There are no partial results. Either you succeed or you have to start all over again.

Searching the Internet I found a blog by Marek Läll that deals with the subject of copying LOBs [1].

The core of the matter is that the LOB locator (LOB address) has not yet been determined at the time of the insert. A new LOB must be created, which means you also need a new locator. As a result, the LOB to be copied is first written to the TEMP tablespace. Then the new locator is determined and then the LOB is copied again from the TEMP tablespace to the actual target. Läll now suggests another way: First an empty LOB is written, its locator is then read via SELECT .. FOR UPDATE. The source LOB can be copied there in one go. This saves you from writing and reading the LOB, which is important. As already mentioned, the most important thing for efficiency is how the LOBs are treated.

I implemented the code in PL / SQL and optimized it a bit, rewritten it to BULK and avoided the SELECT .. FOR UPDATE via the returning clause.

The code is very efficient compared to other approaches I tested. In my real application, I can easily get to 1.5 million rows / hour with a parallel 24.

Copy Part

To illustrate my code let us assume this simple table:

CREATE table doc_table( doc_id Number,
                        document BLOB); 

The table has a BLOB column called document.

DECLARE
   TYPE t_BLOB_tab IS TABLE OF BLOB; 
   v_blob t_BLOB_tab;
   v_blob_length NUMBER;
   CURSOR c1 is
      SELECT /*+ noparallel */ doc_id , document   -- 1: noparallel hint
	 FROM doc_table 
   	WHERE ROWID BETWEEN :start_id AND :end_id;  -- 2: Start and End Rowid
   TYPE c1_tab IS TABLE OF c1%rowtype;
   v_c1_tab c1_tab;	
   c_limit PLS_INTEGER := 10000;			 
 BEGIN 
 OPEN c1;
 LOOP
	 FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
	 EXIT WHEN v_c1_tab.COUNT=0;
	 FORALL i IN 1 .. v_c1_tab.COUNT
	    INSERT INTO doc_table_new (doc_id , document P) -- 3: Conventional Insert
			   VALUES (v_c1_tab(i).doc_id, empty_blob())
                       RETURNING document BULK COLLECT INTO v_blob; -- 4: Loblocator of new LOB
	 FOR i IN 1 .. v_c1_tab.COUNT 
	 LOOP
	    v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
	    IF nvl(v_blob_length,0) > 0 THEN -- 5: DBMS_LOB.COPY will throw an exception
		DBMS_LOB.COPY(v_blob(i),       -- for empty LOBS
                           v_c1_tab(i).document,
				 v_blob_length);
	    END IF;
	 END LOOP;
	 COMMIT; 
 END LOOP;
 COMMIT;
END;
/

Annotations:

1.       The anonymous block is parallelized in the next step via DBMS_PARALLEL_EXECUTE. It would be illogical to parallelize again within a parallel process.

2.       The start and end id must be used in the central select. They must not be declared, they are set from the DBMS_PARALLEL_EXECUTE framework.

3.       A direct path write would be possible here using the APPEND_VALUES hint. I refrained from it so as not to provoke an exclusive table lock. I have not tested whether this actually would be the case. I am so satisfied with the performance of the solution described here that I consider a test to be unnecessary.

4.       The return saves a SELECT .. FOR UPDATE.

5.       DBMS_LOB.COPY is the fastest way to copy and seems to use a direct path.

Parallelize

I could have called a stored function via parallel SQL in order to parallelize the approach. The decision to use DBMS_PARALLEL_EXECUTE was rather by instinct. There are some objective points for PL/SQL parallelization, however. E.g. a stored function would have resulted in many context switches. DBMS_PARALLEL_EXECUTE allows you to stay in PL / SQL. In addition, the code below will also work with Standard Edition.

I also did some tests with parallel SQL and functions, but never got them to scale.  I would not exclude that there is a better approach than the one I present here. However, compared to the alternatives I have seen I rather like the approach presented below.

Here is the code for parallelization, I highlighted the anonymous block that we discussed in the previous paragraph.

DECLARE
 l_sql_stmt CONSTANT VARCHAR2 ( 20000 ) := 
	 q'[DECLARE
		 TYPE t_BLOB_tab IS TABLE OF BLOB; 
		 v_blob t_BLOB_tab;
		 v_blob_length NUMBER;
		 CURSOR c1 is
		    SELECT /*+ noparallel */ doc_id , document 
		      FROM doc_table 
		     WHERE ROWID BETWEEN :start_id AND :end_id;
		 TYPE c1_tab IS TABLE OF c1%rowtype;
		v_c1_tab c1_tab;	
        c_limit PLS_INTEGER := 10000;			
	 BEGIN 
	 OPEN c1;
	 LOOP
		 FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
		 EXIT WHEN v_c1_tab.COUNT=0;
		 FORALL i IN 1 .. v_c1_tab.COUNT
		    INSERT INTO doc_table (doc_id , document) 
				   VALUES (v_c1_tab(i)."doc_id", empty_blob()) 
                             RETURNING document BULK COLLECT INTO v_blob; 
		 FOR i IN 1 .. v_c1_tab.COUNT 
		 LOOP
			 v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
			 IF nvl(v_blob_length,0) > 0 THEN
				DBMS_LOB.COPY(v_blob(i),
							 v_c1_tab(i).document,
							 v_blob_length);
			 END IF;
		 END LOOP;
		 COMMIT; 
	 END LOOP;
	 COMMIT;
	END; ]';

 l_chunk_sql CONSTANT VARCHAR2 ( 10000 ) :=       -- 1: chunking statement. Breaks the input data
	 q'[SELECT min(r) start_id, max(r) end_id  -- into equal size pieces
		 FROM (
		SELECT ntile(10) over (order by rowid) grp, rowid r –- 2: 10 chunks will be produced
		 FROM doc_table                                     -- this can be equal or a multiple
		 )                                                -- of the parallel_level
	 GROUP BY grp]';
 l_try INTEGER;
 l_status INTEGER;
 l_task_name CONSTANT VARCHAR2( 20 ) := 'BLOB_MOVE';
BEGIN
 BEGIN
 dbms_parallel_execute.drop_task( l_task_name );
 EXCEPTION
 WHEN others then
 null;
 END;
 dbms_parallel_execute.create_task( l_task_name );
 dbms_parallel_execute.create_chunks_by_sql(l_task_name, l_chunk_sql, true);

 dbms_parallel_execute.run_task(
 task_name => l_task_name,
 sql_stmt  => l_sql_stmt,
 language_flag => dbms_sql.native,
 parallel_level => 10 –- 3: that many processes will be generated                 
 );
 
 dbms_output.put_line( 'DONE..' || dbms_parallel_execute.task_status(l_task_name));
 
END;
/

Annotations:

  1. There are several ways to divide the work. The chunking query is the most flexible. The search conditions here must also be found again in cursor c1.
  2. Ntile (10) means that the result of the query will hopefully be divided into 10 equal sections.

 

References:

Wie man am besten einen LOB kopiert

Das Grundproblem

Wir müssen eine Tabelle, die einen LOB enthält, umkopieren. Das kann unterschiedliche Gründe haben. Beispielsweise wenn die Tabelle zu fragmentiert ist, oder wenn auf dem aktuellen Tablespace kein Platz mehr ist.

Der gradlinige Ansatz mit Insert /*+ append parallel(n) enable_parallel_dml */ die Daten einfach umzukopieren scheitert aber.  Es dauert zunächst sehr lange. Man kann erkennen, dass die Zeit beim Einfügen und nicht beim Lesen anfällt.  Hier wiederum ist das Kopieren der LOBS entscheidend. Der Rest der Zeit fällt vergleichsweise kaum ins Gewicht.

Noch ein Problem hat der Insert .. Select Ansatz: Es gibt keine Teilergebnisse. Entweder es geht alles gut, oder man muss wieder von vorne anfangen.

Beim Suchen im Internet fand ich einen Blog von Marek Läll, der sich mit dem Thema LOB kopieren befasst [1].

Der Kern der Sache ist, dass der LOB Locator (LOB Adresse) zum Zeitpunkt des Inserts noch nicht feststeht. Es muss ein neuer LOB angelegt werden, das heißt man braucht auch einen neuen Locator. In der Folge wird der zu kopierende LOB zunächst in den TEMP Tablespace geschrieben wird. Dann wird der neue Locator ermittelt und dann wird der LOB vom TEMP Tablespace noch einmal in das eigentliche Ziel kopiert. Läll schlägt jetzt einen anderen Weg vor: Es wird zunächst ein empty LOB geschrieben, dessen Locator wird dann über SELECT .. FOR UPDATE gelesen. Dorthin kann der Quell LOB in einem Zug kopiert werden. Dadurch erspart man sich einmal Schreiben und Lesen des LOB, was wichtig ist. Wie schon erwähnt zählt für die Effizienz vor allem wie die LOBs behandelt werden.

Ich habe den Code in PL/SQL realisiert und noch etwas optimiert, also auf BULK umgeschrieben und den SELECT .. FOR UPDATE über die returning Klausel vermieden.

Der Code ist sehr effizient verglichen mit anderen Ansätzen, die ich getestet habe. In meinem echten Anwendungsfall komme ich locker bei parallel 24 auf 1,5 Millionen Rows/Stunde.

Kopieren

Um das folgende Beispiel verständlich zu machen, erstelle ich einmal die folgende einfache Tabelle, die kopiert werden soll:

CREATE table doc_table( doc_id Number,
                        document BLOB); 

Die Tabelle hat eine BLOB Spalte namens document.

DECLARE
   TYPE t_BLOB_tab IS TABLE OF BLOB; 
   v_blob t_BLOB_tab;
   v_blob_length NUMBER;
   CURSOR c1 is
      SELECT /*+ noparallel */ doc_id , document   -- 1: noparallel hint
	 FROM doc_table 
   	WHERE ROWID BETWEEN :start_id AND :end_id;  -- 2: Start and End Rowid
   TYPE c1_tab IS TABLE OF c1%rowtype;
   v_c1_tab c1_tab;	
   c_limit PLS_INTEGER := 10000;			 
 BEGIN 
 OPEN c1;
 LOOP
	 FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
	 EXIT WHEN v_c1_tab.COUNT=0;
	 FORALL i IN 1 .. v_c1_tab.COUNT
	    INSERT INTO doc_table_new (doc_id , document P) -- 3: Conventional Insert
			   VALUES (v_c1_tab(i).doc_id, empty_blob())
                       RETURNING document BULK COLLECT INTO v_blob; -- 4: Loblocator of new LOB
	 FOR i IN 1 .. v_c1_tab.COUNT 
	 LOOP
	    v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
	    IF nvl(v_blob_length,0) > 0 THEN -- 5: DBMS_LOB.COPY will throw an exception
		DBMS_LOB.COPY(v_blob(i),       -- for empty LOBS
                           v_c1_tab(i).document,
				 v_blob_length);
	    END IF;
	 END LOOP;
	 COMMIT; 
 END LOOP;
 COMMIT;
END;
/

Zu den Kommentaren:

  1. Der anonymous Block wird im nächsten Schritt über DBMS_PARALLEL_EXECUTE parallelisiert. Es wäre unlogisch innerhalb eines parallelen Prozesses noch einmal zu parallelisieren.
  2. Die Start – und die End id müssen im zentralen Select verwendet werden. Man darf sie nicht definieren, sie werden über DBMS_PARALLEL_EXECUTE gesetzt.
  3. Hier wäre über den Hint APPEND_VALUES ein direct path write möglich. Ich habe davon abgesehen, um nicht einen exclusive table lock zu provozieren. Ob dies tatsächlich der Fall wäre, habe ich nicht getestet. Ich bin mit der Perfomance der hier beschrieben Lösung so zufrieden, dass ich einen Test für unnötig erachte.
  4. Das Returning erspart einen SELECT .. FOR UPDATE.
  5. COPY ist die schnellste Art zu kopieren und scheint einen direct path zu verwenden.

 

Parallelisieren

Hier hätte man auch über paralleles SQL eine stored function rufen können, um die Lösung zu parallelisieren. Die Entscheidung für DBMS_PARALLEL_EXECUTE war eher instinktiv begründet. Jedoch wäre es bei einer stored Function zu sehr vielen context Switches gekommen.  DBMS_PARALLEL_EXECUTE erlaubt es in PL/SQL zu bleiben.

Einige Tests mit parallelem SQL waren in der Tat auch wenig effizient.

Hier noch der Code zum Parallelisieren, den anonymen Block, den wir besprochen haben, habe ich markiert.

DECLARE
 l_sql_stmt CONSTANT VARCHAR2 ( 20000 ) := 
	 q'[DECLARE
		 TYPE t_BLOB_tab IS TABLE OF BLOB; 
		 v_blob t_BLOB_tab;
		 v_blob_length NUMBER;
		 CURSOR c1 is
		    SELECT /*+ noparallel */ doc_id , document 
		      FROM doc_table 
		     WHERE ROWID BETWEEN :start_id AND :end_id;
		 TYPE c1_tab IS TABLE OF c1%rowtype;
		v_c1_tab c1_tab;	
        c_limit PLS_INTEGER := 10000;			
	 BEGIN 
	 OPEN c1;
	 LOOP
		 FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
		 EXIT WHEN v_c1_tab.COUNT=0;
		 FORALL i IN 1 .. v_c1_tab.COUNT
		    INSERT INTO doc_table (doc_id , document) 
				   VALUES (v_c1_tab(i)."doc_id", empty_blob()) 
                             RETURNING document BULK COLLECT INTO v_blob; 
		 FOR i IN 1 .. v_c1_tab.COUNT 
		 LOOP
			 v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
			 IF nvl(v_blob_length,0) > 0 THEN
				DBMS_LOB.COPY(v_blob(i),
							 v_c1_tab(i).document,
							 v_blob_length);
			 END IF;
		 END LOOP;
		 COMMIT; 
	 END LOOP;
	 COMMIT;
	END; ]';

 l_chunk_sql CONSTANT VARCHAR2 ( 10000 ) :=       -- 1: chunking statement. Breaks the input data
	 q'[SELECT min(r) start_id, max(r) end_id  -- into equal size pieces
		 FROM (
		SELECT ntile(10) over (order by rowid) grp, rowid r –- 2: 10 chunks will be produced
		 FROM doc_table                                     -- this can be equal or a multiple
		 )                                                -- of the parallel_level
	 GROUP BY grp]';
 l_try INTEGER;
 l_status INTEGER;
 l_task_name CONSTANT VARCHAR2( 20 ) := 'BLOB_MOVE';
BEGIN
 BEGIN
 dbms_parallel_execute.drop_task( l_task_name );
 EXCEPTION
 WHEN others then
 null;
 END;
 dbms_parallel_execute.create_task( l_task_name );
 dbms_parallel_execute.create_chunks_by_sql(l_task_name, l_chunk_sql, true);

 dbms_parallel_execute.run_task(
 task_name => l_task_name,
 sql_stmt  => l_sql_stmt,
 language_flag => dbms_sql.native,
 parallel_level => 10 –- 3: that many processes will be generated                 
 );
 
 dbms_output.put_line( 'DONE..' || dbms_parallel_execute.task_status(l_task_name));
 
END;
/

Zu den Kommentaren:

  1. Es gibt mehrere Möglichkeiten die Arbeit aufzuteilen. Die Chunking Query ist die flexibelste. Die Suchbedingen hier müssen auch im Cursor c1 noch einmal zu finden sein.
  2. Ntile (10) heißt, dass das Ergebnis der Abfrage in 10 hoffentlich gleich große Abschnitte einteilt.

Quellen:

Audit-Spalten mit dem Oracle SQL Developer Data Modeler automatisieren

In vielen Projekten erlebe ich es, dass Entwickler Audit-Spalten verwenden um entsprechende Informationen leicht zugänglich zu haben: Wer hat eine Datenzeile wann angelegt – und wann wurde dieselbe Zeile zuletzt von wem wieder bearbeitet? Wurden Tabellen früher noch direkt per DDL-Script erzeugt (CREATE TABLE my_test (id NUMBER NOT NULL, etc.), kommen hier heutzutage eher Modellierungstools wie der Oracle SQL Developer Data Modeler zum Einsatz:

 

Über das Icon “Spalte am Ende der Liste hinzufügen“ plus wurden hier nacheinander die vier Audit-Spalten (CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON) hinzugefügt und die Spalteneigenschaften definiert.

Die wenigsten Projekte bestehen aber aus nur einer einzigen Tabelle – bei drei oder sieben Tabellen mag das manuelle Hinzufügen der einzelnen Spalten und die ebenfalls manuelle Konfiguration derselben ja noch funktionieren. Aber was, wenn der Entwickler zwei oder drei Dutzend Tabellen modellieren will?

Q: Geht das nicht einfacher?

A: Natürlich geht das – über die Icons „Ausgewählte Spalten kopieren“  und „Kopierte Spalten am Ende einfügen“. Hiermit werden die ganzen Spalten samt ihrer vollständigen Definition kopiert und in der Ziel-Tabelle eingefügt.

Allerdings stößt auch diese Variante ab einer gewissen Tabellenanzahl an ihre Grenzen. Bei mehreren Hundert Tabellen werden wahrscheinlich die wenigsten Entwickler die Geduld aufbringen, bei jeder Tabelle die Icons drücken zu wollen. Hoffnungslos aufwändig wird es, wenn dann nicht neue Spalten hinzugefügt werden sollen – sondern beispielsweise der Datentyp bereits definierter Spalten geändert werden soll, etwa weil [CREATED|MODIFIED]_BY künftig bis zu 100 Zeichen speichern können soll.

Q: Geht das nicht einfacher?

A: Natürlich geht das – viel einfacher sogar. 😉

Hier kommen im Data Modeler die sogenannten Transformationen ins Spiel – im Menü unter Extras > Entwurfsregeln und Transformationen > Transformationen zu finden:

Speziell die Transformation mit dem Namen „Table template – uses column name“ ist hier sehr nützlich:

Um die Wartung von Audit-Spalten zu automatisieren, muss nun eine Tabelle angelegt werden, die nur die gewünschten Audit-Spalten enthält und deren Name in der Transformation referenziert wird, im Beispiel also MY_JOURNAL_TEMPLATE:

Beim Anwenden der Transformation werden dann alle im Model vorhandenen Tabellen durchgelooped und bezüglich der Spalten an die Definition in der Template-Tabelle angepasst. Gibt es die Audit-Spalten noch nicht, werden sie neu hinzugefügt. Gibt es sie bereits, werden ihre Attribute bei Bedarf auf die Werte der Template-Tabelle aktualisiert. So ist es dann beispielsweise auch möglich mit wenigen Klicks eine Datentyp-Änderung für ein Projekt mit mehreren Hundert Tabellen auszuführen. Um zu überprüfen ob eine Spalte aus einem Template stammt, kann man sich in den „Spalteneigenschaften“ die „Dynamischen Eigenschaften“ anschauen. Wenn die Spalte aus einem Template heraus erstellt oder angepasst wurde, wird im Rahmen der Transformation der unter p_name angegebene Wert (in diesem Beispiel also ctemplateID) nämlich als Schlüssel in die Eigenschaften eingetragen.

Bulk Processing

Beinahe jeder Quellcode enthält sowohl PL/SQL als auch SQL Statements. PL/SQL Statements werden von der PL/SQL engine ausgeführt, SQL Statements von der SQL engine. Folgendes Beispiel:

Angenommen es gäbe 1000 customers mit der account_mgr_id 145, dann würde die PL/SQL engine 1000 Mal zwischen PL/SQL engine und SQL engine hin und her wechseln. Und das kostet bei einer großen Datenmenge Performance, getreu dem Motto „Row by Row = Slow by Slow“.

Eine Möglichkeit, um dies zu verhindern, wäre die Verwendung von Bulk Collect. Anstatt Datensatz für Datensatz zu holen, ist es möglich, ein komplettes Datenset auf einmal zu holen. Hierfür kann man alle drei Collection Types verwenden: Assoziative Arrays, Nested Tables und VARRAYs. Das Beispiel von oben kann wie folgt verbessert werden:

Wir haben nun eine sogenannte nested table deklariert und darauf basierend eine Variable. Danach werden alle Daten auf einmal mittels BULK COLLECT in die Variable v_cust_ids gespeichert. Nun wird im FORALL Statement das angegebene DML Statement abgearbeitet. FORALL ist keine Schleife. Alle entsprechenden Datensätze werden auf einmal bearbeitet, somit entsteht nur ein Wechsel zwischen PL/SQL engine und SQL engine. Jedes FORALL Statement darf nur ein DML Statement enthalten. Müssen n DML Statements abgearbeitet werden, braucht man dementsprechend n FORALL Statements.

Mit dem Attribut SQL%ROWCOUNT kann die Anzahl der abgearbeiteten Datensätze ausgegeben werden.

FORALL und DML Errors

Angenommen, wir wollen 10.000 Datensätze in eine Tabelle speichern. Tritt beim 9.001. Datensatz ein Fehler auf, leitet die SQL engine den Fehler zurück an die PL/SQL engine und diese terminiert das ganze FORALL Statement. Die restlichen Datensätze werden nicht mehr gespeichert.

Mittels SAVE EXCEPTION zwingt man die PL/SQL engine dazu, alle validen Datensätze zu speichern. Somit werden die Fehler ignoriert und das Satement wird zu Ende gebracht. Die PL/SQL engine wirft anschließend einen ORA-24381 Fehler. Mit dem Attribut SQL%BULK_EXCEPTIONS können die fehlerhaften Datensätze ausgelesen und beispielsweise in eine Log-Tabelle gespeichert werden.