How to make a time consistent export dump using the expdp datapump utility

In those old days when there was the exp utility we made a time consistent export dump by using the consistent=y parameter.
But today, in fact a couple of years already, we mostly use the expdp datapump utility. How should we make a time consistent export using datapump?
For that we use the flashback_time or flashback_scn parameter. In this post I show you how to set the flashback_time parameter.

The flashback_time parameter needs as input the date-time in the “timestamp” format. If you want a time consistent exportdump of the present time, you should therefore set this parameter as follows:

flashback_time=systimestamp

If you want to use a parameter file, you should make a file with for example this content and give it for example the name scott.par:

schemas=scott
dumpfile=exp_scott.dmp
logfile=exp_scott.log
directory=DATA_PUMP_DIR
flashback_time=systimestamp
..
You then can execute the export using:

expdp system/password parfile=scott.par

If you want a time consistent export on another timestamp, let say september 3rd 2014 on 14:41:00 then you should set the flashback_time parameter as follows:

flashback_time=”to_timestamp(’03-09-2014 14:41:00′, ‘DD-MM-YYYY HH24:MI:SS’)”

From version 11.2 and higher it is also possible to use the so called legacy mode: you can use the parameters from the old exp utilities! You can use the consistent=y parameter again to make a time consistent export:

$ expdp schemas=scott consistent=y dumpfile=exp_scott.dmp logfile=exp_scott.log directory=DATA_PUMP_DIR

This is the output you will get:

Export: Release 11.2.0.4.0 – Production on Wed Sep 3 15:32:03 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Release 11.2.0.4.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “consistent=TRUE” Location: Command Line, Replaced with: “flashback_time=TO_TIMESTAMP(‘2014-09-03 15:32:03’, ‘YYYY-MM-DD HH24:MI:SS’)”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** schemas=RPCR_DEV flashback_time=TO_TIMESTAMP(‘2014-09-03 15:32:03’, ‘YYYY-MM-DD HH24:MI:SS’) dumpfile=rpcr_dev2.dmp logfile=rpcr_dev.log directory=EXP_DIR reuse_dumpfiles=true
Estimate in progress using BLOCKS method…
….

So you see that expdp is translating consistent=y to a flashback_time parameter.
As I said this works only in version 11.2 and higher.

THE HAPPY OLD DAYS ARE BACK AGAIN in version 11.2!! 🙂