基于Oracle闪回详解(必看篇)

2023-09-19 08:42:07 46

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

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

一、Oracle闪回概述

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

二、Oracle闪回使用详解

1、闪回开启

(1)开启闪回必要条件

a.开启归档日志

SQL>archiveloglist;
DatabaselogmodeArchiveMode
AutomaticarchivalEnabled
Archivedestination/home/U01/app/oracle/oradata/testdb/arch
Oldestonlinelogsequence844
Nextlogsequencetoarchive846
Currentlogsequence846

##如未开启，在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
-------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON
SQL>deletefromscott.deptwheredeptno=40;
rowdeleted.
SQL>commit;
Commitcomplete.
SQL>select*fromscott.deptasoftimestampsysdate-10/1440;

DEPTNODNAMELOC
-------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON
SQL>select*fromscott.deptasoftimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh24:mi:ss');

DEPTNODNAMELOC
-------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

SQL>select*fromscott.deptasofscn16801523;

DEPTNODNAMELOC
-------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

b.闪回版本查询

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

c.闪回事务查询

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

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

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

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

闪回表示例：

SQL>flashbacktablescott.depttotimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh24:mi:ss');
flashbacktablescott.depttotimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh24:mi:ss')
ERRORatline1:
ORA-08189:cannotflashbackthetablebecauserowmovementisnotenabled

SQL>selectrow_movementfromdba_tableswheretable_name='DEPT'andowner='SCOTT';
ROW_MOVE
--------
DISABLED
SQL>altertablescott.deptenablerowmovement;
Tablealtered.

SQL>flashbacktablescott.depttotimestampto_timestamp('2017-12-1416:20:00','yyyy-mm-ddhh24:mi:ss');
Flashbackcomplete.
SQL>select*fromscott.dept;
DEPTNODNAMELOC
-------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON
SQL>altertablescott.deptdisablerowmovement;
Tablealtered.

(3)闪回DROP(droptable)

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

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

闪回DROP示例：

SQL>select*fromt;

IDNAME
-------------------------------------------------
2
4

SQL>droptablet;

Tabledropped.

SQL>showrecyclebin;
ORIGINALNAMERECYCLEBINNAMEOBJECTTYPEDROPTIME
-----------------------------------------------------------------------------
TBIN$YEh2QcvZdJLgUxyAgQpnVQ==$0TABLE2017-12-14:15:02:06
SQL>flashbacktablettobeforedrop;

Flashbackcomplete.

SQL>select*fromt;

IDNAME
-----------------------------------------------
2
4

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

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

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

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

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

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

a.全库闪回

数据库闪回示例

SQL>select*fromscott.EMP;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------------------------------
SMITHCLERK79021980-12-1700:00:0080020
ALLENSALESMAN76981981-02-2000:00:00160030030
WARDSALESMAN76981981-02-2200:00:00125050030
JONESMANAGER78391981-04-0200:00:00297520
MARTINSALESMAN76981981-09-2800:00:001250140030
BLAKEMANAGER78391981-05-0100:00:00285030
CLARKMANAGER78391981-06-0900:00:00245010
SCOTTANALYST75661987-04-1900:00:00300020
KINGPRESIDENT1981-11-1700:00:00500010
TURNERSALESMAN76981981-09-0800:00:001500030
ADAMSCLERK77881987-05-2300:00:00110020
JAMESCLERK76981981-12-0300:00:0095030
FORDANALYST75661981-12-0300:00:00300020
MILLERCLERK77821982-01-2300:00:00130010
rowsselected.

SQL>truncatetablescott.EMP;

Tabletruncated.
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>startupmount;
ORACLEinstancestarted.

TotalSystemGlobalArea9.4067E+10bytes
FixedSize2263936bytes
VariableSize9395242112bytes
DatabaseBuffers8.4557E+10bytes
RedoBuffers112766976bytes
Databasemounted.
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:0080020
ALLENSALESMAN76981981-02-2000:00:00160030030
WARDSALESMAN76981981-02-2200:00:00125050030
JONESMANAGER78391981-04-0200:00:00297520
MARTINSALESMAN76981981-09-2800:00:001250140030
BLAKEMANAGER78391981-05-0100:00:00285030
CLARKMANAGER78391981-06-0900:00:00245010
SCOTTANALYST75661987-04-1900:00:00300020
KINGPRESIDENT1981-11-1700:00:00500010
TURNERSALESMAN76981981-09-0800:00:001500030
ADAMSCLERK77881987-05-2300:00:00110020
JAMESCLERK76981981-12-0300:00:0095030
FORDANALYST75661981-12-0300:00:00300020
MILLERCLERK77821982-01-2300:00:00130010
rowsselected.

b.快照闪回

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

SQL>select*fromscott.dept;

DEPTNODNAMELOCADDR
-------------------------------------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO

SQL>createrestorepointbefore_201712151111guaranteeflashbackdatabase;
Restorepointcreated.

SQL>createtablescott.tasselect*fromscott.dept;
Tablecreated.

SQL>truncatetablescott.t;
Tabletruncated.
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>startupmount;
ORACLEinstancestarted.

TotalSystemGlobalArea9.4067E+10bytes
FixedSize2263936bytes
VariableSize9663677568bytes
DatabaseBuffers8.4289E+10bytes
RedoBuffers112766976bytes
Databasemounted.
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.闪回snapshotstandby

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

selectscn,STORAGE_SIZE,to_char(time,'yyyy-mm-ddhh24: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
------------------------------------------------------------------------------------
PHYSICALSTANDBYREADONLYtestdbmsNO


SQL>ALTERDATABASECONVERTTOSNAPSHOTSTANDBY;

Databasealtered.

SQL>selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;

DATABASE_ROLEOPEN_MODEDB_UNIQUE_NAMEFLASHBACK_ON
------------------------------------------------------------------------------------
SNAPSHOTSTANDBYMOUNTEDtestdbmsRESTOREPOINTONLY

SQL>alterdatabaseopen;

Databasealtered.

SQL>selectopen_modefromv$database;

OPEN_MODE
--------------------
READWRITE


此时备库操作：
SQL>select*fromscott.emp;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------------------------------
SMITHCLERK79021980-12-1700:00:0080020
ALLENSALESMAN76981981-02-2000:00:00160030030
WARDSALESMAN76981981-02-2200:00:00125050030
JONESMANAGER78391981-04-0200:00:00297520
MARTINSALESMAN76981981-09-2800:00:001250140030
BLAKEMANAGER78391981-05-0100:00:00285030
CLARKMANAGER78391981-06-0900:00:00245010
SCOTTANALYST75661987-04-1900:00:00300020
KINGPRESIDENT1981-11-1700:00:00500010
TURNERSALESMAN76981981-09-0800:00:001500030
ADAMSCLERK77881987-05-2300:00:00110020
JAMESCLERK76981981-12-0300:00:0095030
FORDANALYST75661981-12-0300:00:00300020
MILLERCLERK77821982-01-2300:00:00130010
rowsselected.

SQL>truncatetablescott.emp;

Tabletruncated.

主库操作：
SQL>createtablescott.tasselect*fromscott.dept;

Tablecreated.

SQL>select*fromscott.t;

DEPTNODNAMELOCADDR
-------------------------------------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO

备库恢复到物理standby
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>startupmount;
ORACLEinstancestarted.

TotalSystemGlobalArea9.4067E+10bytes
FixedSize2263936bytes
VariableSize9663677568bytes
DatabaseBuffers8.4289E+10bytes
RedoBuffers112766976bytes
Databasemounted.
SQL>ALTERDATABASECONVERTTOPHYSICALSTANDBY;

Databasealtered.
SQL>shutdownimmediate;
ORA-01507:databasenotmounted


ORACLEinstanceshutdown.
SQL>startup;
ORACLEinstancestarted.

TotalSystemGlobalArea9.4067E+10bytes
FixedSize2263936bytes
VariableSize9663677568bytes
DatabaseBuffers8.4289E+10bytes
RedoBuffers112766976bytes
Databasemounted.
Databaseopened.

##此时备库的数据已经恢复到转变snapshotstandby时间点
SQL>selectdatabase_role,open_mode,db_unique_name,flashback_onfromv$database;

DATABASE_ROLEOPEN_MODEDB_UNIQUE_NAMEFLASHBACK_ON
------------------------------------------------------------------------------------
PHYSICALSTANDBYREADONLYtestdbmsNO

SQL>select*fromscott.emp;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------------------------------
SMITHCLERK79021980-12-1700:00:0080020
ALLENSALESMAN76981981-02-2000:00:00160030030
WARDSALESMAN76981981-02-2200:00:00125050030
JONESMANAGER78391981-04-0200:00:00297520
MARTINSALESMAN76981981-09-2800:00:001250140030
BLAKEMANAGER78391981-05-0100:00:00285030
CLARKMANAGER78391981-06-0900:00:00245010
SCOTTANALYST75661987-04-1900:00:00300020
KINGPRESIDENT1981-11-1700:00:00500010
TURNERSALESMAN76981981-09-0800:00:001500030
ADAMSCLERK77881987-05-2300:00:00110020
JAMESCLERK76981981-12-0300:00:0095030
FORDANALYST75661981-12-0300:00:00300020
MILLERCLERK77821982-01-2300:00:00130010
rowsselected.


SQL>alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnect;

Databasealtered.

SQL>select*fromscott.t;

DEPTNODNAMELOCADDR
-------------------------------------------------------------------
ACCOUNTINGNEWYORK
RESEARCHDALLAS
SALESCHICAGO


SQL>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和undoauto管理下进行

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

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

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

三、备注

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-ddhh24:mi:ss')fromsys.smon_scn_time;

(6)查看闪回restore_point

selectscn,STORAGE_SIZE,to_char(time,'yyyy-mm-ddhh24: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-ddhh24:mi:ss');

d.闪回查询

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

e.闪回快照

createrestorepointbefore_201712151111guaranteeflashbackdatabase;

flashbackdatabasetorestorepointbefore_201712151111;

(7)闪回空间爆满问题处理

请参照基于ORA-19815闪回空间爆满问题的处理方法

以上这篇基于Oracle闪回详解(必看篇)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。

基于Oracle闪回详解(必看篇)

热门推荐

随机推荐