Datenbank auf anderen Server migrieren

Gelegentlich kann es vorkommen, dass eine Datenbank „so wie sie ist“ auf einen anderen Server verschoben werden soll. Kann man dabei eine Downtime in Kauf nehmen, stellt sich die Sache als relativ simpel dar:

Auf dem Zielsystem muss exakt dieselbe Verzeichnisstruktur wie auf dem Quellsystem geschaffen werden; insbesondere die Dump-Verzeichnisse (bdump, udump, etc.), die Control-Files, die Redo-Logs und natürlich die Datenfiles müssen an dieselbe Stelle verschoben werden. Nicht zu vergessen das SPFILE (sofern vorhanden), die INIT.ORA und das Password-File; alle drei Dateien befinden sich überlicherweise unter $ORACLE_HOME/dbs bzw. /database.

Auf Windows-Systemen ist ein weiterer Schritt erforderlich: hier muss der Dienst neu angelegt werden, der die Instanz erzeugt. Dies geschieht mit dem Oracle-Tool ORADIM.

oradim -new -sid orcl

„ORCL“ ist hierbei die zu vergebende SID. Im Dienstemanager von Windows taucht dann sofort der Dienst „OracleServiceORCL“ auf. Nun stellt man diesen ggf. noch auf „Automatisch starten“ und schon kann man die verschobene Datenbank hochfahren.

Im Netzwerk bzw. auf den Clients muss dann noch die Net8-Konfiguration geändert werden, damit die Clients auch wissen, wo sich die neue Datenbank befindet.

User-DDL extrahieren

Mit den folgenden Abfragen kann man ganz elegant DDL-Scripte eines oder mehrerer User aus der Datenbank extrahieren:

SQL> select dbms_metadata.get_ddl('USER','TEST') from dual;
DBMS_METADATA.GET_DDL('USER','TEST')
--------------------------------------------------------------------------------
   CREATE USER "TEST" IDENTIFIED BY VALUES 'S:BB9F382BFDA8AF92EE4AFC033609CAB510
1E5ED198D7CB942F4E9ABDD038;8CF86D821FB0249F'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT','TEST') from dual;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TEST')
--------------------------------------------------------------------------------
   GRANT "CONNECT" TO "TEST"
   GRANT "RESOURCE" TO "TEST"

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','TEST') from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST')
--------------------------------------------------------------------------------
  GRANT UNLIMITED TABLESPACE TO "TEST"

SQL> select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'TEST') from dual;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TEST')
--------------------------------------------------------------------------------
  GRANT SELECT ON "SYS"."SYS_DUMMY" TO "TEST"

Setzt man alle vier Abfragen untereinander, hat man ein funktionsfähiges Script, um einen bestehenden User zu „klonen“:

select dbms_metadata.get_ddl('USER','TEST') from dual
union
select dbms_metadata.get_granted_ddl('ROLE_GRANT','TEST') from dual
union
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','TEST') from dual
union
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'TEST') from dual;

Der Nachteil dieser Vorgehensweise ist die hässliche Eigenart von Oracle, dass, wenn ein Aufruf von GET_GRANTED_DDL keinen Wert zurückliefert (wenn z.B. keine Rechte vergeben sind), folgende Fehlermeldung erscheint:

SQL>  select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) from dual;
ERROR:
ORA-31608: Angegebenes Objekt vom Typ OBJECT_GRANT nicht gefunden
ORA-06512: in "SYS.DBMS_SYS_ERROR", Zeile 86
ORA-06512: in "SYS.DBMS_METADATA", Zeile 3915
ORA-06512: in "SYS.DBMS_METADATA", Zeile 5826
ORA-06512: in Zeile 1

Es wurden keine Zeilen ausgewahlt

Wie man das o.g. Script so ergänzen kann, dass diese Meldung vorher abgefangen werden, erfährt man bspw. hier und hier.

Wo ist der CONSISTENT-Parameter bei Data Pump?

Beim Einsatz von Data Pump ab 10g mag sich mancher wundern, wo der CONSISTENT-Parameter geblieben ist. Dieser Parameter stellte beim EXP-Tool die Konsistenz der Daten während des gesamten Exports sicher — kein völlig unwichtiges Feature also.

Bei Data Pump (EXPDP / IMPDP) übernehmen diese Funktion die Parameter FLASHBACK_SCN und FLASHBACK_TIME. Diese Parameter schließen sich bei Benutzung gegenseitig aus. Der Einsatz von FLASHBACK_SCN sichert die Konsistenz eines Exports zu einer bestimmten SCN, und FLASHBACK_TIME zu einem bestimmten Zeitpunkt. Üblicherweise würde man hier SYSDATE bevorzugen.

Das Ganze liest sich dann wie folgt:

#> expdp system/passwd directory=flsh dumpfile=user001_2.dmp
  logfile=user001_2.log schemas=usr001
  flashback_time="TO_TIMESTAMP (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
    'YYYY-MM-DD HH24:MI:SS')"

Hierzu wird übrigens das Feature „Flashback Query“ herangezogen, die FLASHBACK AREA muss hierfür nicht definiert sein. „Flashback Query“ bedient sich aus dem UNDO-Tablespace.

Interessant hierzu ist der Metalink Artikel 377218.1: Expdp Message „FLASHBACK automatically enabled“ Does Not Guarantee Export Consistency. Demnach wirft EXPDP die Meldung „FLASHBACK automatically enabled“ aus, was aber nicht automatisch heißt, dass der gerade gestartete Export konsistent ist. Um dies zu erreichen, muß immer mit den Parametern FLASHBACK_SCN oder FLASHBACK_TIME gearbeitet werden.

Bei EXP/IMP kannte nur EXP den CONSISTENT-Parameter, bei Datapump sind es sowohl EXPDP wie auch IMPDP — schließlich kann IMPDP via Network-Link auch „exportieren“.

Schema klonen mit Datapump

Oracle hat mit Datapump ab 10g ein neues Tool eingeführt, das die alteingesessenen, aber bewährten Tools EXP und IMP ablösen soll. Dabei ist es deutlich mächtiger, bringt aber auch einige Einschränkungen im Vergleich zur bisherigen Vorgehensweise mit sich. Doch dazu in einem späteren Artikel mehr.

Datapump kann hervorragend zum Klonen ganzer Schemata (neudeutsch auch Schemas) benutzt werden, und das sogar ohne eine Export-Datei anzulegen.

Hierzu bedient man sich der NETWORK_LINK-Funktionalität von Datapump, in dem es unter Umgehung des Filesystems die zu importierenden Daten direkt aus der Quelldatenbank saugt. Notwendig hierzu ist ein DATABASE LINK auf die Quelldatenbank. Will man ein Schema in der gleichen Datenbank klonen, legt man einfach einen Link auf die eigene Datenbank an (loopback).

Beispiel: wir möchten die Schemas SCOTT und EMILY in die Schemas SCOTT_CLONE und EMILY_CLONE klonen. Quell- und Zieldatenbank sind identisch: ORCL.

Zunächst wird der Database Link angelegt, z.B. mit

CREATE PUBLIC DATABASE LINK "TO_ORCL" CONNECT TO "SYSTEM" IDENTIFIED by "manager" USING 'orcl'

Wie der Dokumentation zu entnehmen ist, sollte für das Logfile ein Directory-Objekt vorhanden sein:

CREATE DIRECTORY "DATA_PUMP_DIR" AS '/u01/oracle/admin/ORCL/dpdump'

Dann startet man den IMport, denn der EXport wird bei Verwendung des Network-Modus impliziert:

impdp system network_link=to_orcl logfile=cloning.log
remap_schema='SCOTT':'SCOTT_CLONE','EMILY':'ELIMY_CLONE' schemas=SCOTT,EMILY

Dies ist ein Beispiel mit zwei zu klonenden Schemas, das funktioniert natürlich auch mit einem oder auch drei equivalent.

Wichtig ist, dass man SCHEMAS=… angibt, sonst kopiert Datapump die gesamte Datenbank (was nicht funktionieren würde), und remappt dabei die angegebenen Schemas.

Typisches RMAN-Backup-Script

Heute stellen wir ein typisches RMAN-Backup-Script vor, dass man in einer 10g-Umgebung zur vollständigen Datenbanksicherung benutzen kann. Zunächst meldet man sich beim RMAN an (ORACLE_SID ist gesetzt):

rman target /

Hier wird das Controlfile als Katalog benutzt, kein Katalog-Repository. Die aktuellen Einstellungen lässt man sich mit

RMAN> show all;

ausgeben.

Wir ändern die grundlegenden Einstellungen:

RMAN> configure channel device type disk format ‘z:\backup\rman_%d_t%t_s%s_p%p’;
=> Bestimmt das Zielverzeichnis für die Sicherung und den Aufbau der Dateinamen.

RMAN> configure controlfile autobackup format for device type disk to ‘z:\backup\%F’;
=> Bestimmt das Zielverzeichnis für das Controlfile-Autobackup. %F ist die DBID und sollte im Namen vorkommen.

RMAN> configure controlfile autobackup on;
=> Das automatische Backup des Controlfiles wird eingeschaltet.

RMAN> configure retention policy to redundancy 2;
=> Es werden 2 Backup-Sätze aufbewahrt.

RMAN> configure backup optimization on;
=> Es werden nur neue Archivelogs gesichert, nicht immer alle.

Mit diesem Script starten wir das Backup:

RMAN> run {
backup database plus archivelog delete all input;
backup current controlfile;
backup spfile;
}

Zunächst werden alle Datendateien gesichert. Dann wird das aktuelle Logfile archiviert, die Archivelogs gesichert, das dann aktuelle Logfile ebenfalls archiviert und auch gesichert. (Diesen ganzen Vorgang steuert ab 10g das ‘plus archivelog’-Statement.) Zu guter Letzt werden die gesicherten Archivelogs aus dem Quellverzeichnis (z.B. der Flash Recovery Area) gelöscht.

Das „backup current controlfile“ benötigt man eigentlich nicht, wenn „autobackup controlfile“ aktiviert ist, aber doppelt genäht hält einfach besser. Außerdem ist man als DBA grundsätzlich mißtrauisch.

Am Ende wird das SPFILE gesichert, was natürlich nur Sinn macht, wenn die Datenbank auch mit einem SPFILE gestartet wurde.

Überprüfen kann man das Backup z.B. mit

RMAN> report need backup;

Hier darf es kein Ergebnis geben, sofern das Kommando zeitnah nach dem Backup ausgeführt wird.

RMAN> list backup summary;

…listet alle Backups auf.

RMAN> restore database validate;

…prüft, ob die Datenbank im Fehlerfall wieder vollständig zurückgeholt werden könnte.

Achtung: RMAN sichert unter Windows nicht auf Netzlaufwerken und/oder in UNC-Pfaden. Ein Backup muss auf eine lokale Platte stattfinden, anschließend kann z.B. mit ROBOCOPY das lokale Backup-Verzeichnis mit einem Pfad im Netzwerk abgeglichen werden.

Tablespace-Belegungshistorie

Wer wissen möchte, wie sich seine Tablespaces in den letzten Wochen speicherplatzmäßig entwickelt haben, kann dies ganz einfach durch ein einfaches Query herausfinden, ohne dazu teure Zusatztools bemühen zu müssen:

select ts.name „Tablespace“,
vor_4_wochen.full „FULL%_4_WEEKS_AGO“,
letzte_woche.full „FULL%_LAST_WEEK“,
heute.full „FULL%_TODAY“
from (select tsu.tablespace_id,
round(
tsu.tablespace_usedsize/tsu.tablespace_maxsize*100,0) as
full
from wrh$_tablespace_space_usage tsu
where to_date(substr(tsu.rtime,1,13),’MM/DD/YYYY
HH24′)=to_date(to_char(trunc(sysdate),’MM/DD/YYYY
HH24′),’MM/DD/YYYY HH24′)
) heute,
(select tsu.tablespace_id,
round(
tsu.tablespace_usedsize/tsu.tablespace_maxsize*100,0) as
full
from wrh$_tablespace_space_usage tsu
where to_date(substr(tsu.rtime,1,13),’MM/DD/YYYY
HH24′)=to_date(to_char(trunc(sysdate-7),’MM/DD/YYYY
HH24′),’MM/DD/YYYY HH24′)
) letzte_woche,
(select tsu.tablespace_id,
round(
tsu.tablespace_usedsize/tsu.tablespace_maxsize*100,0) as
full
from wrh$_tablespace_space_usage tsu
where to_date(substr(tsu.rtime,1,13),’MM/DD/YYYY
HH24′)=to_date(to_char(trunc(sysdate-28),’MM/DD/YYYY
HH24′),’MM/DD/YYYY HH24′)
) vor_4_wochen,
v$tablespace ts
where ts.ts# = heute.tablespace_id
and ts.ts# = letzte_woche.tablespace_id
and ts.ts# = vor_4_wochen.tablespace_id
order by 4 desc;

Wichtig ist, dass die Datenhaltungszeit der ADDM-Statistiken > 28 Tage eingestellt ist und dass die Statistiken stündlich (alle 60 Minuten) gesammelt werden. Ansonsten liefert das Script eine leere Menge zurück.

E-Mail-Versand in Triggern

Aus dem Sekretariat kam folgende Anforderung: ändert sich die Spalte einer bestimmten Tabelle auf einen bestimmten Wert, soll eine E-Mail mit Informationen dieser Transaktion versendet werden.

Die Herangehensweise ist klar: ein On-Update-Trigger ruft UTL_MAIL (oder UTL_SMTP vor 10g) auf und versendet die E-Mail. Dieser Lösungsweg ist aber nur dann akzeptabel, wenn sichergestellt ist, dass der Benutzer diese Transaktion nicht mehr rückgängig machen kann, z.B. wenn die Applikation kein Rollback zulässt.

Wenn nämlich ein Rollback erfolgt, hat die Spalte wieder ihren ursprünglichen Wert, der Versand der E-Mail ist allerdings bereits erfolgt und kann nicht mehr rückgängig gemacht werden.

Tom „AskTom“ Kyte beschreibt im Oracle Magazine und in seinem Blog eine Vorgehensweise, die dieses Verhalten verhindert:

Die Idee ist, dass man statt des E-Mail-Versandes einen Eintrag in die Queue von DMBS_JOBS vornimmt, die sich um den Versand der Post kümmert. Der Insert in die Job-Queue ist ja auch eine Transaktion, wird also nur vorgenommen, wenn die ursprüngliche Transaktion (die, die den Trigger auslöst) commited wird. Im Detail funktioniert das so:

SQL> create table do_ddl (job number primary key, stmt varchar2(4000));
Table created.

Diese Tabelle beinhaltet die Jobnummern und die Statements, die im jeweiligen Job ausgeführt werden sollen. In unserem Fall z.B. UTL_MAIL.SEND (…).

Anschließend liegen wir eine Stored Procedure an, die die Jobtabelle ausliest und die dort hinterlegten Statements ausführt:

SQL> create or replace procedure do_ddl_safely (p_job in number) is
2   l_rec do_ddl%rowtype;
3   begin
4     select * into l_rec from do_ddl where job = p_job;
5     execute immediate l_rec.stmt;
6   end;
7   /
Procedure created.

Im Trigger schließlich wird der Job in der Queue angelegt:

SQL> declare
2   l_job number;
3   begin
4     dbms_job.submit (l_job, 'do_ddl_safely (JOB);' );
5     insert into do_ddl (job, stmt) values (l_job, 'begin utl_mail.send (...) end;');
6   end;
7   /

Was passiert also?

1. Der Trigger legt einen Job an und schreibt das Statement mit der Jobnummer in die Tabelle DO_DDL.
2. Da für die ursprüngliche Transaktion noch kein Commit vorliegt, wird weder das DBMS_JOB.SUBMIT noch der Insert in die Tabelle wirklich ausgeführt.
3. Beim Rollback wird die ursprüngliche Transaktion (das Ändern des Spaltenwertes) sowie Schritt 1 rückgängig gemacht
   oder
   beim Commit wird die Änderung in der Spalte festgeschrieben, der Job wird erstellt und die Tabelle DO_DDL befüllt.
4. Der Job wird kurzzeitig nach dem Commit ausgeführt und die E-Mail wird versendet.

Diese Vorgehensweise ist natürlich nicht nur auf den Versand von E-Mails beschränkt, sondern eignet sich hervorragend, um ganz allgemein DDL in Triggern auszuführen.

Denn wie schreibt Mr. Kyte so schön:

Whenever you are tempted to do something nontransactional in a trigger, think 500 times more about it and then always decide againt it. It can lead only to really bad things.

Data Recovery Advisor in 11g

Seit Oracle 11g gibt es ja einige neue Backup- und Recovery-Features, unter anderem auch den Data Recovery Advisor.

Dieser neue Assistent soll Fehler ermitteln, klassifizieren, darstellen und auch lösen. In bunten Oracle-Bildchen sieht das dann so aus:

(Quelle: Oracle)

Wir haben den Ratschlaggeber Advisor mit einer kleinen Aufgabe auf die Probe gestellt: man nehme eine x-beliebige Oracle-Datendatei, zerstückele sie ein bisschen und warte, was passiert.

Zunächst die Ausgangslage: wir suchen uns die Datei EXAMPLE01.DBF aus, die zum Beispielschema der Datenbank gehört. Der Benutzer HR greift mit seinen Objekten auf diese Datei zu.

Wir öffnen die Datei mit dem VI und löschen mitten in der Datei wahllos zwei Zeilen (Kommando „dd“). Dann speichern wir die nun logisch korrupte Datei mit „wq!“ ab.

Der nächste Screenshot zeigt, dass sich die Größe der veränderten EXAMPLE01.DBF vom Ausgangszustand unterscheidet.

Als Benutzer HR führen wir nun ganz unbedarft im SQL*Plus-Statement aus, das potenziell die von uns zerstörten Daten betrifft.

Und tatsächlich — wir haben auf Anhieb die kaputte Stelle getroffen und werden als Anwender sofort über den Schadensfall informiert. Falls wir wissend sind und genügend Rechte haben, können wir noch ein

analyze <Tabelle> validate structure;

ausführen und Oracle die Struktur der Tabelle prüfen lassen. Auch hier bekommen wir eine Fehlermeldung. (Falls der Anwender den Analyze-Befehl nicht ausführen kann, sollte der DBA auf diese Weise die Integrität der Tabelle überprüfen.) Nun ist es also höchste Zeit, den DBA anzurufen.

Dieser versucht sich ggf. mit dem DBVERIFY-Utility einen Überblick über die Lage zu verschaffen.

Im Enterprise Manager wird mittlerweile auch schon vor einem „kritischen Vorfall“ gewarnt.

Die Fehlerbeschreibung ist eindeutig: EXMAPLE01.DBF ist defekt und somit sind einige Objekte im Example-Tablespace nicht verfügbar.

Der Recovery-Advisor bietet sogleich seine Hilfe an.

Die Empfehlung wird anhand des RMAN-Skripes dargestellt, das der Advisor zur Lösung des Problems ausführen möchte, so man ihn denn lässt: Hier soll die Datei EXAMPLE01.DBF aus dem vorhandenen RMAN-Backup wiederhergestellt und dann mittels der Archive-Log-Dateien auf den Stand vor dem Crash aktualisiert werden.

Gibt man dem Advisor freie Fahrt, legt er das RMAN-Skript als Job an und macht sich sogleich an die Arbeit…

Während der Laufzeit des Jobs hat man eine Übersicht über die einzelnen Schritte:

…und fertig!

Das ausgeführte Skript liest sich dann wie folgt:

RMAN-Report (PDF)

Fazit: In unserem einfachen Testszenario leistet der Advisor gute Arbeit. Man muss sich nicht um RMAN-Skripte kümmern, hat jederzeit den Überblick und konzentriert sich auf das Wesentliche. Wie sich der Advisor bei komplexeren Problemen verhält, bleibt auszutesten. Allerdings darf man nicht die eierlegende Wollmilchsau erwarten, denn Oracle setzt dem Advisor relativ enge Grenzen:

A. Physical corruption such as block checksum failures and invalid block header field values
B. I/O failures such as hardware errors and operating system driver failures
C. Inconsistencies such as a datafile that is older than other database files
D. Failures on standby databases

Für diese Fälle wird er sich gut eignen, nimmt dem DBA aber nicht die verantwortungsvolle Planung und Administration eines sicheren Backup- und Recovery-Konzeptes ab.