Immer wieder erhalte ich die Frage, ob und wie man den Oracle Object Storage für die Oracle Datenbank verwenden kann. Die erforderliche Funktionalität wird über die PL/SQL Schnittstelle zur Verfügung gestellt. Mit dem Package DBMS_CLOUD ist es möglich den Oracle Cloud Object Storage als Datenspeicher für die Datenbank zu nutzen. Allerdings war diese Funktionalität ursprünglich den Usern von Oracle Autonomous Database vorbehalten. Seit jeher gehörte DBMS_CLOUD zu der Standardfunktionalität der Oracle Autonomous Datenbank. Mit Oracle Database Release 19c (genauer >= 19.9 ) gibt es nun eine Änderung. DBMS_CLOUD  steht nun auch für alle anderen Datenbank Installationen zur Verfügung – On-Premises und in der Cloud.

Was ist eigentlich der Oracle Object Storage? Oracle Object Storage ist ein dauerhafter Cloud-Speicherdienst, der in allen Cloud Regionen verfügbar ist. Entwickler und IT-Administratoren können diesen Service nutzen, um Daten zu geringen Kosten zu speichern und darauf zuzugreifen. Weitere Informationen zu Preisen, Dokumentation etc. finden sich hier.

Mit dem Package DBMS_CLOUD ist es zum Beispiel möglich External Tables oder hybrid partitionierte Tabellen anzulegen und somit die Daten nicht nur in der Datenbank sondern auch im Object Storage speichern. 
Folgende Liste zeigt einige Beispiele:

  • COPY_DATA zum Datenladen aus dem Object Storage in Tabellen 
  • CREATE_CREDENTIAL zum Speichern von Cloud Object Storage Credentials
  • CREATE_EXTERNAL_TABLE zum Erzeugen von External Tables 
  • CREATE_EXTERNAL_PART_TABLE zum Erzeugen von partitionierten External Tables 
  • CREATE_HYBRID_PART_TABLE zum Erzeugen von hybrid partitionierten Tabellen 

Die vollständige Liste der Funktionen und Prozeduren finden sich im PL/SQL Packages and Types Reference Guide unter DBMS_CLOUD.

Einen Haken hat die ganze Sache aber noch: Das Package ist nicht vorinstalliert. Es muß manuell installiert werden und Benutzer oder Rollen für die Verwendung dieses Package müssen manuell konfiguriert werden. Informationen zur schrittweisen Installation finden sich in der MOS Note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1).

Folgende Abschnitten sollen das Setup und die Konfiguration illustrieren und zum Nachmachen anregen. Abschließend werden Anwendungsbeispiele gezeigt, um die Verwendung zu demonstrieren.

Es sind zwei Konfigurationsschritte erforderlich – einmal die Installation des Package DBMS_CLOUD und danach die Security Konfiguration von Rollen und Usern um das Package verwenden zu können. 

Installation von DBMS_CLOUD

DBMS_CLOUD wird ab 19.9 unterstützt – 21c ist noch nicht freigegeben. Die erforderlichen Schritte für die Installationen sehen folgendermaßen aus:

1) Erzeugen von Schema C##CLOUD$SERVICE als Owner von DBMS_CLOUD

Zuerst wird das Common Schema C##CLOUD$SERVICE in der CDB und den PDBs angelegt. In der Multitenant Architektur eignet sich dazu die Verwendung des Perl Scripts catcon.pl um sicherzustellen, dass DBMS_CLOUD in allen PDBs installiert wird. Ein SQL Skript mit Namen dbms_cloud_install.sql, das die nötigen Kommandos enthält, wird erzeugt und im Verzeichnis dbc abgelegt. (Der Code findet sich in der MOS Note oder hier).

Hinweis: In den Installationsanweisungen wird davon ausgegangen, dass die Database Vault Option installiert ist. Dies ist allerdings keine Voraussetzung.Tauchen Fehler in diesem Zusammenhang auf, können diese ignoriert werden, wenn die Database Vault-Option nicht installiert ist.

Der Aufruf sieht dann folgendermassen aus:

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/<password> 
  --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle/dbc -l /home/oracle/dbc dbms_cloud_install.sql

catcon.pl: completed successfully



Zur Verfikation können folgende Kommandos hilfreich sein. 

-- im ROOT Container

SQL> select con_id, owner, object_name, status, sharing, oracle_maintained
     from cdb_objects where object_name = 'DBMS_CLOUD' order by con_id;

    CON_ID OWNER                OBJECT_NAME     STATUS  SHARING            O
---------- -------------------- --------------- ------- ------------------ -
         1 PUBLIC               DBMS_CLOUD      VALID   METADATA LINK      Y
         1 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         1 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         3 PUBLIC               DBMS_CLOUD      VALID   METADATA LINK      Y
         3 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         3 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         5 PUBLIC               DBMS_CLOUD      VALID   METADATA LINK      Y
         5 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y
         5 C##CLOUD$SERVICE     DBMS_CLOUD      VALID   METADATA LINK      Y

-- in den PDBs

SQL> select owner, object_name, status, sharing, oracle_maintained 
     from dba_objects where object_name = 'DBMS_CLOUD';

OWNER                OBJECT_NAME          STATUS  SHARING            O
-------------------- -------------------- ------- ------------------ -
PUBLIC               DBMS_CLOUD           VALID   METADATA LINK      Y
C##CLOUD$SERVICE     DBMS_CLOUD           VALID   METADATA LINK      Y
C##CLOUD$SERVICE     DBMS_CLOUD           VALID   METADATA LINK      Y

2) Erzeugen des SSL-Wallets mit Zertifikaten für den Zugriff auf HTTP URIs und den Object Storage

Zuerst müssen die erforderlichen Zertifikate unter folgender URL heruntergeladen und beispielsweise im Verzeichnis /home/oracle/dbc ausgepackt werden. Dann wird ein SSL-Wallet im Verzeichnis /opt/oracle/dcs/commonstore/wallets/ssl mit folgenden orapki Kommandos erzeugt. (Übrigens kann auch jedes andere Verzeichnis gewählt werden.)

cd /opt/oracle/dcs/commonstore/wallets/ssl
orapki wallet create -wallet . -pwd <your_wallet_pw> -auto_login
orapki wallet add -wallet . -trusted_cert -cert /home/oracle/dbc/VeriSign.cer -pwd <your_wallet_pw>
orapki wallet add -wallet . -trusted_cert -cert /home/oracle/dbc/BaltimoreCyberTrust.cer -pwd <your_wallet_pw>
orapki wallet add -wallet . -trusted_cert -cert /home/oracle/dbc/DigiCert.cer -pwd <your_wallet_pw>

Nun wird der Ort des SSL-Wallets in der sqlnet.ora bekanntgegeben.

WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
  (DIRECTORY=/opt/oracle/dcs/commonstore/wallets/ssl)))

Nun werden die Netzwerk ACLs (kurz für Access Control List) über DBMS_NETWORK_ACL_ADMIN eingerichtet. Zur Erinnerung ACLs werden von den Usern benötigt um Netzwerk Zugriffe von der Datenbank zum Beispiel über UTL_HTTP oder UTL_TCP zu steuern. Die nötigen Kommandos können aus der MOS Note oder hier zum Download kopiert werden. Zur einfachen Ausführung sind sie in der Datei dbc_aces.sql gespeichert. Auch hier muss die Variable sslwalletdir richtig ersetzt werden. Das Skript wird im Root Container ausgeführt.

connect sys/<password> as sysdba
@@dbc_aces.sql

Folgende Überprüfung zeigt, dass das Wallet angelegt ist:

SQL> select * from database_properties 
     where property_name in ('SSL_WALLET','HTTP_PROXY');

PROPERTY_NAME        PROPERTY_VALUE
-------------------- ---------------------------------------------
DESCRIPTION 
------------------------------------------------------------
SSL_WALLET           /opt/oracle/dcs/commonstore/wallets/ssl
Location of SSL Wallet

Zur abschliessenden Verifizierung kann man ein spezielles Skript verwenden, das man in der MOS Note findet oder hier herunterladen kann. Bitte nicht vergessen, die Variablen durch die korrekten Einträge zu ersetzen. Das Ergebnis der Ausführung sollte dann “valid response” sein. Ansonsten muss die Konfiguration noch einmal kontrolliert und gegebenenfalls wiederholt werden. 

...
old   2: &clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
new   2: C##CLOUD$SERVICE.GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
valid response
...

Konfiguration von Rollen und User

Nun werden die erforderlichen Rechte entweder über eine Rolle oder direkt an den User vergeben. Beide Verfahren werden in der MOS Note erläutert. Zu Testzwecken wird hier die direkte Rechtevergabe am Beispiel vom User SCOTT gezeigt. Generell ist es natürlich empfehlenswert eine Rolle zu definieren, statt die Privilegien direkt und einzeln zu granten. 

grant CREATE TABLE to SCOTT;
grant read, write on directory DATA_PUMP_DIR to SCOTT;
grant EXECUTE on dbms_cloud to SCOTT;

-- nun fehlen noch die ACLs und ACEs

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- Create New ACL / ACE s
begin
-- Allow all hosts for HTTP/HTTP_PROXY
dbms_network_acl_admin.append_host_ace(
      host =>'*',
lower_port => 443,
upper_port => 443,
       ace => xs$ace_type(
        privilege_list => xs$name_list('http', 'http_proxy'),
        principal_name => upper('SCOTT'),
        principal_type => xs_acl.ptype_db));

-- Fuer den Wallet Zugriff
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:/opt/oracle/dcs/commonstore/wallets/ssl',
        ace => xs$ace_type(privilege_list =>
       xs$name_list('use_client_certificates', 'use_passwords'),
       principal_name => upper('SCOTT'),
       principal_type => xs_acl.ptype_db));
end;
/

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Nun ist die Konfiguration für den User SCOTT abgeschlossen und das Package DBMS_CLOUD kann verwendet werden. 

Einige Anwendungsszenarien 

Im ersten Beispiel wird der User SCOTT ein Bucket des Object Storage auslesen. Zur Erinnerung: Buckets im Object Storage sind logische Container zum Speichern von Objekten. Benutzer oder Systeme erstellen Buckets nach Bedarf innerhalb einer Region. Eine Region ist mit einem einzelnen Compartment verknüpft, das über Richtlinien verfügt, die festlegen, welche Aktionen ein Benutzer durchführen kann.

Das Bucket in meinem Beispiel heißt USBUCKET und sieht folgendermaßen aus:

Bevor wir auf den Inhalt des Buckets zugreifen können, ist eine Authentifizierung über OCI API Signing Keys oder Auth-Tokens erforderlich. Wer sich noch nie mit Cloud Credentials beschäftigt hat, kann beispielsweise im Oracle Cloud Infrastructure Documentation unter Kapitel User Credentials weitere Informationen dazu finden.

Angenommen, wir haben ein Auth-Token erstellt, dann wird ein Credential-Objekt im Datenbankschema für die Authentifizierung erstellt.

SQL> connect scott/<password>@US1
Connected.

SQL> BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'TESTCRED',
     username => 'oracleidentitycloudservice/ulrike.schwinn@oracle.com',
     password => '<passwort'>);
END;
/
PL/SQL procedure successfully completed.

Jetzt können die Dateien aus dem Bucket aufgelistet werden. Erforderlich ist dazu nicht nur die Credential Information sondern auch die URL des Buckets (auch location_uri), die man im Object Storage aus dem Menüpunkt “View Object Details” ableiten kann. 

SQL> select object_name, bytes 
     from DBMS_CLOUD.LIST_OBJECTS('TESTCRED','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/'),

OBJECT_NAME                                             BYTES
-------------------------------------------------- ----------
CHANNELS.gz                                               109
CHANNELS_test.txt                                          79
countries01-13_25_43.DMP                                57344
cwallet.sso                                              6661
emp.csv                                                   703
emp_dept_20.csv                                           114
emp_dept_20.exp                                         12288
emp_dept_30.csv                                           121
emp_dept_30.exp                                         12288
exp_scott.dmp                                               0
exp_scott.dmp_aaaaaa                                   471040

Im nächsten Beispiel wollen wir eine External Table verwenden. Die Dateien  sollen im Object Storage liegen. Nun kommt die Funktion CREATE_EXTERNAL_TABLE zum Einsatz. Für eine erfolgreiche Anwendung ist dabei mindestens die Angabe von Tabellenname, der Credential Name, die FILE_URI_LIST, das Argument FORMAT und das Argument COLUMN_LIST, das die Liste der Spaltennamen und Datentypen, durch Kommata getrennt, für die externe Tabelle angibt, notwendig. Die gespeicherten Dateien müssen dabei nicht unbedingt im Textformat vorliegen. Wie in On-premise Installationen ist auch das CSV Format mit der Formatoption TYPE möglich. Auch vorkomprimierte Dateien lassen sich auslesen. Die Formatoption COMPRESSION für komprimierte Dateien ermöglicht den Zugriff auf Dateien des Komprimierungstyps gzip, zlib und bzip2. Übrigens lassen sich auch Parquet Files verwenden. Bei der Formatoption muss dazu die Option TYPE und der Wert ‘parquet’ verwendet werden.

Folgendes Beispiel zeigt die Verwendung.
 

SQL> drop table channels_ext;

SQL> begin DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'CHANNELS_EXT', 
                      credential_name => 'TESTCRED',
                        file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/CHANNELS.gz',
                               format => json_object('compression' value 'auto','delimiter' value '|' ),
                          column_list =>'channel_short varchar2(1),
                                         channel_long varchar2(20),
                                         channel_class varchar2(20)');
end;
/
SQL> select * from channels_ext;

C CHANNEL_LONG         CHANNEL_CLASS
- -------------------- --------------------
S Direct Sales         Direct
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others

Im letzten Beispiel kopieren wir Daten aus der Datei CHANNELS_test.txt in die Datenbanktabelle CHANNELS.

SQL> drop table channels;

SQL> create table channels
   (channel_id CHAR(1),
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20));

SQL> begin DBMS_CLOUD.COPY_DATA(table_name => 'CHANNELS', 
                           credential_name => 'TESTCRED', 
                             file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/CHANNELS_test.txt', 
                               schema_name => 'SCOTT', 
                                    format => json_object('delimiter' value '|'));
end;
/

SQL> select * from channels;

C CHANNEL_DESC         CHANNEL_CLASS
- -------------------- --------------------
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others

Natürlich gibt es noch viel mehr zum Ausprobieren. Interessante Themen sind sicherlich auch Hybrid partitionierte Tabellen zu verwenden und vieles mehr.
Auf jeden Fall viel Spaß beim Testen und Ausprobieren.

Links und Informationen