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