基于flashback_scn的expdp导出

在使用10g后的Oracle data pump导出数据时，我们可以使用flashback_scn参数指定导出的时间点，这时 oracle会使用flashback query查询导出scn时的数据，flashback query使用undo，无需打开flashback database功能。 也就是说，只要undo信息不被覆盖，即使数据库被重启，仍然可以进行基于flashback_scn的导出动作。 --以scott用户做测试 oracle@wang:/home/oracle$sqlplus scott/tiger; SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 15 07:43:24 2019 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user; USER is "SCOTT" SQL>  select current_scn from v$database; CURRENT_SCN -----------    21870773                           (记为1号时间点) SQL> create table t (num number); Table created. SQL>  insert into t values(1); 1 row created. SQL> commit; Commit complete. SQL> select current_scn from v$database; CURRENT_SCN -----------    21870796                            (记为2号时间点) SQL> insert into t values(2); 1 row created. SQL> commit; Commit complete. SQL> select current_scn from v$database; CURRENT_SCN -----------    21870805 SQL> conn / as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  835104768 bytes Fixed Size                  2257840 bytes Variable Size             549456976 bytes Database Buffers          281018368 bytes Redo Buffers                2371584 bytes Database mounted. Database opened. SQL> select current_scn from v$database; CURRENT_SCN -----------    21871307                      (记为3号时间点) SQL> conn scott/tiger; Connected. SQL> insert into t values(3); 1 row created. SQL> commit; Commit complete. SQL> select current_scn from v$database; CURRENT_SCN -----------    21871340                    (记为4号时间点) SQL> select * from t;        NUM ----------          1          2          3 --现在开始做expdp导出 (1号时间点)  expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t flashback_scn=21870773 (2号时间点)   expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t flashback_scn=21870796 (3号时间点)   expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t flashback_scn=21871307 (4号时间点)  expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t flashback_scn=21871340   oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t  flashback_scn=21870773 Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:52:18 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t flashback_scn=21870773  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."T"                                 4.984 KB        0 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/DBdb/dpdump/t1.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:52:28 2019 elapsed 0 00:00:08 oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t  flashback_scn=21870796 Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:52:34 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t flashback_scn=21870796  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."T"                                     5 KB       1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/DBdb/dpdump/t2.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:52:44 2019 elapsed 0 00:00:07 oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t  flashback_scn=21871307 Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:52:54 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t flashback_scn=21871307  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."T"                                 5.007 KB       2 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/DBdb/dpdump/t3.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:53:03 2019 elapsed 0 00:00:07 oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t  flashback_scn=21871340 Export: Release 11.2.0.4.0 - Production on Fri Mar 15 07:53:12 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t flashback_scn=21871340  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."T"                                 5.015 KB       3 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/DBdb/dpdump/t4.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:53:22 2019 elapsed 0 00:00:07 oracle@wang:/home/oracle$ --现在开始做impdp导出 (1号时间点)  drop table t purge;  impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp   (2号时间点)   drop table t purge;  impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp   (3号时间点)   drop table t purge;  impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp   (4号时间点)  drop table t purge;  impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp