- ANKUSH THAVALI
- 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.
CREATE OR REPLACE DIRECTORY test_dir AS '/data/backup';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Table backup
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
Schema Backup
expdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema.log
impdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema_imp.log
Full Database Backup
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
Include and Exclude
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
CONTENT and QUERY
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 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.
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.
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)
cat exp_flash.par dumpfile=dbaclass.dmp logfile=dbaclass.log directory=EXPDIR tables=DBACLASS.OBJECT_LIST flashback_scn=2139371
expdp parfile=exp_flash.par
Get Current SCN Value :
SQL> select current_scn from v$database;Get SCN Value at particular time :
SQL> select timestamp_to_scn('15-AUG-23 :22:00:10') from dual;Get timestamp Value from SCN :
SQL> select scn_to_timestamp(<SCN_Number>) from dual;
Remap table:
remap_table=schema.table_name:new_tableexample :
[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.
[oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak,hr.jobs:job_bak dumpfile=test.dmp2. Repeated REMAP_TABLE : Delimiting by commas may not feasible for hundreds of table remapping, so you may split them by the parameter REMAP_TABLE.
[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 :
REMAP_TABLESPACE=<SOURCE_TABLESPACE>:<TARGET_TABLESPACE>For example:
[oracle@test ~]$ impdp system/oracle@pdbprim schemas=TESTUSER REMAP_TABLESPACE=TEST_TBS_01:TEST_TBS_02 dumpfile=test.dmp
remap_schema=<op_source>:<op_target>