DBA Hub

📋Steps in this guide1/12

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.

oracle configurationintermediate
by OracleDba
14 views
1

Overview

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 :

Code/Command (click line numbers to comment):

1
2
3
[oracle@oracle ~]$ mkdir -p /data/datapump

[oracle@oracle ~]$ mkdir -p /data/datapump
2

Section 2

Open the sqlplus utility and fire the below command to create database directory ( test_dir ) point to os level directory( /data/datapump ). Table level backup commad.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE DIRECTORY test_dir AS '/data/datapump';

GRANT READ, WRITE ON DIRECTORY test_dir TO sys;

CREATE OR REPLACE DIRECTORY test_dir AS '/data/datapump';

GRANT READ, WRITE ON DIRECTORY test_dir TO sys;

expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=C##TESTUSER_testtable.log
3

Section 3

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

Code/Command (click line numbers to comment):

1
2
3
4
5
expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=C##TESTUSER_testtable.log

impdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=impdp_C##TESTUSER_testtable.log

impdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=impdp_C##TESTUSER_testtable.log
4

Section 4

Schema backup > Schema backup also take the backup of schema objects. Schema backup also take the backup of schema objects. Schema backup also take the backup of schema objects. Schema Import

Code/Command (click line numbers to comment):

1
2
3
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=C##TESTUSER_USER.log

expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=C##TESTUSER_USER.log
5

Section 5

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
impdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=imp_C##TESTUSER_USER.log

impdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=imp_C##TESTUSER_USER.log

expdp schemas=C##TESTUSER include=TABLE:\""IN ('TESTTABLE2')\"" directory=TEST_DIR dumpfile=C##TESTUSER_include.dmp logfile=C##TESTUSER_include.log

expdp schemas=C##TESTUSER include=TABLE:\""IN ('TESTTABLE2')\"" directory=TEST_DIR dumpfile=C##TESTUSER_include.dmp logfile=C##TESTUSER_include.log
6

Section 6

Schema exclude parameter Below example will take the backup of schema and all schema objects but will exclude object in excluded list. Following command only takes the metadata information of schema. It is possible with the help of content=METADATA_ONLY parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
expdp schemas=C##TESTUSER exclude=TABLE:\""IN ('TESTTABLE3')\"" directory=TEST_DIR dumpfile=C##TESTUSER_exclude.dmp logfile=C##TESTUSER_exclude.log

expdp schemas=C##TESTUSER exclude=TABLE:\""IN ('TESTTABLE3')\"" directory=TEST_DIR dumpfile=C##TESTUSER_exclude.dmp logfile=C##TESTUSER_exclude.log

expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER _meta.dmp logfile=C##TESTUSER_meta.log content=METADATA_ONLY
7

Section 7

Query Parameter Query parameter will take the backup of selected rows from tables as per the where condition. Logtime Parameter

Code/Command (click line numbers to comment):

1
2
3
4
5
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER _meta.dmp logfile=C##TESTUSER_meta.log content=METADATA_ONLY

expdp full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query='SCOTT.EMP:"WHERE deptno=0",SCOTT.DEPT:"WHERE deptno=0"'

expdp full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query='SCOTT.EMP:"WHERE deptno=0",SCOTT.DEPT:"WHERE deptno=0"'
8

Section 8

Logtime parameter display the logtime along with the export output. Logitimes comes with three values as below. - : The default value, which indicates that no timestamps should be included in the output, making the output look similar to that of previous versions. : Timestamps are included in output to the console, but not in the associated log file. : Timestamps are included in output to the log file, but not in the associated console messages. : Timestamps are included in output to the log file and console. 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 .

Code/Command (click line numbers to comment):

1
2
3
expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_logtime.dmp logfile=C##TESTUSER_logtime.log logtime=all

expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_logtime.dmp logfile=C##TESTUSER_logtime.log logtime=all
9

Section 9

Above dump can be imported like below. > Here in import we have %U as dump is split into no of parts. Here in import we have %U as dump is split into no of parts. Here in import we have %U as dump is split into no of parts.

Code/Command (click line numbers to comment):

1
2
3
4
5
expdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=C##TESTUSER_parallel.log

expdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=C##TESTUSER_parallel.log

impdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=impdpC##TESTUSER_parallel.log
10

Section 10

Filesize We can also limit the size of export dump by using filesize parameter. Compression

Code/Command (click line numbers to comment):

1
2
3
4
5
impdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=impdpC##TESTUSER_parallel.log

expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G

expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G
11

Section 11

Datapump export can be compress to reduce the size of export dump. 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G compression=all

expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G compression=all

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
12

Section 12

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!