DBA Hub

📋Steps in this guide1/12

Oracle datapump utility -EXPDP IMPDP

In this post we will be looking for logical backup of oracle database. datapump (EXPDP and IMPDP) utilties are used to take import and export of oracle

oracle configurationintermediate
by OracleDba
14 views
1

Overview

In this post we will be looking for logical backup of oracle database. datapump (EXPDP and IMPDP) utilties are used to take import and export of oracle databases. Mode of Datapump - Entire database - Schema level - Table level - Tablespace level - Transportable tablespace level Use of Datapump - Moving data from one schema to another - Moving data from one version of Oracle to another - Moving data from one OS to another - Creating logical backups Create Directory and Grant permission on directory.
2

Section 2

Table backup

Code/Command (click line numbers to comment):

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

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

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

expdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1.log
3

Section 3

Schema Backup

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
expdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1.log

impdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1_imp.log

impdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1_imp.log

expdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema.log

expdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema.log
4

Section 4

Full Database Backup

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
impdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema_imp.log

impdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema_imp.log

expdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_db.log

expdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_db.log

impdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_IMP_db.log
5

Section 5

Include and Exclude

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
impdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_IMP_db.log

expdp schemas=TEST include=TABLE:\""IN ('TESTTABLE')\"" directory=TEST_DIR dumpfile=test_include_testtable.dmp logfile=test_include_testtable.log

expdp schemas=TEST include=TABLE:\""IN ('TESTTABLE')\"" directory=TEST_DIR dumpfile=test_include_testtable.dmp logfile=test_include_testtable.log

expdp schemas=TEST exclude=TABLE:\""IN ('EXCLUDE_TESTTABLE_NAME')\"" directory=TEST_DIR dumpfile=test_exclude_testtable.dmp logfile=test_exclude_testtable.log

expdp schemas=TEST exclude=TABLE:\""IN ('EXCLUDE_TESTTABLE_NAME')\"" directory=TEST_DIR dumpfile=test_exclude_testtable.dmp logfile=test_exclude_testtable.log
6

Section 6

CONTENT and QUERY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_content.dmp logfile=TEST_content.log content=METADATA_ONLY

expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_content.dmp logfile=TEST_content.log content=METADATA_ONLY

expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_data_only.dmp logfile=TEST_data_only.log content=DATA_ONLY

expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_data_only.dmp logfile=TEST_data_only.log content=DATA_ONLY

expdp full=Y directory=TEST_DIR dumpfile=TEST_query.dmp logfile=TEST_query.log query='test.testtable:"WHERE id=0"'
7

Section 7

LOGTIME PARAMETER If you are looking to print the logtime then from 12c its possible. It will print the logtime of the object which are going to exported with datapump. Following are the parameter can be put with LOGTIME. LOGTIME=[NONE | STATUS | LOGFILE | ALL] 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.

Code/Command (click line numbers to comment):

1
expdp full=Y directory=TEST_DIR dumpfile=TEST_query.dmp logfile=TEST_query.log query='test.testtable:"WHERE id=0"'
8

Section 8

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. We can use the flashback parameter ( FLASHBACK_SCN or FLASHBACK_TIMESTAMP) in expdp to take a consistent backup of the table as of a specific point in time.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
expdp tables=C##TESTUSER.TESTTABLE directory=TEST_DIR dumpfile=C##TESTUSER_logfile.dmp logfile=C##TESTUSER_logfile.log LOGTIME=ALL

expdp tables=C##TESTUSER.TESTTABLE directory=TEST_DIR dumpfile=C##TESTUSER_logfile.dmp logfile=C##TESTUSER_logfile.log LOGTIME=ALL

SQL> SET numwidth 20
 SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL; 

GET_SYSTEM_CHANGE_NUMBER 
------------------------ 
                 2139371
9

Section 9

Now prepare PARFILE with FLASHBACK_SCN: ( Use the SCN value from step 1) Get Current SCN Value : Get SCN Value at particular time : Get timestamp Value from SCN :

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SET numwidth 20
 SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL; 

GET_SYSTEM_CHANGE_NUMBER 
------------------------ 
                 2139371

cat exp_flash.par

dumpfile=dbaclass.dmp logfile=dbaclass.log directory=EXPDIR tables=DBACLASS.OBJECT_LIST flashback_scn=2139371

expdp parfile=exp_flash.par

SQL> select current_scn from v$database;

SQL> select timestamp_to_scn('15-AUG-23 :22:00:10') from dual;

SQL> select scn_to_timestamp(<SCN_Number>) from dual;

SQL> select current_scn from v$database;
10

Section 10

Remap Multiple Tables : There are 2 ways to remap multiple tables : 1. Delimited by Commas : Every mapping set should be delimited by a comma. 2. Repeated REMAP_TABLE : Delimiting by commas may not feasible for hundreds of table remapping, so you may split them by the parameter REMAP_TABLE.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
SQL> select timestamp_to_scn('15-AUG-23 :22:00:10') from dual;

SQL> select scn_to_timestamp(<SCN_Number>) from dual;

remap_table=schema.table_name:new_table

[oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees remap_table=hr.employees:emp_bak dumpfile=full.dmp

[oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak,hr.jobs:job_bak dumpfile=test.dmp

[oracle@test ~]$ impdp system/password@orclpdb tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak remap_table=hr.jobs:job_bak dumpfile=full.dmp
11

Section 11

Remap tablespace: While importing tables , we can get error ORA-00959:tablespace doesnot exist, to solve this error or to prevent this error, we can create same tablespace in the target database or remap tablespace. The parameter we should use in data pump (impdp) is REMAP_TABLESPACE . Syntax : For example:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
[oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak,hr.jobs:job_bak dumpfile=test.dmp

[oracle@test ~]$ impdp system/password@orclpdb tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak remap_table=hr.jobs:job_bak dumpfile=full.dmp

REMAP_TABLESPACE=<SOURCE_TABLESPACE>:<TARGET_TABLESPACE>

[oracle@test ~]$ impdp system/oracle@pdbprim schemas=TESTUSER REMAP_TABLESPACE=TEST_TBS_01:TEST_TBS_02 dumpfile=test.dmp

REMAP_TABLESPACE=<SOURCE_TABLESPACE>:<TARGET_TABLESPACE>

[oracle@test ~]$ impdp system/oracle@pdbprim schemas=TESTUSER REMAP_TABLESPACE=TEST_TBS_01:TEST_TBS_02 dumpfile=test.dmp
12

Section 12

Code/Command (click line numbers to comment):

1
remap_schema=<op_source>:<op_target>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!