Gleich vorweg: EXPDP/IMPDP ist keine valide Backup-Lösung, da dies nur eine Momentaufnahme erstellt und somit per Definition alle nachfolgenden Änderungen verloren gehen. Außerdem muss explizit dafür gesorgt werden, dass der erzeugte Dump konsistent ist – dazu später mehr – und beim Importieren per IMPDP kann es auch die eine oder andere Überraschung geben.
In diesem Artikel beschreibe ich die typischen Probleme und deren Lösungen, setze aber bei den Beispielen mindestens Oracle 12c voraus, da erst ab dieser Version in den Views DBA_USERS und DBA_ROLES die Spalte ORACLE_MAINTAINED vorhanden ist. Bei älteren Oracle-Versionen müssen in die Abfragen leider hardcoded die entsprechenden Benutzer aufgenommen werden.
Der Artikel berücksichtigt nicht die vielen Optionen, mit denen Objekte manipuliert oder umbenannt werden können.
Wie exportiert man richtig?
Es gibt seitens EXPDP viele Varianten beim Exportieren (FULL, SCHEMA, TABLE). In allen Fällen kann es beim Import zu unvorhergesehenen Problemen kommen. Damit nicht erst beim IMPDP auffällt, dass Informationen fehlen, empfehlen wir immer einen FULL Database Export zu machen.
# FULL Database Export - zu exkludierende Schemas
Bei einem FULL Database Export dürfen die von Oracle erzeugten Schemas nicht mitgenommen werden. Welche das sind, kann folgende Abfrage beantworten:
Für Unix:
SELECT listagg('\'''||username||'\''',',') within group(order by username) FROM dba_users where oracle_maintained='Y' or username like 'APEX%';
Für Windows:
SELECT listagg(''''||username||'''',',') within group(order by username) FROM dba_users where oracle_maintained='Y' or username like 'APEX%';
Diese Liste an Namen wird dem Parameter EXCLUDE=SCHEMA::\"IN \(<output_von_query>\)\" beim EXPDP mitgegeben.
APEX schließe ich zusätzlich ein – den Grund beschreibe ich am Ende dieses Artikels.
Natürlich könnte man beim EXPDP alle Schemas mitnehmen, dann müssten aber die Oracle-Schemas beim IMPDP exkludiert werden, da es sonst zu Fehlern kommt.
# UNIFIED AUDIT exkludieren
In den meisten Fällen wird man die Informationen aus UNIFIED AUDIT nicht übertragen wollen. Auch wenn das Schema AUDSYS exkludiert wird, werden die Daten trotzdem exportiert. Damit das verhindert werden kann, muss beim EXPDP folgende Option mitgeben werden: EXCLUDE=AUDIT_TRAILS
# EXPDP konsistent erzeugen
Das Default-Verhalten ist, dass der EXPDP jede Tabelle für sich lesekonsistent exportiert. Wird das so belassen, gibt es beim Import Probleme, weil die Tabellen untereinander zu verschiedenen Zeitpunkten konsistent sind. Um das zu verhindern, muss der Parameter FLASHBACK_TIME (oder FLASHBACK_SCN) gesetzt werden – siehe Beispiel weiter unten.
# Wo sollen die EXPDP / IMPDP Files liegen?
Das/die erzeugten Dumpfile(s) landen in einem Database Directory. Oracle bringt ein Default Directory mit dem Namen DATA_PUMP_DIR mit, das auf $ORACLE_BASE/admin/<ORACLE_SID>/dpdump zeigt.
In vielen Fällen ist dort im Filesystem aber nicht ausreichend Platz, daher sollte im Vorfeld ein Database Directory für eine passende Location angelegt werden:
CREATE DIRECTORY MY_DUMPDIR as '/verzeichnis/mit/viel/platz/';
# Beispiel für einen sinnvollen FULL Database Export
Dieses Beispiel enthält die zuvor besprochenen Parameter, wobei das EXCLUDE=SCHEMA auch erst beim IMPDP genutzt werden kann.
expdp "'/ as sysdba'" full=y directory=MY_DUMPDIR dumpfile=full_expdp.dmp logfile=full_expdp.log flashback_time=systimestamp EXCLUDE=AUDIT_TRAILS EXCLUDE=SCHEMA:\"IN \(\'ANONYMOUS\',\'APPQOSSYS\',\'AUDSYS\',\'CTXSYS\',\'DBSFWUSER\',\'DBSNMP\',\'DIP\',\'DVF\',\'DVSYS\',\'GGSYS\',\'GSMADMIN_INTERNAL\',\'GSMCATUSER\',\'GSMROOTUSER\',\'GSMUSER\',\'LBACSYS\',\'MDDATA\',\'MDSYS\',\'OJVMSYS\',\'OLAPSYS\',\'ORACLE_OCM\',\'ORDDATA\',\'ORDPLUGINS\',\'ORDSYS\',\'OUTLN\',\'REMOTE_SCHEDULER_AGENT\',\'SI_INFORMTN_SCHEMA\',\'SYS\',\'SYS$UMF\',\'SYSBACKUP\',\'SYSDG\',\'SYSKM\',\'SYSRAC\',\'SYSTEM\',\'WMSYS\',\'XDB\',\'XS$NULL\'\)\"
Was sollte vor dem IMPDP gemacht werden, um Probleme zu vermeiden?
Das ist keine vollständige Auflistung! Es sind nur die mir in der Praxis am häufigsten untergekommenen Probleme. Der Full Database Import macht mehr als der Schema-Import – in diesem Artikel wird nur der Full Import betrachtet. Aus Performancegründen sollte die Ziel-Datenbank nicht im Archivelog-Modus sein.
# Tablespaces, Datafiles und Pfade
Der EXPDP (FULL DATABASE) und der IMPDP legen zwar Tablespaces und Datenbankfiles an, nicht immer will man die aber 1:1 übernehmen!
Die folgende Abfrage erzeugt CREATE TABLESPACE Statements, wobei die Pfade für die Datenbankfiles entsprechend korrigiert werden müssen:
SELECT 'CREATE ' || decode (CONTENTS,'PERMANENT','',CONTENTS) ||' TABLESPACE ' || TABLESPACE_NAME || ' ' ||
decode(contents,'TEMPORARY','TEMP','DATA')||'FILE ''d:\oradata\MYDB\' || TABLESPACE_NAME || '.dbf'' SIZE 100M AUTOEXTEND ON NEXT 100M;'
FROM dba_tablespaces
where tablespace_name not in ('UNDOTBS','SYSTEM','SYSAUX','TEMP', 'USERS', 'UNDOTBS1','UNDOTBS2','UNDOTBS01');
Allerdings legt das Statement pro Tablespace nur ein Datenbankfile an. Das kann unter Umständen zu wenig sein. Wenn die Datenbank größer ist, kann folgendes PL/SQL-Programm eingesetzt werden, um eine ausreichende Anzahl an Datenbankfiles pro Tablespace zu bekommen.
set serveroutput on;
declare
begin
for t in (
select 'CREATE ' || decode (CONTENTS,'PERMANENT','',CONTENTS) ||' TABLESPACE ' || TABLESPACE_NAME || ' ' ||
decode(contents,'TEMPORARY','TEMP','DATA')|| 'FILE ''d:\oradata\MYDB\' || TABLESPACE_NAME || '.dbf'' SIZE 100M AUTOEXTEND ON NEXT 100M;' as text
from dba_tablespaces
where tablespace_name not in ('UNDOTBS','SYSTEM','SYSAUX','TEMP', 'USERS', 'UNDOTBS1','UNDOTBS2','UNDOTBS01'))
loop
dbms_output.put_line(t.text);
end loop;
for i in (
Select round((a.used_GB/20),0) as anz_files, a.tablespace_name
from ( select sum(bytes/1024/1024/1024) used_GB, tablespace_name from dba_segments
group by tablespace_name) a
where a.used_GB/20 >= 1)
loop
FOR b IN 1..i.anz_files
LOOP
dbms_output.put_line('ALTER TABLESPACE '|| i.tablespace_name || ' ADD DATAFILE ''d:\oradata\MYDB\' || i.TABLESPACE_NAME || b || '.dbf'' SIZE 100M AUTOEXTEND ON next 100M;');
END LOOP;
end loop;
end;
/
Durch die Berechnung round((a.used_GB/20),0) wird festgelegt, dass ein Datenbankfile beim IMPDP voraussichtlich maximal 20 GB groß werden soll.
Damit ist noch Raum für Wachstum vorhanden, ohne dass gleich weitere Datenbankfiles angelegt werden müssen.
# Applikationsrollen sollten vor dem Import existieren
EXPDP kann GRANTS auf Rollen enthalten, bevor die Rollen überhaupt durch den IMPDP angelegt werden. Dadurch gehen diese Grants verloren und das endet oft in Troubleshooting, weil die Applikation nicht funktioniert. Natürlich können die fehlgeschlagenen GRANTS aus dem IMPDP Logfile extrahiert werden, das kann aber aufwendig sein. Daher ist es zu empfehlen, die Rollen schon im Vorfeld anzulegen.
Bei normalen Rollen geht das ganz einfach, nur bei Rollen mit Passwort oder anderer Authentication wird es komplizierter. Die folgende Query gibt aus, welche Rollen in der Datenbank angelegt wurden und ob diese ein Passwort oder eine andere Authentifizierung nutzen – in diesem Fall muss entsprechend mehr getan werden:
select ROLE, PASSWORD_REQUIRED, AUTHENTICATION_TYPE, COMMON from dba_roles where oracle_maintained='N';
Sofern hier in PASSWORD_REQUIRED nicht NO ist und in AUTHENTICATION_TYPE nicht NONE steht, muss mit den Applikationsverantwortlichen geklärt werden, was dafür benötigt wird.
In diesem Fall ist es meistens einfacher, wirklich die fehlgeschlagenen GRANTS aus den IMPDP Logfiles zu extrahieren.
In allen anderen Fällen liefert das folgende Statement die CREATE ROLE Statements, die dann einfach vor dem Import in die Datenbank eingespielt werden:
select 'CREATE ROLE ' || ROLE ||';' as STMT from dba_roles where oracle_maintained='N' and PASSWORD_REQUIRED='NO' and AUTHENTICATION_TYPE='NONE';
# Applikations-Objekte im Schema SYS angelegt
Grundsätzlich sollten Objekte, die zur Applikation gehören, niemals im Schema SYS angelegt werden. Es gibt jedoch Situationen, wo es wirklich nicht anders geht. In diesem Fall gehört dies explizit dokumentiert! Leider ist das in der Praxis oft nur ein frommer Wunsch. Die typische Situation ist oft, dass es entweder nie dokumentiert wurde oder sich niemand an die Dokumentation erinnern kann.
Somit ergibt sich die Frage: Wie könnte man diesen Umstand erkennen? Das hängt davon ab, wie und wann die Datenbank erzeugt wurde und wann die Applikationsobjekte erzeugt wurden. Sofern dies nicht am gleichen Tag erfolgt ist, kann mit folgenden Abfragen versucht werden, die Objekte zu identifizieren:
with ZEITPUNKTE AS
(select OWNER, TRUNC(CREATED) AS OBJECT_ERZEUGT, COUNT(*) ANZAHL
from DBA_OBJECTS
where OWNER='SYS'
group by OWNER, TRUNC(CREATED)
having COUNT(*) < 10)
select OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME
from DBA_OBJECTS
where TRUNC(CREATED) in (select OBJECT_ERZEUGT from ZEITPUNKTE)
and OWNER not in (select USERNAME from DBA_USERS where ORACLE_MAINTAINED='Y' and username <>'SYS')
and OBJECT_NAME not like 'WRI$_OPTSTAT%'
and OBJECT_NAME not like 'SYS_IL%$$'
and OBJECT_NAME not like 'SYS_LOB%$$'
and OBJECT_NAME not like 'WRP$_REPORTS%'
and not (OWNER='PUBLIC' and OBJECT_TYPE='SYNONYM')
order by CREATED, OWNER, OBJECT_NAME;
Was macht diese Anfrage genau?
In der WITH Query werden zuerst jene Zeitpunkte (Tage) ermittelt, an denen im Schema SYS maximal 10 Objekte angelegt wurden - typischerweise werden von Applikationen nur wenige Objekte im SYS-Schema angelegt.
Mit diesen Zeitpunkten wird dann nochmals überprüft, welche Objekte (ausgenommen die Oracle-Datenbank-Schemas) zum gleichen Zeitpunkt mit diesen Objekten im SYS-Schema angelegt wurden. In der Regel werden ja die SYS-Schema-Objekte gemeinsam mit den Applikationsobjekten im Applikationsschema angelegt.
Das Ergebnis ist eine Liste aller Objekte, die im Applikationsschema und im SYS-Schema am gleichen Tag erzeugt wurden. Die zusätzlichen Ausschließungen von bestimmten Objekt-Namen und -Typen sorgt nur dafür, dass Oracle-interne Objekte nicht gewertet werden. Die Ausgabe muss nun durchgegangen werden und man kann somit erkennen, welche Objekte im Schema SYS potentiell zur Applikation gehören könnten. Natürlich wird es dabei einige FALSE-Positiv geben, ganz ohne Nachdenken und Checken der Objekte wird es nicht gehen.
Importieren mit IMPDP
Sobald die Vorarbeiten erledigt sind, gestaltet sich der IMPDP in der Regel recht einfach.
Nachdem das Datenbank-Directory wieder angelegt wurde, kann der IMPDP erfolgen.
CREATE DIRECTORY MY_DUMPDIR as '/verzeichnis/mit/viel/platz/';
impdp "'/ as sysdba'" DIRECTORY=MY_DUMPDIR FULL=y dumpfile=full_expdp.dmp logfile=full_impdp.log
Nacharbeiten nach dem IMPDP
Je nachdem, welche Funktionalitäten genutzt werden, sind noch einige Nacharbeiten zu erledigen.
# Objekt Grants von SYS an andere Schemas oder Rollen
Werden an Benutzer oder Rollen direkt vom SYS-Schema aus Rechte gegrantet, wird dies nicht mitgenommen. Das folgende Statement erzeugt die fehlenden Grants inkl. aller PUBLIC Grants – hier kann man nicht feststellen, ob diese seitens Oracle oder eines DBAs vergeben wurden.
Da es bei Oracle kein Problem ist, wenn es den gleichen Grant mehrfach gibt, ist dies aber unkritisch.
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' as STMT
from dba_tab_privs
where owner='SYS' and GRANTEE not in (select USERNAME from DBA_USERS where ORACLE_MAINTAINED='Y' union all select ROLE from DBA_ROLES where ORACLE_MAINTAINED='Y');
# Java Grants von SYS an andere Schemas oder Rollen
Leider werden auch Java Grants nicht automatisch mitgenommen. Die folgende Query liefert die Informationen, welche Grants möglicherweise fehlen. Am besten den Output von der Quelle mit dem Ziel vergleichen.
select TYPE_SCHEMA, TYPE_NAME, NAME, GRANTEE, ENABLED
from dba_java_policy
where grantee not in (select USERNAME from DBA_USERS where ORACLE_MAINTAINED='Y' union all select ROLE from DBA_ROLES where ORACLE_MAINTAINED='Y')
and kind = 'GRANT';
# Proxy User
Weiter gibt es sehr oft Probleme mit PROXY-Usern. Der Grund ist, dass die CONNECT-THROUGH-Berechtigung beim EXPDP nicht mitgenommen wird.
Das folgende Statement erzeugt die benötigten ALTER-USER-Statements:
select 'alter user "'||client||'" grant connect through "'||proxy||'";' as STMT from proxy_users;
# Application Context
Nutzt die Applikation "Application Context", sollte immer verifiziert werden, dass diese korrekt angelegt wurden:
select 'CREATE OR REPLACE CONTEXT '||NAMESPACE||' USING '||SCHEMA||'.'||PACKAGE||' '||TYPE||';' from dba_context where schema not in (select username from dba_users where oracle_maintained='Y');
# Invalide Objekte
Nach einem IMPDP sind in der Regel viele Objekt invalid. Nach einem Rebuild mit folgendem Statement
sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql
exit;
sollte verifiziert werden, welche Objekte in der Quell- und Ziel-Datenbank invalid sind – es sollten die gleichen Objekte sein!
select owner,object_name, object_type from dba_objects where status <>'VALID';
Hinweis: Sind in der Quelldatenbank mehr Objekte invalid, sollt dort ebenfalls mittels UTLRP.sql ein Rebuild gestartet und erneut geprüft werden!
# Database Directories
Oft werden Database Directories als Benutzer SYS angelegt oder die Pfade modifiziert. Diese werden vom EXPDP leider nicht mitgenommen!
select * from dba_directories;
Sowohl in der Quell- als auch der Ziel-Datenbank ausgeben und vergleichen. Wenn es Unterschiede gibt, müssen diese entsprechend korrigiert werden.
# Database Links
Wenn Datenbank-Links als Benutzer SYS angelegt wurden, werden diese ebenfalls nicht exportiert.
So kann das in der Quell-Datenbank geprüft werden:
select owner, DB_LINK, USERNAME, HOST, CREATED from dba_db_links where OWNER = 'SYS';
# Network ACLs
Auch Netzwerk ACLs werden durch EXPDP nicht übernommen und müssen manuell nachgetragen werden.
Da die dazugehörigen Statements den Rahmen dieses Artikels sprengen würden: Hier ist ein Link, wo eine entsprechende Anleitung zu finden ist:
www.database-blog.at/2023/02/02/oracle-expdp-impdp-und-netacls-von-applikationen/
# Oracle Text Indizes
Leider gibt es auch immer wieder Probleme beim Übertragen der Oracle-Text-Indizes.
Im ersten Schritt wird in Quelle und Ziel überprüft, ob alle Indizes vorhanden sind:
select index_name, table_name, index_type from dba_indexes where ityp_owner='CTXSYS' and ityp_name='CONTEXT' order by 1,2;
Sind diese nicht gleich, kann man sich für jeden FEHLENDEN Index die Definition aus der Quelldatenbank auslesen:
set long 500000
select ctx_report.describe_index('NAME_DES_FEHLENDEN_INDEX') from dual;
select ctx_report.create_index_script('NAME_DES_FEHLENDEN_INDEX') from dual;
Sobald alle Indizes in der Zieldatenbank angelegt wurden, sollte unbedingt noch überprüft werden, ob auch Daten vorhanden sind:
SELECT c.idx_owner,c.idx_name,i.TABLE_NAME,c.idx_text_name,c.idx_type,c.idx_docid_count, i.status,i.domidx_status,i.DOMIDX_OPSTATUS FROM ctxsys.ctx_indexes c, dba_indexes i WHERE c.idx_name=i.index_name ORDER BY 2,3;
Der Status muss auf VALID stehen und in der Spalte idx_docid_count findet sich die Anzahl der Records (DOCIDs).
Das folgende Statement erzeugt Select-Statements, die den Aufbau der Indizes anstoßen:
SELECT 'SELECT ctx_report.create_index_script (''' || c.idx_owner || '.' || c.idx_name || ''') AS line_ FROM dual;'
FROM ctxsys.ctx_indexes c, dba_indexes i WHERE c.idx_name=i.index_name
WHERE i.status <> 'VALID'
or c.idx_docid_count = 0 ;
Zum Abschluss sollte man für den Index noch einen Sync durchführen, wenn es Pending Records gibt. Das folgende Statement erzeugt die dazu nötigen Befehle:
select 'exec ctxsys.ctx_ddl.sync_index(idx_name =>''' || PND_INDEX_OWNER || '.' || PND_INDEX_NAME ||''');' from ctxsys.CTX_PENDING;
Weiterführende Themen
# Ziel Datenbank
Beim Anlegen der Ziel-Datenbank muss man viele Punkte beachten, unter anderem:
Zeichensatz (will man den gleichen Zeichensatz oder soll dieser konvertiert werden)
- Blocksize
- Global_database_name – mit oder ohne Domain?
- ONLINE Logfiles in der richtigen Größe anlegen
- Prüfen der Passwortlimits (Profiles)
- Alle notwendigen Datenbank-Optionen installieren (am besten nur die nötigen, nicht alle). Eventuell vorher prüfen, was wirklich verwendet wird?
Siehe auch folgende Webinare:
“Oracle-Datenbank-Optionen: Welche gibt es?”
www.dbmasters.at/db/masters/artikel/grundlagen-webinar-oracle-datenbank-optionen-welche-gibt-es-funktionalitaet-und-lizenzierung
“Unnötige Optionen in der Oracle DB vermeiden”
www.dbmasters.at/db/masters/artikel/warum-soll-man-unnoetige-optionen-in-der-oracle-db-vermeiden
- Netzwerkkonfigurationsfiles (Listener.ora, sqlnet.ora, tnsnames.ora, Oracle Wallets)
# APEX
Im Fall von APEX ist es empfehlenswert, wie folgt vorzugehen:
- APEX in der aktuellsten Version in der Ziel-Datenbank installieren
- Mittels APEX die Applikation in der Quell-Datenbank exportieren
- Mittels APEX in der Ziel-Datenbank die Applikation wieder einspielen.
Man kann APEX auch mit EXPDP/IMPDP übernehmen, sofern in der Ziel-Datenbank kein APEX vorhanden ist. Dann gibt es aber immer wieder Probleme mit Berechtigungen und Ähnlichem, daher empfehlen wir die oben genannte Vorgangsweise.
# ORA-01555
Kommt es beim EXPDP zu einem ORA-01555 muss man die UNDO_RETENTION höher setzen (auf die Dauer des Exports) oder am UNDO Tablespace RETENTION GUARANTEE einstellen. Gegebenenfalls muss man den UNDO Tablespace vergrößern.
- ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
- EXPDP
- ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
# Multitenant Database / Pluggable Database
Hier muss man sich mit beim Export direkt an der Pluggable-Datenbank anmelden! Meldet man sich nur an der CDB an, wird praktisch nichts exportiert werden.
# EXPDP/IMPDP Performance und Laufzeit
Abhängig von der Datenbank (Standard Edition oder Enterprise Edition) gibt es mehrere verschiedene Wege um den EXPDP/IMPDP deutlich beschleunigen. Da dies von mehreren Faktoren abhängt, muss man sich das im Detail genauer anschauen.
Christian Pfundtner
DOAG Themenverantwortlicher Tuning
