【Flashback】Flashback EXP功能实践

2024-01-29 19:18
文章标签 实践 功能 flashback exp

本文主要是介绍【Flashback】Flashback EXP功能实践,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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功能实践的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/657921

相关文章

Spring WebFlux 与 WebClient 使用指南及最佳实践

《SpringWebFlux与WebClient使用指南及最佳实践》WebClient是SpringWebFlux模块提供的非阻塞、响应式HTTP客户端,基于ProjectReactor实现,... 目录Spring WebFlux 与 WebClient 使用指南1. WebClient 概述2. 核心依

MyBatis-Plus 中 nested() 与 and() 方法详解(最佳实践场景)

《MyBatis-Plus中nested()与and()方法详解(最佳实践场景)》在MyBatis-Plus的条件构造器中,nested()和and()都是用于构建复杂查询条件的关键方法,但... 目录MyBATis-Plus 中nested()与and()方法详解一、核心区别对比二、方法详解1.and()

Spring Boot @RestControllerAdvice全局异常处理最佳实践

《SpringBoot@RestControllerAdvice全局异常处理最佳实践》本文详解SpringBoot中通过@RestControllerAdvice实现全局异常处理,强调代码复用、统... 目录前言一、为什么要使用全局异常处理?二、核心注解解析1. @RestControllerAdvice2

Spring事务传播机制最佳实践

《Spring事务传播机制最佳实践》Spring的事务传播机制为我们提供了优雅的解决方案,本文将带您深入理解这一机制,掌握不同场景下的最佳实践,感兴趣的朋友一起看看吧... 目录1. 什么是事务传播行为2. Spring支持的七种事务传播行为2.1 REQUIRED(默认)2.2 SUPPORTS2

Java中的雪花算法Snowflake解析与实践技巧

《Java中的雪花算法Snowflake解析与实践技巧》本文解析了雪花算法的原理、Java实现及生产实践,涵盖ID结构、位运算技巧、时钟回拨处理、WorkerId分配等关键点,并探讨了百度UidGen... 目录一、雪花算法核心原理1.1 算法起源1.2 ID结构详解1.3 核心特性二、Java实现解析2.

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE

MySQL 中 ROW_NUMBER() 函数最佳实践

《MySQL中ROW_NUMBER()函数最佳实践》MySQL中ROW_NUMBER()函数,作为窗口函数为每行分配唯一连续序号,区别于RANK()和DENSE_RANK(),特别适合分页、去重... 目录mysql 中 ROW_NUMBER() 函数详解一、基础语法二、核心特点三、典型应用场景1. 数据分

Golang如何用gorm实现分页的功能

《Golang如何用gorm实现分页的功能》:本文主要介绍Golang如何用gorm实现分页的功能方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录背景go库下载初始化数据【1】建表【2】插入数据【3】查看数据4、代码示例【1】gorm结构体定义【2】分页结构体

深度解析Spring AOP @Aspect 原理、实战与最佳实践教程

《深度解析SpringAOP@Aspect原理、实战与最佳实践教程》文章系统讲解了SpringAOP核心概念、实现方式及原理,涵盖横切关注点分离、代理机制(JDK/CGLIB)、切入点类型、性能... 目录1. @ASPect 核心概念1.1 AOP 编程范式1.2 @Aspect 关键特性2. 完整代码实

Java Web实现类似Excel表格锁定功能实战教程

《JavaWeb实现类似Excel表格锁定功能实战教程》本文将详细介绍通过创建特定div元素并利用CSS布局和JavaScript事件监听来实现类似Excel的锁定行和列效果的方法,感兴趣的朋友跟随... 目录1. 模拟Excel表格锁定功能2. 创建3个div元素实现表格锁定2.1 div元素布局设计2.