Everything Cloud? Das Event für die Oracle-Community

Everything Cloud? AOUG AK 23 – Die Plattform für die Oracle-Community

AOUG Anwenderkonferenz 2023!

Die AOUG Anwenderkonferenz findet auch dieses Jahr (12-13 Juni) im Austria Trend Hotel Savoyen Vienna statt. Sie ist die ideale Plattform, um sich über aktuelle Trends, Technologien und Lösungen im Oracle Technologie-Umfeld zu informieren.

Nur bei der Anwenderkonferenz:

  • Gibt es die einzigartige Möglichkeit aktuelle Herausforderungen mit einem Oracle-Partner zu besprechen
  • Gedanken und Ideen mit der Community auszutauschen
  • Neueste Technologien zu erforschen
  • Das Wissen anderer Anwender:innen nutzen

Das Publikum ist ein Querschnitt der Branche: Techniker, IT-Leiter, Oracle-Partner, Hersteller und Anbieter von Hard-und Software-Lösungen.

DIE Plattform für die Oracle Community

Die AOUG Anwenderkonferenz ist eine jährliche Veranstaltung, die sich an alle Oracle-Anwender:innen richtet. Hier kommen Expert:innen und Praktiker:innen zusammen, um ihre Erfahrungen und Ideen auszutauschen, neue Trends zu diskutieren und sich über die neuesten Entwicklungen bei Oracle-Produkten zu informieren. Sie bietet eine hervorragende Gelegenheit, sich mit Kolleg:innen aus der Branche zu vernetzen und wertvolle Kontakte zu knüpfen. Hier kann man von den Best Practices anderer Unternehmen lernen und wertvolle Einblicke in neue Technologien gewinnen.

Anwenderkonferenz 23 – “Everything Cloud? – Alles in die Cloud oder vielleicht doch nicht?

Das Spektrum der Vorträge reicht dieses Jahr von den Core Datenbank Themen, über On-Premise und Cloud Infrastrukturen, bis hin zu aktuellen Development Themen. Die Keynote von Dominic Giles, Master Product Manager für die Oracle Datenbank beschäftigt sich mit der Frage: “ Oracle Database: What’s next“. Weiters dürfen wir Mike Dietrich mit einem On-Hands Workshop (Upgrade and migrate to Oracle 23c) und zwei Vorträgen, sowie mehrere Oracle Anwendervorträge aus dem Cloud Umfeld, bei der Konferenz begrüßen.

Auch DBConcepts ist dieses Jahr mit zwei Vorträgen dabei. Roland Brandfellner hält einen Vortag über Automatisches Testen von APEX Applikationen und Andreas Schlögl ist mit dem Thema “cost based or expansion: a troubleshooting case study” dabei.

In 1 ½ Tagen mit insgesamt 36 Vorträgen kann man sich informieren, Gedanken, Herausforderungen und Ideen innerhalb der Community besprechen.

Am 12. Juni abends, werden alle angemeldeten Konferenzteilnehmer:innen zum mittlerweile traditionellen AOUG Bowling Abend mit anschließendem Abendessen in der Luftburg eingeladen.

Falls Sie noch nicht angemeldet sind –> geht es hier zur Anmeldung!

 

Network Encryption in Oracle

Today I want to share with you one of my security projects which Iwas working on at DBConcepts GmbH.

One of the topics was the encryption of Oracle’s Network Traffic.

The purpose of a secure cryptosystem is to convert plaintext data into unintelligible ciphertext based on a key, in such a way that it is very hard (computationally infeasible) to convert ciphertext back into its corresponding plaintext without knowledge of the correct key.

The setup is as followed:

A high availablity setup using dataguard with a primary and standby database 19c EE Edition. Enterprise Manager Cloud Control 13c, a recovery catalog database and a application server corresponding to each database server are also in the IT Infrastructure and to be secured.

The task is as follows:

  • the secure communication between the application server to the database server
  • the encryption of Dataguard logshipping from the primary to the standby database
  • the encryption of network traffic from the database hosts to the recovery catalog
  • the encryption of JDBC-thin clients connecting to the database

I want to give you a quick overview about the possibilities which oracle offers and how unsecure a not encrypted communication is.

First, there are two encryption options which oracle provides:

  • Oracle’s Native Network Encryption (using TCP Port 1521)
  • TLS/SSL Encryption Standard (using custom TCPS Port f.e. 1522)

Oracle Database 19c supports the usage of a combination of those encryption options.

Let’s get into action:

I did a tcpdump from our monitoring host where an oracle client is installed to connect to a 19c database. It sends select statements to gather information for specific metrics.The tcpdump listens to all outgoing connections with the target database specified as the destination.

(Due to privacy policies hostnames and ip adresses are changed)

The result without a secure communication, which is the default setup for all database installations:

[root@<monitoring01> ~]# tcpdump -i eth0 dst <target db ip adress> -A -v

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes




…

<start of packet>

11:12:34.455642 IP (tos 0x0, ttl 64, id 62432, offset 0, flags [DF], proto TCP (6), length 1161)

    <monitoring01>.30585 > <target db ip adress>.ncube-lm: Flags [P.], cksum 0x5599 (incorrect -> 0xf31b), seq 1684:2793, ack 2135, win 501, options [nop,nop,TS val 3707091369 ecr 1327380418], length 1109

E.....@.@..p

3.



zjfwy... /9.&......U......

................................................................................................................................................@select replace(WAIT_CLASS,'/','') WAIT_CLASS,ROUND(FG,2) FG,ROUN@D(BG,2) FG_AND_BG,DBTIME,to_char(end_time,'yyyy-mm-dd hh24:mi:ss@') end_time from (

select sw.wait_class,wc.END_TIME, wc.WAIT_CLA@SS#, (wc.TIME_WAITED_FG)/(wc.INTSIZE_CSEC/1) fg, (wc.TIME_WAITED@)/(wc.INTSIZE_CSEC/1) bg, 0 dbtime

from V$WAITCLASSMETRIC WC,V$@SYSTEM_WAIT_CLASS SW

where WC.WAIT_CLASS#=SW.WAIT_CLASS#

and wai@t_class!='Idle'

union

select 'CPU',FG.END_TIME, -1, FG.value/100@, BG.value/100, DBTIME.value from V$SYSMETRIC FG, V$SYSMETRIC BG@, V$SYSMETRIC DBTIME

where BG.METRIC_NAME = 'Background CPU Usag@e Per Sec'

and BG.GROUP_ID = 2

and FG.METRIC_NAME = 'CPU Usage P@er Sec'

and FG.GROUP_ID = 2

and DBTIME.METRIC_NAME = 'Average Ac@tive Sessions'

and DBTIME.GROUP_ID = 2

and BG.END_TIME = FG.END_3TIME

and FG.END_TIME = DBTIME.END_TIME

order by 1)

.....................................................

<End of packet>

11:12:34.509827 IP (tos 0x0, ttl 64, id 62433, offset 0, flags [DF], proto TCP (6), length 52)

…v

As you see this is pretty scary. Every SQL-Statement and every response from the database is sent over the network in plaintext. In terms of privary and security Data modification attacks and replay attacks are possible.

Examples:

  • Intercepting a $100 bank deposit, changing the amount to $10,000, and retransmitting the higher amount is a data modification attack.
  • Repetitively retransmitting an entire set of valid data is a replay attack, such as intercepting a $100 bank withdrawal and retransmitting it ten times, thereby receiving $1,000.

This is were encryption comes to place to secure your company and enviroment against unauthorized parties intercepting data in transit.

After the implementation of security and encryption, this is the final result:

There is no plain text send over the network, only ciphertext !

[root@<monitoring01> ~]# tcpdump -i eth0 dst <target db ip adress> -A -v

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes



<start of packet>

11:23:46.582209 IP (tos 0x0, ttl 64, id 3746, offset 0, flags [DF], proto TCP (6), length 696)

<monitoring01>.59236 > <target db ip adress>.ssh: Flags [P.], cksum 0x53c8 (incorrect -> 0xb685), seq 1633:2277, ack 3158, win 130, options [nop,nop,TS val 3707763496 ecr 1328052548], length 644

E.....@.@...

3.



zjf.d..............S......

...(O(yD...U

J3R.Ok.J._.W/....ZpZ

*.:/..d.#.......Bl.&.Z5a.3.....B.....m.S....U..NtS.......y..x......*>.b....$...^ij........Z6..KPK......~v\.WvHm.6.$&9.%;.x..x#a.:..5.... .X=[M...?......xv3i&(.|.-+.p.~.n...i.J.wW...qS"d....Z...........eY1.L..V..F5..(..E.O...'.os.

Y.xIG8......K....5rh.F@..D=..~............].U.Fi..7.u......e..y/..C'..... |...{o_..6b>`..}.X.f...H..K=`v.>

.....i.....B ..d*os.h&.....:....W.G....C.s...2.V..HWA(o..e ....$x.....|. ....Kwz.....:p.Nz.Aqz..5..+V+dtria..lv7T..k.n..5E.....



<end of packet>

11:23:46.582899

…v

Hardening the systems and implementing security features is a must !

 

to be continued …

TOO_MANY_ROWS Exception

TOO_MANY_ROWS Exception

Wird in einem PL/SQL Block das Ergebnis einer Select Abfrage mittels INTO in eine Variable geschrieben, so muss das Statement EXAKT 1 Zeile zurück liefern. Wird keine Zeile zurückgegeben, so wird eine NO_DATA_FOUND Exception ausgelöst. Ist die Where-Klausel nicht exakt genug und es werden mehrere Zeilen zurück geliefert, so wird eine TOO_MANY_ROWS Exception ausgelöst. Der Unterschied zwischen diesen beiden Fehlern ist jedoch ein potentiell sehr relevanter:

Tritt der Fall einer NO_DATA_FOUND Exception auf, so bleibt der Wert der Variable unverändert – sprich wenn die Variable NULL ist, denn bleibt der Wert NULL, ist der Wert beispielsweise 2, so bleibt er 2 (siehe auch die Ergebnisse des Beispielskriptes).

Tritt der Fall einer TOO_MANY_ROWS Exception auf, so ändert sich der Wert der Variable – und zwar auf eventuell unvorhersehbare Weise. Der Wert der Variable wird auf den ersten gefundenen Wert der Abfrage gesetzt. Mit Order By kann das kontrolliert werden, aber ohne ist es nicht vorhersehbar, welcher Wert eingetragen wird.

Das wirklich wichtige an diesem Verhalten ist, dass bei einem größeren Block mit nachfolgenden Schritten berücksichtigt werden muss, dass bei einem TOO_MANY_ROWS Fehler die Variable einen Wert hat trotz der Exception.

Das folgende Skript verdeutlich diese Möglichkeiten:

declare
  v_value number := 2;
begin
  -- create no data found exception - v_value is NULL
  dbms_output.put_line('v_value am Ende = ' || v_value);
  begin
    with numbers as
     (select level eindeutig from dual connect by level <= 10),
    base as
     (select eindeutig, mod(eindeutig, 3) mehrdeutig from numbers)
    select eindeutig into v_value from base where eindeutig = 11;
  exception
    when no_data_found then
      dbms_output.put_line('v_value bei no_data_found = ' || v_value);
  end;
  -- create too many rows exception - v_value is NOT NULL
  begin
    with numbers as
     (select level eindeutig from dual connect by level <= 10),
    base as
     (select eindeutig, mod(eindeutig, 3) mehrdeutig from numbers)
    select eindeutig into v_value from base where mehrdeutig = 1;
  exception
    when too_many_rows then
      dbms_output.put_line('v_value bei too_many_rows ohne order = ' || v_value);
  end;
  -- create too many rows exception - v_value is NOT NULL
  begin
    with numbers as
     (select level eindeutig from dual connect by level <= 10),
    base as
     (select eindeutig, mod(eindeutig, 3) mehrdeutig from numbers)
    select eindeutig into v_value from base where mehrdeutig = 1 order by 1 desc;
  exception
    when too_many_rows then
      dbms_output.put_line('v_value bei too_many_rows mit order = ' || v_value);
  end;
end;

 

Oracle SQL Model Clause Vertiefung

Grundlegender Aufbau

Der grundlegende Aufbau der Model Clause ist bereits in einem anderen Blog-Eintrag beschrieben, den sie HIER finden.

Weitere Steuerungsmöglichkeiten

Im ersten Blog-Artikel wurde nur das main_model beachtet und auch von diesem nur die verpflichtenden Teile. Ergänzen wir das ganze nun um die cell_reference_options.

Die Cell Reference Options definieren wie mit fehlenden und leeren Werten umgegangen wird und wie streng die Eindeutigkeit geprüft wird. Im Detail sieht das wie folgt aus:

IGNORE NAV
Wenn diese Anweisung inkludiert wird, dann werden folgende Default Werte bei NULL-Werten oder fehlenden Werten zurückgegeben

  • 0 für numerische Spalten
  • 01.2000 für Datumsspalten
  • Ein leerer String für Textspalten
  • NULL für alle anderen Datentypen

KEEP NAV (Default)
Wenn diese Anweisung inkludiert wird, wird immer NULL zurückgegeben, wenn ein Wert fehlt oder NULL ist

UNIQUE DIMENSION (Default)
Wenn diese Anweisung inkludiert ist, muss die Kombination der Spalten der PARTITION BY und der DIMENSION Spalten eine Zeile eindeutig identifizieren (die Spalten müssten also einen Unique Key definieren können)

UNIQUE SINGLE REFERENCEMit dieser Anweisung werden lediglich Referenzen auf eine einzelne Zelle auf der rechten Seite auf Uniqueness geprüft, nicht das gesamte Set

NAV Anweisung

Wenn Werte nicht gefunden werden, definiert diese Anweisung wie damit umgegangen wird. Als Beispiel auf Basis der vorhandenen Testdaten sollen die folgenden beiden Statements zeigen wie die Auswirkungen sind:

select schueler, note, schulstufe
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler, schulstufe)
 MEASURES(note) keep nav(note [ 'Anton Anger',
           5 ] = note [ schueler = 'Anton Anger',
           schulstufe = 4 ]);

In diesem Fall mit KEEP NAV werden nicht vorhandene Werte mit NULL ausgegeben. Die Zeile mit Schulstufe 5 hat also in der Notenspalte einen NULL Wert stehen.

select schueler, note, schulstufe
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler, schulstufe)
 MEASURES(note) ignore nav(note [ 'Anton Anger',
           5 ] = note [ schueler = 'Anton Anger',
           schulstufe = 4 ]);

Wenn nun IGNORE NAV verwendet wird, dann wird der Wert 0 eingetragen.

UNIQUE Anweisung

Die Unique Anweisung ist sehr simpel umzusetzen. Auf Basis der schon im ersten Blogeintrag verwendeten Testdaten würde folgendes Statement fehlschlagen:

  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler) MEASURES(note)
 unique dimension (note [ schueler = 'Anton Anger'
                 ] = round(avg(note) [ schueler = 'Anton Anger' ], 0));

Der Grund dafür ist simpel: Die Spalte SCHUELER definiert keine eindeutige Zuweisung. Um das Statement valid zu machen, müsste man das Jahr oder die Schulstufe ergänzen, das würde dann wie folgt aussehen:

select schueler, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler, schulstufe)
 MEASURES(note) unique
 dimension(note [ schueler = 'Anton Angera',
                 schulstufe = 4
                 ] = round(avg(note) [ schueler = 'Anton Angera',
                           schulstufe between 1 and 3 ],

Oder alternativ die UNIQUE Anweisung ändern, was dann so aussehen würde:

select schueler, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler) MEASURES(note)
 unique single
 reference(note [ schueler = 'Anton Anger'
                 ] = round(avg(note) [ schueler = 'Anton Angera' ], 0));

 

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!

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.

 

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:

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.