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:

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.

JavaScript in Quick Picks in Oracle APEX

Wer diesen Blog schon öfter besucht hat, wird das Feature der Quick Picks bereits kennen. Diese dienen dazu, Usereingaben zu optimieren, indem mittels eines Klicks auf ein entsprechendes Quick Pick direkt ein Wert in ein Feld eingetragen wird.

 Quickpicks_dynamic_01

Ein Nachteil der Quick Picks ist aber, dass standardmäßig nur vorgefertigte (sprich beim Rendern der Seite bereits definierte) Werte eingesetzt werden können. Auch im letzten Blogeintrag über Quick Picks, in dem wir die Anzahl der Quick Picks dynamisch variierbar gemacht haben, haben wir dieses Problem.
Würde man mit den Quick Picks nun beispielsweise immer den aktuellen Wert eines anderen Feldes übernehmen wollen, so ist dies nicht einfach so möglich.
Angenommen es wäre zum Beispiel gewünscht, den Wert des Feldes „Betrag“ (in der oben angeführten Abbildung) mittels eines Klicks auf einen Quick Pick in das Feld „Betrag bezahlt“ zu übernehmen.
Mit den bisher bekannten Methoden kann man zwar das Feld als Quick Pick angeben (siehe folgende Abbildung), allerdings wurde der zu setzende Betrag bereits beim Rendern der Seite erstellt. War dieser davor „1234“ und wurde nachträglich vom Endbenutzer auf „12345678“ geändert, so wäre auch der Quick Pick immer noch mit dem Wert „1234“ definiert und zwar solange, bis die Region (oder die gesamte Seite) erneut geladen werden.

Quickpick_Eigenschaften_marked

Wie Leser des letzten Beitrages über Quick Picks bereits wissen, arbeiten diese selbst mit JavaScript-Code. Es ist also möglich, beim Erstellen eigener Quick Picks beliebigen JavaScript-Code auszuführen. Anstatt also einen Quick Pick zu generieren, der mittels JavaScript ein Item mit einem vordefinierten Wert befüllt, kann dieser Code auch direkt den Wert zum aktuellen Zeitpunkt aus einem anderen Item auslesen.
Der HTML-Code für das entsprechende Quick Pick würde in diesem Beispiel also wie folgt aussehen:

<span class="apex-quick-picks"><a href="javascript:$s('#CURRENT_ITEM_NAME#',$v('P1_BETRAG'), 'JS-QP')">JS-QP</a></span>

„$s“ ist die in Oracle APEX verwendete JavaScript-Methode zum setzten von Items, während „$v“ die Methode zum Auslesen eines Wertes eines beliebigen Items verwendet werden kann. In diesem Beispiel wird also der Wert aus Item „P1_BETRAG“ in das Item „#CURRENT_ITEM_NAME#“ gesetzt.
„#CURRENT_ITEM_NAME#“ ist nur ein Platzhalter für das gewünschte Item. Wenn dieser HTML-Code nun im Post-Text eines gewünschten Items eingetragen wird (in diesem Fall im Item „P1_BETRAG_BEZAHLT“), wird dieser durch den korrekten Itemnamen ersetzt.
Quickpicks advanced

Um das Layout der Quick Picks korrekt darzustellen muss noch im selben Eigenschaftsfenster im Bereich „Quick Picks“ das Flag „Show Quick Picks“ auf „Yes“ gesetzt werden.

Nun wird der entsprechende HTML-Code mittels CSS in die bekannte Formatierung und Positionierung gebracht. Bei Klick auf den Quick Pick wird immer der aktuelle Wert des Feldes „Betrag“ übernommen, ganz egal, wann dieser geändert wurde.

Dynamic Quick Picks

Um die Quick Picks nun wieder dynamischer zu erstellen, kann man natürlich auf „Shortcuts“ in den Shared Components“ zurückgreifen und die Quick Picks über PL/SQL dynamisch erstellen.

Oracle APEX

Wenn man einen neuen Shortcut erstellt muss als Type „PL/SQL Function Body“ ausgewählt und im Feld „Shortcut“ ein entsprechender PL/SL-Code geschrieben werden.

declare
  v_qp varchar2(32767);
begin
  v_qp := apex_string.format(q'{%1}', 'P1_BETRAG', 'JS-QP');
  return apex_string.format('%0', v_qp);
end;

Create Shortcut

Danach muss der Name des Shortcuts noch im Eigenschaftsfenster des gewünschten Items unter „Post Text“ mittels Anführungszeichen angegeben werden, um APEX mitzuteilen, dass hier der aus dem Shortcut generierte HTML-Code ausgegeben werden soll.

Advanced Post

Man erhält dasselbe gewünschte Ergebnis, es ist aber bei der Umsetzung mit Shortcuts möglich, beliebig viele Quick Picks mit unterschiedlicher Funktion zu erstellen, ohne ständig das Item anpassen zu müssen.

Dynamic Quick Picks

Generell ist es so nun möglich, jeden erdenklichen JavaScript-Code in Quick Picks zu verpacken, es muss nicht einmal eine Quick Pick-Funktionalität dahinterstehen.

Dynamische Quick Picks in Oracle APEX

Wer seine Web-Applikation in Oracle APEX auf Usability optimiert, wird wahrscheinlich schon einmal über das Feature der sogenannten Quick Picks gestoßen sein.
Unter Quick Picks versteht man vordefinierte Usereingaben, die ein Endanwender mittels Mausklick auslösen kann und das entsprechende Feld mit einem vordefinierten Wert befüllt, ohne diesen extra eintippen zu müssen.

 Quickpicks_dynamic_01

Konfiguriert werden diese Quick Picks über das Eigenschafts-Fenster des jeweiligen Page-Items.

Quickpick Eigenschaften

Quick Picks können also die Eingabe des Endbenutzers ein wenig beschleunigen, allerdings sieht es auf den ersten Blick so aus, als ob nur statische Werte vergeben werden können. Dies ist nicht ganz korrekt, denn mit der üblichen Notation in Oracle APEX können auch Werte aus beliebigen Items angesprochen werden (Kaufmännisches Und + Item-Name + Punkt), dies sieht zum Beispiel wie folgt aus:

Quickpick_Eigenschaften_marked

Diese Art der Definition führt dazu, dass der Wert aus dem jeweiligen Item als Quick Pick gesetzt wird.

Quickpick dynamic zweiter wert

Einzige Bedingung hierfür ist allerdings, dass der Wert bereits beim Aufbau der Seite gesetzt wird, denn der Quick Pick wird vorab mit dem Wert des entsprechenden referenzierten Items erstellt und ändert sich nicht dynamisch mit. Auch ist die Anzahl der Quick Picks vorab auf die statisch definierten Labels und Values beschränkt, es ist also auf diese Art beispielsweise nicht möglich dynamisch einmal zwei und einmal drei Quick Picks unter einem Feld anzuzeigen.

Um nun beliebig viele Werte dynamisch als Quick Picks anzuzeigen, muss man etwas tiefer in die Trickkiste greifen. Um dies zu ermöglichen, bedient man sich dem Wissen, dass die Quick Picks über eine CSS-Klasse positioniert und gestaltet werden und der entsprechende HTML-Code einfach an das gewünschte Item angehängt wird. Mit diesem Wissen ist es nun möglich, die Quick Picks eigenständig zu erstellen.

Für die hier verwendete Testseite wurde eine simple Tabelle erstellt, welche beliebig viele Werte und Bezeichnungen für Quick Picks enthält. Die Tabelle wurde QP_TAB genannt und beinhaltet aktuell folgende Werte:

Werte Tabelle Quickpick

Ziel soll es nun sein für jede Zeile dieser Tabelle einen separaten Quick Pick für ein Item zu generieren. (Allgemein ist es egal, woher die Daten für die Quick Picks stammen, einzige Bedingung ist, dass man diese mittels PL/SQL ermitteln kann.)

Um die Anzahl der Quick Picks nun auch dynamisch umzusetzen zu können, benötigt man ein weiteres Oracle APEX Feature: die sogenannten „Shortcuts“. Diese befinden sich in den Shared Components wie im folgenden Screenshot ersichtlich ist.

Oracle Apex

Klickt man nun auf Shortcuts, kann man auf der Folgeseite ein neues Objekt erstellen.

Create Shortcut

Wichtig hierbei ist, dass als „Type“ „PL/SQL Function Body” ausgewählt wird und dann im Feld „Shortcut“ ein entsprechender Code geschrieben wird, der die gewünschten Daten in eine entsprechende HTML-Struktur bringt. Der Code für das hier verwendete Beispiel sieht nun folgendermaßen aus:

declare
  v_qp varchar2(32767);
begin
  for rec_qp in (select bezeichnung, wert from qp_tab) loop
    if v_qp is not null then
      v_qp := v_qp || ', ';
    end if;
      v_qp := v_qp || apex_string.format(q'{%1}', rec_qp.wert, rec_qp.bezeichnung);
  end loop;
  return apex_string.format('%0', v_qp);
end;

Wichtig ist hier eigentlich nur, dass die einzelnen Links (die resultierenden Quick Picks) in einem Span-Container der CSS-Klasse „apex-quick-picks“ liegen.

Der eigentliche Link ist dann Javascript-Code, welcher mit „$s“ das Setzen eines Items mit der entsprechenden Angabe des Wertes umsetzt. (Der Platzhalter „#CURRENT_ITEM_NAME#“ wird im folgenden Schritt aufgelöst.)

Wieder zurück auf der Seite kann man im Eigenschaftsfenster des gewünschten Items nun im „Advanced“-Bereich im „Post Text“-Feld den Namen des zuvor erstellten Shortcuts in Anführungszeichen eintragen. Dadurch wird der durch den Shortcut generierte HTML-Text nach dem Item im Quellcode eingefügt. Oracle APEX weiß nun auch, dass der Platzhalter „#CURRENT_ITEM_NAME#“ mit dem Namen des Items ersetzt wird (diese Funktionalität des Ansprechens des Item-Namens ist über den Post Text allgemein möglich).

Bei den Quick Picks werden diesmal keine statischen Werte angegeben (da der Code für die Quick Picks ja bereits durch den Shortcut an das Item angehängt wurde). Um aber die Positionierung des durch den Shortcut erstellten HTML-Code auch korrekt anzuzeigen, wird das Flag „Show Quick Picks“ auf „Yes“ gesetzt.

So erhält man die dynamisch erstellten Quick Picks:

Würde man in Tabelle QP_TAB nun einen weiteren Eintrag erfassen, würde dieser auch entsprechend auf der APEX-Seite als weiterer Quick Pick erscheinen, ohne dass man dafür das Item anpassen müsste.

Die einzige Limitierung der Anzahl der Quick Picks entsteht durch die Zeichenanzahl des VARCHAR2 im angelegten Shortcut.

Unterstützung der Projektplanung mit Allocatus

Situation:

Es gibt unterschiedliche Möglichkeiten, die Projekttermine an das Projektteam auszusenden. Jede dieser Möglichkeiten hat Ihre Vor- und Nachteile:

  1. Kopie des Projektplans als PDF an die betreffenden MitarbeiterInnen senden – Nachteile:
    1. Mit dem Ausdruck ist der Inhalt schon wieder alt
    2. schwierig, das richtige Format zu finden, damit möglichst alles gut lesbar ist.
  2. Alle haben MS-Project installiert und sehen dort die aktuelle Taskplanung – Nachteile:
    1. Höhere Lizenzkosten
    2. Nicht alle können und wollen mit dem Tool umgehen.
  3. Project Web App einsetzen – Nachteile:
    1. Lizenzen für Project Essentials notwendig
    2. Es sind leider viele Informationen, die zwar in der Client-Version von MS-Project dargestellt werden können, nicht aber in der Webapp.
  4. Allocatus

Vorteile:

    1. Einbindung der Tasks als Outlook-Termine ins MS Project durch ein Plug in
    2. Die Termine müssen von den MitarbeiterInnen nicht bestätigt werden.
    3. Es können auch gezielt Informationen aus den Tasks (z.B. Notizen) in diese Termine übernommen werden.
    4. E-Mail-Adressen der Mitarbeiter werden auf dem Server hinterlegt und die Mitarbeiter müssen kein neues Tool einsetzen. Die Termine werden mit Allocatus eingetragen bzw. aktualisiert.

 Nachteile:

      1. Es kostet auch Lizenzen und Wartung – ist aber überschaubar
      2. Bei der Projektplanung muss man genau unterscheiden, was bereits veröffentlicht werden kann und was noch nicht.

 

Lösung bei DBConcepts mit Allocatus:

Allocatus ermöglicht, gezielt die Termine von MS Project in die Outlook Kalender der jeweiligen Mitarbeiter zu synchronisieren.

Planänderungen (z.B. im Zeithorizont, Ressourcenzuteilung, etc.) werden im Projektteam abgesprochen und durch unsere Projektmanager im Projektplan entsprechend geändert.

Anschließend mit einem Klick wieder mit Outlook synchronisiert. Dies erspart unseren Technikern enorm viel Zeit und nimmt zugleich die Last der Terminorganisation weg.

 

Kommt es einmal zu einer kurzfristigen Planänderung sind die Outlook Kalender aller Beteiligten mit nur wenigen Änderungen aktualisiert und veröffentlicht.  Der Status „veröffentlich“ bedeutet, ob dieser Task vom Projektplan im Kalender sichtbar sein soll oder nicht.

Die Veröffentlichung kann gezielt verhindert werden, wenn die Planung einzelner Tasks oder Teilprojekte erst in der Entwurfsphase ist.

Terminplanung im MS-Project

Terminplanung im MS-Project

 

Die Termine können als ganztätig eingetragen oder mit genauer Zeitangabe. Dies eignet sich besonders für ein stundengenaue Planung (z.B. in Cut-Over Situationen). Die eingetragenen Termine durch den Allocatus müssen nicht vom Bearbeiter bestätigt werden, weil Sie in der Planung bereits abgestimmt worden sind. Werden im Projektplan Notizen hinterlegt – so werden diese bei der Veröffentlichung vom Allocatus auch berücksichtigt und mit dem Termin eintragen (siehe Bild oben).

Ansicht im Outlook Kalender

Ansicht im Outlook Kalender

 

Terminansicht vom Allocatus im Outlook

Terminansicht vom Allocatus im Outlook

Terminansicht vom Allocatus im Outlook Kalender mit den Projektinformationen wie:

  • Name
  • Uhrzeit
  • Notizen
  • Arbeitspaketnummer

Auch die Abwesenheiten lassen sich optimal mit den Allocatus verbinden. Im MS Project wird die Abwesenheit der Mitarbeiterinnen eingetragen und mit Allocatus zugleich in den Outlook Kalender. Somit hat der Projektleiter und auch die Kollegen einen Überblick über die Abwesenheit des Kollegen.

Fazit

  • Die Einbindung von Allocatus hat die Terminorganisation enorm verbessert und erleichtert.
  • Allocatus erspart uns Zeit bei der manuellen Pflege von Projektterminen.
  • Mitarbeiterinnen sehen sogar Ihre Termine am Mobiltelefon (Voraussetzung Outlook Client ist eingerichtet).

Installation und Inbetriebnahme

Um einen Allocatus Dienst einzurichten sind folgende Komponenten bzw. Schritte nötig:

  1. Allocatus Lizenzen erwerben und den Allocatus Server installieren
  2. Abstimmung der Inhalte für die Outlooktermine mit dem Hersteller.
  3. Konfiguration der E-Mail-Adressen im MS-Project (Ressourcenpool) und im Allocatus
  4. Plugin MS-Project installieren und konfigurieren
  5. Allocatus-Settings im MS-Project
Allocatus-Settings

Allocatus-Settings

  1. Projektplan veröffentlichen:

Schritt 1: Tasks zur Veröffentlichung kennzeichnen

Schritt 2: Button „Update“ aktivieren

Quellen:

https://www.allocatus.com/home

Oracle APEX: Custom error messages mit APEX_ERROR

Die Haupt-Anwendungsfälle im Zusammenhang mit der Ausgabe von Fehlermeldungen in APEX-Applikationen dürften die meisten Entwickler nach einer verhältnismäßig kurzen Einarbeitungszeit kennen:

  • Entweder werden Validations ausgeführt, schlagen an und sollen den Endanwender darüber informieren, dass eine entsprechende Bedingung verletzt wurde.
  • Oder während der Ausführung eines Page Processes tritt ein Fehler auf, über den der Endanwender in Kenntnis gesetzt werden soll.

 

Wollten Entwickler in älteren APEX-Versionen eigene Fehlermeldungen ausgeben, geschah dies häufig über die Nutzung der Variable apex_application.g_print_success_message unter zusätzlicher Angabe von Style-Informationen:

apex_application.g_print_success_message := '<span style="color:red;">Achtung: Bei der Aufbereitung der Datei-Inhalte sind insgesamt 23 Fehler aufgetreten, die Daten konnten daher nicht importiert werden.</span>';

Mittlerweile jedoch stellt das APEX-eigene Package APEX_ERROR viel elegantere Möglichkeiten für den Umgang mit eigenen Fehlermeldungen zur Verfügung:

 

Die überladene Prozedur ADD_ERROR bietet verschiedene Varianten an um eigene Fehlermeldungen auf den Error Stack zu legen, die Darstellung übernimmt dabei APEX, so dass die Notwendigkeit zu expliziten Style-Angaben entfallen kann. Ein entsprechender Prozess im Page Processing könnte damit beispielsweise so aussehen:

DECLARE
 -- define some variables
 v_status NUMBER;
 ...
BEGIN
-- do some fancy stuff
...
 v_status := my_schema.my_package.my_function(p_var => '[MY_VALUE]');

IF v_status > 0 THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Es ist ein Fehler aufgetreten, so dass…');
 END IF;
EXCEPTION
WHEN OTHERS THEN
-- do some error logging
...
RAISE;
END;

Das eigentliche Potential entfaltet dieses Package aber dann, wenn man auf auftretende Fehler speziell reagieren will, beispielsweise weil gewisse Fehler häufiger auftreten und die Seite speziell für bestimmte Fehlerfälle bestimmte zusätzliche Button-Aktionen oder Auswertungen zur Verfügung stellen soll. Dafür kann man statt der Variable v_status im obigen Code ein Hidden Item P12_STATUS setzen und nach dem Ausführen der Prozesse über einen Branch die Seite neu aufrufen und das Item dabei übergeben. Der Aufruf von APEX_ERROR geschieht nun nicht mehr im Page Processing sondern im Page Rendering und nur dann wenn P12_STATUS einen Wert > 0 enthält

IF :P12_STATUS = '1' THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
 p_message => 'Achtung: Das Hochladen der Daten war nicht erfolgreich, so dass…');
ELSIF :P12_STATUS = '2' THEN
 apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Das Parsen der Daten ist fehlgeschlagen, so dass…');
ELSE
...
END IF;

Zusätzlich besteht nun natürlich die Möglichkeit status-abhängig zusätzliche Items etc. anzubieten – zum Beispiel ein Button um die Daten erneut hochzuladen für P12_STATUS = ‚1‘ oder ein Report mit den Parsing-Fehlern für P12_STATUS = ‚2‘.

Setup IPSec VPN between Oracle Cloud Infrastructure and Customer On-Premise Equipment

Eine Möglichkeit zur Verbindung Ihres On-Premise-Netzwerks und Ihres virtuellen Cloud-Netzwerks (VCN) besteht in der Verwendung von VPN Connect, einem IPSec-VPN. IPSec steht für Internet Protocol Security oder IP Security. IPSec ist eine Protokollsuite, die den gesamten IP-Traffic verschlüsselt, bevor die Pakete von der Quelle zum Ziel übertragen werden.

Voraussetzungen:

  • vorhandenes VCN (Virtual Cloud Network) in der OCI (Oracle Cloud Infrastructure)
  • vorhandenes VPN-Gateway / IP-Netz auf der Customer On-Premise Site

*Wichtig: die Netze in der OCI und On-Premise dürfen sich nicht im gleichen Subnetz befinden (z. B. 192.168.0.0/24 und 192.168.0.0/28192.168.0.0/24 und 192.168.99.0/28)

 

Konfiguration in der OCI:

  1. Erstellen Sie unter OCI -> Networking -> Dynamische Routinggateways ein neues „Dynamische Routinggateway (DRG)“ (Wählen Sie das gewünschte Compartment und einen freiwählbaren Namen)
  2. Verknüpfen Sie das Dynamische Routinggateway mit dem vorhandenen VCN: OCI -> Networking -> Virtual Cloud Network -> Dynamische Routinggateways -> „Dynamische Routinggateway anhängen“ -> Wählen Sie das zuvor erstelle Dynamische Routinggateway
  3. Erstellen Sie ein Customer Premises Equipment: OCI -> Networking -> Customer Premises Equipment -> „Customer Premises Equipment erstellen“ (Wählen Sie hier einen Namen, das Compartment, den Hersteller (ggf. Sonstige) und die öffentliche IPv4-Adresse des CPE-Gateways)
  4. Erstellen der VPN-Verbindung: OCI -> Networking -> VPN-Verbindungen -> IPSec-Verbindung erstellen (Wählen Sie hier einen Namen und das Compartment für den VPN aus, anschließend wählen Sie bitte das „CPE“ und das „DRG“ aus dem richtigen Compartment aus und geben Sie das Customer Netz unter „CIDR mit statischer Route“ bei statischen Routen an)
  5. Anpassen der VPN-ID: OCI -> Networking -> VPN-Verbindungen -> angelegten VPN wählen -> „Bearbeiten“ -> CPE-IKE-ID-Typ kann zwischen IP und FQDN gewählt werden
  6. Auslesen der Informationen für den VPN (öffentliche IPs der OCI, PreSharedKeys, …) OCI -> Networking -> VPN-Verbindungen -> angelegten VPN wählen -> CPE- und Tunnelinformationen

Beispielkonfiguration On-Premise (hier Sophos SG mit UTM 9.7):

  1. Erstellen der IPSec Policies über Sophos UTM -> Site-to-site VPN -> IPSec -> Policies -> „New IPSec Policy…“ die OCI unterstützt folgende Parameter (Stand 10/2020):

Phase 1 (ISAKMP)

ParameterOptionen
ISAKMP-ProtokollVersion 1
AustauschartHauptmodus
AuthentifizierungsmethodePre-Shared Keys
VerschlüsselungsalgorithmusAES-256-cbc (empfohlen)

AES-192-cbc

AES-128-cbc

AuthentifizierungsalgorithmusSHA-2 384 (empfohlen)

SHA-2 256

SHA-1(auch als SHA oder SHA1-96 bezeichnet)

Diffie-Hellman-GruppeGruppe 1 (MODP 768)

Gruppe 2 (MODP 1024)

Gruppe 5 (MODP 1536)

Gruppe 14 (MODP 2048)

Gruppe 19 (ECP 256)

Gruppe 20 (ECP 384) * (empfohlen)

Gültigkeitsdauer des IKE-Sessionschlüssels28800 Sekunden (8 Stunden)
* Gruppe 20 wird in Kürze in allen Oracle Cloud Infrastructure-Regionen unterstützt.

Phase 2 (IPSec)

ParameterOptionen
IPSec-ProtokollESP, Tunnelmodus
VerschlüsselungsalgorithmusAES-256-gcm (empfohlen)

AES-192-gcm

AES-128-gcm

AES-256-cbc

AES-192-cbc

AES-128-cbc

AuthentifizierungsalgorithmusBei Verwendung von GCM (Galois/Counter Mode) ist kein Authentifizierungsalgorithmus erforderlich, da die Authentifizierung in der GCM-Verschlüsselung enthalten ist.

Wenn GCM nicht verwendet wird, werden folgende Algorithmen unterstützt:

HMAC-SHA-256-128 (empfohlen)

HMAC-SHA-196

Gültigkeitsdauer des IPSec-Sessionschlüssels3600 Sekunden (1 Stunde)
Perfect Forward Secrecy (PFS)aktiviert, Gruppe 5
  1. Erstellen Sie unter „Remote Gateways“ ein, bzw. zwei neue Remotedesktopgateways, die IPs und PSKs finden Sie in der OCI unter: OCI -> Networking -> VPN-Verbindungen -> angelegten VPN wählen -> CPE- und Tunnelinformationen, hier tragen Sie bei Remotenetz das IPv4 des OCI VCN, bzw. Subnetzes aus dem VCN ein
  2. Legen Sie nun die Conncetion an und wählen hier Ihre Policy, das Remotegateway und Ihre lokalen Netzwerke aus
  3. nach erfolgtem Verbindungsaufbau werden die VPN Tunnel in der Sophos, sowie der OCI als UP/Hochgefahren angezeigt: