Implementing an Oracle dump during operational activity often produces an inconsistent dump that happens when a parent table gets exported before its child table. In the dump, the child table may contain 'orphan' records. Such a scenario leads to loosing foreign key constraints when a dump gets restored on a test system. Loosing a foreign key constraint introduces a loose consistency and may lead to a lost index because N4 automatically creates indexes for all foreign key constraints. This may affect performance.
To implement consistent data dumps, you can use the following parameters:
With the expdp command:
FLASHBACK_TIME
FLASHBACK_SCN
With the older exp command:
FLASHBACK_TIME
FLASHBACK_SCN
CONSISTENT
Navis recommends that you use the Data Pump Export utility with parameter FLASHBACK_TIME. You can use the following template:
expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=expdp.dmp logfile=expdp.log schemas=schema_name flashback_time=systimestamp
Alternatively, you can use parameter FLASHBACK_SCN, which uses SCN (System Change Number) to specify the Export session snapshot. You have to obtain this number prior to the dump itself. You can use it with both commands, expdp and exp.
Prerequisites
To avoid a "Snapshot too old" error during data export and for a consistent export to succeed, make sure a DBA properly configures the UNDO setting using either Automatic or Manual mode and the following parameters:
UNDO_RETENTION
UNDO_TABLESPACE
UNDO_MANAGEMENT
UNDO_RETENTION and UNDO datafile sizing should be sufficient to hold all data required for a consistent export for the duration of the export session.
For more information and recommendations, refer to Oracle documentation.
Notes
The FLASHBACK_TIME and FLASHBACK_SCN parameters are mutually exclusive.
Navis recommends not to use the CONSISTENT parameter approach because it has a number of limitations.
To avoid the "Snapshot too old" error, the UNDO_RETENTION value for consistent export should be the same or higher as the value currently used in the database.