Problém
Export celé databáze zabral 20 min. Import pouze dvou schémat, ale trval 5 hodin. Z toho většinu času datová pumpa stála na promptu DOMAIN_INDEX/INDEX
impdp '/ as sysdba' schemas=TOMAS,TOMAS_TDMS directory=pump_dir dumpfile=TOMAS_full.dmp logfile=TOMAS_imp_full_2016_02_04.log
Analýza
Po 10-ti minutách importu bylo hotovo 99%.
SQL> select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;
USERNAME
------------------------------
OPNAME
----------------------------------------------------------------
TARGET_DESC SOFAR TOTALWORK
-------------------------------- ---------- ----------
MESSAGE
--------------------------------------------------------------------------------
SYS
SYS_IMPORT_SCHEMA_01
IMPORT 11845 11846
SYS_IMPORT_SCHEMA_01: IMPORT : 11845 out of 11846 MB done
Job stále běžel.
SQL> select JOB_NAME,JOB_MODE,STATE,OPERATION from dba_datapump_jobs; JOB_NAME ------------------------------------------------------------------------------- JOB_MODE STATE ------------------------------ ------------------------------ OPERATION ------------------------------ SYS_IMPORT_SCHEMA_01 SCHEMA EXECUTING IMPORT
Co se tedy dělo?
C:\Users\Administrator>impdp '/ as sysdba' attach=SYS_IMPORT_SCHEMA_01
Import: Release 12.1.0.1.0 - Production on Lt -no 4 15:06:09 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
P°ipojeno k: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
-loha: SYS_IMPORT_SCHEMA_01
VlastnÝk: SYS
Operace: IMPORT
OprßvnýnÝ tv¨rce: TRUE
GUID: 339D473B12594D4AAF14C47994ADE784
Doba zahßjenÝ: Ltvrtek, 04 -nor, 2016 14:51:02
Re×im: SCHEMA
Instance: TOMAS1
Maximum paralelnÝch spuÜtýnÝ: 1
LasovÚ pßsmo: -05:00
LasovÚ pßsmo exportu: +01:00
Verze ŔasovÚho pßsma: 18
Verze ŔasovÚho pßsma exportu: 0
Endianness: LITTLE
Znakovß sada NLS: EE8MSWIN1250
Znakovß sada NLS NCHAR: AL16UTF16
Parametry ˙lohy EXPORT:
Nßzev parametru Hodnota parametru:
CLIENT_COMMAND '/******** AS SYSDBA' full=y directory=pump_dir dumpfile=TOMAS_full.dmp logfile=TOMAS_full_2016_02_04.log
Parametry ˙lohy IMPORT:
Nßzev parametru Hodnota parametru:
CLIENT_COMMAND "/******** AS SYSDBA" schemas=TOMAS,TOMAS_TDMS directory=pump_dir dumpfile=TOMAS_full.dmp logfile=TOMAS_imp_full_2016_02_04.log
Stav: EXECUTING
Zpracovßno bajt¨: 12.420.584.216
Hotovo procent: 99
AktußlnÝ paralelnÝ spuÜtýnÝ: 1
PoŔet chyb ˙lohy: 0
Soubor dump: F:\dump\TOMAS_full.dmp
Proces worker 1 Status:
ID instance: 1
Instance name: TOMAS1
Nßzev hostitelskÚho poŔÝtaŔe: ORACLE1
Stav: EXECUTING
SchÚma objektu: TOMAS_TDMS
Nßzev objektu: IX_DR_BLOBSTORAGE_CTX
Typ objektu: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
ParalelnÝ prßce procesu worker: 1
Na vině byl domain index.
Řešení
Vyjmout z importu domain index. Poté import doběhl za 7 minut.
impdp '/ as sysdba' schemas=TOMAS,TOMAS_TDMS EXCLUDE=DOMAIN_INDEX/INDEX directory=pump_dir dumpfile=TOMAS_full.dmp logfile=TOMAS_imp_full_bez_DM_2016_02_04.log
-loha "SYS"."SYS_IMPORT_SCHEMA_02" byla ˙spýÜný dokonŔena na Lt -no 4 15:24:50 2016 elapsed 0 00:06:52
Contextový index pak můžete naimportovat zvlášť nebo jej vytvořit znovu.
impdp '/ as sysdba' schemas=TOMAS,TOMAS_TDMS include=DOMAIN_INDEX/INDEX directory=pump_dir dumpfile=TOMAS_full.dmp logfile=jen_DM_2016_02_04.log
MOS
DataPump Import (IMPDP) Is Very Slow for DOMAIN_INDEX (Doc ID 1948129.1)
Bug 16488349 : DATAPUMP IMPORT IS VERY SLOW AT DOMAIN_INDEX IMPORT
Strategy for Creating Oracle Text Indexes (Doc ID 150453.1)
DataPump Import (IMPDP) Performance Known Problems (Doc ID 1948188.1)
WORKAROUND:
———–
first import using EXCLUDE=DOMAIN_INDEX/INDEX then import
INCLUDE=DOMAIN_INDEX/INDEX