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 空闲等待;
![](http://img.blog.itpub.net/blog/attachment/201702/10/28602568_1486711990DrzH.png?x-oss-process=style/bb)
使用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
![](http://img.blog.itpub.net/blog/attachment/201702/10/28602568_1486712009GPq3.png?x-oss-process=style/bb)
【源于本人笔记】 若有书写错误,表达错误,请指正...