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
Use of Datapump
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 |
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 |
Now prepare PARFILE with FLASHBACK_SCN: ( Use the SCN value from step 1)
| 1 2 3 | cat exp_flash.par |
Now use this PARFILE with expdp.
| 1 | expdp parfile=exp_flash.par |
Get Current SCN Value :
| 1 | SQL> select current_scn from v$database; |
Get SCN Value at particular time :
| 1 | SQL> select timestamp_to_scn('15-AUG-23 :22:00:10') from dual; |
Get timestamp Value from SCN :
| 1 | SQL> select scn_to_timestamp(<SCN_Number>) from dual; |
syntax :
| 1 | remap_table=schema.table_name:new_table |
example :
| 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 |
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.
| 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> |
For example:
| 1 | [oracle@test ~]$ impdp system/oracle@pdbprim schemas=TESTUSER REMAP_TABLESPACE=TEST_TBS_01:TEST_TBS_02 dumpfile=test.dmp |
Remap Schema: If schema names are different in source and target , you must remap schema names. add the following line in import command :
| 1 | remap_schema=<op_source>:<op_target> |
Your information will never be shared with any third party