Consistent backup with EXPDP in Oracle with flashback parameter

Consistent backup with EXPDP in Oracle with flashback parameter

Take consistent backup dump from EXPDP datapump utility by setting the flashback parameters during the backup.
Traditional EXP utility use CONSISTENT=Y for point in time backup or consistent backup.

By Default, EXPDP is consistent at table level, if you want whole backup remains consistent then we use the FLASHBACK_SCN or FLASHBACK_TIME parameter with EXPDP command.

Example of using the EXPDP with FLASHBACK TIME
Take a full hr schema backup:

expdp hr@xepdb1 directory=dbbackup dumpfile=hr.dmp logfile=hr.log flashback_time=systimestamp

If you want to specify the time then you can do as follows:
flashback_time="to_timestamp('09-01-2021 14:00:00', 'DD-MM-YYYY HH24:MI:SS')"

If you want to using command as script or bash shell then you can use \ sign for quotation as shown below:

expdp \"/@ as sysdba\" directory=dbbackup dumpfile=expdp_xepdb1.dmp logfile=expdp_xepdb1.log full=y
flashback_time=\"to_timestamp\(to_char\(SYSDATE-1/8640,\'DD-MON-YYYY HH24:MI:SS\'\), \'DD-MON-YYYY HH24:MI:SS\'\)\"

Example of using the EXPDP with FLASHBACK SCN
If you want to use SCN then you can change the time to scn
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
OR
You may check the current SCN:
SELECT current_scn FROM v$database;

Example of EXPD with SCN:

-- Check current SCN
SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
18411709


--Expdp backup
expdp hr@xepdb1 directory=dbbackup dumpfile=hr.dmp logfile=hr.log flashback_scn=18411709

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.