[转帖]EXPDP dumpfile和parallel的关系

http://blog.itpub.net/28602568/viewspace-2133375/

转帖 EXPDP 里面 parallel 与 dumpfile 里面的文件数的关系.

但是我这里有一个疑惑 我impdp的时候 导出的parallel 用的是 8 产生了 8个文件 我导入的时候 选择是 4 就报错.

可能导入与导出需要的文件数目必须相同才可以. 

 

 

同事晚上值班使用expdp备份1TB量的DB，“开并行”备份了5个小时。

命令：

expdp "'/ as sysdba'" dumpfile=all.dmp  directory=expdp schemas=用户 parallel=8 compression=all

 

原因：

导出文件数量少于并发数时，多于并发将不会工作，也就是说导出文件dumpfile的个数就是有效的parallel并行个数。

dumpfile=file_name.%U.dmp  文件将按需要创建n+1(通配符 %U )

【如果dumpfile 指定一个文件，并发设置过大，在导出过程中可能直接报错（ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes）

导入是paralle要小于dump文件数。 如果paralle 大于dump文件的个数，就会因为超过的那个进程获取不到文件，就不能对性能提高。】

 

演示：

命令：

expdp "'/ as sysdba'" dumpfile=al1.dmp,al2.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all

 

 

attach观察expdp导出情况：

$ expdp "'/ as sysdba'" attach=SYS_EXPORT_SCHEMA_05
Job: SYS_EXPORT_SCHEMA_05
  Owner: SYS
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 44C8EA6252512B44E0530701F00AF814
  Start Time: Thursday, 29 December, 2016 16:31:45
  Mode: SCHEMA
  Instance: test
  Max Parallelism: 4
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        "/******** AS SYSDBA" dumpfile=al1.dmp,al2.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all
     COMPRESSION           ALL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: /data/auto/al1.dmp
    bytes written: 4,096
  Dump File: /data/auto/al2.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: ITEM
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 35
  Total Objects: 35
  Worker Parallelism: 1
 
Worker 2 Status:
  Process Name: DW01
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: CON_CONTENT_MOVEHIS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 35
  Completed Rows: 5,408,389
  Worker Parallelism: 1
 
Worker 3 Status:
  Process Name: DW02
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: CON_CONTENT_HISTORYHIS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 35
  Completed Rows: 6,535,089
  Worker Parallelism: 1
 
Worker 4 Status:
  Process Name: DW03
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: CON_CONTENT_HISTORY
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 35
  Worker Parallelism: 1
 
Export>
Export> status
 
Job: SYS_EXPORT_SCHEMA_05
  Operation: EXPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: /data/auto/al1.dmp
    bytes written: 4,096
  Dump File: /data/auto/al2.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: ITEM
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 35
  Total Objects: 35
  Worker Parallelism: 1
 
Worker 2 Status:
  Process Name: DW01
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: CON_CONTENT_MOVEHIS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 35
  Completed Rows: 26,723,846
  Worker Parallelism: 1
 
Worker 3 Status:
  Process Name: DW02
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: CON_CONTENT_HISTORYHIS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 35
  Completed Rows: 34,458,978
  Worker Parallelism: 1
 
Worker 4 Status:
  Process Name: DW03
  State: EXECUTING
  Object Schema: USR_WMS_CITY
  Object Name: CON_CONTENT_HISTORY
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 35
  Worker Parallelism: 1
 
Export>
****** 只有2个进程(2个dumpfile)在工作(有completed rows:XXX)

 

后台数据库层查看正在执行的sql如下...event = direct path read 是2个工作进程，其他2个transceive_int 对应等待是wait for unread message on broadcast channel 空闲等待；

使用dumpfile=$bak_time.%U.dmp后台情况..

expdp "'/ as sysdba'" dumpfile= $bak_time.%U.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all logfile=$bak_time.log

  【源于本人笔记】 若有书写错误，表达错误，请指正...

转载于:https://www.cnblogs.com/jinanxiaolaohu/p/10789215.html