在使用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/,如需转载,请注明出处,否则将追究法律责任。