ITPub博客

首页 > 数据库 > Oracle > EXPDP dumpfile和parallel的关系

EXPDP dumpfile和parallel的关系

原创 Oracle 作者:maohaiqing0304 时间:2017-02-10 15:30:38 0 删除 编辑


作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


同事晚上值班使用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


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


此条目发表在   Oracle  分类目录。将固定连接加入收藏夹。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-2133375/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长SQL编写及SQL优化,分析瓶颈,性能调优、故障处理,根据实际情况定制备份策略; 擅长编写脚本来实现自动化功能,600+SQL优化经验案例,为人热爱学习,喜欢钻研技术,对工作认真负责。

注册时间:2013-03-13

  • 博文量
    121
  • 访问量
    2305006