Oracle Data Pump: expdp & impdp

Problem Statement:

1. Backup entire database using Data Pump.
2. Backup table(s)
3. Backup tablespace(s)
4. Backup schema(s)
5. Backup using Transportable tablespaces (TTS)
6. Generate multiple small sizes of dump files
7. Backup in parallel mode

Approach:

There are single shot solution to all the above problem statement and it is Data Pump.  It is one of various backup tools provided by Oracle and it is regularly improved by them.

One can access Data Pump utility using its interface DBMS_DATAPUMP PL/SQL package, DBMS_METADATA, expdp & impdp.  The use of expdp facilitates database backup at various levels.

expdp can be utilized in various ways to achieve different objectives related to backups.  However, to execute expdp command user needs to have  DATAPUMP_EXP_FULL_DATABASE role assigned in case if any table beyond your schema needs to be exported.

A schema export is specified using the SCHEMAS parameter. This is the default export mode.  In fact expdp command is supplied with few parameters:

1. directory : Name of directory which will be the location of file creation.
2. dumpfile : Name of dump file. For multiple files generic format <name>_%u.dmp can also be given.
3. logfile : Name of logfile.
4. include : Specific object can be included by this parameter.
5. exclude : Specific object can be excluded by this parameter.
6. content : This will limit content of dump file. Possible values would be METADATA_ONLY, DATA_ONLY, ALL
7. query : Filter rows of tables by supplying where clause. For example where clause can be supplied as SCOTT.DEPT:'”WHERE ROWNUM = 0″‘
8. network_link : This is used to utilized database linke during export.
9. flashback_time : This feature is supplied to get data consistency. Possible values: systimestamp
10. flashback_scn : This feature is same as flashback_time. However, this will use scn number instead of timestamp.
11. tables : List of tables can be provided using this parameter.
12. attach : If a expdp job is executing in background, user can connect with the specific job using this parameter.
13. compression : Compress the dump file contents. Possible values: METADATA_ONLY, DATA_ONLY, ALL, NONE
14. estimate : This will help to estimate required disk space. Possible value : Y, N
15. filesize : Size of dump file can be limited to the value of this parameter.
16. job_name : Name of export job to be defined using this parameter.
17. nologfile : Log file creation can be suppressed.
18. parallel : Cuncurrent number of thread for execution of expdp job.
19. parfile : Parameters can be defined with expdp on command line or can be defined in a file and name of the file is provided using this parameter.
20. reuse_dumpfiles : Existing dump file with same name will be Overwritten.
21. schemas : List of schemas to be provided.
22. status : The frequency in seconds which job displays client feedback
23. tablespaces : List of tablespaces to be provided.
24. transport_full_check : This is helpfule in case of transportable tablespaces for checking dependencies of objects to be exported.
25. transport_tablesapces : Using this parameter metadata of DB can be exported. This is the method through which we can migrate/move/copy multi terabytes database to another server.

The expdp command can be written for table level backup as:

expdp oracle/********@orcl tables=t1 directory=TEST_DIR dumpfile=T1_%U.dmp logfile=expdpEMP_DEPT.log filesize=1M

[oracle@mishika expdp]$ expdp oracle/********@orcl tables=t1 directory=TEST_DIR dumpfile=T1_%U.dmp logfile=expdpEMP_DEPT.log filesize=1M

Export: Release 12.2.0.1.0 – on XXXX XXXXXXXX XXXX

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit
Starting “oracle”.”SYS_EXPORT_TABLE_02″: oracle/********@orcl tables=t1 directory=TEST_DIR dumpfile=T1_%U.dmp logfile=expdpEMP_DEPT.log filesize=1M
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “oracle”.”T1″ 22.69 MB 173992 rows
Master table “oracle”.”SYS_EXPORT_TABLE_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for oracle.SYS_EXPORT_TABLE_02 is:
/Dump/expdp/T1_01.dmp
/Dump/expdp/T1_02.dmp
/Dump/expdp/T1_03.dmp
/Dump/expdp/T1_04.dmp
/Dump/expdp/T1_05.dmp
/Dump/expdp/T1_06.dmp
/Dump/expdp/T1_07.dmp
/Dump/expdp/T1_08.dmp
/Dump/expdp/T1_09.dmp
/Dump/expdp/T1_10.dmp
/Dump/expdp/T1_11.dmp
/Dump/expdp/T1_12.dmp
/Dump/expdp/T1_13.dmp
/Dump/expdp/T1_14.dmp
/Dump/expdp/T1_15.dmp
/Dump/expdp/T1_16.dmp
/Dump/expdp/T1_17.dmp
/Dump/expdp/T1_18.dmp
/Dump/expdp/T1_19.dmp
/Dump/expdp/T1_20.dmp
/Dump/expdp/T1_21.dmp
/Dump/expdp/T1_22.dmp
/Dump/expdp/T1_23.dmp
Job “oracle”.”SYS_EXPORT_TABLE_02″ successfully completed at Fri Sep 1 16:05:44 2017 elapsed 0 00:00:49
[oracle@mishika expdp]$