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?

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
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
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
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
Thanks Hassel!
Cheers,
Mike
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
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
Hi Mike
No problem.
I’ll will collect some more info an forward it to You.
Might take some time however
Thanks
Christian