EXPDP如何運(yùn)用PARALLEL 和FILESIZE參數(shù)提高備份效率

EXPDP如何運(yùn)用PARALLEL 和FILESIZE參數(shù)提高備份效率，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

EXPDP命令的PARALLEL和FILESIZE參數(shù)聯(lián)合使用在“某些情況下”可以提高備份的效率。
這種方法可以完成并行的邏輯備份。

1.環(huán)境準(zhǔn)備
1）創(chuàng)建directory對(duì)象
sys@secooler> create or replace directory expdp_dir as '/expdp';

Directory created.

2）讀寫權(quán)限授權(quán)給sec用戶
sys@secooler> grant read,write on directory expdp_dir to sec;

Grant succeeded.

3）確認(rèn)EXPDP_DIR目錄指向
sys@secooler> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='EXPDP_DIR';

DIRECTORY_PATH
-------------------------------------
/expdp

2.并行備份高效的場(chǎng)景
備份生成的文件大概在1.3G

1）備份測(cè)試腳本如下
secooler@secDB /expdp$ vi test.sh
date
exp sec/sec file=sec_exp.dmp log=sec_exp.log
date
expdp sec/sec directory=expdp_dir dumpfile=sec_expdp_%U.dmp logfile=sec_expdp.log filesize=500m parallel=4
date
~

2）運(yùn)行測(cè)試腳本
sec@secDB /expdp$ sh test.sh
Mon Mar 29 13:36:38 GMT 2010

Export: Release 10.2.0.3.0 - Production on Mon Mar 29 13:36:38 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC
About to export SEC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC's tables via Conventional Path ...
. . exporting table           SEC_ATTACH_VPDS_TEMP         37 rows exported
. . exporting table         SEC_CHANGE_DEGDSE_TEMP      67915 rows exported
……省略……
. . exporting table                T_SEC_SDE_RESES          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Mon Mar 29 13:45:12 GMT 2010

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 29 March, 2010 13:45:12

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01":  SEC/******** directory=expdp_dir dumpfile=SEC_expdp_%U.dmp logfile=SEC_expdp.log filesize=500m parallel=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.110 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
. . exported "SEC"."T_SEC_DAC_LOG"                  94.67 MB 1984475 rows
. . exported "SEC"."T_SEC_AGRTT_APPLY"              93.57 MB  572489 rows
……省略……
. . exported "SEC"."T_VSE_CLD_PDESOD"               19.25 KB     282 rows
. . exported "SEC"."T_VSE_SEC_RLELE_DAY_TYPE"       9.296 KB      18 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
  /expdp/SEC_expdp_01.dmp
  /expdp/SEC_expdp_02.dmp
  /expdp/SEC_expdp_03.dmp
  /expdp/SEC_expdp_04.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:45:59

Mon Mar 29 13:46:00 GMT 2010

3）列選三個(gè)時(shí)間點(diǎn)進(jìn)行比較
Mon Mar 29 13:36:38 GMT 2010
Mon Mar 29 13:45:12 GMT 2010
Mon Mar 29 13:46:00 GMT 2010

exp邏輯備份方法共用時(shí)8分34秒，而expdp的并行備份僅僅用時(shí)48秒。expdp并行備份的效率可見一斑。

3.并行備份某些場(chǎng)景低效表現(xiàn)
此時(shí)備份文件大概在100M左右。

1）測(cè)試腳本如下
secooler@secDB /expdp$ vi test.sh
date
exp sec/sec file=sec_exp.dmp log=sec_exp.log
date
expdp sec/sec directory=expdp_dir dumpfile=sec_expdp_%U.dmp logfile=sec_expdp.log filesize=10m parallel=4
date
~

注意，此時(shí)是按照10m大小一個(gè)文件進(jìn)行并行導(dǎo)出的。這樣100M多的備份文件會(huì)生成10多個(gè)小的備份文件。因此會(huì)加重系統(tǒng)的負(fù)擔(dān)。

2）腳本運(yùn)行過(guò)程
secooler@secDB /expdp$ sh test.sh
Mon Mar 29 21:16:48 CST 2010

Export: Release 11.2.0.1.0 - Production on Mon Mar 29 21:16:48 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 option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC
About to export SEC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC's tables via Conventional Path ...
. . exporting table                              T    1155520 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Mon Mar 29 21:17:25 CST 2010

Export: Release 11.2.0.1.0 - Production on Mon Mar 29 21:17:25 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_SCHEMA_01":  sec/******** directory=expdp_dir dumpfile=sec_expdp_%U.dmp logfile=sec_expdp.log filesize=10m parallel=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . exported "SEC"."T"                                   111.7 MB 1155520 rows
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
  /expdp/sec_expdp_01.dmp
  /expdp/sec_expdp_02.dmp
  /expdp/sec_expdp_03.dmp
  /expdp/sec_expdp_04.dmp
  /expdp/sec_expdp_05.dmp
  /expdp/sec_expdp_06.dmp
  /expdp/sec_expdp_07.dmp
  /expdp/sec_expdp_08.dmp
  /expdp/sec_expdp_09.dmp
  /expdp/sec_expdp_10.dmp
  /expdp/sec_expdp_11.dmp
  /expdp/sec_expdp_12.dmp
  /expdp/sec_expdp_13.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:19:08

Mon Mar 29 21:19:13 CST 2010

3）整理一下三個(gè)時(shí)間點(diǎn)
整理一下三個(gè)時(shí)間：
Mon Mar 29 21:16:48 CST 2010
Mon Mar 29 21:17:25 CST 2010
Mon Mar 29 21:19:13 CST 2010

第一次備份使用了37秒，第二次備份使用了1分48秒，說(shuō)明啟用并行備份不一定會(huì)比普通備份性能高，因?yàn)樾枰芏嗟念~外開銷。

4.關(guān)于腳本中“%U”參數(shù)的說(shuō)明
“dumpfile=sec_expdp_%U.dmp”出現(xiàn)的“%U”表示自動(dòng)生成遞增的序列號(hào)。
更多關(guān)于%U參數(shù)的使用說(shuō)明請(qǐng)參考如下Oracle官方文檔描述：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref44

5.小結(jié)
EXPDP備份工具在數(shù)據(jù)量較大并且配置合理的情況下，可以極大地提高我們的備份效率。
在使用前做好充分的測(cè)試。

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。