Generate consistent backup through Oracle Datapump export

The original export utility when setting with consistent=y will create the export dump file of the database objects from the point in time to the beginning of the export session.

Conventional Export
==============

exp userid="/ as sysdba" owner= file=.dmp log=.log consistent=y

However we don’t have have consistent parameter for datapump export. By default datapump export is consistent, but not for the entire dump file.It is consistent for one table at a time and not for the entire export.

We can use FLASHBACK_SCN or FLASHBACK_TIME parameters to achieve the consistency of the backup. Find the below equivalent commands for consistent backups in both original export and datapump export.

Datapump Export
==============

vi expdp.par

schemas=
directory=
dumpfile= .dmp
logfile= .log
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"
job_name=

expdp userid="/ as sysdba" parfile=expdp.par
Advertisements