Managing Oracle Data Pump Resource Utilization with Concurrent Job Limits per PDB
MAX_DATAPUMP_JOBS_PER_PDB parameter controls the maximum number of concurrent Oracle Data Pump jobs allowed per Pluggable Database (PDB), enabling database administrators (DBAs) to better manage and govern Oracle Data Pump resource usage.
Key Details
- Default Behavior: The default setting may not suit all databases. DBAs should evaluate if the default value meets the specific needs of their database environments.
- AUTO Setting: When set to
AUTO
, Oracle Data Pump dynamically determines this parameter’s value as 50% of theSESSIONS
initialization parameter. - Impact of Values:
- High Values: Setting this parameter too high may lead Oracle Data Pump to consume excessive system resources.
- Low Values: A value that is too low may restrict users from executing necessary Data Pump jobs, potentially impacting workload efficiency.
Resource Utilization
The primary resource Oracle Data Pump utilizes is the Shared Pool within the System Global Area (SGA) of the database. Parallel jobs increase the session count, and depending on the workload, may also increase the number of parallel query (PQ) slaves used. Proper tuning of this parameter is essential to maintain an optimal balance between resource usage and job execution capacity.
Parameter change
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ODBAPDB READ WRITE NO
[oracle@odbadb ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 29 21:55:48 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter MAX_DATAPUMP_JOBS_PER_PDB
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_datapump_jobs_per_pdb string 100
- for Multitenant, in CDB$ROOT:
SQL> alter system set MAX_DATAPUMP_JOBS_PER_PDB=2 container=all;
System altered.
- for non-Multitenant:-
SQL> alter system set MAX_DATAPUMP_JOBS_PER_PDB=2;
System altered.
SQL> show parameter MAX_DATAPUMP_JOBS_PER_PDB
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_datapump_jobs_per_pdb string 2
First Session running Export Data Pump
[oracle@odbadb ~]$ export ORACLE_SID=odba
[oracle@odbadb ~]$ expdp system/oracle@odbapdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes
Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:02 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/********@odbapdb directory=DATA_PUMP_DIR dumpfile=data_dump_01.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Session continue to run………
Second Session running Export Data Pump
[oracle@odbadb ~]$ export ORACLE_SID=odba
[oracle@odbadb ~]$ expdp system/oracle@odbapdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes
Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:02 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/********@odbapdb directory=DATA_PUMP_DIR dumpfile=data_dump_01.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Session continue to run………
Initiating 3rd Session of Export Data Pump
[oracle@odbadb ~]$ export ORACLE_SID=odba
[oracle@odbadb ~]$ expdp system/oracle@odbapdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes
Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:02 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_03 for user SYSTEM
ORA-06512: at “SYS.KUPV$FT”, line 1142
ORA-06512: at “SYS.KUPV$FT”, line 1744
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 1099
ORA-39391: maximum number of Data Pump jobs (2) exceeded
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPV$FT_INT”, line 969
ORA-06512: at “SYS.KUPV$FT”, line 1646
ORA-06512: at “SYS.KUPV$FT”, line 1103
No Comments