基于flashback_scn的expdp导出实验想到的问题

在做flashback_scn expdp导出实验时，总是实验不成功，我就想到了一个问题，是不是flashback_scn 的导出导入是基于开了闪回恢复区的呢？

（本实验是基于oracle database 11gR2做的实验）

查阅资料发现：

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

最后发现 expdp导出实验失败是由于 创建的 directory 并没有成功赋予用户权限：

重新创建directory ，赋予权限，实验成功：

实验如下:

（1）查询当前的scan

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1276440


向highgo3用户下的创建表，使得scan号发生改变
SQL> connect highgo3/oracle
Connected.
SQL> create table highgo_t (num number);

Table created.

SQL> insert into highgo_t values(1);        

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;    

CURRENT_SCN
-----------
    1276522               ----第一scan点

SQL> create table highgo_h (num number);    

Table created.

SQL> insert into highgo_h values(2);

1 row created.

SQL> commit;

Commit complete.


SQL> select current_scn from v$database;   

CURRENT_SCN
-----------
    1276642                    ----第二scan点


开始做基于flashback_scn的expdp导出实验
$ mkdir ~/scan_expdp
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 17 01:40:49 2015

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>  create or replace directory scanexpxp_dr as '/home/oracle/scan_expdp';

Directory created.
SQL> grant read,write on directory scanexpxp_dr to highgo1,highgo2,highgo3;

Grant succeeded.

     

在第一scan点导出
$ expdp highgo3/oracle directory=scanexpxp_dr dumpfile=01flasnback_scan_expdp.dmp tables=highgo_t flashback_scn=1276522 logfile=01flasnbackscan_expdp.log

Export: Release 11.2.0.4.0 - Production on Thu Dec 17 01:45:59 2015

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 "HIGHGO3"."SYS_EXPORT_TABLE_01":  highgo3/******** directory=scanexpxp_dr dumpfile=01flasnback_scan_expdp.dmp tables=highgo_t flashback_scn=1276522 logfile=01flasnbackscan_expdp.log 
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 "HIGHGO3"."HIGHGO_T"                        5.007 KB       1 rows
Master table "HIGHGO3"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HIGHGO3.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scan_expdp/01flasnback_scan_expdp.dmp
Job "HIGHGO3"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 17 01:46:09 2015 elapsed 0 00:00:09

在第二scan点导出

$ expdp highgo3/oracle directory=scanexpxp_dr dumpfile=02flasnback_scan_expdp.dmp tables=highgo_h flashback_scn=1276642 logfile=02flasnbackscan_expdp.log

Export: Release 11.2.0.4.0 - Production on Thu Dec 17 01:50:48 2015

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 "HIGHGO3"."SYS_EXPORT_TABLE_01":  highgo3/******** directory=scanexpxp_dr dumpfile=02flasnback_scan_expdp.dmp tables=highgo_h flashback_scn=1276642 logfile=02flasnbackscan_expdp.log 
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 "HIGHGO3"."HIGHGO_H"                        5.007 KB       1 rows
Master table "HIGHGO3"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HIGHGO3.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scan_expdp/02flasnback_scan_expdp.dmp
Job "HIGHGO3"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 17 01:50:54 2015 elapsed 0 00:00:05

实验成功