1. 首页
  2. IT资讯

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

这个压缩比例可以和操作系统“gzip -9”相媲美,某些特例下有可能比gzip还要高效。体验之,供参考。

1.Oracle11g中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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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在改进和优化中不断的前进,慢慢体会吧。

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195411.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code