Oracle 11g版本expdp的COMPRESSION参数压缩比堪比“gzip -9”

166次阅读

共计 5911 个字符，预计需要花费 15 分钟才能阅读完成。

1.Oracle 11g 中 expdp 帮助页中关于 COMPRESSION 参数的描述
secooler@secDB /home/oracle$ expdp -help
……
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
……

可见，11g 中的 COMPRESSION 参数提供四个选项，分别是 ALL、DATA_ONLY、METADATA_ONLY 和 NONE，非常的丰富，稍后我们将使用 ALL 参数进行操作。

2.Oracle 10g 中关于 COMPRESSION 参数的描述如下
Keyword Description (Default)
———————————————————————
……
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
……
10g 中的 COMPRESSION 参数只提供 METADATA_ONLY 和 NONE 两个选项，基本上没有提供压缩功能。

3. 使用 11g EXPDP 工具的四个压缩参数分别生成四个 dump 文件
1）使用 ALL 参数
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_ALL.dmp logfile=sec_ALL.log tables=t directory=dump_dir compression=ALL

Export: Release 11.2.0.1.0 – Production on Sat Mar 20 22:48:52 2010

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting “SEC”.”SYS_EXPORT_TABLE_01″: sec/******** dumpfile=sec_ALL.dmp logfile=sec_ALL.log tables=t directory=dump_dir compression=ALL
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SEC”.”T” 14.26 MB 1155520 rows
Master table “SEC”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_ALL.dmp
Job “SEC”.”SYS_EXPORT_TABLE_01″ successfully completed at 22:49:08

2）使用 DATA_ONLY 参数
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_DATA_ONLY.dmp logfile=sec_DATA_ONLY.log tables=t directory=dump_dir compression=DATA_ONLY

Export: Release 11.2.0.1.0 – Production on Sat Mar 20 22:49:32 2010

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting “SEC”.”SYS_EXPORT_TABLE_01″: sec/******** dumpfile=sec_DATA_ONLY.dmp logfile=sec_DATA_ONLY.log tables=t directory=dump_dir compression=DATA_ONLY
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SEC”.”T” 14.26 MB 1155520 rows
Master table “SEC”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_DATA_ONLY.dmp
Job “SEC”.”SYS_EXPORT_TABLE_01″ successfully completed at 22:49:48

3）使用 METADATA_ONLY 参数
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_METADATA_ONLY.dmp logfile=sec_METADATA_ONLY.log tables=t directory=dump_dir compression=METADATA_ONLY

Export: Release 11.2.0.1.0 – Production on Sat Mar 20 22:50:16 2010

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting “SEC”.”SYS_EXPORT_TABLE_01″: sec/******** dumpfile=sec_METADATA_ONLY.dmp logfile=sec_METADATA_ONLY.log tables=t directory=dump_dir compression=METADATA_ONLY
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SEC”.”T” 111.7 MB 1155520 rows
Master table “SEC”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_METADATA_ONLY.dmp
Job “SEC”.”SYS_EXPORT_TABLE_01″ successfully completed at 22:50:27

4）使用 NONE 参数
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_NONE.dmp logfile=sec_NONE.log tables=t directory=dump_dir compression=NONE

Export: Release 11.2.0.1.0 – Production on Sat Mar 20 22:50:43 2010

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting “SEC”.”SYS_EXPORT_TABLE_01″: sec/******** dumpfile=sec_NONE.dmp logfile=sec_NONE.log tables=t directory=dump_dir compression=NONE
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SEC”.”T” 111.7 MB 1155520 rows
Master table “SEC”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_NONE.dmp
Job “SEC”.”SYS_EXPORT_TABLE_01″ successfully completed at 22:50:53

4. 比较生成的四个 dump 文件大小
secooler@secDB /expdp$ du -sm sec*.dmp
15 sec_ALL.dmp
15 sec_DATA_ONLY.dmp
112 sec_METADATA_ONLY.dmp
112 sec_NONE.dmp
secooler@secDB /expdp$ du -sb sec*.dmp
14987264 sec_ALL.dmp
14987264 sec_DATA_ONLY.dmp
117223424 sec_METADATA_ONLY.dmp
117223424 sec_NONE.dmp

实验表明，使用 ALL 参数和 DATA_ONLY 参数生成的备份文件基本一样大；使用 METADATA_ONLY 参数与 NONE 参数效果一样。

使用压缩与不进行压缩的比率是：15/112 约等于 1 /7！
这才是真正的压缩。

5. 使用“gzip -9”对未压缩的备份文件进行压缩，做一下比较
secooler@secDB /expdp$ gzip -9 sec_NONE.dmp
secooler@secDB /expdp$ du -sm sec*dmp*
15 sec_ALL.dmp
15 sec_DATA_ONLY.dmp
112 sec_METADATA_ONLY.dmp
15 sec_NONE.dmp.gz
secooler@secDB /expdp$ du -sb sec*dmp*
14987264 sec_ALL.dmp
14987264 sec_DATA_ONLY.dmp
117223424 sec_METADATA_ONLY.dmp
14717055 sec_NONE.dmp.gz

14987264 与 14717055 的区别，相差甚微，基本相同，“gzip -9”稍胜一点点（也许在您的具体环境下“gzip -9”会逊色一点点）。

6. 小结
Oracle 11g 的 EXPDP 工具提供了真正意义上的“备份压缩”，这个技术在备份空间不足的情况下非常实用。
Oracle 在改进和优化中不断的前进，慢慢体会吧。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-09/135240.htm

正文完

星哥说事-微信公众号