Consistent export using expdp with flashback_scn

The exp utility used the CONSISTENT=Y parameter to indicate the export should be consistent to a point in time. By default the expdp utility exports are only consistent on a per table basis. If you want all tables in the export to be consistent to the same point in time, you need to use the FLASHBACK_SCN or FLASHBACK_TIME parameter.

sqlplus / as sysdba
SQL> create table hr.t1 as select * from dba_objects;

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
431668

SQL> create table hr.t2 as select * from dba_tables;

Table created.

SQL> grant select on hr.t2 to support;

Grant succeeded.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
431738

SQL> exit
[oracle@bcmsdbs01 tmp]$ expdp hr/hr directory=tmp dumpfile=hr_1.dmp logfile=exp_hr_1.log flashback_scn=431668

Export: Release 11.2.0.3.0 - Production on Tue Jan 19 09:38:24 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 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=tmp dumpfile=hr_1.dmp logfile=exp_hr_1.log flashback_scn=431668
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.375 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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 "HR"."T1" 1.237 MB 13824 rows
ORA-31693: Table data object "HR"."T2" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/tmp/hr_1.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 09:38:45

Note: DATA in t2 was not exported. However, the table definition and privilege WAS exported.

[oracle@bcmsdbs01 tmp]$ sqlplus / as sysdba

SQL> drop user hr cascade;

User dropped.

SQL> create user hr identified by hr;

User created.

SQL> grant connect, resource to hr;

Grant succeeded.

SQL> select * from dba_tab_privs where grantee='SUPPORT';

no rows selected

SQL> exit

Now we restore the data of hr schema.

[oracle@bcmsdbs01 tmp]$ impdp hr/hr directory=tmp dumpfile=hr_1.dmp logfile=exp_hr_meta.log content=metadata_only sqlfile=hr.sql

Import: Release 11.2.0.3.0 - Production on Tue Jan 19 09:39:38 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 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "HR"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_SQL_FILE_FULL_01": hr/******** directory=tmp dumpfile=hr_1.dmp logfile=exp_hr_meta.log content=metadata_only sqlfile=hr.sql
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "HR"."SYS_SQL_FILE_FULL_01" successfully completed at 09:39:41
[oracle@bcmsdbs01 tmp]$ grep -i grant /tmp/hr.sql
-- new object type path: SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT SELECT ON "HR"."T2" TO "SUPPORT";

We can see that the grant statement was included in the dump file and the data was not imported into database:

[oracle@bcmsdbs01 tmp]$ sqlplus hr/hr

SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist
SQL> desc t2
ERROR:
ORA-04043: object t2 does not exist

According to bug 8219226, FLASHBACK_SCN is not supported to metadata export. so exporting the table without the data is correct behavior with expdp. However, using FLASHBACK_SCN with exp does not export the ddl.

Reference:

http://neeraj-dba.blogspot.jp/2011/04/flashbackscn-and-flashbacktime.html
https://oracle-base.com/articles/10g/oracle-data-pump-10g
http://www.acehints.com/2011/07/data-pump-impdp-expdp-sqlfile-option-to.html

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