【Flashback】Flashback EXP功能实践

最新推荐文章于 2024-02-23 10:40:26 发布

sslflying

最新推荐文章于 2024-02-23 10:40:26 发布

阅读量514

点赞数

分类专栏： oracle 备份恢复文章标签： flashback

oracle 备份恢复专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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