如何执行Oracle数据库闪回查询

发布网友发布时间：2022-04-28 20:08

共2个回答

懂视网时间：2022-04-08 01:08

在Oracle的操作工程中，会不可避免地出现操作失误或者用户失误，例如不小心删除了一个表等，这些失误和错误可能会造成重要数据的丢失，最终导致Oracle数据库停止。
l在传统意义上，当发生数据丢失、数据错误问题时，解决的主要办法是数据的导入导出、备份恢复技术，这些方法都需要在发生错误前，有一个正确的备份才能进行恢复。
l为了减少这方面的损失，Oracle提供了闪回技术。有了闪回技术，就可以实现数据的快速恢复，而且不需要数据备份。

闪回的好处

恢复中，闪回技术是革命性的进步传统的恢复技术缓慢: ?它是整个数据库或者一个文件恢复，不只恢复损坏的数据 ?在数据库日志中每个修改都必须被检查闪回速度快: ?通过行和事务把改变编入索引 ?仅仅改变了的数据会被恢复闪回命令很容易: ?没有复杂棘手的多步程序

闪回的类型

l闪回表(flashback table) 将表回退到过去的某个时间上，该时间在某个操作之前 l闪回删除(flashback drop) 使用Oracle的回收站 l闪回版本查询(flashback version query) select l闪回事务查询(flashback transaction query) select ---> undo_sql
分别针对四种问题： 1. 错误地删除了一条数据，并且commit 2. 错误地删除了表 3. 如何查询表上的历史记录 4. 如何撤销一个已经提交了的事务

闪回表

闪回表，实际上是将表中的数据快速恢复到过去的一个是焦点或者系统改变号SCN上。实现表的闪回，需要使用到与撤销表空间相关的undo信息，通过show parameter undo命令可以了解这些信息。
用户对表数据的修改操作，都记录在撤销表空间中，这为表的闪回提供了数据恢复的基础。例如，某个修改操作在提交后被记录在撤销表空间中，保留时间为900秒，用户可以在这900秒的时间内对表进行闪回操作，从而将表中的数据恢复到修改之前的状态。
执行表的闪回，需要有flashback any table的权限

闪回表的语法

FLASHBACK TABLE [schema.]<table_name>

TO

{[BEFORE DROP [RENAME TO table]]

[SCN|TIMESTAMP]expr

[ENABLE|DISABLE]TRIGGERS}

schema：模式名，一般为用户名。 TO TIMESTAMP：系统邮戳，包含年、月、日、时、分、秒。 TO SCN：系统更改号， ENABLE TRIGGERS：表示触发器恢复以后为enable状态，而默认为disable状态。 TO BEFORE DROP：表示恢复到删除之前。 RENAME TO table：表示更换表名。

********************************************************** 注意：闪回表需要将表的行移动功能打开 ?alter table <table_name> enable row movement; **********************************************************

闪回表举例

查询某时间对应的SCN号： select to_char(sysdate,‘yyyy-mm-dd hh24:mi:mm‘) 时间,timestamp_to_scn(sysdate) SCN from dual;

时间 SCN
------------------- ----------
2012-03-29 10:26:03 1066202

************************************************************************* 创建表，进行删除操作，并闪回完整示例：

create table flashback_table
2 (fid number,fname varchar2(20));

表已创建。

SQL> insert into flashback_table values(1,‘Tom‘);

已创建 1 行。

SQL> insert into flashback_table values(2,‘Mary‘);

已创建 1 行。

SQL> insert into flashback_table values(3,‘Mike‘);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from flashback_table;

FID FNAME
---------- --------------------
1 Tom
2 Mary
3 Mike

SQL> select to_char(sysdate,‘yyyy-mm-dd hh24:mi:mm‘) 时间,timestamp_to_scn(sysdate) SCN
2 from dual;

时间 SCN
------------------- ----------
2012-03-29 10:32:03 1066413

SQL> delete from flashback_table where fid=2;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from flashback_table;

FID FNAME
---------- --------------------
1 Tom
3 Mike

SQL> --授权：grant flashback any table to scott;
SQL> flashback table flashback_table to SCN 1066413;
flashback table flashback_table to SCN 1066413
*
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> --开启表的行移动
SQL> alter table flashback_table enable row movement;

表已更改。

SQL>
SQL> flashback table flashback_table to SCN 1066413;

闪回完成。

SQL> select * from flashback_table;

FID FNAME
---------- --------------------
1 Tom
2 Mary
3 Mike

闪回表需要考虑的事情

FLASHBACK TABLE命令作为单一的事务执行，会得到一个单一的DML锁表的统计数据不会被闪回当前的索引和从属的对象会被维持闪回表操作: ?系统表不能被闪回 ?不能跨越DDL操作 ?会被写入警告日志 ?产生撤销和重做的数据

闪回删除

闪回删除，实际上从系统的回收站中将已删除的对象，恢复到删除之前的状态。
系统的回收站只对普通用户有作用。

闪回删除：回收站（recyclebin）简介

l回收站是所有被删除对象及其相依对象的逻辑存储容器，例如当一个表被删除(drop)时，该表及其相依对象并不会马上被数据库彻底删除，而是被保存到回收站中。 l l回收站将用户执行的drop操作记录在一个系统表中，也就是将被删除的对象写到一个数据字典中。如果确定不再需要该对象，可以使用purge命令对回收站进行清空。 l l被删除的对象的名字可能是相同的，例如用户创建了一个test表，使用drop命令删除该表后，又创建了一个test表，这时，如果再次删除该表就会导致向回收站中添加了两个相同的表。

Oracle回收站举例。

SQL> --闪回删除(drop): oracle回收站
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP20 TABLE
TESTSAVEPOINT TABLE
TESTDELETE TABLE
BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE
TEST3 TABLE
MYPERSON TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPINCOMEVIEW VIEW
TESTSEQ TABLE
HREMP SYNONYM
MSG1 TABLE
TEST1 TABLE
PM_CI TABLE
PM_STU TABLE
FLASHBACK_TABLE TABLE
SYS_TEMP_FBT TABLE

已选择20行。

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPINCOME BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE 2012-03-27:15:34:39
SQL> purge recyclebin;

回收站已清空。

SQL> drop table testseq purge; （不经回收站直接彻底删除）

表已删除。

闪回删除：回收站中对象的命名规则

为了确保添加到回收站中的对象的名称都是唯一的，系统会对这些保存到回收站中的对象进行重命名，重命名的格式如下：

BIN$globalUID$version

?其中： BIN表示RECYCLEBIN；globalUID是一个全局唯一的、24个字非长的对象，该标识与原对象名没有任何关系；version指数据库分配的版本号。

闪回删除举例

闪回使用drop命令删除的表 ?使用表名闪回
SQL> select * from test3;

TID TNAME GEND
---------- -------------------- ----
1 Tom 男

SQL> drop table test3;

表已删除。

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$vORkLd1GQKKsPs9PO/H8oQ==$0 TABLE 2012-03-29:10:46:20
SQL> flashback table test3 to before drop;

闪回完成。

SQL> show recyclebin
SQL> select * from test3;

TID TNAME GEND
---------- -------------------- ----
1 Tom 男

?使用recyclebin name闪回
SQL> drop table test3;

表已删除。

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$V2swGcDdROGeCxvlh9SA3A==$0 TABLE 2012-03-29:10:51:45
SQL> flashback table "BIN$V2swGcDdROGeCxvlh9SA3A==$0" to before drop;

闪回完成。

闪回重名的表
SQL> drop table test3;

表已删除。

SQL> create table test3(tid number);

表已创建。

SQL> drop table test3;

表已删除。

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$R5hrMHspQ1GmHWOXKJX1Ig==$0 TABLE 2012-03-29:10:49:03
TEST3 BIN$WO/lCOgSTo6kuIJ/thjM/A==$0 TABLE 2012-03-29:10:48:44
SQL> flashback table test3 to before drop;

闪回完成。

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$WO/lCOgSTo6kuIJ/thjM/A==$0 TABLE 2012-03-29:10:48:44
SQL> flashback table test3 to before drop;
flashback table test3 to before drop
*
第 1 行出现错误:
ORA-38312: 原始名称已被现有对象使用

SQL> select * from test3;

未选定行

SQL> flashback table test3 to before drop rename to test3new;

闪回完成。

SQL> select * from test3new;

TID TNAME GEND
---------- -------------------- ----
1 Tom 男

闪回删除: 需要考虑的事情

1. Oracle的回收站：管理员没有回收站
2. 执行闪回删除: 一般的闪回删除，闪回重名的表，通过回收站中的名字执行闪回(加双引号)

3.闪回删除对下列表无效: ?在SYSTEM 表空间内的表 ?用精细审计的数据库或虚拟的私人数据库 ?属于字典管理的表空间 ?由于空间不足已经被手动或自动删除的表 4.以下依赖不被保护: ?位图索引 ?表之前删掉的索引

闪回版本查询

语法

闪回版本查询，提供了一个审计行改变的查询功能，通过它可以查找到所有已经提交了的行记录。其语法格式如下：

select column_name[,column_name,...]

from table_name

versionsbetween [SCN|TIMESTAMP] [expr|MINVALUE]

and [epxr|MAXVALUE] as of [SCN|TIMESTAMP] expr;

?其中：column_name列名；table_name表名；between...and时间段；SCN系统改变号；TIMESTAMP时间戳；AS OF表示恢复单个版本;MAXVALUE最大值；MINVALUE最小值；expr指定一个值或者表达式。

闪回版本查询举例

--闪回版本查询

SQL> create table versions_table
2 (vid number,vname varchar2(20));

表已创建。

SQL> --第一天
SQL> insert into versions_table values(1,‘Tom‘);

已创建 1 行。

SQL> commit;

提交完成。

SQL> --第二天
SQL> insert into versions_table values(2,‘Mary‘);

已创建 1 行。

SQL> commit;

提交完成。

SQL> --第三天
SQL> insert into versions_table values(3,‘Mike‘);

已创建 1 行。

SQL> commit
2 ;

提交完成。

SQL> --第四天
SQL> update versions_table set vname=‘Mary123‘ where vid=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from versions_table;

VID VNAME
---------- --------------------
1 Tom
2 Mary123
3 Mike

SQL> --执行闪回版本查询
SQL> select vid,vname
2 from versions_table
3 versions between timestamp minvalue and maxvalue;

VID VNAME
---------- --------------------
2 Mary123
3 Mike
2 Mary
1 Tom

SQL> select vid,vname,versions_operation,versions_starttime,versions_endtime
2 from versions_table
3 versions between timestamp minvalue and maxvalue
4 order by 1,4;

VID VNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME
--- --------- - ---------------------------------------- ---------------------------------------------------------------------------
1 Tom I 29-3月 -12 11.07.26 上午
2 Mary I 29-3月 -12 11.07.41 上午 29-3月 -12 11.08.26 上午
2 Mary123 U 29-3月 -12 11.08.26 上午
3 Mike I 29-3月 -12 11.07.53 上午

闪回事务查询

l闪回事务查询实际上闪回版本查询的一个扩充，通过它可以审计某个事务甚至撤销一个已经提交的事务。
l实现闪回事务查询，需要先了解flashback_transaction_query视图，从该视图中可以获取事务的历史操作记录以及撤销语句(UNDO_SQL)。
l使用闪回事务查询，可以了解某个表的历史操作记录，这个操作记录对应一个撤销SQL语句，如果想要撤销这个操作，就可以执行这个SQL语句。

Flashback_transaction_query

名称类型说明

------------------------------------------------------------------------------------------------

XID RAW(8) 事务编号

START_SCN NUMBER 事务的开始的系统改变号

START_TIMESTAMP DATE 事务的开始时间

COMMIT_SCN NUMBER 事务提交时的系统改变号

COMMIT_TIMESTAMP DATE 事务提交时的时间

LOGON_USER VARCHAR2(30) 对应的用户

UNDO_CHANGE# NUMBER 撤销操作对应的编号

OPERATION VARCHAR2(32) 操作

TABLE_NAME VARCHAR2(256) 表

TABLE_OWNER VARCHAR2(32) 所有者

ROW_ID VARCHAR2(19) 行号

UNDO_SQL VARCHAR2(4000) 撤销事务的SQL语句

注意

l注意，要查询flashback_transaction_query视图的信息，需要有select any transaction的权限。

闪回事务查询举例

步骤：

l从闪回版本查询中，获取xid号。
l基于xid号，在flashback_transaction_query视图中获取相应事务的信息。
l利用UNDO_SQL撤销事务操作。

代码：

SQL> --闪回事务查询
SQL> create table transaction_table
2 (tid number,tname varchar2(20));

表已创建。

SQL> --第一个事务
SQL> insert into transaction_table values(1,‘Tom‘);

已创建 1 行。

SQL> insert into transaction_table values(2,‘Mary‘);

已创建 1 行。

SQL> commit;

提交完成。

SQL> --第二个事务
SQL> insert into transaction_table values(3,‘Mike‘);

已创建 1 行。

SQL> update transaction_table set tname=‘Mary123‘ where tid=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> --如何撤销第二个事务??
SQL> desc flashback_transaction_query
名称是否为空? 类型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)

SQL> --授权：grant select any transaction to scott;
SQL> select tid,tname,versions_operation,versions_starttime,versions_endtime,versions_xid
2 from transaction_table
3 versions between timestamp minvalue and maxvalue
4 order by 1,4;

TID TNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID
---------- -------------------- - ---------------------------------------- ---------------------------------------- ----------------
1 Tom I 29-3月 -12 11.22.00 上午 06001F00B9010000
2 Mary I 29-3月 -12 11.22.00 上午 29-3月 -12 11.22.36 上午 06001F00B9010000
2 Mary123 U 29-3月 -12 11.22.36 上午 08000F00B8010000
3 Mike I 29-3月 -12 11.22.36 上午 08000F00B8010000

SQL> select operation,undo_sql
2 from flashback_transaction_query
3 where xid=‘08000F00B8010000‘;

SQL> set linesize 150
SQL> col UNDO_SQL for a60
SQL> /

OPERATIO UNDO_SQL
-------- ------------------------------------------------------------
UPDATE update "SCOTT"."TRANSACTION_TABLE" set "TNAME" = ‘Mary‘ wher
e ROWID = ‘AAAM6vAAEAAAALtAAB‘;

INSERT delete from "SCOTT"."TRANSACTION_TABLE" where ROWID = ‘AAAM6
vAAEAAAALtAAC‘;

BEGIN

SQL> update "SCOTT"."TRANSACTION_TABLE" set "TNAME" = ‘Mary‘ where ROWID = ‘AAAM6vAAEAAAALtAAB‘;

已更新 1 行。

SQL> delete from "SCOTT"."TRANSACTION_TABLE" where ROWID = ‘AAAM6vAAEAAAALtAAC‘;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from TRANSACTION_TABLE;

TID TNAME
---------- --------------------
1 Tom
2 Mary

Oracle学习（15）【DBA向】：闪回

标签：dba oracle 闪回

热心网友时间：2022-04-07 22:16

可按以下步骤配置闪回数据库：（确保数据库处于ARCHIVELOG 模式。）
1. 配置快速恢复区。
2. 使用初始化参数DB_FLASHBACK_RETENTION_TARGET 设置保留目标。可指定
一个上限（以分钟为单位），指示数据库能够闪回到多长时间以前。本示例使用
了 2880 分钟，相当于两天。此参数只是一个目标，并不提供任何保证。闪回时间
间隔取决于快速恢复区中保留的闪回数据量的大小。
3. 使用以下命令启用闪回数据库：
ALTER DATABASE FLASHBACK ON;
必须先配置数据库以进行归档，且必须在 MOUNT EXCLUSIVE 模式下启动数据库
后，才能发出此命令来启用闪回数据库。
可以使用以下查询来确定是否已启用闪回数据库：
SELECT flashback_on FROM v$database;
可以使用ALTER DATABASE FLASHBACK OFF 命令禁用闪回数据库。这样，会自动删
除所有现有的闪回数据库日志。
注：仅当在独占模式下装载（而不是打开）数据库时才能启用闪回数据库。

1.确认当前模式
　　SYS AS SYSDBA on 29-MAR-05 >select flashback_on from v$database;
　　FLA
　　---
　　NO
　　2.检查/修改恢复区设置
　　SYS AS SYSDBA on 29-MAR-05 >show parameter db_recovery_file_dest
　　NAME　　　　　　　　 TYPE　　VALUE
　　------------------------------------ ----------- ------------------------------
　　db_recovery_file_dest　　　　string /u01/app/oracle/flash_recovery_area　　db_recovery_file_dest_size　　 big integer 10G
SYS AS SYSDBA on 29-MAR-05 >alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
SYS AS SYSDBA on 29-MAR-05 >alter system set db_recovery_file_dest_size=15G;

　　3.检查/修改闪回时间设置
　　SYS AS SYSDBA on 29-MAR-05 >show parameter db_flashback_retention_target
　　NAME　　　　　　　　 TYPE　　VALUE
　　------------------------------------ ----------- ------------------------------
　　db_flashback_retention_target　　integer 60
　　SYS AS SYSDBA on 29-MAR-05 >alter system set db_flashback_retention_target=1440;--分钟
　　System altered.
如何设置flash recovery area
(1).db_recovery_file_dest：指定闪回恢复区的位置
(2).db_recovery_file_dest_size：指定闪回恢复区的可用空间大小
(3).db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟，也就是一天。当然，实际上可回退的时间还决定于闪回恢复区的大小，因为里面保存了回退所需要的 flash log。所以这个参数要和db_recovery_file_dest_size配合修改。
alter system set db_flashback_retention_target=1440;
alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
alter system set db_recovery_size=15G;

　　4.重新启动数据库到Mount状态
　　启动flashback database选项。
　　SYS AS SYSDBA on 29-MAR-05 >shutdown immediate;
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　SYS AS SYSDBA on 29-MAR-05 >startup mount exclusive;
　　ORACLE instance started.
　　Total System Global Area 314572800 bytes
　　Fixed Size　　　　 1301704 bytes
　　Variable Size　　 261890872 bytes
　　Database Buffers　　 50331648 bytes
　　Redo Buffers　　　　1048576 bytes
　　Database mounted.

　　SYS AS SYSDBA on 29-MAR-05 >alter database flashback on;
　　Database altered.
　　SYS AS SYSDBA on 29-MAR-05 >alter database open;
　　Database altered.