CNDBA社区
Flashback Database 闪回数据库功能测试
1.使用Flashback Database的前提条件
1）启用了flashback database
2）必须打开flash recovery area,若为RAC，flash recovery area必须位于共享存储中。
3）必须处于archivelog模式，开启FORCE LOGGING
2.确认上面的前提条件是否满足
1）验证是否启用了flashback database并确认FORCE LOGGIN是否开启
SQL> col flashback_on for a20
SQL> col force_logging for a20
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
-------------------- --------------------
NO NO
若flashback_on为“NO”，请开启flashback database,具体步骤如下：
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=4G scope=BOTH;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='/oradata/flashback' SCOPE=BOTH;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 9141328 bytes
Variable Size 1358954496 bytes
Database Buffers 1124073472 bytes
Redo Buffers 24412160 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
--------------------
YES
若force_logging为“NO”，请使如下SQL语句开启
SQL> select force_logging from v$database;
FORCE_LOGGING
--------------------
NO
SQL> alter database force logging;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 439
Next log sequence to archive 441
Current log sequence 441
3.确认数据库可以前滚到的SCN和Time的方法
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
23600863 2022-08-25 21:54:36
SQL> create table flash1 as select * from dba_objects;
Table created.
SQL> create table flash2 as select * from flash1;
Table created.
SQL> create table flash3 as select * from flash1;
Table created.
SQL> select count(*) from flash1;
COUNT(*)
----------
73536
SQL> select count(*) from flash2;
COUNT(*)
----------
73536
SQL> select count(*) from flash3;
COUNT(*)
----------
73536
SQL> set time on
21:59:11 SQL> select sysdate from dual;
SYSDATE
-------------------
2022-08-25 21:59:20
21:59:20 SQL> truncate table flash2;
Table truncated.
21:59:32 SQL> drop table flash3;
Table dropped.
3)使用Flashback Database功能进行恢复到删除前的时间点2022-08-25 21:59:20
21:59:41 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:01:57 SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 9141328 bytes
Variable Size 1358954496 bytes
Database Buffers 1124073472 bytes
Redo Buffers 24412160 bytes
Database mounted.
22:02:11 SQL> flashback database to timestamp(to_date('2022-08-25 21:59:20','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
闪回后修复数据库两种方式之一：open read only
推荐使用这样的方法进行恢复，因为在read only方式打开之后，将需要恢复的表EXP导出，然后通过recover database将数据库恢复到原状态，再将缺失的数据IMP到数据库中。这样操作对数据库的影响可以降低到最小，可以保证其他表没有数据的丢失。
22:03:05 SQL> alter database open read only;
Database altered.
22:04:49 SQL> select count(*) from flash1;
COUNT(*)
----------
73536
22:04:58 SQL> select count(*) from flash2;
COUNT(*)
----------
73536
22:05:04 SQL> select count(*) from flash3;
COUNT(*)
----------
73536
取消闪回结果，恢复到闪回前状态的方法：
22:05:07 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:06:25 SQL> startup mount
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 9141328 bytes
Variable Size 1358954496 bytes
Database Buffers 1124073472 bytes
Redo Buffers 24412160 bytes
Database mounted.
22:07:48 SQL> recover database;
Media recovery complete.
22:08:44 SQL> alter database open;
Database altered.
22:08:57 SQL> select count(*) from flash1;
COUNT(*)
----------
73536
22:09:42 SQL> select count(*) from flash2;
COUNT(*)
----------
0
22:09:47 SQL> select count(*) from flash3;
select count(*) from flash3
*
ERROR at line 1:
ORA-00942: table or view does not exist
可见，通过上面的recover后，数据库恢复到了闪回前的状态。
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle
