Consistent Export with Data Pump Utility (updated)

In oracle 10g, Data Pump utility is introduced to replace old Export/Import utility. Database Administrators and Backup Administrators who used to backup their production databases on regular basis using Export utility can use Data Pump utility instead. Oracle does not have plan to desupport the Import utility in the future releases (as of February 29, 2009, see metalink note 132904.1). But as of 11g, the original Export utility is desupported for general use:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/exp_imp.htm#i1023560

When running the original Export utility 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 happened if we run a Data Pump export on a live database without setting the value of FLASHBACK_SCN or FLASHBACK_TIME?


1. Set up the environment.

connect / as sysdba

create or replace directory dpump_dir as '/tmp';
grant read, write on directory dpump_dir to system;
drop user dpumpusr cascade;

create user dpumpusr identified by dpumpusr default tablespace users temporary tablespace temp;
grant connect, resource to dpumpusr;

connect dpumpusr/dpumpusr

create table test1
(
col11 number,
col12 date,
col13 varchar2(1000),
Constraint col11_pk primary key (col11)
)
partition by range (col11)
(
partition part101 values less than (5001),
partition part102 values less than (10001)
)
;

create table test2
(
Col21 number,
Col22 date,
Col23 varchar2(1000),
Col24 number,
Constraint col24_fk
foreign key (col24) References test1(col11)
on delete cascade
)
partition by range (col21)
(
partition part201 values less than (5001),
partition part202 values less than (10001)
)
;
I use “on delete cascade” here so when records in the table test1 are deleted, corresponding records in the child table test2 are deleted as well.

Now insert records into test1 and test2:
begin
for i in 1..10000 loop
insert into test1 values (i, sysdate, lpad (to_char(i), 100, '0'));
commit;
end loop;
end;
/
select count(*) from test1 partition(part101);

COUNT(*)
----------
5000

select count(*) from test1 partition(part102);

COUNT(*)
----------
5000


begin
for i in 1..10000 loop
insert into test2 values (i, sysdate, lpad (to_char(i), 100, '0'), i);
commit;
end loop;
end;
/

select count(*) from test2 partition(part201);

COUNT(*)
----------
5000

select count(*) from test2 partition(part202);

COUNT(*)
----------
5000

2. Perform a Data Pump Export on a database running active transactions without setting FLASHBACK_SCN or FLASHBACK_TIME.

a. expdp system/password@rmdb1 dumpfile=dpump_dir:dpumpusr.dmp logfile=dpump_dir:dpumpusr.log schemas=dpumpusr job_name=expdpumpusr

b. During the Data Pump export, start a Sql*Plus session as dpumpusr user and delete some records. Each transaction in following PL/SQL block consists of deleting one record from both tables:
begin
for i in 1..10000 loop
delete from test1 where col11=i;
commit;
end loop;
end;
/
Here is the result of Data Pump export:
Export: Release 10.2.0.3.0 - 64bit Production on Friday, 27 March, 2009 8:40:38

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."EXPDPUMPUSR": system/********@rmdb1 dumpfile=dpump_dir:dpump usr.dmp logfile=dpump_dir:dpumpusr.log schemas=dpumpusr job_name=expdpumpusr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.671 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "DPUMPUSR"."TEST2":"PART201" 290.5 KB 2410 rows
. . exported "DPUMPUSR"."TEST2":"PART202" 596.5 KB 5000 rows
. . exported "DPUMPUSR"."TEST1":"PART101" 274.9 KB 2358 rows
. . exported "DPUMPUSR"."TEST1":"PART102" 576.7 KB 5000 rows
Master table "SYSTEM"."EXPDPUMPUSR" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.EXPDPUMPUSR is:
/tmp/dpumpusr.dmp
Job "SYSTEM"."EXPDPUMPUSR" successfully completed at 08:41:09
Number of records in the parent table (test1) is not same as number of records in child table (test2). This test proves two points:
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.

3. Perform another Data Pump export. This time set FLASHBACK_TIME to the beginning of export session:
Create a parameter file, /tmp/dpump.par with following entries:

dumpfile=dpump_dir:dpumpusr.dmp
logfile=dpump_dir:dpumpusr.log
schemas=dpumpusr
flashback_time="TO_TIMESTAMP('27-03-2009 10:02:00', 'DD-MM-YYYY HH24:MI:SS')"
job_name=expdpumpusr
During the export, run the PL/SQL block to delete records.
Here is the output of the export log:
>expdp system/password@rmdb1 parfile=/tmp/dpump.par

Export: Release 10.2.0.3.0 - 64bit Production on Friday, 27 March, 2009 10:03:30

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."EXPDPUMPUSR": system/********@rmdb1 parfile=/tmp/dpump.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.671 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "DPUMPUSR"."TEST2":"PART201" 596.3 KB 5000 rows
. . exported "DPUMPUSR"."TEST2":"PART202" 596.5 KB 5000 rows
. . exported "DPUMPUSR"."TEST1":"PART101" 576.6 KB 5000 rows
. . exported "DPUMPUSR"."TEST1":"PART102" 576.7 KB 5000 rows
Master table "SYSTEM"."EXPDPUMPUSR" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.EXPDPUMPUSR is:
/tmp/dpumpusr.dmp
Job "SYSTEM"."EXPDPUMPUSR" successfully completed at 10:03:58
All records that exist in the database at the beginning of export are included in the export dump file. The export is consistent.

4. For comparison, let’s do an export with original Export utility using consistent=y:

a. Drop and recreate the same partitioned tables.
b. Run the export.
c. Run the same PL/SQL block while the export session is running.

Here is the result:
>exp system/password@rmdb1 file=/tmp/dpumpusr_export.dmp log=/tmp/dpumpusr_export.log owner=dpumpusr consistent=y

Export: Release 10.2.0.3.0 - Production on Fri Mar 27 09:19:03 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DPUMPUSR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DPUMPUSR
About to export DPUMPUSR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DPUMPUSR's tables via Conventional Path ...
. . exporting table TEST1
. . exporting partition PART101 5000 rows exported
. . exporting partition PART102 5000 rows exported
. . exporting table TEST2
. . exporting partition PART201 5000 rows exported
. . exporting partition PART202 5000 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
All the rows that existed before the delete command were exported. The objects in the export file were consistent to the point at the beginning of the export session.


5. Now repeat the steps above. This time set consistent=n.

Here is the result:
exp system/password@rmdb1 file=/tmp/dpumpusr_export2.dmp log=/tmp/dpumpusr_export2.log owner=dpumpusr consistent=n

Export: Release 10.2.0.3.0 - Production on Fri Mar 27 09:25:37 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DPUMPUSR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DPUMPUSR
About to export DPUMPUSR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DPUMPUSR's tables via Conventional Path ...
. . exporting table TEST1
. . exporting partition PART101 3997 rows exported
. . exporting partition PART102 5000 rows exported
. . exporting table TEST2
. . exporting partition PART201 3957 rows exported
. . exporting partition PART202 5000 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Number of records in table test1 is not same as in table test2. The export is not consistent.

6. Clean up:

Drop the tables and user dpumpusr.

SQL> drop table dpumpusr.test2 purge;

Table dropped.

SQL> drop table dpumpusr.test1 purge;

Table dropped.

SQL> drop user dpumpusr cascade;

User dropped.
7. Conclusions:

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 insure 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.
(I wish to thank friends and online readers who provided valuable inputs after the last version of this blog was published.)