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

EXPDP how to use PARALLEL and FILESIZE parameters to improve backup efficiency, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The combination of the PARALLEL and FILESIZE parameters of the EXPDP command can improve backup efficiency in "some cases".

This method can complete parallel logical backups.

1. Environmental preparation

1) create a directory object

Sys@secooler > create or replace directory expdp_dir as'/ expdp'

Directory created.

2) read and write permissions are granted to sec users

Sys@secooler > grant read,write on directory expdp_dir to sec

Grant succeeded.

3) confirm that the EXPDP_DIR directory points to

Sys@secooler > select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='EXPDP_DIR'

DIRECTORY_PATH

-

/ expdp

two。 Efficient scenarios for parallel backup

The file generated by backup is about 1.3G.

1) the backup test script is as follows

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) run the test script

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

…… Omit.

. . 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

…… Omit.

. . 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) three time points are selected for comparison

Mon Mar 29 13:36:38 GMT 2010

Mon Mar 29 13:45:12 GMT 2010

Mon Mar 29 13:46:00 GMT 2010

The exp logical backup method takes 8 minutes and 34 seconds, while the parallel backup of expdp takes only 48 seconds. The efficiency of expdp parallel backup is evident.

3. Parallel backup of inefficient performance in some scenarios

At this time, the backup file is about 100m.

1) the test script is as follows

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

~

Note that at this time, a file is exported in parallel according to the size of 10m. In this way, more than 100m backup files will generate more than 10 small backup files. Therefore, it will increase the burden on the system.

2) script running process

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) sort out the three time points

Sort out three times:

Mon Mar 29 21:16:48 CST 2010

Mon Mar 29 21:17:25 CST 2010

Mon Mar 29 21:19:13 CST 2010

The first backup took 37 seconds and the second backup took 1 minute and 48 seconds, indicating that enabling parallel backups does not necessarily have better performance than normal backups because it requires a lot of extra overhead.

4. Description of the "% U" parameter in the script

The "% U" that appears in "dumpfile=sec_expdp_%U.dmp" indicates that an incremented serial number is automatically generated.

For more information on the use of the% U parameter, please refer to the following Oracle official documentation description:

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref44

5. Summary

EXPDP backup tool can greatly improve our backup efficiency when the amount of data is large and the configuration is reasonable.

Do a full test before use.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.