Oracle DataPump 10g v 11g

With original Export utility running on a live database, a parameter CONSISTENT=Y is usually set so the objects in the backup is consistent to a single point in time. For Data Pump Export, that parameter does not exist.

According to 10g Oracle documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref181
A parameter comparable to CONSISTENT is not needed, use FLASHBACK_SCN and FLASHBACK_TIME for this functionality.

The default value of either FLASHBACK_SCN or FLASHBACK_TIME is none.

What you run a Data Pump export on a live database without setting the value of FLASHBACK_SCN or FLASHBACK_TIME, the no. of records in the parent table (parent1) is not same as number of records in child table (child1_of_parent1).

This means
a. The export dump file could capture data from different transactions, and
b. The dump file may be useless if the records in the dump file violated referential integrity constraint on the tables.

Use the following during the export:

FLASHBACK_TIME="TO_TIMESTAMP('08-05-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"

Conclusion is:

a. The behavior of Data Pump Export utility is different from original Export utility in terms of how the active transactions are treated.
b. The original Export utility, when setting consistent=y, will create export dump file of database objects from the point in time at the beginning of the Export session.
c. Without setting values for FLASHBACK_SCN or FLASHBACK_TIME, Data Pump Export utility may create an inconsistent export.
d. To ensure a consistent export with Data Pump export, either set the FLASHBACK_SCN or FLASHBACK_TIME parameter, or restart the database in restrict mode before the export session starts.

The parameter CONSISTENT=Y can be used with Oracle Datapump 11g. According to 11g Oracle documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm you can continue using FLASHBACK_SCN or FLASHBACK_TIME.

Advertisements

About mpoojari
Oracle Database/Applications Administrator

One Response to Oracle DataPump 10g v 11g

  1. Nice summary, I can’t see that consistent is listed as an option anywhere in the documentation. I think its been completely replaced by flashback. Thanks for the article

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: