Does Data Pump import only serially into PDBs?

Posted on October 15, 2020 by Mike.Dietrich Data Pump 7

Daniel and I did Web Seminars this week. One of them was about the different migration strategies. We did talk a lot about Data Pump. One attendee mentioned that Data Pump Import does not work parallel into PDBs in Oracle 12.2.0.1 and Oracle 18c. We were skeptical, and today I tried out to check: Does Data Pump import only serially into PDBs?

Does Data Pump import only serially into PDBs?

Photo by Nikola Johnny Mirkovic on Unsplash

Very simple test setup

For this test, I take a schema export from an 11.2.0.4 database in our Hands-On Lab, the TPCC user HammerORA uses in the UPGR database.

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=dumpfile.dmp
LOGFILE=logfile.log
SCHEMAS=TPCC
METRICS=YES
EXCLUDE=STATISTICS
PARALLEL=6

And when I call it with:

expdp system/oracle parfile=exp.par

a minute later I have a dumpfile of a bit more than 300MB size.

Import Preparation

Then I’m going to import it into 3 different databases, all of course PDBs.

Oracle 12.2.0.1 with the July 2020 RU
Oracle 18.8.0
Oracle 19.8.0

This is my parameter file for all 3 databases:

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=dumpfile.dmp
LOGFILE=logfile.log
SCHEMAS=TPCC
METRICS=YES
LOGTIME=ALL
PARALLEL=6

And I need to create a few things upfront:

alter session set container=PDB1;
create tablespace TPCCTAB datafile '/u02/oradata/CDB2/pdb1/tpcc01.dbf' size 500M autoextend on online;
create user tpcc identified by tpcc default tablespace TPCCTAB;
grant connect, resource, dba to tpcc;

Afterwards I can kick off the import simply with:

impdp system/oracle@PDB1 parfile=imp.par

Import Log File Comparison

Now let me compare the logs of the 3 different runs and check whether only 1 worker is action.

Oracle 12.2.0.1.0

$ impdp system/oracle@pdb3 parfile=imp.par

Import: Release 12.2.0.1.0 - Production on Thu Oct 15 17:40:19 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
15-OCT-20 17:40:20.739: W-1 Startup took 1 seconds
15-OCT-20 17:40:21.686: W-1 Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
15-OCT-20 17:40:21.953: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@pdb3 parfile=imp.par 
15-OCT-20 17:40:21.996: W-1 Processing object type SCHEMA_EXPORT/USER
15-OCT-20 17:40:22.244: ORA-31684: Object type USER:"TPCC" already exists

15-OCT-20 17:40:22.279: W-1      Completed 1 USER objects in 0 seconds
15-OCT-20 17:40:22.279: W-1      Completed by worker 1 1 USER objects in 0 seconds
15-OCT-20 17:40:22.288: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15-OCT-20 17:40:22.371: W-1      Completed 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 17:40:22.371: W-1      Completed by worker 1 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 17:40:22.372: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
15-OCT-20 17:40:22.454: W-1      Completed 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:40:22.454: W-1      Completed by worker 1 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:40:22.456: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
15-OCT-20 17:40:22.530: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:40:22.530: W-1      Completed by worker 1 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:40:22.531: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
15-OCT-20 17:40:22.605: W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:40:22.605: W-1      Completed by worker 1 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:40:22.606: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:40:22.891: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:22.891: W-1      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:22.892: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
15-OCT-20 17:40:23.547: W-2 Startup took 3 seconds
15-OCT-20 17:40:23.584: W-5 Startup took 4 seconds
15-OCT-20 17:40:23.612: W-6 Startup took 4 seconds
15-OCT-20 17:40:23.634: W-3 Startup took 3 seconds
15-OCT-20 17:40:23.655: W-4 Startup took 3 seconds
15-OCT-20 17:40:24.681: W-1      Completed 9 TABLE objects in 2 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 3 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 4 4 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.725: W-2 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 17:40:25.555: W-4 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.556: W-2 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.568: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.707: W-5 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.708: W-3 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.709: W-2 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.749: W-2 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.780: W-2 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 17:40:29.436: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 5 seconds using direct_path
15-OCT-20 17:40:29.466: W-4 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
15-OCT-20 17:40:29.614: W-5      Completed 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.614: W-5      Completed by worker 1 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.615: W-5 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
15-OCT-20 17:40:29.844: W-2      Completed 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.844: W-2      Completed by worker 3 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.915: W-4 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
15-OCT-20 17:40:30.524: W-2      Completed 8 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 1 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 2 2 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 3 2 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 4 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 5 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 6 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.525: W-2 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:40:30.607: W-6      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:30.607: W-6      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:30.653: W-4      Completed 9 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 5 seconds
15-OCT-20 17:40:30.743: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 15 17:40:30 2020 elapsed 0 00:00:11

Oracle 18.8.0

$ impdp system/oracle@PDB1 parfile=imp.par

Import: Release 18.0.0.0.0 - Production on Thu Oct 15 17:24:57 2020
Version 18.8.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
15-OCT-20 17:24:59.590: W-1 Startup took 0 seconds
15-OCT-20 17:25:00.412: W-1 Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
15-OCT-20 17:25:00.688: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@PDB1 parfile=imp.par 
15-OCT-20 17:25:00.734: W-1 Processing object type SCHEMA_EXPORT/USER
15-OCT-20 17:25:00.905: ORA-31684: Object type USER:"TPCC" already exists

15-OCT-20 17:25:00.949: W-1      Completed 1 USER objects in 0 seconds
15-OCT-20 17:25:00.949: W-1      Completed by worker 1 1 USER objects in 0 seconds
15-OCT-20 17:25:00.958: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15-OCT-20 17:25:01.065: W-1      Completed 1 SYSTEM_GRANT objects in 1 seconds
15-OCT-20 17:25:01.065: W-1      Completed by worker 1 1 SYSTEM_GRANT objects in 1 seconds
15-OCT-20 17:25:01.067: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
15-OCT-20 17:25:01.167: W-1      Completed 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:25:01.167: W-1      Completed by worker 1 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:25:01.168: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
15-OCT-20 17:25:01.270: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:25:01.270: W-1      Completed by worker 1 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:25:01.272: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
15-OCT-20 17:25:01.376: W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:25:01.376: W-1      Completed by worker 1 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:25:01.377: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:25:01.679: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:01.679: W-1      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:01.680: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
15-OCT-20 17:25:02.415: W-5 Startup took 1 seconds
15-OCT-20 17:25:02.452: W-3 Startup took 1 seconds
15-OCT-20 17:25:02.491: W-4 Startup took 1 seconds
15-OCT-20 17:25:02.524: W-6 Startup took 1 seconds
15-OCT-20 17:25:02.560: W-2 Startup took 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed 9 TABLE objects in 2 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 2 4 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 3 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 4 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 5 1 TABLE objects in 0 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.845: W-5 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 17:25:05.204: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.211: W-5 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.212: W-3 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.237: W-2 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.240: W-4 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.307: W-5 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 17:25:05.350: W-5 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 17:25:05.391: W-5 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 17:25:12.017: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 9.00000000000000000000000000000000000003 seconds using direct_path
15-OCT-20 17:25:12.048: W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
15-OCT-20 17:25:12.218: W-3      Completed 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.218: W-3      Completed by worker 4 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.219: W-3 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
15-OCT-20 17:25:12.532: W-5      Completed 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.532: W-5      Completed by worker 2 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.618: W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
15-OCT-20 17:25:13.599: W-5      Completed 8 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 1 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 2 2 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 3 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 4 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 5 2 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 6 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.600: W-5 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:25:13.709: W-6      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:13.709: W-6      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:13.764: W-4      Completed 9 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 9 seconds
15-OCT-20 17:25:13.875: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 15 17:25:13 2020 elapsed 0 00:00:15

Oracle 19.8.0

$ impdp system/oracle@pdb1 parfile=imp.par

Import: Release 19.0.0.0.0 - Production on Thu Oct 15 18:18:03 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
15-OCT-20 18:18:05.268: W-1 Startup took 1 seconds
15-OCT-20 18:18:06.233: W-1 Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
15-OCT-20 18:18:06.504: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@pdb1 parfile=imp.par 
15-OCT-20 18:18:06.552: W-1 Processing object type SCHEMA_EXPORT/USER
15-OCT-20 18:18:06.750: ORA-31684: Object type USER:"TPCC" already exists

15-OCT-20 18:18:06.798: W-1      Completed 1 USER objects in 0 seconds
15-OCT-20 18:18:06.798: W-1      Completed by worker 1 1 USER objects in 0 seconds
15-OCT-20 18:18:06.809: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15-OCT-20 18:18:06.918: W-1      Completed 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 18:18:06.918: W-1      Completed by worker 1 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 18:18:06.919: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
15-OCT-20 18:18:07.025: W-1      Completed 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 18:18:07.025: W-1      Completed by worker 1 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 18:18:07.027: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
15-OCT-20 18:18:07.156: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 18:18:07.156: W-1      Completed by worker 1 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 18:18:07.157: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
15-OCT-20 18:18:07.260: W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 18:18:07.260: W-1      Completed by worker 1 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 18:18:07.263: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
15-OCT-20 18:18:07.601: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:07.601: W-1      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:07.602: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
15-OCT-20 18:18:08.410: W-4 Startup took 1 seconds
15-OCT-20 18:18:08.426: W-2 Startup took 1 seconds
15-OCT-20 18:18:08.448: W-5 Startup took 1 seconds
15-OCT-20 18:18:08.492: W-6 Startup took 1 seconds
15-OCT-20 18:18:08.568: W-3 Startup took 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed 9 TABLE objects in 2 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 3 4 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 4 1 TABLE objects in 0 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.731: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 18:18:10.659: W-3 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.674: W-4 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.734: W-1 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.843: W-1 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 18:18:10.939: W-5 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.987: W-1 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 18:18:10.998: W-2 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 1 seconds using direct_path
15-OCT-20 18:18:11.031: W-1 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 1 seconds using direct_path
15-OCT-20 18:18:15.068: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 6 seconds using direct_path
15-OCT-20 18:18:15.107: W-3 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
15-OCT-20 18:18:15.267: W-5      Completed 5 PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.267: W-5      Completed by worker 4 5 PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.268: W-5 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
15-OCT-20 18:18:15.546: W-1      Completed 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.546: W-1      Completed by worker 2 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.651: W-3 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
15-OCT-20 18:18:16.369: W-1      Completed 8 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 1 2 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 2 2 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 3 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 4 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 5 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 6 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.370: W-1 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
15-OCT-20 18:18:16.476: W-6      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:16.476: W-6      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:16.541: W-3      Completed 9 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 6 seconds
15-OCT-20 18:18:16.666: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 15 18:18:16 2020 elapsed 0 00:00:13

Conclusion

In all 3 cases, I see that 6 workers have been started. This is intended and expected as I used PARALLEL=6 in the parameter file.

And I see also according to the W-n code after the time stamp, that already in Oracle 12.2.0.1 the 6 workers distribute the work, and none of them is just having a great time doing nothing. The HISTORY table data gets imported by W-4, W-2 takes the ORDERS, the NEW_ORDER, the DISTRICT and the WAREHOUSE tables.

15-OCT-20 17:40:23.547: W-2 Startup took 3 seconds
15-OCT-20 17:40:23.584: W-5 Startup took 4 seconds
15-OCT-20 17:40:23.612: W-6 Startup took 4 seconds
15-OCT-20 17:40:23.634: W-3 Startup took 3 seconds
15-OCT-20 17:40:23.655: W-4 Startup took 3 seconds
15-OCT-20 17:40:24.681: W-1      Completed 9 TABLE objects in 2 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 3 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 4 4 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.725: W-2 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 17:40:25.555: W-4 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.556: W-2 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.568: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.707: W-5 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.708: W-3 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.709: W-2 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.749: W-2 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.780: W-2 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 17:40:29.436: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 5 seconds using direct_path

The other logs looks more or less similar.

I compared it also with the logs of a 12.2.0.1 non-CDB database:

15-OCT-20 18:43:52.927: W-6 Startup took 3 seconds
15-OCT-20 18:43:52.963: W-2 Startup took 3 seconds
15-OCT-20 18:43:52.976: W-5 Startup took 3 seconds
15-OCT-20 18:43:52.994: W-3 Startup took 3 seconds
15-OCT-20 18:43:53.004: W-4 Startup took 3 seconds
15-OCT-20 18:43:53.970: W-1      Completed 9 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 3 1 TABLE objects in 0 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 4 4 TABLE objects in 0 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 18:43:54.018: W-6 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 18:43:54.693: W-6 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.698: W-4 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.700: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.799: W-6 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.856: W-3 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.869: W-6 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.917: W-6 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.985: W-5 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 0 seconds using direct_path
15-OCT-20 18:43:59.097: W-2 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 5 seconds using direct_path

But I don’t see any difference, neither between the different PDB versions nor compared to a non-CDB.

Hence, no, the assumption was not correct.

In Oracle Database 12.2.0.1, 18c and 19c you can import with parallel workers into PDBs as well. I don’t see any restriction or any other reason why the import into a PDB should not happen in parallel. And it does also in Oracle 12.1.0 in case somebody is using it.

In case you’ve asked yourself why you don’t see parallel workers for the Meta Data part, this is easy to explain. There is not enough information to import. When you’d have more than 80 users to import, then you would see parallel workers in the meta data import part as well. The meta data is kept in XML in the dump file – and one file contains maximum of 80 users.

–Mike

Tags: 12.2.0.1 18c 19c import Data Pump dumpfile expdp Export impdp PARALLEL parfile

Christian Floreck says:

February 20, 2022 at 08:09

Cheers Mike,
Hope this posting fits in this thread.

I had to do some Updates from 11.2.0.4 to 19 via datapump.
When I checked whether all objects where inported, I was stuned.
Import had run fine, without any error about objects that could not be created.
However, lots of “JAVA CLASS” Objects seem to miss in target db.
For example this one: “/79308356_ExternalCallStreamRe”.
Well, I counted the objects – same number on target and source.
Confusion comes up.
When I searched for %StreamReader% in the target database, this object was found:
“ExternalCall$StreamReaderAccumulator”
Confusion rises.
Then, I noted: In Oracle 11, the name of an Object could only have 30 Charcters.
In 19, it could be 128.
Is this the cause why import “renames” the Object. This makes senses, as the new name is much more informative.
Is there a function, which allows me to do this rename in an SQL statement
to check whether all objects are imported?

Regards
Christian

Reply
- Mike.Dietrich says:
  
  February 27, 2022 at 21:29
  
  Hi Christian,
  
  I don’t think that the issue are the long identifiers since it would have raised an error:
  https://mikedietrichde.com/2018/07/09/export-with-data-pump-and-long-identifiers/
  
  Is this a user created Java class or an Oracle one?
  Actually is this object in your export (do a SQLFILE and check please)?
  Which user did you use?
  
  Thanks,
  Mike
  
  Reply
- Hassel Huespe says:
  
  August 18, 2022 at 22:11
  
  I had the exact same error message trying to drop cascade a user from a 12.2.0.1 database, which was upgraded from 11.2.0.4. I can confirm it’s due to bug
  “26451793 – UPGRADING DB TO 12.2 MAKES JAVA OBJECTS INVALID, RECOMPILATION RESULTS IN 600”. But patch is only available for 12cR2
  
  Reply
  - Mike.Dietrich says:
    
    December 11, 2022 at 22:47
    
    Thanks Hassel!
    
    Cheers,
    Mike
    
    Reply
Christian Floreck says:

March 1, 2022 at 16:59

Hi Mike,
I did an full export/import running under system. By the way, target is an pdb I forgot to mention this.
The Objects can be found in the SQL File.
The missing Object is OSE./f6a1e204_SecurityUtilWithRefl, as You see it is in an user Schema.
In the SQL File I find:
DECLARE
STATUS NUMBER;
BEGIN
STATUS := DBMS_JAVA.START_IMPORT(‘oracle/oc4j/security/SecurityUtilWithReflection’, 0,29,0,3,568,1);
IF STATUS !=0 THEN
IF STATUS > 0 THEN STATUS := -STATUS; END IF;
execute immediate
‘declare an exception; pragma exception_init(an, ‘
|| status || ‘); ‘ || ‘begin raise an; end;’;
END IF;
END;

Hope this helps.
Cheers
Christian

Reply
- Mike.Dietrich says:
  
  March 1, 2022 at 22:55
  
  Thanks Christian – but I fear you may need to open an SR please. Or please drop me an email (mike.dietrich —- oracle.com) in English (!!) and I will forward it to Bill, the Data Pump PM so he can check.
  
  Thanks,
  Mike
  
  Reply
  - christian floreck says:
    
    March 8, 2022 at 18:43
    
    Hi Mike
    No problem.
    I’ll will collect some more info an forward it to You.
    Might take some time however
    Thanks
    Christian
    
    Reply

Does Data Pump import only serially into PDBs?

Very simple test setup

Import Preparation

Import Log File Comparison

Oracle 12.2.0.1.0

Oracle 18.8.0

Oracle 19.8.0

Related

You may also like...

7 Responses

Leave a Reply Cancel reply

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts

Very simple test setup

Import Preparation

Import Log File Comparison

Oracle 12.2.0.1.0

Oracle 18.8.0

Oracle 19.8.0

Related

You may also like...

Virtual Classroom: Migration Strategies – Tipps & Tricks & Secrets

Data Pump’s amazingly useful METRICS=Y and LOGTIME=ALL parameters

Config file for AutoUpgrade – Tweaking init parameters

7 Responses

Leave a Reply Cancel reply

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts