【实验】【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】Flashback EXP功能实践

Flashback EXP功能实现了导出某一个时间点或具体SCN点的数据,在备份和恢复某一个特定时间数据提供了可能。 这个功能得益于EXP工具提供的两个参数:FLASHBACK_SCN和FLASHB...

Flashback TABLE 实验

sys用户 1、简单删除 SQL> flashbacktable scott.emp to before drop; 闪回完成。 SQL> selectindex_name from ...

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

在做flashback_scn expdp导出实验时,总是实验不成功,我就想到了一个问题,是不是flashback_scn 的导出导入是基于开了闪回恢复区的呢? 查阅资料发现: 在使用10g后的O...

【实验-视频过程】闪回数据库Flashback database

一、打开闪回数据库1、确保数据库处于归档模式,如果为非归档模式,将数据库转换成归档模式 SQL> select name,log_mode from v$database;NAME LOG_...

用Oracle闪回功能(flashback)恢复删除的数据---Oracle;闪回;回滚段;数据恢复

http://www.sosdb.com/jdul/dispbbs.asp?boardID=1&ID=268 人为的错误是数据库系统失败的重要原因之一,根据调查约40%的系统问题是操作失误或者用户错误...
  • flfna
  • flfna
  • 2011-03-08 14:14
  • 4409

Oracle Flashback 闪回查询功能操作范例(9i and 10g)

闪回查询 Oracle 从 9i开始提供了基于回滚段的闪回查询(Flashback Query)功能,可用于恢复错误的DML操作。在Oracle 10g中对闪回查询做了较大改进,不再局限...

用Oracle闪回功能(flashback)恢复删除的数据---Oracle;闪回;回滚段;数据恢复

http://www.sosdb.com/jdul/dispbbs.asp?boardID=1&ID=268 人为的错误是数据库系统失败的重要原因之一,根据调查约40%的系统问题是操作失误或者用户错误...
  • flfna
  • flfna
  • 2011-03-08 14:14
  • 384

Oracle Flashback 闪回查询功能操作范例(9i and 10g)

Oracle 从 9i开始提供了基于回滚段的闪回查询(Flashback Query)功能,可用于恢复错误的DML操作。在Oracle 10g中对闪回查询做了较大改进,不再局限于闪回查询,还可用于恢复...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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