impdp (19c) taking way too long ! [processing-DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX]

Question

How to improve and speed up the import further?

impdp status : at 30 % from almost 2 days ::

Operation: IMPORT Mode: SCHEMA State: EXECUTING Bytes Processed: 563,545,353,768 Percent Done: 30 Current Parallelism: 6 Job Error Count: 0 Job heartbeat: 8

impdp - Parfile:

DIRECTORY=EXPDP
logfile=imp_users.log
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
EXCLUDE=STATISTICS
LOGTIME=ALL
dumpfile=schema1_exp_01.dmp,
 -------
 -------
schema1_exp_10.dmp
schemas=schema1,
schema2,
......
......
schema16
ENCRYPTION_PASSWORD=HjtF$y~gr_s6b&dA
parallel=6
cluster=N

Environment :

DB : Oracle 19c, Non-CDB
Expdp dumpsize -  577 GB
OS - AWS Ec2 linux
CPU - 4 
RAM - 30 GB, 
SGA - 15gb , swap - 15gb 
sga_max_size             big integer 15168M
sga_min_size             big integer 0
sga_target              big integer 15168M
unified_audit_sga_queue_size     integer   1048576
pga_aggregate_limit         big integer 12000M
pga_aggregate_target         big integer 6000M
NOTE: Tried with pre-12c --  ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;

The following DB options/parameters were set:

FORCE LOGGING is disabled:      SQL> alter database no force logging;
FLASHBACK is disabled:          SQL> alter database flashback off;
ARCHIVELOG modes is disabled:   SQL> alter database noarchivelog;
Disable BLOCK CORRUPTION checking mechanism:

SQL> alter system set DB_BLOCK_CHECKING=FALSE; SQL> alter system set DB_BLOCK_CHECKSUM=OFF; Disable DLM Statistics Collection: SQL> alter system set “_dlm_stats_collect”=0 SCOPE=SPFILE;

NOTE

Source tbs are conventional smallfile, but in target all are bigfile tables where import is in progress (not sure if this will have any impact on import).

Import current status :

What does the actual import log say? Where is the process at? Remember, if you included indexes in the import those are being rebuilt too, so your actual data volume in the DB may be considerably larger than the size of the export dump files. Some of those transactions will not be immune from archive logging exemptions either. — pmdba, Sep 21 '22 at 18:05

score 0 · Answer 1 · answered Sep 21 '22 at 20:30

Here you can generate the command to attach to the running process. From the impdp CLI you can issue 'status'. This will give you information of which object is being processed.

select
  'host ' ||
  decode(trim(operation), 'IMPORT', 'impdp', 'EXPORT', 'expdp') ||
  ' userid=' || lower(owner_name) || ' attach=' || job_name commandline
from
  dba_datapump_jobs;

Best of luck!