Oracle的学习心得和知识总结（一）|Oracle数据库闪回技术详解

最新推荐文章于 2023-08-30 21:30:57 发布

孤傲小二~阿沐

最新推荐文章于 2023-08-30 21:30:57 发布

阅读量6.4k

点赞数 2

分类专栏： Oracle的学习心得和知识总结

本文链接：https://blog.csdn.net/weixin_43949535/article/details/108624327

版权

Oracle的学习心得和知识总结专栏收录该内容

32 篇文章 5 订阅

订阅专栏

目录结构

注：提前言明本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、Oracle闪回详解，点击前往
2、Oracle闪回技术详解，点击前往
3、参考书籍：《涂抹Oracle 三思笔记之一步一步学Oracle》
4、参考书籍：《Oracle Database 11g数据库管理艺术》
5、Flashback Transaction Backout，点击前往
6、关于oracle闪回数据归档的总结，点击前往
7、参考书籍：《Oracle Database 9i10g11g编程艺术深入数据库体系结构(第2版)》

文章快速说明索引

学习目标：

目的：因为接下来想在PostgreSQL上实践实现一下闪回操作，但是就目前而言 PostgreSQL尚不支持此功能。德哥的git和其他PostgreSQL社区爱好者也曾分享过一些类似的闪回简易实现，大家有兴趣也可以去看看！本文主要记录在Oracle数据库上面的闪回功能的使用体验和基于开发者的设计思考，以期在PostgreSQL数据库上面支持此功能！

学习内容：（详见目录）

1、Oracle数据库的闪回技术

学习时间：

2020年9月16日03:36:25 - 2020年9月27日23:33:51

学习产出：

1、Oracle数据库闪回技术学习
2、CSDN 技术博客 1篇
3、PostgreSQL数据库闪回功能实现设计思考

Oracle闪回的概述

闪回概述

Flashback 即数据库闪回操作，它是Oracle自9i版本才开始提供的一项新特性，在Oracle 10g中对功能进行了增强。在10g之前只提供了 闪回查询 的功能，进入10g之后又提供了对事务、对表的恢复，以及真正具有恢复意味的闪回数据库的功能。在Oracle11g之后这些功能日趋更加完善。闪回技术是Oracle强大数据库备份恢复机制的一部分，在数据库发生逻辑错误的时候，闪回技术能提供快速且最小损失的恢复（多数闪回功能都能在数据库联机状态下完成）。需要注意的是，闪回技术旨在快速恢复逻辑错误，对于物理损坏或是介质丢失的错误，闪回技术就回天乏术了，还是得借助于Oracle一些高级的备份恢复工具如RAMN去完成。

闪回特性的优点在于语法简单、操作方便和功能强大，其作用在于快速恢复数据和查询历史数据。在Oracle 10g中的闪回功能可以分成以下三个方面：

闪回查询(Flashback Query) ：通过查询UNDO段，能够重现操作之前的数据
闪回表(Flashback Table) ：该特性与10g中新推出的另外一项新增特性Recycle Bin(回收站) 对应，默认情况下表对象及其关联的索引等对象在DROP后并没有物理删除，而是标记为删除(类似在Windows中删除文件时，文件移向“回收站”的概念)，如果你想对这类表进行恢复，只需要简单的命令即可，而且该操作只修改数据字典，不管要恢复的对象占用多大空间，恢复效率极高
闪回数据库(Flashback Database) ：该功能十分强大，真正实现了不需要备份的恢复(严格地讲还是有备份，只不过这个备份操作不由DBA做，而是Oracle自动进行)

详细内容展开如下表所示：（基于Oracle 11g的闪回技术）

闪回技术	闪回级别	场景描述	功能描述	对象依赖	是否影响数据
Flashback Database	Database	表截断、逻辑错误、其他多表意外事件	将整个数据库倒退到一个特定的时刻	闪回日志、undo log	YES
Flashback Table	Table	更新、删除、插入记录	将表返回到过去的一个状态	还原数据、undo log	YES
Flashback DROP	Drop	删除表	撤销DROP TABLE命令并恢复被删除的表	recyclebin	YES
Flashback QUERY	Query	当前数据和历史数据对比	检索过去某一时刻(或时间间隔)的数据	还原数据、undo log	NO
Flashback Version Query	Version Query	比较行版本	同上	同上	NO
Flashback Transaction Query	Transaction Query	比较事务	同上	同上	NO
Flashback Transaction Backout	Transaction Backout	撤销事务	在Database Control中单击一下，撤销一个事务以及它依赖的所有事务	undo log	YES
Flashback Data Archive	Archive	DDL、DML	存储对一个表所做更改的历史，可用它来构造旧版本数据的查询和用于审计用途	归档日志	YES

下面来看一下闪回功能的启停：
主要操作步骤如下：

1、数据库处于归档模式
2、开启归档日志
3、设置合理的闪回区
4、开启flashback并检查：alter database flashback on/off;

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2213736 bytes
Variable Size		 1090521240 bytes
Database Buffers	  486539264 bytes
Redo Buffers		    7434240 bytes
Database mounted.
SQL>  archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     54
Current log sequence	       56
SQL> alter database archivelog;                         # 注意看下面的 Enabled 和 上面的 Disabled

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>  archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     54
Next log sequence to archive   56
Current log sequence	       56
SQL> alter system set db_recovery_file_dest='/home/oracle/app/flash_recovery_area' scope=both;

System altered.

SQL> alter system set db_recovery_file_dest_size=60G scope=both;

System altered.

SQL>  alter system set db_flashback_retention_target=4320 scope=both;

System altered.

SQL>  archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     54
Next log sequence to archive   56
Current log sequence	       56
SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>

如上，我们成功开启了数据库的闪回功能，下面我们开始准备闪回所需的数据：

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-26 10:19:21

SQL>
SQL> create table test1 (id int,name varchar(16),curtime TIMESTAMP);

Table created.

SQL> insert into test1 values (1,'post',sysdate);

1 row created.

SQL>  insert into test1 values (2,'orac',sysdate);

1 row created.

SQL> insert into test1 values (3,'mysq',sysdate);

1 row created.

SQL> insert into test1 values (4,'redi',sysdate);

1 row created.

SQL>
SQL> set linesize 800
SQL> set pagesize 900                            
SQL>  select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ----------------------------
	 1 post 	    26-SEP-20 10.20.07.000000 AM
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL>

Oracle闪回的使用

闪回使用

闪回查询

闪回查询操作：允许用户查询过去某个时间点的数据，用以重构由于意外删除或更改的数据，但是表中的数据不会变化。于是我们就可以查询过去某个时间点的数据库状态和表中当时的数据。其工作原理为：Oracle 会提取所需要的撤销数据（前提是撤销是可用的，即撤销数据还没被覆盖）进行回滚，但这种回滚是临时的，仅针对当前session可见。

SQL>  select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.20.07.000000 AM
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-26 10:22:29

SQL> delete from test1 where id = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL>
SQL> select * from test1 as of timestamp  to_timestamp('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss');

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.20.07.000000 AM
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL>

如上一条被删除的数据就被我们查回来了，但是建议不要使用时间而是scn：

SQL> select timestamp_to_scn(to_date('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss')) from dual;

TIMESTAMP_TO_SCN(TO_DATE('2020-09-2610:22:29','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
							       2254386

SQL>
SQL> select * from test1  as of scn 2254386;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.20.07.000000 AM
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL> select * from test1 ;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL>

如上，这个闪回查询顾名思义，没有改变现表状态的功能我们可以很清楚的看到数据不会变化。

闪回表(insert delete update)

闪回表操作：闪回表就是对表的数据做回退，回退到之前的某个时间点（也即：可将某个表回退到过去某个时间点的状态和数据内容）。其工作原理为：和上面一样Oracle会先去查询撤销段，提取过去某个时间点之后的所有变更，构造反转这些变更的SQL语句进行回退。闪回操作是一个单独的事务，所以若由于撤销数据过期之类的原因导致无法闪回，整个操作会回滚，不会存在不一致的状态。它这里利用的也是undo的历史数据，与undo_retention设置有关，默认是1440分钟（1天）。但是sys用户表空间不支持闪回表，示例如下：

SQL>  flashback table test1 to timestamp to_timestamp('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss');
 flashback table test1 to timestamp to_timestamp('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss')
                 *
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS


SQL>

OK，我们换一个用户开始同样的操作：

SQL> CREATE user song identified by sys;

User created.

SQL> show user
USER is "SYS"
SQL> alter user song identified by 123456;

User altered.

SQL> grant connect,resource,dba to song;

Grant succeeded.

SQL> show user
USER is "SYS"
SQL> connect song/123456
Connected.
SQL>

然后我们在其他用户下面执行这个闪回表操作（需要注意的是：要想表闪回，需要允许表启动行迁移(row movement)
），实例如下：

SQL> show user
USER is "SONG"
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-26 10:53:01

SQL> create table test1 (id int,name varchar(16),curtime TIMESTAMP);

Table created.

SQL> insert into test1 values (1,'post',sysdate);

1 row created.

SQL> insert into test1 values (2,'orac',sysdate);

1 row created.

SQL> insert into test1 values (3,'mysq',sysdate);

1 row created.

SQL> insert into test1 values (4,'redi',sysdate);

1 row created.

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-26 10:54:10

SQL> delete from test1 where id = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ----------------------
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL> select * from test1 as of timestamp  to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss');

	ID NAME 	    CURTIME
---------- ---------------- ----------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL> flashback table test1 to timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss');
flashback table test1 to timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> select row_movement from dba_tables where table_name='test1' and owner='song';

no rows selected

SQL> alter table test1 enable row movement;

Table altered.

SQL> flashback table test1 to timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ----------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL> alter table test1 disable row movement;

Table altered.

SQL>

如上开启row movement，因为启用表闪回首先要在表上支持行移动（在数据字典中设置标识来标识该操作可能会改变行ID，即同一条数据闪回成功后主键都一样，但行ID其实已经发生变化了）。

闪回表可能会失败，有可能有以下几种情况：

违反了数据库约束：比如用户不小心删除了子表中的数据，现在想利用闪回表技术进行回退，恰好在这中间，父表中与该数据对应的那条记录也被删除了，在这种情况下，由于违反了外键约束，导致闪回表操作失败了
撤销数据失效：比如用于支撑闪回操作的撤销数据被覆盖了，这种情况闪回表操作自然会失败
闪回不能跨越DDL：在闪回点和当前点之间，表结构有过变更，这种情况闪回操作也会失败

注：上述闪回功能都是基于撤销数据（undo log）的，而撤销数据是会被重写的（Expired会被重写，Active不会被重写），所以在需要使用上面闪回功能去恢复数据的时候（确切地说，是需要使用基于撤销数据的闪回功能时），最短时间发现错误，第一时间执行闪回操作，才能最大程度地保证闪回功能的成功。

闪回删除

在Oracle中，当一个表被drop掉，表会被放入recyclebin 即：回收站。于是我们这里的drop闪回就可以通过回收站做表的闪回。在闪回过程中表上的索引、约束等同样会被恢复，同样这里也不支持sys/system用户表空间对象

而回收站功能的开启和关闭是可通过alter system set recyclebin=off; alter system set recyclebin=on scope=spfile;来完成（默认是开启的）示例如下：

SQL> alter database flashback off;

Database altered.

SQL> show recyclebin;                  # 看一眼回收站
ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1		 BIN$sC+Fb/6hJ+3gUAB/AQATZg==$0 TABLE	     2020-09-26:10:52:17
SQL> purge recyclebin;                  # 清空回收站 不影响下面的操作

Recyclebin purged.

SQL> show recyclebin;                  # 回收站为空
SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL> drop table test1;

Table dropped.

SQL> show recyclebin;                  # 表被放到回收站了
ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1		 BIN$sC+Fb/6iJ+3gUAB/AQATZg==$0 TABLE	     2020-09-26:12:24:15
SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-26 12:24:45

SQL> flashback table TEST1  to before drop;

Flashback complete.

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL>

如上我们使用purge recyclebin;来清理当前的回收站。这里我们注意一点：在最开始的时候我可是把flashback关闭了的，但是只要开启了recyclebin，那么就可以闪回DROP表。

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

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL> drop table test1;

Table dropped.

SQL> create table test1 (id int,mytime timestamp);

Table created.

SQL> insert into test1 values (1,sysdate);

1 row created.

SQL>  drop table test1;

Table dropped.

SQL> show recyclebin;                  # 这个时候回收站里面有两个不一样的同名表
ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1		 BIN$sC+Fb/6kJ+3gUAB/AQATZg==$0 TABLE	     2020-09-26:12:31:23
TEST1		 BIN$sC+Fb/6jJ+3gUAB/AQATZg==$0 TABLE	     2020-09-26:12:30:18
SQL> flashback table "BIN$sC+Fb/6kJ+3gUAB/AQATZg==$0" to before drop ;    # 这样可以恢复指定那个

Flashback complete.

SQL> select * from test1;

	ID MYTIME
---------- ---------------------------------------------------------------------------
	 1 26-SEP-20 12.31.17.000000 PM

SQL> flashback table test1 to before drop rename to another_test1;     # 恢复的时候 同时做了个重命名

Flashback complete.

SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    26-SEP-20 10.53.20.000000 AM
	 2 orac 	    26-SEP-20 10.53.28.000000 AM
	 3 mysq 	    26-SEP-20 10.53.36.000000 AM
	 4 redi 	    26-SEP-20 10.53.45.000000 AM

SQL>

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

数据库闪回功能可以基于两种方式来完成：1、数据库闪回必须在mounted状态下进行； 2、基于快照的可以在open下进行闪回库（要求数据库为归档模式）。

闪回数据库主要是将数据库还原到过去的某个时间点或SCN，用于数据库出现逻辑错误时，需要open database resetlogs。当然，闪回点之后的所有工作就丢失了，其实就相当于数据库的不完整恢复，所以只能以resetlogs模式打开数据库。闪回数据库会造成停机时间，当然相比于传统备份恢复机制，恢复过程会快很多。其工作原理为：闪回数据库不使用撤销数据（undo log），使用另外一种机制来保留回退所需要的恢复数据。当启用闪回数据库时，发生变化的数据块会不断从数据库缓冲区缓存中复制到闪回缓冲区，然后被称为恢复写入器（Recovery Writer）的后台进程会将这些数据刷新到磁盘中的闪回日志文件中。闪回的过程：是一个提取闪回日志到将块映像复制回数据文件的过程。

虽然在上面我们已经配置过闪回数据库的一些属性，但是这个很重要，下面再来详细看一下闪回数据库功能的配置：
主要操作步骤如下：

1、数据库处于归档模式 startup mount
2、开启归档日志 alter database archivelog;
3、设置合理的闪回区 指定闪回恢复区指定恢复区大小指定闪回日志保存时间
指定闪回恢复区，也就是存放闪回日志的位置，但闪回恢复区不仅仅是为了存放闪回日志。Oracle的很多备份恢复技术都用到这个区域，比如控制文件的自动备份等都会存放到此区域
alter system set db_recovery_file_dest='/home/oracle/app/flash_recovery_area' scope=both;
指定恢复区大小
alter system set db_recovery_file_dest_size=60G scope=both;
指定闪回日志保留时间为4320 / 60 = 72小时，即通过闪回操作，可以将数据库回退到前72小时内的任意时间点
alter system set db_flashback_retention_target=4320 scope=both;
4、开启flashback并检查：alter database flashback on/off;

下面是详细的SQL演示：

SQL> shutdown immediate                      # 因为当前用户是song 不是sys  没有权限
ORA-01031: insufficient privileges
SQL> conn /as sysdba                      # 连接 用sys用户
Connected.
SQL> shutdown immediate                      # 关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount                      # 开启mounted状态
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2213736 bytes
Variable Size		 1090521240 bytes
Database Buffers	  486539264 bytes
Redo Buffers		    7434240 bytes
Database mounted.
SQL> archive log list;             # 查看归档日志参数
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     58
Next log sequence to archive   60
Current log sequence	       60
SQL> alter database archivelog;            # 开启归档日志

Database altered.

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     58
Next log sequence to archive   60
Current log sequence	       60
SQL>  select flashback_on from v$database;                 # 查看闪回是否开启

FLASHBACK_ON
------------------
NO

SQL>                       # 下面是配置闪回属性
SQL> alter system set db_recovery_file_dest='/home/oracle/app/flash_recovery_area' scope=both;

System altered.

SQL> alter system set db_recovery_file_dest_size=60G scope=both;

System altered.

SQL> alter system set db_flashback_retention_target=4320 scope=both;

System altered.

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     58
Next log sequence to archive   60
Current log sequence	       60
SQL>  alter database flashback on;       # 开启闪回

Database altered.

SQL> select flashback_on from v$database;        # 查看 是开启的

FLASHBACK_ON
------------------
YES

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';         # 设置时间格式

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-27 10:55:26

SQL> select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter database open;                # 打开数据库

Database altered.

SQL> select * from test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 2 orac 	    26-SEP-20 10.20.15.000000 AM
	 3 mysq 	    26-SEP-20 10.20.23.000000 AM
	 4 redi 	    26-SEP-20 10.20.32.000000 AM

SQL> connect song/123456                      # 使用自定义用户连接
Connected.
SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

SQL>

如下是闪回数据库的实例：

SQL> connect /as sysdba
Connected to an idle instance.
SQL>  startup
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2213736 bytes
Variable Size		 1090521240 bytes
Database Buffers	  486539264 bytes
Redo Buffers		    7434240 bytes
Database mounted.
Database opened.
SQL> connect song/123456
Connected.
SQL> select table_name from dba_tables where owner = 'SONG';

TABLE_NAME
------------------------------
SHANHUI
SYS_TEMP_FBT
ANOTHER_TEST1

SQL> select * from ANOTHER_TEST1;

	ID NAME
---------- ----------------
CURTIME
---------------------------------------------------------------------------
	 1 post
27-SEP-20 02.05.11.000000 PM

	 2 orac
27-SEP-20 02.06.02.000000 PM

	 3 mysq
27-SEP-20 02.06.02.000000 PM


	ID NAME
---------- ----------------
CURTIME
---------------------------------------------------------------------------
	 4 redi
27-SEP-20 02.06.35.000000 PM


SQL> set linesize 800
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-09-27 14:29:26

SQL> truncate table ANOTHER_TEST1;

Table truncated.

SQL> select * from ANOTHER_TEST1;

no rows selected

SQL> commit;

Commit complete.

SQL> connect /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2213736 bytes
Variable Size		 1090521240 bytes
Database Buffers	  486539264 bytes
Redo Buffers		    7434240 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2020-09-27 14:29:26','yyyy-mm-dd HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> conn song/123456
Connected.
SQL> select * from ANOTHER_TEST1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

SQL> show user
USER is "SONG"
SQL>

如上，在闪回完成之后，还需要注意的是：闪回数据库主要是将数据库还原值过去的某个时间点或SCN，用于数据库出现逻辑错误时，千万不能忘记open database resetlogs。

在上面我们也曾说过：闪回数据库的功能也是可以基于快照来实现。即：创建闪回快照点，然后恢复到指定的快照点。示例如下：

SQL> select * from ANOTHER_TEST1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

SQL> create restore point myfirstpoint guarantee flashback database;
create restore point myfirstpoint guarantee flashback database
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn /as sysdba
Connected.
SQL> create restore point myfirstpoint guarantee flashback database; # 创建闪回快照点

Restore point created.

SQL> conn song/123456
Connected.
SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

SQL> truncate table another_test1;

Table truncated.

SQL> commit;

Commit complete.

SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2213736 bytes
Variable Size		 1090521240 bytes
Database Buffers	  486539264 bytes
Redo Buffers		    7434240 bytes
Database mounted.
SQL> flashback database to restore point myfirstpoint; # 基于快照点，开始闪回

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> show user
USER is "SYS"
SQL> conn song/123456
Connected.
SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> archive log list;
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence	       1
SQL>
SQL>  select NAME,OPEN_MODE ,DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ON from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	CURRENT_SCN FLASHBACK_ON
--------- -------------------- ---------------- ----------- ------------------
ORCL	  READ WRITE	       PRIMARY		    2364180 YES

SQL> show user
USER is "SYS"
SQL> select to_char(systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt , dbms_flashback.get_system_change_number scn from dual;

SYSDT			   SCN
------------------- ----------
2020-09-27 15:52:26    2364190

SQL>  select * from V$FLASHBACK_DATABASE_LOG; # 查看数据库可恢复的时间点

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
	     2328783 27-SEP-20		   4320       31883264		      388988928

SQL>

闪回数据归档

闪回数据归档：使得表具有回退到过去任何时间点的能力(前面提到的闪回查询、闪回表都会受限于撤销数据是否失效，如果撤销数据被覆盖重写了，闪回操作自然会失败；闪回删除则受限于表空间是否有足够可用空间) 而闪回数据归档，则没有这些限制。

该功能实现的步骤如下：

1、创建一个用户闪回数据归档的表空间
2、创建一个保留时间为一定时间的闪回归档
3、为某一个表启用闪回归档

示例如下：

SQL> show user
USER is "SYS"
SQL> conn song/123456
Connected.
SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

# 创建一个用户闪回数据归档的表空间
SQL> create tablespace another_test1 datafile 'test1.dbf' size 20m;

Tablespace created.

# 创建一个保留时间为一年时间的闪回归档
SQL> create flashback archive test1_flahback_archive tablespace another_test1 retention 1 year;

Flashback archive created.

# 赋予用户归档的权限
SQL> grant flashback archive on test1_flahback_archive to song;

Grant succeeded.

# 为 another_test1 表启用闪回归档
SQL> alter table another_test1 flashback archive test1_flahback_archive;

Table altered.

SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

# 查哪些表已经启用了闪回数据归档
SQL> select table_name from dba_flashback_archive_tables;

TABLE_NAME
------------------------------
ANOTHER_TEST1

SQL> set line 300
SQL> col FLASHBACK_ARCHIVE_NAME for a50
SQL> col tablespace_name for a50
SQL> col quota_in_mb for a50

# 查看有关闪回数据归档所使用的表空间的信息
SQL> select flashback_archive_name,tablespace_name,quota_in_mb from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME				   TABLESPACE_NAME				      QUOTA_IN_MB
-------------------------------------------------- -------------------------------------------
TEST1_FLAHBACK_ARCHIVE				   ANOTHER_TEST1

SQL> # 查询数据库中所有的闪回数据归档
SQL> select flashback_archive_name,retention_in_days from dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME				   RETENTION_IN_DAYS
-------------------------------------------------- -----------------
TEST1_FLAHBACK_ARCHIVE						 365

SQL>

取消对于数据表的闪回归档可以使用如下命令：

alter table table_name no flashback archive;

下面来实际操作一下闪回数据归档首先记录一下SCN，从数据库表中删除部分数据：

原表数据内容如下：

SQL> select * from another_test1;

	ID NAME 	    CURTIME
---------- ---------------- ---------------------------------------------------------------------------
	 1 post 	    27-SEP-20 02.05.11.000000 PM
	 2 orac 	    27-SEP-20 02.06.02.000000 PM
	 3 mysq 	    27-SEP-20 02.06.02.000000 PM
	 4 redi 	    27-SEP-20 02.06.35.000000 PM

SQL>

通过闪回查询如下：

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
		 2367563

SQL> delete from another_test1 where id <= 2;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from another_test1 as of scn 2367563;

  COUNT(*)
----------
	 4

SQL>

其查询计划如下：

SQL> explain plan for select count(*) from another_test1 as of scn 2367563;

Explained.

SQL> select count(*) from another_test1 as of scn 2367563;

  COUNT(*)
----------
	 4

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------
Plan hash value: 3878810653

----------------------------------------------------------------------------
| Id  | Operation	   | Name	   | Rows  | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		   |	 1 |	 6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |		   |	 1 |		|	   |
|   2 |   TABLE ACCESS FULL| ANOTHER_TEST1 |	 4 |	 6   (0)| 00:00:01 |
----------------------------------------------------------------------------

9 rows selected.

SQL>

接下来执行一段代码，使UNDO数据老化并覆盖。之后再来看一下闪回数据归档发挥作用的闪回查询，通过执行计划能够看到和之前查询执行方式的不同：

SQL> begin
	delete from another_test1 where rownum < 2;
	commit;
	end;
	/  2    3    4    5  

PL/SQL procedure successfully completed.

SQL> select * from another_test1;

	ID NAME 					      CURTIME
---------- -------------------------------------------------- --------------------------------------------
	 4 redi 					      27-SEP-20 02.06.35.000000 PM

SQL> select count(*) from another_test1 as of scn 2367563;

  COUNT(*)
----------
	 4

SQL> explain plan for select count(*) from another_test1 as of scn 2367563;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 1720804579

----------------------------------------------------------------------------------------------------------
| Id  | Operation		  | Name	       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	  |		       |     1 |       |    13	 (8)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE 	  |		       |     1 |       |	    |	       |       |       |
|   2 |   VIEW			  |		       |     3 |       |    13	 (8)| 00:00:01 |       |       |
|   3 |    UNION-ALL		  |		       |       |       |	    |	       |       |       |
|   4 |     PARTITION RANGE SINGLE|		       |     2 |    52 |     3	 (0)| 00:00:01 |     1 |     1 |
|*  5 |      TABLE ACCESS FULL	  | SYS_FBA_HIST_75607 |     2 |    52 |     3	 (0)| 00:00:01 |     1 |     1 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
|*  6 |     FILTER		  |		       |       |       |	    |	       |       |       |
|*  7 |      HASH JOIN OUTER	  |		       |     1 |  2040 |    10	(10)| 00:00:01 |       |       |
|*  8 |       TABLE ACCESS FULL   | ANOTHER_TEST1      |     1 |    12 |     6	 (0)| 00:00:01 |       |       |
|*  9 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_75607 |     2 |  4056 |     3	 (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("ENDSCN">2367563 AND "ENDSCN"<=2367777 AND ("STARTSCN" IS NULL OR "STARTSCN"<=2367563))
   6 - filter("STARTSCN"<=2367563 OR "STARTSCN" IS NULL)

PLAN_TABLE_OUTPUT
----------------------------------------------------------
   7 - access("T".ROWID=CHARTOROWID("RID"(+)))
   8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
   9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>2367777) AND ("STARTSCN"(+) IS NULL OR
	      "STARTSCN"(+)<2367777))

Note
-----
   - dynamic sampling used for this statement (level=2)

30 rows selected.

SQL>

通过以上执行计划可以看到，查询闪回来自SYS_FBA_TCRV_75607系统表，该表隶属于闪回归档表空间，用于记录闪回数据：

SQL> set linesize 100
SQL> desc SYS_FBA_TCRV_75607
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 RID								VARCHAR2(4000)
 STARTSCN							NUMBER
 ENDSCN 							NUMBER
 XID								RAW(8)
 OP								VARCHAR2(1)

SQL> select count(*) from SYS_FBA_TCRV_75607;

  COUNT(*)
----------
	 3

SQL> select * from SYS_FBA_TCRV_75607;

RID		       STARTSCN     ENDSCN XID		    O
-------------------- ---------- ---------- ---------------- -
AAASe9AAEAAAAI/AAA	2367600 	   06000C0004060000
AAASe9AAEAAAAI/AAB	2367600 	   06000C0004060000
AAASe9AAEAAAAI/AAC	2368015 	   08001F0001060000

SQL>

闪回功能生成的字典对象有多个，通过查询USER_TABLES / USER_OBJECTS视图可以获得这些对象的详细信息：

SQL> select table_name,tablespace_name from user_tables where table_name like '%FBA%';

TABLE_NAME		       TABLESPACE_NAME
------------------------------ --------------------------------------------------
SYS_FBA_DDL_COLMAP_75607   ANOTHER_TEST1
SYS_FBA_TCRV_75607	       ANOTHER_TEST1
SYS_FBA_HIST_75607

SQL> select object_name,object_type from user_objects where object_name like '%FBA%';

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -------------------
SYS_FBA_TCRV_IDX_75607	       INDEX
SYS_FBA_TCRV_75607	      	   TABLE
SYS_FBA_HIST_75607	           TABLE PARTITION
SYS_FBA_HIST_75607	           TABLE
SYS_FBA_DDL_COLMAP_75607       TABLE

SQL>

还可以通过数据字典视图来查看关于闪回归档表的记录：

SQL> select * from user_flashback_archive_tables;

TABLE_NAME		       OWNER_NAME		      FLASHBACK_ARCHIVE_NAME			ARCHIVE_TABLE_NAME			STATUS
------------------------------------------------------------ ---------------------------------------------- ---------
ANOTHER_TEST1		       SONG			      TEST1_FLAHBACK_ARCHIVE			SYS_FBA_HIST_75607			ENABLED

SQL>

# 可以通过dict字典查询和闪回归档有关的数据字典表
SQL> select table_name from dict where table_name like '%FLASHBACK_ARCHIVE%';

TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES

SQL>

本文小结：

闪回查询包括基本闪回查询，闪回表等技术都依赖于撤销数据（还有一类闪回技术为闪回事务，可以对指定事务进行闪回操作，原理类似，借助于撤销数据来构建用于反转事务的SQL语句），依赖于撤销数据，则自然受限于撤销数据的保留时间，可能会由于撤销数据被覆写而导致闪回失败

闪回删除则是由于10g版本后对表的删除仅表现为一个rename操作，引入回收站的概念，但此回收站仅是当前表空间的一块逻辑划分，所以会受限于当前表空间的可用空间的限制

闪回归档可提供查询或回退到过去任意时间点的功能

闪回数据库则是一中更极端的数据库恢复功能，相当于不完整恢复，依赖于闪回日志

孤傲小二~阿沐

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle的学习心得和知识总结（一）|Oracle数据库闪回技术详解

目录结构注：提前言明本文借鉴于以下博主或网站的内容，其列表如下：1、Oracle闪回详解，点击前往2、Oracle闪回技术详解，点击前往Oracle数据库闪回技术文章快速说明索引Oracle文章快速说明索引学习目标：目的：因为接下来想在PostgreSQL上实践实现一下闪回操作，但是就目前而言 PostgreSQL尚不支持此功能。德哥的git和其他PostgreSQL社区爱好者也曾分享过一些类似的闪回简易实现，大家有兴趣也可以去看看！本文主要记录在Oracle数据库上.
复制链接

扫一扫