ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于flashback_scn的expdp导出

基于flashback_scn的expdp导出

原创 Linux操作系统 作者:is.x 时间:2011-06-28 20:07:48 0 删除 编辑

在使用10g后的Oracle data pump导出数据时,我们可以使用flashback_scn参数指定导出的时间点,这时oracle会使用flashback query查询导出scn时的数据,flashback query使用undo,无需打开flashback database功能。也就是说,只要undo信息不被覆盖,即使数据库被重启,仍然可以进行基于flashback_scn的导出动作。

 

来做一个简单的实验:

 

oracle@ibmvs_a@/oracle $ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 21:34:55 2011

Copyright (c) 1982, 2006, 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

 

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

   11850458                     (记为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

-----------

   11850483                      (记为2号时间点)

 

SQL> insert into t values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

   11850489

 

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 1124073472 bytes

Fixed Size                  2078688 bytes

Variable Size             591398944 bytes

Database Buffers          524288000 bytes

Redo Buffers                6307840 bytes

Database mounted.

Database opened.

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

   11850721                        (记为3号时间点)

 

SQL> conn test/test

Connected.

SQL> insert into t values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

   11850893              (记为4号时间点)

 

好,现在开始做expdp导出。

 

oracle@ibmvs_a@/other/dumpdir $ export ORACLE_SID=HT

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850458         (1号时间点)

 

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:39:02

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 "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850458

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

ORA-31693: Table data object "TEST"."T" 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 "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 21:39:38

 

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850483    (2号时间点)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:40:19

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 "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850483

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 "TEST"."T"                                  4.906 KB       1 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 21:40:30

 

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850721    (3号时间点)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:41:09

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 "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850721

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 "TEST"."T"                                  4.914 KB       2 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 21:41:23

 

oracle@ibmvs_a@/other/dumpdir $ expdp test/test directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850893     (4号时间点)

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 27 June, 2011 21:41:47

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 "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=dumpdir dumpfile=t.dmp tables=t flashback_scn=11850893

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 "TEST"."T"                                  4.921 KB       3 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /other/dumpdir/t.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 21:42:01

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-700913/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    255524