Logo Oracle Deutschland   DBA Community  -  August 2013
Pluggable Database, SQL Skripte & catcon.pl
von Sebastian Solbach ORACLE Deutschland B.V. & Co.KG

Mit der neuen Multitenant Datenbankarchitektur mit Container Datenbank (CDB) und den dazugehörigen Pluggable Datenbanken (PDB) kommen auf CDB Ebene einige neue Funktionalitäten hinzu. Zwar ändert sich für den Administrator einer einzelnen PDB kaum etwas, aber einige Spezialitäten sollte der Datenbank Administrator auf Ebene der PDB bzw. CDB doch kennen.

Eine dieser Änderungen ist das Ausführen von einzelnen SQL Skripten auf unterschiedlichen Ebenen. Da PDBs prinzipell über Services angesprochen werden, unterliegen alle Skripte ohne eine explizite Service Spezifizierung einer speziellen Vorgehensweise. Darunter fallen auch einige von Oracle gelieferte Skripte wie z.B. utlsampl.sql oder das Demo Schema Skript mkplug.sql auf $ORACLE_HOME/demo/schema. Diese verwenden einen "direkten" Connect ohne Service Namen, in utlsampl.sql wäre dies "connect scott/tiger".

Noch essentieller trifft es einen CDB Administrator, wenn es darum geht, SQL Skripte auf CDB Ebene und zusätzlich PDB Ebene durchzuführen. Für diesen Anwendungsfall führt Oracle 12c ein Perl Skript ein: catcon.pl.

Skripte ohne Service Spezifizierung (mit direkten Connects)

Generell laufen Applikationsskripte ohne Änderungen auch in der PDB. Hierzu hat der Administrator mehrere Möglichkeiten:

  • das Verbinden an die CDB und dann den Wechsel in die entsprechende PDB via alter session
  • den direkten Connect über den Service Namen, z.B. per Easy Connect Syntax
  • die Verwendung der Environment Variable TWO_TASK
  • Mit Hilfe des Perl Skriptes catcon.pl unter Angabe der entsprechenden CDB
Diese Änderungen müssen aber Applikationen, die SQL Skripte zur Installation ausführen zumindest schon berücksichtigen:
Möglichkeit 1:
$ set ORACLE_SID=CDB
$ sqlplus / as sysdba
SQL> alter session set container = pdb1;
SQL> @utlsampl
Möglichkeit 2:
$ sqplus sys/oracle@localhost:1521/pdb1
SQL> @utlsampl
Utlsampl.sql ist hier absichtlich ein problematisches Beispiel, denn das einfache Skript enthält einen direkten Connect ohne Angabe eines Service Namens. Damit läuft es genau an der Stelle, wo der "connect scott/tiger" durchgeführt wird, auf einen Fehler (zur Sichtbarkeit wurde das utlsampl.sql Skript geändert mit "set ECHO ON" und "set TERMOUT ON"):
SQL> @utlsampl.sql
...
SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
Grant succeeded.

SQL>
SQL> CONNECT SCOTT/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
Mit der Einführung der Multitenant Architektur muss dieser Connect fehlschlagen, da ein "connect / as sysdba" oder ein "connect scott/tiger" in einem SQL Skript nicht die PDB adressiert, sondern die CDB. Die ORACLE_SID verweist auf die CDB und in dieser ist der User SCOTT selbstverständlich nicht angelegt (und sollte ja auch nicht angelegt werden). Schlimmer wäre in Skripten ein "connect / as sysdba" oder ein Connect mit einem existierenden User in der CDB$ROOT, da der Verbindungsaufbau implizit gegen die CDB$ROOT laufen würde und somit Objekte an der komplett falschen Stelle angelegt würden. Eine Möglichkeit wäre natürlich die entsprechenden Skripte mit einem "alter session set container = <pdb>;" anzupassen. Das hätte aber den Nachteil, dass für die Multitenant Architektur andere Skripte benötigt würden, als für eine Non-CDB Datenbank. Alternativ könnte beim Connect der SERVICE Name mitgegeben werden. Das wiederum würde die Flexibilität des Skriptes einschränken, da der entsprechende Service mit entsprechendem Namen vorhanden sein müsste. Als einfacher Workaround für dieses Problem kann mit der TWO_TASK Umgebungsvariable gearbeitet werden, wenn der Service in der TNSNAMES.ORA eingetragen ist:
$ export ORACLE_SID=cdb        
$ export TWO_TASK=pdb1
$ sqlplus sys/oracle as sysdba
SQL> @utlsampl
Allerdings wird hierdurch implizit der Connect @pdb1 durchgeführt (also über den Service), womit ein "connect / as sysdba" nicht funktioniert, sondern nur über Passwort.

Sonderfall Demo Schemata

Da auch die Example Demo Schemata, die sonst über mkplug.sql angelegt werden können, ebenfalls direkte Connects enthalten, hat Oracle für die Erstellung dieser Schemata in einer PDB Umgebung noch einen anderen/zusätzlichen Weg gewählt. Dieser zeigt vielleicht auch auf, wie Applikationen in Zukunft anders installiert werden könnten als über Skripte. Hierzu kann im DBCA (oder manuell) einfach die entsprechende Demo Datenbank mit all ihren Schemata in die CDB geplugged werden.

Hier der Screenshot aus dem DBCA "Manage Pluggable Databases" => "Create a Pluggable Database" => <CDB> => Create Pluggable Database using PDB File set (sampleschema.xml / sampleschema.dfb)



CATCON.PL

Ist ein SQL Skript auf mehreren PDBs gleichzeitig durchzuführen, kommt man nach dem Studium der Dokumentation vielleicht auf die Idee, dass hierfür das Perl Skript catcon.pl geeignet sein könnte. Allerdings muss ein Skript für die Verwendung mit catcon.pl einige Vorraussetzungen erfüllen, um nicht auf Fehler zu laufen:

Dann erlaubt catcon.pl es durchaus, ein SQL Skript gleichzeitig auf mehreren PDBs durchzuführen (und dies durchaus sogar parallelisiert durch den Parameter -p).

Common DDLs in Catcon.pl

Startet man utlsampl.sql einfach ohne oben erwähnte Änderungen mit catcon.pl, werden die unterschiedlichen Fehlerquellen Schritt für Schritt ersichtlich.

$ cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -d /home/oracle 
-l /home/oracle -c PDB1 -b utlsampl utlsampl.sql
Das Ergebnis der Ausführung findet man in den Logdateien utlsampl0.log (-b Parameter) in /home/oracle (-l Parameter). In der Logdatei ist ersichtlich, dass catcon.pl den Befehl des Create Users so nicht zulässt:
$ vi utlsampl0.log
...
SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
...
Dies liegt daran, dass für bestimmte DDLs ein Container mitgegeben werden sollte - in diesem Falle container=current.
$ vi utlsampl.sql
...
SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger container=CURRENT;
...

Direkte Connects und Catcon.pl

Nun läuft zwar der Create User, der direkte "connect scott/tiger" funktioniert aber dennoch nicht. Optimal wäre es, das Skript in 2 Teile aufzuteilen und dies getrennt mit 2 unterschiedlichen catcon.pl aufzurufen: Einen Teil mit dem User SYS oder System (-u SYS) und ein weiteres Mal mit dem Benutzer SCOTT (-u SCOTT).

Zu Demonstrationszwecken teilen wir das Skript aber nun nicht auf, und es reicht uns in diesem Falle aus, einen "alter session set current_schema = SCOTT" anstelle des "connect scott/tiger" im Skript utlsampl.sql zu verwenden.

Skripte mit explizitem Exit mit Catcon.pl

Das EXIT am Ende eines Skriptes sollte bei der Verwendung mit catcon.pl auskommentiert werden, da catcon.pl dies nicht benötigt und sonst unnötig abbricht mit der Fehlermeldung:

A process terminated prior to completion.
Review the /home/oracle/utlsampl*.log files to identify the failure.
Died at catcon.pm line 3648.

Verwendung von Catcon.pl auf mehrere PDBs

Ist das SQL Skript angepasst, kann nun mit Hilfe von catcon.pl das SQL Skript direkt in mehreren PDBs gleichzeitig ausgeführt werden.

$ $ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -d /home/oracle 
-l /home/oracle -c "PDB1 PDB2" -b utlsampl utlsampl.sql
Die einzelnen Logfiles sind nun utlsampl0.log für PDB1 und utsampl1.log für PDB2. Allerdings sollten zur erfolgreichen Ausführung auf allen PDBs alle entsprechenden PDBs Read/Write geöffnet sein, ansonsten findet man in der Logdatei folgende Fehlermeldung
$ vi utlsampl0.log
...
SQL>   2
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = PDB2 ====

SQL>   2
CATCONSECTION
-----------------------------------
==== CATCON EXEC IN CONTAINERS ====

SQL>   2
BEGIN_RUNNING
------------------------------------
==== @/home/oracle/utlsampl.sql ====

SQL> DROP USER SCOTT CASCADE;
DROP USER SCOTT CASCADE
*
ERROR at line 1:
ORA-01109: database not open
...
Daher empfiehlt es sich, vor Ablauf des Befehls ein "alter pluggable database all open" in der CDB abzusetzen.

Eine weitere Funktion von catcon.pl besteht nun darin, ein SQL Skript in der kompletten CDB inklusive aller PDBs (auch CDB$ROOT und PDB$SEED) durchzuführen.
$ $ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -d /home/oracle -l /home/oracle -b utlsampl utlsampl.sql
Allerdings sollte man gerade mit CDB$ROOT und PDB$SEED aufpassen. Es empfiehlt sich, diese (zumindest CDB$ROOT) explizit (-C) auszuschließen. Interessanterweise erlaubt dies nun auch, obwohl PDB$SEED nur als read only in v$pdbs angezeigt wird, den User SCOTT in PDB$SEED mit den gewünschten Objekten anzulegen.

Im Gegensatz dazu ist der Create User in der CDB$ROOT nicht erlaubt, weshalb dieser Test bei mir nun "ungewünschte" Objekte unter SYS in der CDB$ROOT hinterlassen hat. Dies ist im utlsampl0.log, das die Ergebnisse von CDB$ROOT und PDB$SEED enthält, ersichtlich. Im utlsampl1.log findet sich die erfolgreiche Durchführung in den übrigen PDBs.
$ vi utlsampl0.log
...
SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger container=CURRENT;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger container=CURRENT
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
...

User und Catcon.pl

Das sind aber nicht die einzigen Unterschiede von catcon.pl. Einen weiteren stellt man fest, wenn man den User SCOTT nachträglich versucht, in SQL*Plus zu löschen.

SQL> drop user scott cascade;
drop user scott cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative users
User die mit catcon.pl erstellt worden sind, können und sollten auch nur über catcon.pl gelöscht werden.

Der spezielle SESSION Parameter _oracle_script

Beschäftigt man sich näher mit catcon.pl, stößt man relativ schnell auf den undokumentierten _Parameter _oracle_script. Dieser ist nicht nur neuerdings in einigen Oracle eigenen Skripten unter $ORACLE_HOME/rdbms/admin verwendet, sondern wird auch implizit in catcon.pl gesetzt. Damit ist dies auch die Lösung, um den obigen administrativen Benutzer, der mit catcon.pl angelegt wurde, zu löschen, wie es auch in MOS Note 1566042.1: "ORA-28014: Cannot Drop Administrative Users" beschrieben ist.

SQL> alter session set "_oracle_script" = TRUE;
Session altered.
SQL> drop user scott cascade;
User dropped.
Allerdings rate ich dringend von der Verwendung dieses Parameters ab, ohne den Oracle Support mit einzubeziehen. Bei falscher Verwendung ist es hiermit möglich, einige Basisfunktionalitäten der Multitenant Architektur zu verbiegen, wie z.B. wichtige interne Oracle Benutzer für Oracle Grundfunktionalitäten zu löschen. Dies kann zu sehr unvorhergesehenen Ergebnissen führen.

Das Setzen des Parameters ohne explizite Anweisung von Oracle Support geschieht also auf eigene Gefahr.

Aufgrund dieser Tatsache plant Oracle mit einem der nächsten Patches auch, ein catcon.pl Programm bereitzustellen, welches generell für Enduser Skripte gedacht ist und dann auch ohne Gefahr verwendet werden kann, um SQL Skripte auf mehreren PDBs gleichzeitig zu verwenden.

FAZIT

SQL Skripte sind nicht unbedingt 1:1 sofort in der Multitenant Architektur anzuwenden, insbesondere wenn direkte Connects verwendet werden. Beachtet man dies, können die Skripte relativ schnell so umgesetzt werden, dass Sie auch in einer PDB ohne Probleme laufen. Sind Skripte auf mehrere PDBs gleichzeitig anzuwenden, eignet sich catcon.pl unter Berücksichtigung einiger Besonderheiten. So lassen sich selbst vorgefertigte Objekte in der PDB$SEED gleich mit anlegen, obwohl dies nicht unbedingt empfohlen ist. Besser ist es, eine eigene PDB anzulegen, die als SEED für weitere Datenbanken funktioniert.

Nützliche Links und Referenzen

  • Oracle Database Administrator's Guide 12c Release 1 (12.1) - 40 Running Oracle-Supplied SQL Scripts in a CDB
  • ORA-28014: Cannot Drop Administrative Users (Doc ID 1566042.1)
  • Zurück zur Community-Seite