Shulou(Shulou.com)06/02 Report--

This compression ratio is comparable to that of the operating system "gzip-9" and may be more efficient than gzip in some special cases. Experience it for reference.

Description of COMPRESSION parameters in the expdp help page in 1.Oracle 11g

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.

……

As you can see, the COMPRESSION parameter in 11g provides four options, namely ALL, DATA_ONLY, METADATA_ONLY, and NONE, which are very rich, and we will use the ALL parameter to operate later.

The description of the COMPRESSION parameter in 2.Oracle 10g is as follows

Keyword Description (Default)

……

COMPRESSION Reduce size of dumpfile contents where valid

Keyword values are: (METADATA_ONLY) and NONE.

……

The COMPRESSION parameter in 10g provides only two options, METADATA_ONLY and NONE, and basically does not provide compression.

3. Generate four dump files using the four compression parameters of the 11g EXPDP tool

1) use the ALL parameter

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) use DATA_ONLY parameter

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) use METADATA_ONLY parameter

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) use NONE parameter

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. Compare the size of the four generated dump files

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

The experimental results show that the backup file generated by using ALL parameter and DATA_ONLY parameter is basically the same, and the effect of using METADATA_ONLY parameter is the same as that of NONE parameter.

The ratio of using compression to not compressing is: 15Compact 112 equals 1Universe 7!

This is the real compression.

5. Use "gzip-9" to compress uncompressed backup files and make a comparison

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

There is little difference between 14987264 and 14717055, basically the same, and "gzip-9" is a little better (maybe "gzip-9" will be a little less than in your specific environment).

6. Summary

Oracle 11g's EXPDP tool provides true "backup compression", which is very useful in situations where backup space is insufficient.

Oracle is constantly moving forward in improvement and optimization, so take your time.

Good luck.

Secooler

10.03.20

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.