siehe über Slideshare:
[slideshare id=56210681&doc=03-datapump11g12cgpi2015-151216164539]
Aufgabe:
Export aller Daten einer Entwicklungsumgebung und Import der Content Schemata in eine ähnliche Entwicklungsdatenbank.
Ablauf:
Ale SQL Befehle und der Import / Export werden das SYS User durchgeführt.
Mit entsprechenden Skript dbfiles.sql prüfen, das die gleiche Tablespace Struktur wie im Ziel auch in der Quelle gegeben ist.
Directories anzeigen lassen:
SELECT owner ,directory_name ,directory_path FROM dba_directories ORDER BY 1 ,2 /
Siehe auch passendes Script unter: directory.sql
Passendes Directory wie DATA_PUMP_DIR auswählen, Dateipfad prüfen, auf ausreichend Plattenplatz achten.
Für einen konsistenten Export (Alle Daten entsprechen logisch dem gleichen Lesezeitpunkt in der Datenbank) wird der Parameter FLASHBACK_SCN gesetzt.
Ermitteln mit:
SELECT name , to_char(sysdate,'dd.mm.yyyy hh24:mi') , current_scn FROM v$database / NAME TO_CHAR(SYSDATE, CURRENT_SCN --------- ---------------- ----------- GPIDB1 08.08.2013 19:49 582159018
Parfile für den Export anlegen:
vi ~/full_export_GPIDB1.dpctl DIRECTORY=DATA_PUMP_DIR LOGFILE=expdp_08-08-2013_19_49_GPIDB1.log DUMPFILE=expdp_08-08-2013_19_49_GPIDB1%u.dmp FILESIZE=20G REUSE_DUMPFILES=Y COMPRESSION=ALL FULL=Y FLASHBACK_SCN=582159018 JOB_NAME=EXPDP_582159018
Nach 20GB die Datei aufsplitten, damit das nicht zu groß für das Dateisystem ist.
Alternativ eine festen Zeitpunkt setzen mit FLASHBACK_TIME, für einen Aufruf in der Kommando Zeile entsprechend escapen:
TIMESTAMP_EXPORT=`date "+%Y%m%d-%H%M%S"` expdp … FLASHBACK_TIME=\"to_timestamp\(\'$TIMESTAMP_EXPORT\',\'yyyymmdd-hh24miss\'\)\" …
Da eine EE Edition zur Verfügung steht kann mit Compression gearbeitet werden.
Der Job Name hilft bei Unterbrechungen und Problemen den Job wieder kontrolliert auf zunehmen.
Export aufrufen:
$ORACLE_HOME/bin/expdp "'/ as SYSDBA'" parfile=~/full_export_GPIDB1.dpctl
Logfile auf Fehler prüfen, dmp zusätzlich mit „gzip“ einpacken und auf die Ziel Maschine kopieren.
Das einige Anhängigkeiten zu anderen Schemas in der Datenbank bestehen, wurde auf die Option das Schema mit „drop user xxxx cascade;“ zu löschen und neu mit Data Pump anzulegen verzichtet.
Alle Objekte in den zu importierenden Schemata werden gelöscht.
Das Lösch Script erstellt mit Hilfe des Data Dictionary die entsprechenden Drop Befehl:
Siehe folgendes Script: clean_user.sql
Vor dem Aufruf des erzeugten Delete Skripts sollte auf jeden Fall der Recycle Bin des Schemas geleert werden, da zum Teil DB Objekte wie ein „Type“ sich nicht löschen lassen, wenn Objekte im Recycle Bin diese verwenden.
Vor dem Import bei Bedarf das Audit Log der DB und den gesamten Recyle Bin leeren und prüfen ob die Recovery Area nicht vollläuft.
TRUNCATE TABLE aud$; PURGE DBA_RECYCLEBIN; achive log list
Recovery Area prüfen mit: reco.sql , ungültige Objekte übersetzen und mit invalid.sql kontrollieren, dass auch alle Object in der DB gültig sind.
@reco.sql @?/rdbms/admin/utlrp @invalid.sql
Prüfen ob das Directory für den Import auf der Quelle auf das richtige Verzeichnis zeigt (siehe ersten Schritt weiter oben).
ParFile für den Import erstellen:
vi ~/import_GPIDB1_DATA.dpctl DIRECTORY=DATA_PUMP_DIR LOGFILE=impdp_08-08-2013_20_20_GPIDB1.log DUMPFILE=expdp_08-08-2013_19_49_GPIDB1.dmp JOB_NAME=IMP_GPIDATA_v1 SCHEMAS=GPDATA,WEB_DATA,ETL_CONFIG
Sollen die Daten von einen Schema in ein anders übertragen werden, wird diese über die „REMAP_SCHEMA“ Anweisung definiert, muss auch noch der Tablespace angepasst werden hilft „REMAP_TABLESPACE“ in der Steuerdatei:
SCHEMAS=USER1 REMAP_SCHEMA=USER1:USER2 REMAP_TABLESPACE=DEV_DATA:USER_DATA
Aufruf:
$ORACLE_HOME/bin/impdp "'/ as SYSDBA'" parfile=~/import_GPIDB1_DATA.dpctl
Log file auf Fehler prüfen.
Der Job Name ist sehr wichtig um bei Bedarf wieder die Data Pump Console zu starten mit:
$ORACLE_HOME/bin/impdp "'/ as SYSDBA'" attach=IMP_GPIDATA_v1
Auf Ziel und Quelle Objektanzahl und Rechte vergleichen:
SET linesize 130 pagesize 30 recsep OFF SELECT owner ,obj_type ,obj_count FROM ( SELECT COUNT(*) AS obj_count, object_type AS obj_type, owner FROM dba_objects GROUP BY object_type,owner ) WHERE owner NOT IN ('SYS','MDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDDATA','ORDSYS','EXFSYS','XS$NULL','XDB','CTXSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSTEM','FLOWS_FILES','PUBLIC','SYSMAN','OLAPSYS','OWBSYS','OWBSYS_AUDIT') GROUP BY owner,obj_type,obj_count ORDER BY owner,obj_type / COLUMN grantee format a25 COLUMN GRANTOR format a25 COLUMN PRIVILEGE format a25 COLUMN cnt format 9999 SELECT GRANTOR , grantee , PRIVILEGE , COUNT(*) AS cnt FROM DBA_TAB_PRIVS WHERE owner NOT IN ('SYS','MDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDDATA','ORDSYS','EXFSYS','XS$NULL','XDB','CTXSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSTEM','FLOWS_FILES','PUBLIC','SYSMAN','OLAPSYS','OWBSYS','OWBSYS_AUDIT') GROUP BY owner,grantee,GRANTOR,PRIVILEGE ORDER BY 1,2,3 /
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('GPI')
Mit dem Parameter „estimate_only=y“ läßt sich ermittlen wie groß ein Export ungefähr werden wird.
expdp "'/ as sysdba'" ESTIMATE_ONLY=YES FULL=Y DIRECTORY=FULL_DB_EXPORT JOB_NAME=EXP_SPACE_V01 LOGFILE=estimate_export.log ... Total estimation using BLOCKS method: 108.7 GB Job "SYS"."EXP_SPACE_V01" successfully completed at Mon Nov 5 18:30:57 2018 elapsed 0 00:01:28 ...
Mit Hife eines extracts allem SQL's läßt sich auch verifizieren ob ein Dump einer Datenbank komplett ist, zum Beispiel wenn der Dump mit %U exportiert wurde und aus vielen Dateien besteht.
impdp "'/ as sysdba'" directory=TEST_DUMP dumpfile=GPI_%U.dmp logfile=check_GPI.log sqlfile=GPI_check.sql JOB_NAME=GPI_CHECK
Mit dem Parameter SQLFILE läßt sich das SQL aus dem Export extrahieren (liegt dann im directory exp_dir):
impdp scott/tiger directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql
Dies ist recht hilfreich um zum Beispiel all Object Grants wieder herzustellen:
impdp scott/tiger directory=exp_dir dumpfile=scott.dmp sqlfile=grant_obj.sql content=metadata_only include=OBJECT_GRANT remap_schema=scott:gpi #SQL File bereinigen falls notwendig: sed -i -e 's/gpi/gpi_prod/g' grant_obj.sql # und grants einspielen: sqlplus / as sysdba @grant_obj.sql
Oder zum Beispiel die Tablespaces und die User und Rollen vor dem Import anzulegen mit „include=TABLESPACE,USER,ROLE“
Anlegen eines Parameter Datei und aufrufen mit „expdp “'/ as sysdba'„ parfile=export.dpctl“
DIRECTORY=DATA_PUMP_DIR TABLES=GPI."EMP",GPI."DEPT" TABLES=GPI."HIBERNATE_UNIQUE_KEY",GPI."USER_ADDRESSES"
Falls viele Tabellen exportiert werden sollen, in mehre Zeilen aufzählen damit der Input Wert nicht zu lang wird.
Im zu exportierenden Schema wird eine Tabelle angelegt, die alle Tabellenname enthält die exportiert werden sollen. Auf diese Tabelle wird dann referenziert.
SCHEMAS=GPI INCLUDE=TABLE:"IN (SELECT table_name FROM GPI_EXP_TAB)" FLASHBACK_TIME="to_timestamp('20140820-112640','yyyymmdd-hh24miss')" CONTENT=ALL
Nur Tabellen
DIRECTORY=DATA_PUMP_DIR SCHEMAS=GPI INCLUDE=TABLE
Für den richtigen Include Parameter für die jeweiligen Objekte die folgenden Views abfragen:
Filter auf nur bestimmte Tabellen
DIRECTORY=DATA_PUMP_DIR DUMPFILE=tab_gpi_users.dmp SCHEMAS=GPI INCLUDE=TABLE:"like 'USER%'"
Leider kann Schema und Tablespace nicht gemischt werden!
DIRECTORY=DATA_PUMP_DIR DUMPFILE=tablespace_users.dmp TABLESPACES=USERS
Anlegen eines Parameter Datei und aufrufen mit „imdp “'/ as sysdba'„ parfile=export.dpctl“
impdp "'/ as sysdba'" full=y 'exclude=SCHEMA:"IN(select username from all_users)"'
DIRECTORY=DATA_PUMP_DIR DUMPFILE=tablespace_users.dmp TABLES=GPI.EMP
DIRECTORY=DATA_PUMP_DIR DUMPFILE=tab_gpi_users.dmp SCHEMAS=GPI INCLUDE=TABLE:"like 'USER%'"
siehe für Include Anweisung auch : http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL3145
Regel was passieren soll falls die Tabelle bereits exisiert:
TABLE_EXISTS_ACTION=REPLACE
Regeln
Parameter: SCHEMAS=GPI INCLUDE=TABLE:"like 'EMP%'" #Fehler ORA-31655: no data or metadata objects selected for job ORA-39039: Schema expression " IN ('GPI')" contains no valid schemas.
Fehler: Export wurde im Tablespace Mode durchgeführt, dann kann bei einem Import nicht mit Schema gefiltert werden!
Mit „transform=segment_attributes“ können die Storage parameter beim Anlegen eines Objects ingnoriert werden.
Syntax : TRANSFORM = transform_name:value[:object_type]
Beispiele SEGMENT_ATTRIBUTES:
Auf allen Objekten ignorieren und anderen Tablespace als den Default verwenden:
impdp gpi/gpi transform=segment_attributes:n remap_tablespace=PROD_DATA:GPI_DATA
Nur auf Tabellen ignorieren:
impdp gpi/gpi transform=segment_attributes:n:TABLE
Beispiele STORAGE:
impdp gpi/gpi transform=storage:n
see: http://docs.oracle.com/cd/E11882_01/server.112/e16536/dp_import.htm#SUTIL939 ( seach for TRANSFORM)
Wie:
EXCLUDE=TABLE:"IN ('ORDER')"
Sollen bei einem Export bestimmte Objekte nicht exportiert werden, kann mit der EXCLUDE Anweisung gearbeitet werden, diese kann dann auch direkt auf die Objekt ID's der nicht gewünschten Objekte abgestimmt werden.
Mit diesem Parfile werden:
exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))"
Quelle siehe ⇒ https://mikedietrichde.com/2011/12/04/exclude-dbms_scheduler-jobs-from-expdp/
Über einen DB Link aus dem Zielsystem das Quellsystem abfragen und dort die Daten einfügen:
|-------| |-----| | PROD | <==== DB Link | TST | |-------| |-----| source destination
Der Export erfolgt von der „source database“ in die „destination database“, d.h. der DB Link wird in der „destination database“ angelegt!.
Ablauf:
DB Directory und Link auf der DESTINATION DB TST:
-- DB Link CREATE DATABASE link DP_TRANSFER CONNECT TO system IDENTIFIED BY hugo1oguh USING 'gpi_db1'; -- DB Directory CREATE OR REPLACE directory import_dir AS '/opt/oracle/import/GPIDB'; -- pürfen ob es auch die richtige DB ist: SELECT global_name FROM global_name@DP_TRANSFER;
Tablespace SQL und Grants holen:
impdp "'/ as sysdba'" directory=import_dir network_link=DP_TRANSFER sqlfile=tablespaces_gpidb.sql content=metadata_only include=TABLESPACE full=Y impdp "'/ as sysdba'" directory=import_dir network_link=DP_TRANSFER sqlfile=users_gpidb.sql content=metadata_only include=USER full=Y impdp "'/ as sysdba'" directory=import_dir network_link=DP_TRANSFER sqlfile=roles_gpidb.sql content=metadata_only include=ROLE full=Y impdp "'/ as sysdba'" directory=import_dir network_link=DP_TRANSFER sqlfile=object_grants_gpidb.sql content=metadata_only include=OBJECT_GRANT full=Y
Vor dem eigentlichen Import die Tablespaces vorbereiten ( gleich wie Prod, in meine Fall werden mehr als 400GB übertragen und es muss entsprechend viel Platz vorgehalten werden!)
Full import durchführen in der DESTINATION DB:
# in meinen Fall lasse ich eine sehr große Tabelle erstmal weg um Transactional zu importieren vi import_gpidb.ctl DIRECTORY=IMPORT_DIR LOGFILE=impdp_09-04-2016_08_48_GPIDB.log network_link=DP_TRANSFER JOB_NAME=IMP_GPIDB_V1 FLASHBACK_SCN=8237536 EXCLUDE=TABLE:"IN ('DOKU')" FULL=Y #importieren $ORACLE_HOME/bin/impdp "'/ as SYSDBA'" parfile=import_gpidb.ctl #Und nun die fehlende Tabelle: DIRECTORY=BACKUP LOGFILE=impdp_12-05-2018_11_45_GPIDDBPRD_mediatab.log network_link=DP_TRANSFER JOB_NAME=IMP_GPIDB_MEDIATAB TABLES=GPIMEDIA."DOKU"
Kann plötzlich der DB Link TNS Name nicht mehr in einer Cluster Umgebung aufgelöst werden, kann es daran liegen das zwar im Cluster erst in der Grid tnsnames gesucht wird und dann im Oracle Home der Datenbank, gelgentlich aber wieder um andersherum, je nach dem in welchen Context die DB wohl ursprünglich gestartet wurde.
Setzte daher die TNS_ADMIN auf die Grid Umgebung und verlink aus der Oracle DB Home Umgebung auf diese tnsnames.ora. Dann gibt es nur eine Datei und wo was gepflegt werden muss, ist gelöst!
Nacharbeiten:
-- Object Grant Script laufen lassen -- auf ungültige Objecte prüfen -- alte User wieder entfernen DROP USER OLAPSYS cascade; DROP USER MDDATA cascade; DROP USER SCOTT cascade; -- synonyme die auf nichts zeigen entfernen @invalid_synoyms.sql -- alles ungültige neu übersetuen @?/rdbms/admin/utlrp -- Directories prüfen und bei Bedarf anpassen @directory
Die Scripte finden Sie in meiner Script Library OraPowerShell
Einschränkungen - ORA-31679:
Es können keine Tabelle mit LONG Spalten übertragen werden! Fehler:
ORA-31679: TABLE DATA object "GPI"."VERY_OLD_TABLE" has long COLUMNS, AND longs can NOT be loaded/unloaded USING a network link
Diese Tabelle muss nun einzeln aus der Quelle extrahiert werden ….
siehe Metalink Node „How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)“.
expdp / attach=job_name_as_definied_in_start_of_the_export Export> KILL_JOB
siehe Metalink Node:
Statistiken der DB zuvor optimeren:
EXEC DBMS_STATS.gather_fixed_objects_stats; EXEC DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_percent => 100, degree => 8,options => 'GATHER')
Export mit „EXCLUDE=STATISTICS“ (keine Statistiken mit exportieren) testen
Leider werden bei einem DataPump Import die nls_length_semantics='CHAR' Einstellungen ignoriert, es wird die Zieltabelle 1zu1 zum Quellsystem angelegt.
Am einfachsten VOR dem Export der Daten im Quellsystem die Varchar2 Spalten auf CHAR anpassen, Siehe dazu auch die Metalink Note: „SCRIPT: Changing columns to CHAR length semantics ( NLS_LENGTH_SEMANTICS ) (Doc ID 313175.1)“.
Oder erst die Meta Daten importieren „content=metadata_only“, dann alle Tabellen anpassen und dann die Daten mit „content=data_only“ nachladen.
Fehler:
ORA-39083: Object TYPE TRIGGER failed TO CREATE WITH error: ORA-00942: TABLE OR VIEW does NOT exist
Siehe ⇒
Undo einstellungen kontrollieren ⇒https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/undo_stat.sql und https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/undo.sql
Optmieren (ersmal nur im Speicher und auf beiden Instancen wenn RAC!) :
ALTER system SET undo_retention=4800 scope=memory sid='*'; # Für RAC, tablespace auch optimieren, falls auf NOGUARANTEE! # Tablespace beobachten ! damit kann der Tablespace recht voll laufen, falls sich viel im System ändert! # 36000 = 10h ALTER TABLESPACE UNDOTBS01 RETENTION GUARANTEE; ALTER system SET undo_retention=36000 scope=memory sid='GPIDB1'; ALTER TABLESPACE UNDOTBS02 RETENTION GUARANTEE; ALTER system SET undo_retention=36000 scope=memory sid='GPIDB2';
Parameter im Detail siehe https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/UNDO_RETENTION.html#GUID-D238E854-A2C5-463E-A146-31F61300124E
In der Kommandozeile kann das nicht so einfach übergeben werden:
impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=expdp_10-11-2018_23_31_INTERSHOP_LIVE_ISORCL7%u.dmp REMAP_TABLESPACE=IS_EDIT_INDX:IS_LIVE_INDX:IS_EDIT_INDX_CTX:IS_LIVE_INDX_CTX:IS_EDIT_USERS:IS_LIVE_USERS sqlfile=INTERSHOP_LIVE_sql_script.sql
Lösung: Parameter Datei anlegen:
directory=DATA_PUMP_DIR dumpfile=expdp_10-11-2018_23_31_INTERSHOP_LIVE_ISORCL7%u.dmp REMAP_TABLESPACE=IS_EDIT_INDX:IS_LIVE_INDX REMAP_TABLESPACE=IS_EDIT_INDX_CTX:IS_LIVE_INDX_CTX REMAP_TABLESPACE=IS_EDIT_USERS:IS_LIVE_USERS sqlfile=INTERSHOP_LIVE_sql_script.sql
Aber! Die Tablespace Clause (ctxsys.driimp.set_value('P_TABLE_CLAUSE','TABLESPACE IS_EDIT_INDX_CTX'); ) von Oracle Text Indexen wird nicht beachtet!
Teste gerade in einer 12c R1, der Bug ist dort noch nicht gefixt ⇒ Bug 6969504 : IMPDP REMAP_TABLESPACE PARAMETER NOT APPLIED FOR SPATIAL INDEXES
D.h. hier muss manuell nachgebessert werden.
Import über einen Datenbank Link schlägt fehl:
Fehler:
Release 19.0.0.0.0 - Production ON Thu Feb 4 12:02:18 2021 Version 19.9.0.0.0 Copyright (c) 1982, 2020, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39004: invalid state ORA-39091: unable TO determine logical standby AND streams STATUS ORA-06550: line 1, COLUMN 54: PLS-00103: Encountered the symbol "-" WHEN expecting one OF the following:
Lösung: Datenbank Link ohne - anlegen wie z.B. „gpi.world“ !
Siehe diesen Eintrag:https://juliandontcheff.wordpress.com/2011/12/20/on-4-undocumented-datapump-parameters/
Für alles:
TRACE=1FF0300
Web:
Oracle:
Oracle Support:
12c