【Flashback】Flashback EXP功能实践
来源：互联网 发布：mac怎么进入苹果系统 编辑：程序博客网 时间：2024/06/07 01:17
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.
这个功能得益于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
0 0
- 【Flashback】Flashback EXP功能实践
- 【实验】【Flashback】Flashback EXP功能实践
- 【Flashback】Flashback Drop闪回删除功能实践
- 【Flashback】Flashback Database闪回数据库功能实践
- flashback
- Flashback
- flashback
- flashback
- Flashback
- flashback
- flashback
- flashback
- flashback
- Flashback
- flashback
- flashback
- Flashback
- flashback
- 多舒服撒打开了非就
- POJ 2386
- Nginx学习之十-超时管理（定时器事件）
- ASP.NET MVC之验证终结者篇
- HOJ 2634 网络流 【蕴含式最大获利问题】
- 【Flashback】Flashback EXP功能实践
- 1818挂Q网—科技集团：www.1818keji.tk
- [乐意黎原创] 怪异的php 语法, 求解！
- 地图添加与获取
- 12 个球一个天平,现知道只有一个和其它的重量不同,问怎样称才能用三次就找到那个
- 传递引用类型参数(ref)
- 如何编写更棒的代码：11个核心要点
- matlab_图片，视频，摄像头的读取和显示
- 郑州金水中医院收费高不高
