【Flashback】Flashback Database闪回数据库功能实验

来源：这里教程网时间：2026-03-03 17:24:46 作者：

Flashback Database闪回数据库为数据库的恢复提供了一种途径，上篇复习闪回删除实验，它解决了误删除个别表，其实是在表空间开辟一个空间做回收站，用来存放误删除的表空间，当然删除的表空间命名发生了变化，这个可以从select命令查出与原来表的对应关系，从这里可以看出，他的局限性，就是删除的表会被清空。本次闪回数据恰巧解决了上面的问题。 Flashback Database闪回数据库决定其闪回的时间,数据量与三个设置有关； 1、db_recovery_file_dest 闪回存在的具体目录位置； 2、db_recovery_file_dest_size 闪回存放目录存放文件大小；（先设置大小，再设置目录位置，否则会报错） 3、db_flashback_retention_target 闪回保留时间数据库默认是1440分钟=24小时当然要想使用闪回数据库功能，必须开始归档日志哦SYS@orcl>archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /orcldg_archivelogOldest online log sequence 22Next log sequence to archive 23Current log sequence 23 开启归档数据库处于mount状态 alter database archivelog 设置闪回数据库数据库处于mount状态数据库设置为归档模式设置闪回目录大小，闪回目录位置 alter system set db_recovery_file_dest_size=4g scope=both;

alter system set db_recovery_file_dest='/orcldg_flash_recovery' scope=both;

设置数据库闪回时间（一般使用默认时间： 1天） SYS@orcl>show parameter flashback; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440

上面看了闪回数据库设置方法，下面我们通过实验看到闪回库效果；

首先：在数据库上创建pos/pos 用户及密码赋予创建表、连接权限，创建it1、it2、it3 POS@orcl>select table_name,tablespace_name from user_tables where table_name like 'IT%'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ IT1 DATA IT2 DATA IT3 DATA POS@orcl>select count(*) from it1; COUNT(*) ---------- 86996 POS@orcl>select count(*)from it2; COUNT(*) ---------- 86997 POS@orcl>select count(*)from it3; COUNT(*) ---------- 86998

其次，查看现在库时间、数据库闪回最大能恢复的时间区间

POS@orcl>desc v$flashback_database_log; Name Null? Type ----------------------------------------- -------- ---------------------------- OLDEST_FLASHBACK_SCN NUMBER OLDEST_FLASHBACK_TIME DATE RETENTION_TARGET NUMBER FLASHBACK_SIZE NUMBER ESTIMATED_FLASHBACK_SIZE NUMBER POS@orcl>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. POS@orcl>select OLDEST_FLASHBACK_SCN ,OLDEST_FLASHBACK_TIME from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM -------------------- -------------------- 1408110 2022-01-17 15:13:32

POS@orcl>select sysdate from dual; SYSDATE -------------------- 2022-01-18 09:18:04

再次，删除上面it1 ,it2表空间，保留it3表空间 POS@orcl>drop table it1; Table dropped. POS@orcl>drop table it2 purge; Table dropped. POS@orcl>commit; Commit complete. 最后，闪回数据库，恢复it1,it2表，保留it3表，开始操作关闭数据库，启动数据库到exclusive模式（独占模式） SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl>startup mount exclusive; ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 939527568 bytes Database Buffers 553648128 bytes Redo Buffers 7770112 bytes Database mounted. 闪回库有两个方式：一是到指定时间点，二是指定到SCN；本次使用指定恢复到指定时间点SYS@orcl>flashback database to timestamp(to_date('2022-01-18 09:18:04','yyyy-mm-dd hh24:mi:ss'));flashback database to timestamp(to_date('2022-01-18 09:18:04','yyyy-mm-dd hh24:mi:ss'))*ERROR at line 1:ORA-38754: FLASHBACK DATABASE not started; required redo log is not availableORA-38762: redo logs needed for SCN 1425892 to SCN End-of-RedoORA-38761: redo log sequence 20 in thread 1, incarnation 4 could not beaccessed使用sqlplus下无法实现数据库闪回，显示sequence 20 in thread 1无法访问。通过使用rman方式实现库闪回 RMAN> flashback database to time="2022-01-18 09:18:04','yyyy-mm-dd hh24:mi:ss"; Starting flashback at 18-JAN-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=129 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished flashback at 18-JAN-22 打开库到只读状态 [oracle@db1 flashback]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 18 09:34:32 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl>alter database open read only; Database altered. SYS@orcl> 使用exp imp工具将原来删除的表导出，导入（无法使用expdp impdp因为数据库只读） [oracle@db1 ~]$ exp pos/pos@orcl file=it.dmp log=it.log tables=IT1,IT2 Export: Release 11.2.0.4.0 - Production on Tue Jan 18 09:37:16 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table IT1 87002 rows exported . . exporting table IT2 87001 rows exported Export terminated successfully without warnings. 关闭库，恢复到正常状态后，导入表it1 It2 SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl>startup mount; ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 939527568 bytes Database Buffers 553648128 bytes Redo Buffers 7770112 bytes Database mounted. SYS@orcl>recover database;（恢复库正常状态，it1 it2已删除，it3保留） Media recovery complete. SYS@orcl>alter database open; POS@orcl>select count(*) from it1; select count(*) from it1 * ERROR at line 1: ORA-00942: table or view does not exist POS@orcl>select count(*) from it2; select count(*) from it2 * ERROR at line 1: ORA-00942: table or view does not exist POS@orcl>select count(*)from it3; COUNT(*) ---------- 87002 导入表it1 it2 [oracle@db1 ~]$ imp pos/pos@orcl file=it.dmp log=it11.log tables=IT1,IT2 Import: Release 11.2.0.4.0 - Production on Tue Jan 18 09:40:47 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing POS's objects into POS . importing POS's objects into POS . . importing table "IT1" 87002 rows imported . . importing table "IT2" 87001 rows imported Import terminated successfully without warnings. POS@orcl>select table_name,tablespace_name from user_tables where table_name like 'IT%'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ IT1 DATA IT2 DATA IT3 DATA POS@orcl>select count(*)from it1; COUNT(*) ---------- 87002 POS@orcl>select count(*)from it2; COUNT(*) ---------- 87001 POS@orcl>select count(*)from it3; COUNT(*) ---------- 87002 表恢复正常。如果在恢复完成后使用，不导入导出数据表，使用alter database open resetlogs方式打开库，则会造成数据库数据的丢失（数据库所有文件恢复到了一个以前时间的状态，注意不要使用）总结：闪回数据库给我们提供了闪回删除以外的方式恢复数据表，是以牺牲空间为代价换回数据库的还原时间，可以在sqlplus、 rman下进行恢复操作，闪回数据库的恢复速度快于用户下的备份恢复，主要他是基于闪回日志。数据库能够闪回恢复的程度