Navneet_Oracledba
DataPump
(EXPDP/IMPDP)
In Oracle Database
DataPump(EXPDP/IMPDP)
EXPDP/IMPDP is the utility in oracle database by which we can
take and restore the backup of tables,tablespace,schemas,Full
database.
To take the backup of tables
Command:
expdp tables=u1.t1 directory=test_dir dumpfile=table.dmp
logfile=table.log
here, u1.t1= the table t1 is created by user u1
directory= test_dir means we need to create a directory, I am
creating the directory /data/backup and pointing this directory to
test_dir
dumpfile= the backup file name
logfile= the log file name
And then taking the table backup
We can check the backup file and log file in the backup location
Now, to understand how to restore the backup (IMPDP), we are
deleting the table which is created by the user u1
Run the command
Command:
impdp tables=u1.t1 directory=test_dir dumpfile=table.dmp
logfile=table_imp.log
here we are changing only logfile name.
Schema level backup:
If we take the schema level backup that means Everything in the
schema will be backed up.
Ex: I am creating the schema u1 and create two table inside to that
schema u1.t1,u1.t2
Now, if we take the u1 schema backup, the the u1.t1,u1.t2 will also
be backup.
Command:
expdp schemas=u1 directory=test_dir dumpfile=schema.dmp
logfile=schema.log
Schema level restore backup:
Now we drop the schema u1 by which all the data present in the
u1 schema also be deleted.
And now to restore the schema backup, we run the impdp command
Command:
impdp schemas=u1 directory=test_dir dumpfile=schema.dmp
logfile=schema_1.log
When we take a backup of a table, all the data in it and the
metadata of that data are backed up.
We can take a backup of only data or metadata of the table
(structure of the table).
Sceniro1
We want to take a backup of metadata only (structure of the table).
Command:
expdp tables=u1.t1 directory=test_dir dumpfile=u1_metadata.dmp
logfile=u1_metadata.log content=metadata_only
Sceniro2
We want to take a backup of data only.
Command:
expdp tables=u1.t1 directory=test_dir dumpfile=u1_data.dmp
logfile=u1_data.log content=data_only
Include & exclude
let’s assume u1 is my schema and there are tables t1,t2,t3,t4 in the
schema if I took export backup of schema then both table and schema
will be backed up but if we want to take backup of some tables (only
t1 & t2) along with schema then we can use include parameter.
Include:
I want to take schema backup u1 along with t1 table only
Command:
expdp schemas=u1 directory=test_dir dumpfile=u1_include.dmp
logfile=u1_include.log include=TABLE:\""IN ('T1')\""
I want to take schema backup u1 along with t1 & t2 table only
Exclude:
I want to take the backup of schema which has t1,t2,t3 & t4 tables
but we will not take the backup of t3 and t4 along with schema that
means exclude the t1 & t2 tables.
Command:
expdp schemas=u1 directory=test_dir dumpfile=u1_exclude_t1_t2.dmp
logfile=u1_exclude__t1_t2.log exclude=TABLE:\""IN ('T1','T2')\""
Flashback Parameter in Datapump(EXPDP)
We use the flashback parameter (FLASHBACK_SCN or
FLASHBACK_TIMESTAMP) in expdp to take a consistent backup of a
table as of a specific point in time.
The SCN no continues to increase with Timestamp.
We can change SCN no to time stamp and vice versa.
To check the current scn no:
Command:
select current_scn from v$database;
Get timestamp Value from SCN:
select scn_to_timestamp(<SCN_Number>) from dual;
select scn_to_timestamp(2260515) from dual;
Get SCN from timestamp value:
select timestamp_to_scn(‘TIMESTAMP') from dual;
select timestamp_to_scn(' 01-APR-25 10.15.43.000000000 PM')
from dual;
Let’s assume we have a table and 12 records are there at 1am,
after those 24 records at 2 am (it’s a current time).
We want to take the backup of 1am table data, that means only
12 records
If we take the backup from SCN NO= 2261507 the 24 records have been
backup and if we take the backup from SCN NO=2261483 then 12 records
have been backup.
We need to create par file with FLASHBACK_SCN
I am creating exp_flash.par and enter the command for expdp with
flashback parameter to backup 12 records
tables=u1.t1 directory=test_dir dumpfile=scn.dmp logfile=scn.log
flashback_scn=2261483 and save the par file (exp_flash.par)
Run the par file
expdp parfile=exp_flash.par
Hence, here you can see 12 records has been backup.
