Oracle exp、expdp 压缩备份

原创心在梦在 2023-05-29

5612

Oracle exp、expdp 压缩备份

背景：

除了rman备份，我们还会遇到一些需求，需要经常或者定期做exp、expdp的dmp备份。你是否遇到过如下问题：

导出的dmp文件过大，磁盘空间也比较紧张，无法正常导出。
crontab定期导出dmp文件，再压缩并保存最近一周的，但是随着数据量变大之后，空间紧张，甚至会因为导出任务导致磁盘使用率达到100%。

针对这种问题，我们通常采取解决办法：

采用nfs挂盘方式来解决，往大的盘符中导出。
增加磁盘，扩容。
缩短dmp文件保留周期，减少磁盘占用。

这里我们再介绍一下数据泵的压缩备份，来减少磁盘空间占用。

一、expdp 方式

– 查看expdp 帮助文档

[oracle@ora11204 dmp]$ expdp -help |grep COMPRESSION -A 3
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
复制

all ：对导出的元数据和表数据都进行压缩，得到的导出文件是最小的，耗时也是最长的。
data_only：仅对表数据进行压缩，对于大数据量的导出效果明显，会比metadata_only方式得到更小的压缩文件。
metadata_only：仅对元数据进行压缩，而不会对表数据进行压缩，这种压缩执行后效果一般不是很明显，不过速度比较快。
none：不进行任何的压缩，导出后的文件也是最大的。
default：默认方式，即不指定compression参数，会采用默认的压缩方式metadata_only。

– 通过实验验证一下：

1. 创建一张大表

SQL> create table test as select * from dba_objects;

Table created.

-- 多插入几次
SQL> insert into test select * from test;

5056064 rows created.

SQL> commit;

-- 查询表大小：1.13G
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='TEST' and owner='SXC';

SUM(BYTES/1024/1024/1024)
-------------------------
     1.13085938
复制

2. 不同压缩模式导出

all 压缩方式

[oracle@ora11204 ~]$ expdp sxc/sxc directory=dir_exp dumpfile=test_all.dmp tables=test compression=all

Export: Release 11.2.0.4.0 - Production on Fri May 26 07:34:14 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SXC"."SYS_EXPORT_TABLE_02":  sxc/******** directory=dir_exp dumpfile=test_all.dmp tables=test compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.130 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TEST"                                127.9 MB 10112128 rows
Master table "SXC"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_02 is:
  /home/oracle/dmp/test_all.dmp
Job "SXC"."SYS_EXPORT_TABLE_02" successfully completed at Fri May 26 07:34:51 2023 elapsed 0 00:00:31
复制

data_only 压缩方式

[oracle@ora11204 ~]$ expdp sxc/sxc directory=dir_exp dumpfile=test_data_only.dmp tables=test compression=data_only

Export: Release 11.2.0.4.0 - Production on Fri May 26 07:35:28 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SXC"."SYS_EXPORT_TABLE_02":  sxc/******** directory=dir_exp dumpfile=test_data_only.dmp tables=test compression=data_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.130 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TEST"                                127.9 MB 10112128 rows
Master table "SXC"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_02 is:
  /home/oracle/dmp/test_data_only.dmp
Job "SXC"."SYS_EXPORT_TABLE_02" successfully completed at Fri May 26 07:36:00 2023 elapsed 0 00:00:31
复制

metadata_only 压缩方式

[oracle@ora11204 ~]$ expdp sxc/sxc directory=dir_exp dumpfile=test_metadata_only.dmp tables=test compression=metadata_only

Export: Release 11.2.0.4.0 - Production on Fri May 26 07:36:21 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SXC"."SYS_EXPORT_TABLE_02":  sxc/******** directory=dir_exp dumpfile=test_metadata_only.dmp tables=test compression=metadata_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.130 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TEST"                                978.9 MB 10112128 rows
Master table "SXC"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_02 is:
  /home/oracle/dmp/test_metadata_only.dmp
Job "SXC"."SYS_EXPORT_TABLE_02" successfully completed at Fri May 26 07:36:32 2023 elapsed 0 00:00:10
复制

none 压缩方式

[oracle@ora11204 ~]$ expdp sxc/sxc directory=dir_exp dumpfile=test_none.dmp tables=test compression=none

Export: Release 11.2.0.4.0 - Production on Fri May 26 07:36:56 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SXC"."SYS_EXPORT_TABLE_02":  sxc/******** directory=dir_exp dumpfile=test_none.dmp tables=test compression=none
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.130 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TEST"                                978.9 MB 10112128 rows
Master table "SXC"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_02 is:
  /home/oracle/dmp/test_none.dmp
Job "SXC"."SYS_EXPORT_TABLE_02" successfully completed at Fri May 26 07:37:06 2023 elapsed 0 00:00:09
复制

default 压缩方式（其实就是metadata_only 压缩方式）

[oracle@ora11204 ~]$ expdp sxc/sxc directory=dir_exp dumpfile=test_default.dmp tables=test

Export: Release 11.2.0.4.0 - Production on Fri May 26 07:37:25 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SXC"."SYS_EXPORT_TABLE_02":  sxc/******** directory=dir_exp dumpfile=test_default.dmp tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.130 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TEST"                                978.9 MB 10112128 rows
Master table "SXC"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_02 is:
  /home/oracle/dmp/test_default.dmp
Job "SXC"."SYS_EXPORT_TABLE_02" successfully completed at Fri May 26 07:37:35 2023 elapsed 0 00:00:10
复制

dmp文件大小

[oracle@ora11204 dmp]$ ll -lrth *.dmp
-rw-r----- 1 oracle oinstall 128M May 26 07:34 test_all.dmp
-rw-r----- 1 oracle oinstall 128M May 26 07:36 test_data_only.dmp
-rw-r----- 1 oracle oinstall 980M May 26 07:36 test_metadata_only.dmp
-rw-r----- 1 oracle oinstall 980M May 26 07:37 test_none.dmp
-rw-r----- 1 oracle oinstall 980M May 26 07:37 test_default.dmp
复制

3. 对比总结

压缩方式	dmp文件大小	导出时间
all	128M	31s
data_only	128M	31s
metadata_only	980M	10s
none	980M	9s
default	980M	10s

1)从文件大小对比：可以看到对于all 和data_only压缩方式，都是将1.12G的导出文件压缩到了128M，压缩效果较好。剩下的3种压缩方式大小都是980M，压缩效果较差，其实也很好理解，metadata 元数据本身就很小，所以是否压缩本身意义也不大。

2)从导出时间上：压缩效果最好的all 和data_only方式，相应的时间也是最长的，比另外3种方式时间上差了3倍。

二、exp 方式

针对一下低版本数据库，没有expdp工具，我们也可以使用exp备份，可以边导出边压缩，做一个测试：

1. 使用gzip压缩导出

[oracle@ora11204 ~]$ time exp sxc/sxc file=>(gzip>/home/oracle/dmp/test.dmp.gz) tables=test
Export: Release 11.2.0.4.0 - Production on Fri May 26 08:15:39 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                           TEST   10112128 rows exported
Export terminated successfully without warnings.
real    1m3.569s
user    0m25.762s
sys     0m1.569s
复制

– 在上面导出期间，观察dmp文件，可以看到exp导出过程只生产了test.dmp.gz压缩文件，且空间占用较小。相比于先导出，再压缩，空间不需要那么大。

[oracle@ora11204 dmp]$ ls -lrth
total 8.0M
-rw-r--r-- 1 oracle oinstall 5.6M May 26 08:06 test.dmp.gz
[oracle@ora11204 dmp]$ ls -lrth
total 16M
-rw-r--r-- 1 oracle oinstall 10M May 26 08:06 test.dmp.gz
[oracle@ora11204 dmp]$
[oracle@ora11204 dmp]$ ls -lrth
total 133M
-rw-r--r-- 1 oracle oinstall 133M May 26 08:07 test.dmp.gz
复制

2. 不使用压缩导出

[oracle@ora11204 ~]$ time exp sxc/sxc file=/home/oracle/dmp/test_exp_nocompress.dmp tables=test
Export: Release 11.2.0.4.0 - Production on Fri May 26 08:14:45 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                           TEST   10112128 rows exported
Export terminated successfully without warnings.
real    0m35.421s
user    0m24.766s
sys     0m2.027s
复制

3. 对比总结

[oracle@ora11204 ~]$ ls -lrth
total 1.3G
-rw-r--r-- 1 oracle oinstall 133M May 26 08:07 test.dmp.gz
-rw-r--r-- 1 oracle oinstall 1.1G May 26 08:10 test_exp_nocompress.dmp
复制

压缩方式	dmp文件大小	导出时间
gzip	133M	1m3.569s
none	1.1G	0m35.421s

1)从文件大小对比：压缩大小133M，压缩效果很好。

2)从导出时间上：压缩比不压缩方式时间上差了2倍。

4. 导入

另外这种方式，导入时，不需要手工进行解压，也可以使用gunzip边解压缩边导入。

SQL> drop table sxc.test;

Table dropped.
 

[oracle@ora11204 dmp]$ time imp sxc/sxc file=<(gunzip</home/oracle/dmp/test.dmp.gz) fromuser=sxc touser=sxc
Import: Release 11.2.0.4.0 - Production on Fri May 26 08:45:47 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SXC's objects into SXC
. . importing table                         "TEST"   10112128 rows imported
Import terminated successfully without warnings.
real    1m18.443s
user    0m28.644s
sys     0m2.243s
复制