How to export/import faster with export data pump with parallel clause

Example:

Create database directory for physical path for backup.

SQL> create directory DBBACKUP as ‘/archive/backup/’;

Directory created.

Grant permission to everyone.

SQL> grant read,write on directory DBBACKUP to public;

Grant succeeded.

SQL>

Start expdp with parallel clause

[oracle@dbsrv ~]$ expdp scott/tiger directory=DBBACKUP parallel=4 dumpfile=SCOT_%U.dmp logfile=SCOT-EXPDP.log

Export: Release 10.2.0.5.0 – 64bit Production on Thursday, 30 May, 2013 9:49:18

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting “SCOTT”.”SYS_EXPORT_SCHEMA_01″: scott/******** directory=DBBACKUP parallel=4 dumpfile=SCOT_%U.dmp logfile=SCOT-EXPDP.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
. . exported “SCOTT”.”DEPT” 5.656 KB 4 rows
. . exported “SCOTT”.”EMP” 7.820 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.585 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/archive/backup/SCOT_01.dmp
/archive/backup/SCOT_02.dmp
/archive/backup/SCOT_03.dmp
Job “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 09:49:46

Backup pieces will be multiple

[oracle@dbsrv ~]$ ls -rlt /archive/backup/*
-rw-rw—- 1 oracle oinstall 28672 May 30 09:49 /archive/backup/SCOT_01.dmp
-rw-rw—- 1 oracle oinstall 147456 May 30 09:49 /archive/backup/SCOT_03.dmp
-rw-rw—- 1 oracle oinstall 36864 May 30 09:49 /archive/backup/SCOT_02.dmp
-rw-rw-r– 1 oracle oinstall 1795 May 30 09:49 /archive/backup/SCOT-EXPDP.log
[oracle@dbsrv ~]$

 

How to start import with parallel clause
[oracle@dbsrv ~]$ impdp scott/tiger directory=DBBACKUP parallel=4 dumpfile=SCOT_%U.dmp logfile=SCOT-IMPDP.log

Import: Release 10.2.0.5.0 – 64bit Production on Thursday, 30 May, 2013 9:55:24

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_FULL_01″: scott/******** directory=DBBACKUP parallel=4 dumpfile=SCOT_%U.dmp logfile=SCOT-IMPDP.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”DEPT” 5.656 KB 4 rows
. . imported “SCOTT”.”EMP” 7.820 KB 14 rows
. . imported “SCOTT”.”SALGRADE” 5.585 KB 5 rows
. . imported “SCOTT”.”BONUS” 0 KB 0 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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SCOTT”.”SYS_IMPORT_FULL_01″ successfully completed at 09:55:31

[oracle@dbsrv ~]$

See Import log file

[oracle@dbsrv ~]$ ls -rlt /archive/backup/
total 256
-rw-rw—- 1 oracle oinstall 28672 May 30 09:49 SCOT_01.dmp
-rw-rw—- 1 oracle oinstall 147456 May 30 09:49 SCOT_03.dmp
-rw-rw—- 1 oracle oinstall 36864 May 30 09:49 SCOT_02.dmp
-rw-rw-r– 1 oracle oinstall 1795 May 30 09:49 SCOT-EXPDP.log
-rw-rw-r– 1 oracle oinstall 1431 May 30 09:55 SCOT-IMPDP.log
[oracle@dbsrv ~]$

 

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle backup & recovery. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s