Consistent export with expdp using flashback_scn (cont)

Q: Will flashback_scn help to avoid primary key conflict during export and import of data pump?

A: Yes.

DB: 11.2.0.3
OS:Redhat Linux 5.4

Here is my test case.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1800301

SQL> select seq_test.currval from dual;

CURRVAL
----------
102

SQL> begin
2 for i in 1..100 loop
3 insert into t1 values(seq_test.nextval);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select seq_test.currval from dual;

CURRVAL
----------
202

export data:

[oracle@odd tmp]$ expdp caoff/caoff directory=tmp dumpfile=caoff.dmp logfile=exp_caoff.log flashback_scn=1800301

Export: Release 11.2.0.3.0 - Production on Sat Jun 11 08:31:28 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "CAOFF"."SYS_EXPORT_SCHEMA_01": caoff/******** directory=tmp dumpfile=caoff.dmp logfile=exp_caoff.log flashback_scn=1800301
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "CAOFF"."T1" 5.695 KB 100 rows
Master table "CAOFF"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CAOFF.SYS_EXPORT_SCHEMA_01 is:
/tmp/caoff.dmp
Job "CAOFF"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:31:49

Import Data:

[oracle@odd tmp]$ impdp caoff/caoff directory=tmp dumpfile=caoff.dmp logfile=imp_caoff.log sqlfile=caoff.sql

Import: Release 11.2.0.3.0 - Production on Sat Jun 11 08:32:28 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CAOFF"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "CAOFF"."SYS_SQL_FILE_FULL_01": caoff/******** directory=tmp dumpfile=caoff.dmp logfile=imp_caoff.log sqlfile=caoff.sql
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Job "CAOFF"."SYS_SQL_FILE_FULL_01" successfully completed at 08:32:30

in caoff.sql, we can find the following:

Note that the sequence should start with 121 if expdp can keep the consistency.

CREATE SEQUENCE "CAOFF"."SEQ_TEST" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 221 CACHE 20 NOORDER NOCYCLE ;

Conclusion:

1. FLASHBACK_SCN does NOT reserve sequence consistency during export
2. Data pump exports SEQUENCE definitions first and TABLE later
3. With consistent export, since modified data after the specified SCN was not included, exported sequences would not lead to primary key conflicts after import.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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