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
