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 Mode

Automatic archival Enabled

Archive destination /orcldg_archivelog

Oldest online log sequence 22

Next log sequence to archive 23

Current 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 available

ORA-38762: redo logs needed for SCN 1425892 to SCN End-of-Redo

ORA-38761: redo log sequence 20 in thread 1, incarnation 4 could not be

accessed

使用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

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

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

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 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下进行恢复操作，闪回数据库的恢复速度快于用户下的备份恢复，主要他是基于闪回日志。数据库能够闪回恢复的程度

取决于闪回空间大小闪回空间不断循环的使用，而闪回保留策略决定了闪回日志的保留长度。

总之，合理的平衡恢复速度和可使用空间之间的关系。

ITPub博客

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

最新文章