小编给大家分享一下基于Oracle闪回的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

Oracle 9i 开始支持闪回，Oracle10g开始全面支持闪回功能，Oracle11g有所完善，为大家快速的恢复数据，查询历史数据提供了很大的便捷方法。

本文主要对Oracle常用闪回使用做些详细介绍，其中对于不常用的事务和版本闪回，这里就不做介绍

一、Oracle闪回概述

闪回级别闪回场景闪回技术对象依赖　　影响数据数据库表截断、逻辑错误、其他多表意外事件闪回DATABASE闪回日志、undo是DROP删除表闪回DROP回收站(recyclebin)是表更新、删除、插入记录闪回TABLE还原数据,undo是查询当前数据和历史数据对比闪回QUERY还原数据,undo否版本查询比较行版本闪回Version Query还原数据,undo否事务查询比较闪回Transaction Query还原数据,undo否归档DDL、DML闪回Archive归档日志是

二、Oracle闪回使用详解

1、闪回开启

(1)开启闪回必要条件

a.开启归档日志

SQL>archiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/home/U01/app/oracle/oradata/testdb/archOldestonlinelogsequence844Nextlogsequencetoarchive846Currentlogsequence846##如未开启，在mount状态执行alterdatabasearchivelog;

b.设置合理的闪回区

db_recovery_file_dest：指定闪回恢复区的位置

db_recovery_file_dest_size：指定闪回恢复区的可用空间大小

db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟(1天),实际取决于闪回区大小

(2)检查是否开启闪回

SQL>selectflashback_onfromv$database;FLASHBACK_ON------------------NO

(3)开启闪回

a.开启归档

mount状态：alterdatabasearchivelog;

b.设置闪回区

SQL>altersystemsetdb_recovery_file_dest='/home/U01/app/oracle/fast_recovery_area'scope=both;Systemaltered.SQL>altersystemsetdb_recovery_file_dest_size=60Gscope=both;Systemaltered.SQL>altersystemsetdb_flashback_retention_target=4320scope=both;Systemaltered.

c.开启flashback (10g在mount开启)

SQL>alterdatabaseflashbackon;Databasealtered.

(4)确定闪回开启

SQL>selectflashback_onfromv$database;FLASHBACK_ON------------------YES

(5)关闭闪回

SQL>alterdatabaseflashbackoff;Databasealtered.

2、闪回使用

(1)闪回查询

闪回查询主要是根据Undo表空间数据进行多版本查询，针对v$和x$动态性能视图无效，但对DBA_、ALL_、USER_是有效的

a.闪回查询

允许用户查询过去某个时间点的数据，用以重构由于意外删除或更改的数据，数据不会变化。

SQL>select*fromscott.dept;DEPTNODNAMELOC-------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOOPERATIONSBOSTONSQL>deletefromscott.deptwheredeptno=40;rowdeleted.SQL>commit;Commitcomplete.SQL>select*fromscott.deptasoftimestampsysdate-10/1440;DEPTNODNAMELOC-------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOOPERATIONSBOSTONSQL>select*fromscott.deptasoftimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh34:mi:ss');DEPTNODNAMELOC-------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOOPERATIONSBOSTONSQL>select*fromscott.deptasofscn16801523;DEPTNODNAMELOC-------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOOPERATIONSBOSTON

b.闪回版本查询

用于查询行级数据库随时间变化的方法

c.闪回事务查询

用于提供查看事务级别数据库变化的方法

(2)闪回表(update/insert/delete)

闪回表就是对表的数据做回退，回退到之前的某个时间点，其利用的是undo的历史数据，与undo_retention设置有关，默认是14400分钟（1天）

同样，sys用户表空间不支持闪回表,要想表闪回，需要允许表启动行迁移(row movement)

闪回表示例：

SQL>flashbacktablescott.depttotimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh34:mi:ss');flashbacktablescott.depttotimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh34:mi:ss')ERRORatline1:ORA-08189:cannotflashbackthetablebecauserowmovementisnotenabledSQL>selectrow_movementfromdba_tableswheretable_name='DEPT'andowner='SCOTT';ROW_MOVE--------DISABLEDSQL>altertablescott.deptenablerowmovement;Tablealtered.SQL>flashbacktablescott.depttotimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh34:mi:ss');Flashbackcomplete.SQL>select*fromscott.dept;DEPTNODNAMELOC-------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOOPERATIONSBOSTONSQL>altertablescott.deptdisablerowmovement;Tablealtered.

(3)闪回DROP(drop table)

当一个表被drop掉，表会被放入recyclebin回收站，可通过回收站做表的闪回。表上的索引、约束等同样会被恢复

不支持sys/system用户表空间对象，可通过alter system set recyclebin=off;关闭回收站功能

闪回DROP示例：

SQL>select*fromt;IDNAME-------------------------------------------------24SQL>droptablet;Tabledropped.SQL>showrecyclebin;ORIGINALNAMERECYCLEBINNAMEOBJECTTYPEDROPTIME-----------------------------------------------------------------------------TBIN$YEh3QcvZdJLgUxyAgQpnVQ==$0TABLE2017-12-14:15:02:06SQL>flashbacktablettobeforedrop;Flashbackcomplete.SQL>select*fromt;IDNAME-----------------------------------------------24

备注：即使不开始flashback，只要开启了recyclebin，那么就可以闪回DROP表。

但如果连续覆盖，就需要指定恢复的表名，如果已经存在表，则需要恢复重命名。

SQL>showrecyclebin;ORIGINALNAMERECYCLEBINNAMEOBJECTTYPEDROPTIME-----------------------------------------------------------------------------TBIN$YEh3QcvddJLgUxyAgQpnVQ==$0TABLE2017-12-14:15:07:54TBIN$YEh3QcvcdJLgUxyAgQpnVQ==$0TABLE2017-12-14:15:07:27SQL>flashbacktable"BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0"tobeforedrop;Flashbackcomplete.SQL>showrecyclebin;ORIGINALNAMERECYCLEBINNAMEOBJECTTYPEDROPTIME-----------------------------------------------------------------------------TBIN$YEh3QcvddJLgUxyAgQpnVQ==$0TABLE2017-12-14:15:07:54SQL>flashbacktablettobeforedroprenametott;Flashbackcomplete.

(4)闪回数据库(truncate/多表数据变更)

数据库闪回必须在mounted状态下进行，基于快照的可以再open下进行闪回库

闪回数据库主要是将数据库还原值过去的某个时间点或SCN，用于数据库出现逻辑错误时，需要open database resetlogs

a.全库闪回

数据库闪回示例

SQL>select*fromscott.EMP;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------------------------------SMITHCLERK79021980-12-1700:00:0080020ALLENSALESMAN76981981-02-2000:00:00160030030WARDSALESMAN76981981-02-2200:00:00125050030JONESMANAGER78391981-04-0200:00:00297520MARTINSALESMAN76981981-09-2800:00:001250140030BLAKEMANAGER78391981-05-0100:00:00285030CLARKMANAGER78391981-06-0900:00:00245010SCOTTANALYST75661987-04-1900:00:00300020KINGPRESIDENT1981-11-1700:00:00500010TURNERSALESMAN76981981-09-0800:00:001500030ADAMSCLERK77881987-05-2300:00:00110020JAMESCLERK76981981-12-0300:00:0095030FORDANALYST75661981-12-0300:00:00300020MILLERCLERK77821982-01-2300:00:00130010rowsselected.SQL>truncatetablescott.EMP;Tabletruncated.SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupmount;ORACLEinstancestarted.TotalSystemGlobalArea9.4067E+10bytesFixedSize2263936bytesVariableSize9395242112bytesDatabaseBuffers8.4557E+10bytesRedoBuffers112766976bytesDatabasemounted.SQL>flashbackdatabasetotimestampto_timestamp('2017-12-1414:12:46','yyyy-mm-ddHH24:MI:SS');Flashbackcomplete.SQL>alterdatabaseopenresetlogs;Databasealtered.SQL>select*fromscott.emp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------------------------------SMITHCLERK79021980-12-1700:00:0080020ALLENSALESMAN76981981-02-2000:00:00160030030WARDSALESMAN76981981-02-2200:00:00125050030JONESMANAGER78391981-04-0200:00:00297520MARTINSALESMAN76981981-09-2800:00:001250140030BLAKEMANAGER78391981-05-0100:00:00285030CLARKMANAGER78391981-06-0900:00:00245010SCOTTANALYST75661987-04-1900:00:00300020KINGPRESIDENT1981-11-1700:00:00500010TURNERSALESMAN76981981-09-0800:00:001500030ADAMSCLERK77881987-05-2300:00:00110020JAMESCLERK76981981-12-0300:00:0095030FORDANALYST75661981-12-0300:00:00300020MILLERCLERK77821982-01-2300:00:00130010rowsselected.

b.快照闪回

针对主库和备库都可以创建闪回快照点，然后恢复到指定的快照点，但主库一旦恢复到快照点，备库的同步则需要重新同步

SQL>select*fromscott.dept;DEPTNODNAMELOCADDR-------------------------------------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOSQL>createrestorepointbefore_201712151111guaranteeflashbackdatabase;Restorepointcreated.SQL>createtablescott.tasselect*fromscott.dept;Tablecreated.SQL>truncatetablescott.t;Tabletruncated.SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupmount;ORACLEinstancestarted.TotalSystemGlobalArea9.4067E+10bytesFixedSize2263936bytesVariableSize9663677568bytesDatabaseBuffers8.4289E+10bytesRedoBuffers112766976bytesDatabasemounted.SQL>flashbackdatabasetorestorepointbefore_201712151111;Flashbackcomplete.SQL>alterdatabaseopenresetlogs;Databasealtered.此时主库scott.t已不存在：SQL>select*fromscott.t;select*fromscott.t*ERRORatline1:ORA-00942:tableorviewdoesnotexist此时从库的scott.依旧存在，主备同步终止解决方案：在主库创建快照时间点，从库自动停止应用日志，等主库闪回后，重新应用日志即可。如果已经做了上述操作，从库可以选择重建ALTERDATABASEREGISTERLOGFILE'/xx/xx/archive.dbf';

c.闪回snapshot standby

此功能在11GR2非常实用，可自动创建闪回点、开启闪回日志，可完成线上数据测试后，然后做数据库闪回恢复主备关系

selectscn,STORAGE_SIZE,to_char(time,'yyyy-mm-ddhh34:mi:ss')time,NAMEfromv$restore_point;selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;SQL>setline200;SQL>setpagesize2000;SQL>selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;DATABASE_ROLEOPEN_MODEDB_UNIQUE_NAMEFLASHBACK_ON------------------------------------------------------------------------------------PHYSICALSTANDBYREADONLYtestdbmsNOSQL>ALTERDATABASECONVERTTOSNAPSHOTSTANDBY;Databasealtered.SQL>selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;DATABASE_ROLEOPEN_MODEDB_UNIQUE_NAMEFLASHBACK_ON------------------------------------------------------------------------------------SNAPSHOTSTANDBYMOUNTEDtestdbmsRESTOREPOINTONLYSQL>alterdatabaseopen;Databasealtered.SQL>selectopen_modefromv$database;OPEN_MODE--------------------READWRITE此时备库操作：SQL>select*fromscott.emp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------------------------------SMITHCLERK79021980-12-1700:00:0080020ALLENSALESMAN76981981-02-2000:00:00160030030WARDSALESMAN76981981-02-2200:00:00125050030JONESMANAGER78391981-04-0200:00:00297520MARTINSALESMAN76981981-09-2800:00:001250140030BLAKEMANAGER78391981-05-0100:00:00285030CLARKMANAGER78391981-06-0900:00:00245010SCOTTANALYST75661987-04-1900:00:00300020KINGPRESIDENT1981-11-1700:00:00500010TURNERSALESMAN76981981-09-0800:00:001500030ADAMSCLERK77881987-05-2300:00:00110020JAMESCLERK76981981-12-0300:00:0095030FORDANALYST75661981-12-0300:00:00300020MILLERCLERK77821982-01-2300:00:00130010rowsselected.SQL>truncatetablescott.emp;Tabletruncated.主库操作：SQL>createtablescott.tasselect*fromscott.dept;Tablecreated.SQL>select*fromscott.t;DEPTNODNAMELOCADDR-------------------------------------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGO备库恢复到物理standbySQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupmount;ORACLEinstancestarted.TotalSystemGlobalArea9.4067E+10bytesFixedSize2263936bytesVariableSize9663677568bytesDatabaseBuffers8.4289E+10bytesRedoBuffers112766976bytesDatabasemounted.SQL>ALTERDATABASECONVERTTOPHYSICALSTANDBY;Databasealtered.SQL>shutdownimmediate;ORA-01507:databasenotmountedORACLEinstanceshutdown.SQL>startup;ORACLEinstancestarted.TotalSystemGlobalArea9.4067E+10bytesFixedSize2263936bytesVariableSize9663677568bytesDatabaseBuffers8.4289E+10bytesRedoBuffers112766976bytesDatabasemounted.Databaseopened.##此时备库的数据已经恢复到转变snapshotstandby时间点SQL>selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;DATABASE_ROLEOPEN_MODEDB_UNIQUE_NAMEFLASHBACK_ON------------------------------------------------------------------------------------PHYSICALSTANDBYREADONLYtestdbmsNOSQL>select*fromscott.emp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------------------------------SMITHCLERK79021980-12-1700:00:0080020ALLENSALESMAN76981981-02-2000:00:00160030030WARDSALESMAN76981981-02-2200:00:00125050030JONESMANAGER78391981-04-0200:00:00297520MARTINSALESMAN76981981-09-2800:00:001250140030BLAKEMANAGER78391981-05-0100:00:00285030CLARKMANAGER78391981-06-0900:00:00245010SCOTTANALYST75661987-04-1900:00:00300020KINGPRESIDENT1981-11-1700:00:00500010TURNERSALESMAN76981981-09-0800:00:001500030ADAMSCLERK77881987-05-2300:00:00110020JAMESCLERK76981981-12-0300:00:0095030FORDANALYST75661981-12-0300:00:00300020MILLERCLERK77821982-01-2300:00:00130010rowsselected.SQL>alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnect;Databasealtered.SQL>select*fromscott.t;DEPTNODNAMELOCADDR-------------------------------------------------------------------ACCOUNTINGNEWYORKRESEARCHDALLASSALESCHICAGOSQL>selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;DATABASE_ROLEOPEN_MODEDB_UNIQUE_NAMEFLASHBACK_ON------------------------------------------------------------------------------------PHYSICALSTANDBYREADONLYWITHAPPLYtestdbmsNO

(5)闪回归档(增加、修改、重命名、删除表的列、truncate表、修改表的约束、以及修改分区表的分区规范)

3、闪回注意事项

(1)数据库闪回需要在mounted下进行，并且open时需要使用resetlogs

(2)闪回DROP只能用于非系统表空间和本地管理的表空间，外键约束无法恢复，对方覆盖、重命名需注意

(3)表DROP，对应的物化视图会被彻底删除，物化视图不会存放在recyclebin里

(4)闪回表，如果在做过dml，然后进行了表结构修改、truncate等DDL操作，新增/删除结构无法做闪回

(5)闪回归档，必须在assm管理tablespace和undo auto管理下进行

(6)注意闪回区管理，防止磁盘爆满，闪回区空间不足等

(7)主库做库的闪回，会影响备库，需要重新同步

(8)snapshot standby 不支持最高保护模式

三、备注

1、相关数据字典

V$FLASHBACK_DATABASE_LOG ##查看数据库可闪回的时间点/SCN等信息 V$flashback_database_stat ##查看闪回日志空间记录信息

2、常用查询语句

(1)查看数据库状态

SQL>selectNAME,OPEN_MODE,DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ONfromv$database;NAMEOPEN_MODEDATABASE_ROLECURRENT_SCNFLASHBACK_ON------------------------------------------------------------------------------TESTDBREADWRITEPRIMARY16812246YES

(2)获取当前数据库的系统时间和SCN

SQL>selectto_char(systimestamp,'yyyy-mm-ddHH24:MI:SS')assysdt,dbms_flashback.get_system_change_numberscnfromdual;SYSDTSCN-----------------------------2017-12-1414:28:3316813234

(3)查看数据库可恢复的时间点

SQL>select*fromV$FLASHBACK_DATABASE_LOG;OLDEST_FLASHBACK_SCNOLDEST_FLASHBACK_TIRETENTION_TARGETFLASHBACK_SIZEESTIMATED_FLASHBACK_SIZE---------------------------------------------------------------------------------------------168015232017-12-1411:35:054320104857600244113408

(4)查看闪回日志空间情况

SQL>select*fromV$flashback_database_stat;BEGIN_TIMEEND_TIMEFLASHBACK_DATADB_DATAREDO_DATAESTIMATED_FLASHBACK_SIZE------------------------------------------------------------------------------------------------2017-12-1414:34:532017-12-1414:56:431703936997785614878720

(5)SCN和timestamp装换关系查询

selectscn,to_char(time_dp,'yyyy-mm-ddhh34:mi:ss')fromsys.smon_scn_time;

(6)查看闪回restore_point

selectscn,STORAGE_SIZE,to_char(time,'yyyy-mm-ddhh34:mi:ss')time,NAMEfromv$restore_point;

(7)闪回语句

a.闪回数据库

FLASHBACKDATABASETOTIMESTAMPto_timestamp('2017-12-1414:28:33','yyyy-mm-ddHH24:MI:SS');;flashbackdatabasetoscn16813234;

b.闪回DROP

其中table_name可以是删除表名称，也可以是别名

flashbacktabletable_nametobeforedrop;flashbacktabletable_nametobeforedroprenametotable_name_new;

c.闪回表

flashbacktabletable_nametoscnscn_number;flashbacktabletable_nametotimestampto_timestamp('2017-12-1414:28:33','yyyy-mm-ddhh34:mi:ss');

d.闪回查询

select*fromtable_nameasoftimestampto_timestamp('2017-12-1414:28:33','yyyy-mm-ddhh34:mi:ss');select*fromscott.deptasofscn16801523;

e.闪回快照

createrestorepointbefore_201712151111guaranteeflashbackdatabase;flashbackdatabasetorestorepointbefore_201712151111;

以上是“基于Oracle闪回的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！