基于flashback

共计 6643 个字符，预计需要花费 17 分钟才能阅读完成。

本篇内容介绍了“基于 flashback_scn 的 expdp 导出方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

在使用 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 

“基于 flashback_scn 的 expdp 导出方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！