【实验】【Flashback】Flashback EXP功能实践

转载 2013年12月05日 16:32:25
Flashback EXP功能实现了导出某一个时间点或具体SCN点的数据,在备份和恢复某一个特定时间数据提供了可能。
这个功能得益于EXP工具提供的两个参数:FLASHBACK_SCN和FLASHBACK_TIME,下面分别使用这两个参数进行一下实践:

1.创建实验环境
sec@ora10g> set time on;
07:23:48 sec@ora10g> create table test_flashback_exp as select * from dba_objects where rownum<101;

Table created.

07:24:06 sec@ora10g> select count(*) from test_flashback_exp;

  COUNT(*)
----------
       100

07:24:25 sec@ora10g> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1248325

07:24:31 sec@ora10g> delete from test_flashback_exp where rownum<51;

50 rows deleted.

07:24:52 sec@ora10g> commit;

Commit complete.

07:24:54 sec@ora10g> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1248339

07:24:57 sec@ora10g> select count(*) from test_flashback_exp;

  COUNT(*)
----------
        50

07:26:01 sec@ora10g> delete from test_flashback_exp;

50 rows deleted.

07:26:11 sec@ora10g> commit;

Commit complete.

07:26:12 sec@ora10g> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1248386

07:27:57 sec@ora10g> select count(*) from test_flashback_exp;

  COUNT(*)
----------
         0

通过上述过程模拟了这样一个场景:
07:23:48(SCN:1248325)创建了测试用含有有100条记录的测试表test_flashback_exp
07:24:31删除其中的50条记录
07:24:54(SCN:1248339)查询该表中含有50条记录
07:26:01删除全部的数据
07:26:12(SCN:1248386)查询该表中含有0条数据

好,到此测试环境已经准备好了,让我们看看EXP基于时间点和SCN的导出:

2.FLASHBACK_SCN对三个时间点的功能演示
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248325

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:06:41 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table             TEST_FLASHBACK_EXP        100 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248339

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:07:02 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table             TEST_FLASHBACK_EXP         50 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248386

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:07:13 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table             TEST_FLASHBACK_EXP          0 rows exported
Export terminated successfully without warnings.

3.FLASHBACK_TIME对三个时间点的功能演示
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:24:54"'

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:16 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table             TEST_FLASHBACK_EXP        100 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:25:54"'

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:46 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table             TEST_FLASHBACK_EXP         50 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:26:54"'

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:57 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table             TEST_FLASHBACK_EXP          0 rows exported
Export terminated successfully without warnings.

4.到此,演示结束,总结一下
1). FLASHBACK_SCN参数
这个参数指定了一个exp导出的特定的SCN,导出的所有数据将保持这个SCN的一致性。默认情况下是none,表示不使用flashback query功能
语法如下:
exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248325
2). FLASHBACK_TIME参数
这个参数表示导出将基于"YYYY-MM-DD HH24:MI:SS"的一个时间戳,exp将找到最近的一个SCN来代替这个时间戳来进行导出。 默认是none,表示不使用flashback query功能
格式如下:
exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:24:54"'
一定要注意时间中引号的使用写成flashback_time='"2009-04-11 07:24:54"'和flashback_time="'2009-04-11 07:24:54'"都可以,但一定要同时有单引号和双引号。不然会报错滴~~

5.EXP帮助文档中
FLASHBACK_SCN参数和FLASHBACK_TIME参数的位置
ora10g@linux5 /exp$ exp -help

Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:57:06 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform. full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

-- The End --

http://blog.itpub.net/519536/viewspace-587038

闪回数据库实验总结-flashback database

闪回数据库概念: 10G新增功能,在启用flashback database功能后,数据库会定期将发生变化的数据块的 前镜像写入闪回日志的日志文件中, 在进行数据库闪回时,这些数据块可以直接复制回来...
  • q947817003
  • q947817003
  • 2013年09月11日 22:17
  • 2834

删除Flashback&nbsp;Log后数据库关闭???

开启了闪回功能,无意中删除了Flashback 日志后停机了? AlertLog: Errors in file d:\oracle\product\10.1.0\admin\myoracle\bd...
  • babymouse1212
  • babymouse1212
  • 2017年05月02日 10:43
  • 277

flashback table 闪回表到指定时间或SCN

闪回表特性: 闪回表是对闪回查询的增强。 可以在线操作, 恢复到指定时间点或SCN的任何数据 自动恢复相关属性,如索引,触发器,约束 满足分布式的一致性? 满足数据一致性,所有相关对象将自动一致。 闪...
  • q947817003
  • q947817003
  • 2013年09月11日 20:45
  • 3321

Oracle 六大闪回技术,flashback

Flashback 技术是以Undo segment中的内容为基础的, 因此受限于UNDO_RETENTON参数。 要使用flashback 的特性,必须启用自动撤销管理表空间。 在Oracle ...
  • demonson
  • demonson
  • 2014年10月14日 09:35
  • 1676

【Flashback】Flashback Database闪回数据库功能实践

Flashback Database闪回数据库功能极大的降低了由于用户错误导致的数据丢失的恢复成本。这是一种以空间换取缩短恢复时间的解决方案,这是值得的。 这里给出闪回数据库的使用方法,体验一下...
  • ldyzgao
  • ldyzgao
  • 2017年11月23日 20:45
  • 45

【Flashback】启用Flashback Database闪回数据库功能

若想顺利的使用闪回数据库功能,需要先将数据库置于闪回数据库状态。此文记录开启闪回数据库功能的步骤,注意调整过程需要重启数据库并确保数据库处于归档模式。1.确认数据库是否开启Flashback Data...
  • ldyzgao
  • ldyzgao
  • 2017年11月23日 20:50
  • 48

oracle flashback技术相关知识整理

oracle flashback是一个很强大的技术,它可以让你实现对一些已经commit的数据进行回滚。 一、ORACLE FLASHBACK 概述 oracle flashbac...
  • killvoon
  • killvoon
  • 2014年10月17日 10:42
  • 488

【Flashback】Flashback Drop闪回删除功能实践

Oracle的Flashback Drop闪回删除功能给出我们一种误DROP删除表的便捷恢复方式,实现这种功能的原理是Oracle的“回收站”(RecycleBin)功能。注意:如果被删除的表原先是存...
  • ldyzgao
  • ldyzgao
  • 2017年11月18日 19:38
  • 40

flashback drop 误删除的表后的恢复及对回收站中表的操作

1.误删除表,使用flashback drop功能找回 SQL> select * from tab; TNAME                          TABTYPE  CLUS...
  • q947817003
  • q947817003
  • 2013年09月11日 21:07
  • 1352

flashback的用法

oracle 自9i之后增加了flashback特性,本来想自己总结下关于这个知识点,但是看到http://blog.chinaunix.net/uid-74941-id-3180228.html,h...
  • dongjiqiu
  • dongjiqiu
  • 2013年08月08日 14:48
  • 381
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【实验】【Flashback】Flashback EXP功能实践
举报原因:
原因补充:

(最多只允许输入30个字)