- Kiran Dalvi
- 28 May, 2019
- 0 Comments
- 4 Mins Read
Oracle 12C Datapump- EXPDP-IMPDP
Datapump is the utility to take the logical backup of the oracle databases. Following can be take with the help of datapump.
- Tables
- Tablespaces
- Full Database Logical Backup
- Users
- Index
- Stats
- Metadata
Following command will create the logical directory which will point to os physical level directory.
First Create the physical directory :
[oracle@oracle ~]$ mkdir -p /data/datapump
Open the sqlplus utility and fire the below command to create database directory ( test_dir ) point to os level directory( /data/datapump ).
CREATE OR REPLACE DIRECTORY test_dir AS '/data/datapump';
GRANT READ, WRITE ON DIRECTORY test_dir TO sys;
Table level backup commad.
expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=C##TESTUSER_testtable.log
Import above table backup with command impdp.
impdp – utility to import the backup of expdp
dumpfile – provide the name of dumpfile. dumpfile name can be taken from expdp command.
logfile – logfile will track the logs of impdp command
impdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=impdp_C##TESTUSER_testtable.log
Schema backup
Schema backup also take the backup of schema objects.
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=C##TESTUSER_USER.log
Schema Import
impdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=imp_C##TESTUSER_USER.log
Schema include parameter
Schema backup comes with two parameter. these are include and exclude. In following example it will only take the backup of included table and rest of the tables and other objects will be excluded.
expdp schemas=C##TESTUSER include=TABLE:\""IN ('TESTTABLE2')\"" directory=TEST_DIR dumpfile=C##TESTUSER_include.dmp logfile=C##TESTUSER_include.log
Schema exclude parameter
Below example will take the backup of schema and all schema objects but will exclude object in excluded list.
expdp schemas=C##TESTUSER exclude=TABLE:\""IN ('TESTTABLE3')\"" directory=TEST_DIR dumpfile=C##TESTUSER_exclude.dmp logfile=C##TESTUSER_exclude.log
Following command only takes the metadata information of schema. It is possible with the help of content=METADATA_ONLY parameter.
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER _meta.dmp logfile=C##TESTUSER_meta.log content=METADATA_ONLY
Query Parameter
Query parameter will take the backup of selected rows from tables as per the where condition.
expdp full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query='SCOTT.EMP:"WHERE deptno=0",SCOTT.DEPT:"WHERE deptno=0"'
Logtime Parameter
Logtime parameter display the logtime along with the export output. Logitimes comes with three values as below.
NONE
: The default value, which indicates that no timestamps should be included in the output, making the output look similar to that of previous versions.STATUS
: Timestamps are included in output to the console, but not in the associated log file.LOGFILE
: Timestamps are included in output to the log file, but not in the associated console messages.ALL
: Timestamps are included in output to the log file and console.
expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_logtime.dmp logfile=C##TESTUSER_logtime.log logtime=all
Parallel
Parallel will allocate the no of worker to finish the work simultaneously. this parameter is basically usefull to increase the performance of datapump export.
In following example %U refer the name of dump. as we are running job parallelly .It will create the no of dump name serially.
dump file name will be like C##TESTUSER_1.dmp , C##TESTUSER_%2.dmp .
expdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=C##TESTUSER_parallel.log
Above dump can be imported like below.
Here in import we have %U as dump is split into no of parts.
impdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=impdpC##TESTUSER_parallel.log
Filesize
We can also limit the size of export dump by using filesize parameter.
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G
Compression
Datapump export can be compress to reduce the size of export dump.
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G compression=all
IMPORT Splittable Dump
IMPDP directory=ABC dumpfile=exp_pdco1mob00_full_1_12232013.dmp,
exp_pdco1mob00_full_2_12232013.dmp,
exp_pdco1mob00_full_3_12232013.dmp,
exp_pdco1mob00_full_4_12232013.dmp
logfile=IMPORT_DUMP full=y
estimate=statistics
expdp \'sys/Oracle1234@pdbcdb as sysdba\' schemas=PDBUSER2 directory=test_dir dumpfile=testtable1_schema_estimate.dmp logfile=testtable11_schema_estimate.log estimate=statistics
Estimate= block
expdp \'sys/Oracle1234@pdbcdb as sysdba\' schemas=PDBUSER2 directory=test_dir dumpfile=testtable1_schema_estimate.dmp logfile=testtable11_schema_estimate.log estimate=block
Estimate the space need for EXPDP backup in Oracle
[oracle@prim ~]$ expdp \'sys/oracle@pdbprim as sysdba\' schemas=PDBUSER4 directory=TEST_DIR logfile=PDBUSER4_schema_12_estimate.log ESTIMATE_ONLY=Y Export: Release 19.0.0.0.0 - Production on Sun May 1 14:53:42 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01": "sys/********@pdbprim AS SYSDBA" schemas=PDBUSER4 directory=TEST_DIR logfile=PDBUSER4_schema_12_estimate.log ESTIMATE_ONLY=Y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . estimated "PDBUSER4"."TESTTABLE4" 288 MB . estimated "PDBUSER4"."TESTTABLE3" 192 MB Total estimation using BLOCKS method: 480 MB Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun May 1 14:53:52 2022 elapsed 0 00:00:08