Oracle 19C 数据泵MAX_DATAPUMP_JOBS_PER_PDB、MAX_DATAPUMP_PARALLEL_PER_JOB参数

MAX_DATAPUMP_JOBS_PER_PDB

引入了MAX_DATAPUMP_JOBS_PER_PDB数据库参数，以使DBA可以更好地控制可以启动的datapump job 数量。此参数对多租户和非多租户环境均有效。

当数据库中 MAX_DATAPUMP_JOBS_PER_PDB 参数设置为 N，并且当前已经启动了 N 个 datapump job，尝试启动新的 datapump job 会碰到错误 ORA-39391，例如：

SQL> show parameter MAX_DATAPUMP_JOBS_PER_PDB

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_datapump_jobs_per_pdb            string      100

- 对于多租户环境，在 CDB$ROOT 中：
SQL> alter system set MAX_DATAPUMP_JOBS_PER_PDB=2 container=all;
System altered.

- 对于非多租户环境:
SQL> alter system set MAX_DATAPUMP_JOBS_PER_PDB=2;
System altered.

- 启动两个 DataPump jobs:

> expdp system/oracle@<PDB_service_name> directory=dptest dumpfile=test1.dmp full=yes

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Development
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@<PDB_service_name> directory=dptest dumpfile=test1.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
-----> 仍在执行

> expdp system/oracle@<PDB_service_name> directory=dptest dumpfile=test1.dmp full=yes

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Development
Starting "SYSTEM"."SYS_EXPORT_FULL_02":  system/********@cdb1_pdb1 directory=dptest dumpfile=test2.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
-----> 仍在执行

- 在 MAX_DATAPUMP_JOBS_PER_PDB=2 的数据库上启动第三个 DP job, 会收到错误:

expdp system/oracle@<PDB_service_name> directory=dptest dumpfile=test3.dmp full=yes

Export: Release 19.0.0.0.0 - Development on Tue Oct 9 07:37:24 2018
Version 19.1.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Development
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
复制

MAX_DATAPUMP_PARALLEL_PER_JOB 数据库参数

引入MAX_DATAPUMP_PARALLEL_PER_JOB来控制可用于单个数据泵 job 的并行worker的数量。以改进 datapump 资源使用策略。

当数据库中 MAX_DATAPUMP_PARALLEL_PER_JOB 参数设置为 N，即使指定parallel>N，也仅会启动 N 个 Worker processes，例如：

例子 1
---------
SQL> alter system set MAX_DATAPUMP_PARALLEL_PER_JOB=1 container=all;
System altered.

- 使用 parallel=7 启动 expdp job:

> expdp system/password@<PDB_service_name> directory=dptest dumpfile=test2.dmp full=yes parallel=7 logtime=all metrics=yes
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Development
09-OCT-18 07:47:14.242: Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@<PDB_service_name> directory=dptest dumpfile=test2.dmp full=yes parallel=7 logtime=all metrics=yes
09-OCT-18 07:47:15.402: W-1 Startup took 1 seconds
09-OCT-18 07:47:28.374: W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
09-OCT-18 07:47:31.271: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
09-OCT-18 07:47:35.128: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
09-OCT-18 07:47:38.190: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
09-OCT-18 07:47:40.581: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
09-OCT-18 07:47:40.874: W-1      Completed 72 INDEX_STATISTICS objects in 0 seconds
09-OCT-18 07:47:41.082: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
09-OCT-18 07:47:41.119: W-1      Completed 3 INDEX_STATISTICS objects in 1 seconds
.........
09-OCT-18 07:53:43.432: W-1 . . exported "TC_USER5"."TAB9"                           5.046 KB       1 rows in 0 seconds using direct_path
09-OCT-18 07:53:45.494: W-1      Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 2 seconds
09-OCT-18 07:53:45.504: W-1      Completed 62 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 1 seconds
09-OCT-18 07:53:45.510: W-1      Completed 18 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 16 seconds
09-OCT-18 07:53:45.516: W-1      Completed 120 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 9 seconds
09-OCT-18 07:53:49.806: W-1 Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
09-OCT-18 07:53:49.866: ******************************************************************************
09-OCT-18 07:53:49.867: Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
09-OCT-18 07:53:49.872:   <PATH>/test2.dmp
09-OCT-18 07:53:49.942: Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Oct 9 07:53:49 2018 elapsed 0 00:06:47

- v$session 仅仅显示了1个 worker process:
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, 
from   v$session s, v$process p, dba_datapump_sessions d
where  p.addr=s.paddr and s.saddr=d.saddr and UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');

DATE                PROGRAM                                       SID
------------------- -------------------------------------- ----------
2018-10-09 07:52:27 oracle@den01znf (DW00)                        65


例子 2

---------
- 在数据库中设置 MAX_DATAPUMP_PARALLEL_PER_JOB 为 4:
SQL> alter system set MAX_DATAPUMP_PARALLEL_PER_JOB=4 container=all;
System altered.

- 任何 DataPump job 最多仅能启动4个 Workers，即使指定 parallel>4:

> expdp system/oracle@<PDB_service_name> directory=dptest dumpfile=test2.dmp full=yes parallel=7 logtime=all  metrics=yes
Export: Release 19.0.0.0.0 - Development on Tue Oct 9 08:19:49 2018
Version 19.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Development
09-OCT-18 08:20:02.544: Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@<PDB_service_name> directory=dptest dumpfile=test2.dmp full=yes parallel=7 logtime=all metrics=yes
09-OCT-18 08:20:03.709: W-1 Startup took 1 seconds
09-OCT-18 08:20:07.888: W-2 Startup took 0 seconds
09-OCT-18 08:20:07.951: W-3 Startup took 0 seconds
09-OCT-18 08:20:08.006: W-2 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
09-OCT-18 08:20:08.013: W-2      Completed 1 MARKER objects in 0 seconds
09-OCT-18 08:20:08.062: W-3 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
09-OCT-18 08:20:08.063: W-3      Completed 1 MARKER objects in 0 seconds
09-OCT-18 08:20:09.914: W-4 Startup took 0 seconds
.....................
09-OCT-18 08:20:33.919: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
09-OCT-18 08:20:35.033: W-2      Completed 4 INC_TYPE objects in 4 seconds
09-OCT-18 08:20:35.194: W-1      Completed 22 TYPE objects in 3 seconds
09-OCT-18 08:20:35.566: W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
09-OCT-18 08:20:35.867: W-1      Completed 2 PROCACT_SYSTEM objects in 0 seconds
09-OCT-18 08:20:37.741: W-3 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
09-OCT-18 08:20:38.376: W-3      Completed 23 PROCOBJ objects in 3 seconds
09-OCT-18 08:20:39.165: W-3 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
09-OCT-18 08:20:40.948: W-3      Completed 2 PROCACT_SYSTEM objects in 2 seconds
09-OCT-18 08:20:42.752: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
...........
09-OCT-18 08:23:30.475: W-3 . . exported "TC_USER5"."TAB8"                           5.046 KB       1 rows in 0 seconds using direct_path
09-OCT-18 08:23:30.592: W-3 . . exported "TC_USER5"."TAB9"                           5.046 KB       1 rows in 0 seconds using direct_path
09-OCT-18 08:23:31.715: W-1 . . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows in 0 seconds using external_table
09-OCT-18 08:23:32.737: W-4 Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
09-OCT-18 08:23:32.829: W-4      Completed 2 MATERIALIZED_VIEW objects in 52 seconds
09-OCT-18 08:23:34.962: W-4      Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 3 seconds
09-OCT-18 08:23:34.974: W-4      Completed 62 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 3 seconds
09-OCT-18 08:23:34.980: W-4      Completed 18 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 31 seconds
09-OCT-18 08:23:34.989: W-4      Completed 120 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 24 seconds
09-OCT-18 08:23:39.255: W-4 Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
09-OCT-18 08:23:39.303: ******************************************************************************
09-OCT-18 08:23:39.304: Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
09-OCT-18 08:23:39.307:   <PATH>/test2.dmp
09-OCT-18 08:23:39.424: Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Oct 9 08:23:39 2018 elapsed 0 00:03:48

- v$session 显示仅仅启动了4个 DataPump worker processes:
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, 
from   v$session s, v$process p, dba_datapump_sessions d
where  p.addr=s.paddr and s.saddr=d.saddr and UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');

DATE                PROGRAM                                       SID
------------------- -------------------------------------- ----------
2018-10-09 08:20:21 oracle@den01znf (DW00)                        168
2018-10-09 08:20:21 oracle@den01znf (DW01)                         50
2018-10-09 08:20:21 oracle@den01znf (DW02)                        164
2018-10-09 08:20:21 oracle@den01znf (DW03)                         41
复制

注意: 当指定的并行度大于 MAX_DATAPUMP_PARALLEL_PER_JOB 的值的时候，系统并不会报出任何错误。