Best Practices / Version 2.0
Copyright © 2024, Oracle and/or its affiliates / Public
a reasonable minimum for database usage. See Oracle Database Reference for details on setting the
STREAMS_POOL_SIZE
Restrict resource usage for Data Pump Jobs
A Multitenant Container Database (CDB) with many Plugable Databases (PDBs) may want to avoid having Data
Pump operations in one PDB affect other PDBs.
Beginning with release 19c, you can set the maximum number of Data Pump jobs and the maximum parallelism
for pluggable databases in a multitenant environment.
MAX_DATAPUMP_JOBS_PER_PDB
parameter restricts the number of jobs created. By default, the parameter is set to
50 percent of
SESSIONS
. This value must be same for each RAC instance. It can be set and changed dynamically,
on a per-PDB basis.
MAX_DATAPUMP_PARALLEL_PER_JOB
parameter restricts the amount of parallelism in an individual Data Pump job.
By default, the perimeter is set to 25 percent of
SESSIONS
. This value can be different for each RAC instance. It
can be set and changed dynamically, on a per-PDB basis.
Note: If you encounter the error, "
ORA-00018: maximum number of sessions exceeded
ORA-00020:
maximum number of processes (%s) exceeded
" you may be allowing too many jobs or too much parallelism,
respectively.
You can start an import (
impdp
) from the target database over a database link. No dump file will be generated,
which can eliminate the need for temporary storage during a migration. Beginning with Oracle Database release
12.2, there is support for Direct Path Load over a database link, including for
LONG
LONG RAW
data, using the
parameter
ACCESS_METHOD=DIRECT_PATH
Oracle recommends using SecureFile LOBs, especially with partitioning. SecureFile LOBs offer superior
performance, functionality and scalability over BasicFile LOBs, including:
!
parallel IO into and out of tables with LOB columns (No parallelism on BasicFile LOBs)
!
!
You can use the impdp parameter
LOB_STORAGE=SECUREFILE
to automatically convert BasicFile LOBs to
SecureFiles. Tables with SecureFile LOBs storage are automatically created in the target database.
Set Database Compatibility
The database compatibility level affects Data Pump export and import operations. The source database
compatibility level determines the compatibility level of the export dumpfile set. If the target database has a lower
compatibility level than the source database, use the expdp parameter
VERSION
to specify the target version. A
network mode import can be performed if the compatibility level of the source database is the same as or differs
by one major version from the target database.
Beginning with Oracle Database 18c,
COMPATIBLE
should be set using only the major release and three digits, for
instance
18.0.0
, with no Release Update number, for example not
19.3.0
Prior to Oracle Database 21c, exports and imports are performed using the
expdp
impdp
client versions that
match the version of the source and target databases, respectively. However,
impdp