- 07 Jun, 2019
- 0 Comments
- 4 Mins Read
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 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.
1 | CREATE OR REPLACE DIRECTORY test_dir AS '/data/backup'; |
1 | GRANT READ, WRITE ON DIRECTORY test_dir TO scott; |
Table backup
1 | expdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1.log |
1 | impdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1_imp.log |
Schema Backup
1 | expdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema.log |
1 | impdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema_imp.log |
Full Database Backup
1 | expdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_db.log |
1 | impdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_IMP_db.log |
Include and Exclude
1 | expdp schemas=TEST include=TABLE:\""IN ('TESTTABLE')\"" directory=TEST_DIR dumpfile=test_include_testtable.dmp logfile=test_include_testtable.log |
1 | expdp schemas=TEST exclude=TABLE:\""IN ('EXCLUDE_TESTTABLE_NAME')\"" directory=TEST_DIR dumpfile=test_exclude_testtable.dmp logfile=test_exclude_testtable.log |
CONTENT and QUERY
1 | expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_content.dmp logfile=TEST_content.log content=METADATA_ONLY |
1 | expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_data_only.dmp logfile=TEST_data_only.log content=DATA_ONLY |
1 | expdp full=Y directory=TEST_DIR dumpfile=TEST_query.dmp logfile=TEST_query.log query='test.testtable:"WHERE id=0"' |
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.
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.
1 | expdp tables=C##TESTUSER.TESTTABLE directory=TEST_DIR dumpfile=C##TESTUSER_logfile.dmp logfile=C##TESTUSER_logfile.log LOGTIME=ALL |
FLASHBACK Parameter In DATAPUMP(EXPDP)
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.
1 2 3 4 5 6 | SQL> SET numwidth 20 SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2139371 |
Now prepare PARFILE with FLASHBACK_SCN: ( Use the SCN value from step 1)
1 2 3 | cat exp_flash.pardumpfile=dbaclass.dmp logfile=dbaclass.log directory=EXPDIR tables=DBACLASS.OBJECT_LIST flashback_scn=2139371 |
1 | expdp parfile=exp_flash.par |
Get Current SCN Value :
1 | SQL> select current_scn from v$database; |
1 | SQL> select timestamp_to_scn('15-AUG-23 :22:00:10') from dual; |
1 | SQL> select scn_to_timestamp(<SCN_Number>) from dual; |
Remap table:
1 | remap_table=schema.table_name:new_table |
1 | [oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees remap_table=hr.employees:emp_bak dumpfile=full.dmp |
Remap Multiple Tables : There are 2 ways to remap multiple tables :
1. Delimited by Commas : Every mapping set should be delimited by a comma.
1 | [oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak,hr.jobs:job_bak dumpfile=test.dmp |
1 | [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:
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 :
1 | REMAP_TABLESPACE=<SOURCE_TABLESPACE>:<TARGET_TABLESPACE> |
1 | [oracle@test ~]$ impdp system/oracle@pdbprim schemas=TESTUSER REMAP_TABLESPACE=TEST_TBS_01:TEST_TBS_02 dumpfile=test.dmp |
1 | remap_schema=<op_source>:<op_target> |