Flashback Database 闪回数据库功能测试

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> select flashback_on,force_logging from v$database;

FLASHBACK_ON       FORCE_LOGGING
------------------ ---------------------------------------
YES           YES

若flashback_on为“NO”，请开启flashback database,具体步骤如下：
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=3g SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='/data/oradata/ocrl/flashback ' SCOPE=BOTH;

System altered.
SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

Oracle 例程已经关闭。

SQL> startup mount;

ORACLE 例程已经启动。

Total System Global Area 849530880 bytes

Fixed Size 1377896 bytes

Variable Size 637536664 bytes

Database Buffers 205520896 bytes

Redo Buffers 5095424 bytes

数据库装载完毕。

SQL> alter database flashback on;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES
若force_logging为“NO”，请使如下SQL语句开启
SQL>alter database force logging;
2）数据库是否处于archivelog模式
SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /data/oradata/ocrl/archivelog
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
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
-------------------- -------------------
         9356807 2017-02-13 14:38:59
4.闪回数据功能测试
1）创建测试表：flash1,flash2,flash3;
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(*)
----------
     90687

SQL> select count(*) from flash2;

  COUNT(*)
----------
     90687

SQL> select count(*) from flash3;

  COUNT(*)
----------
     90687
QL> set time on
09:53:19 SQL> select sysdate from dual;

SYSDATE
-------------------
2017-02-14 09:53:30
2)truncate表flash2、drop掉表flash3
09:53:30 SQL> truncate table flash2;

Table truncated.
09:56:14 SQL> drop table flash3;

Table dropped.
3)使用Flashback Database功能进行恢复到删除前的时间点2017-02-14 09:53:30
9:56:28 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:57:01 SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size            2928968 bytes
Variable Size          524291768 bytes
Database Buffers      222298112 bytes
Redo Buffers            5455872 bytes
Database mounted.
09:57:24 SQL> flashback database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));

Flashback complete.
4)4）闪回后修复数据库两种方式之一：open read only
推荐使用这样的方法进行恢复，因为在read only方式打开之后，将需要恢复的表EXP导出，然后通过recover database将数据库恢复到原状态，再将缺失的数据IMP到数据库中。这样操作对数据库的影响可以降低到最小，可以保证其他表没有数据的丢失。

09:59:45 SQL> alter database open read only;

Database altered.

10:00:08 SQL> select count(*) from flash1;

  COUNT(*)
----------
     90687

10:00:20 SQL> select count(*) from flash2;

  COUNT(*)
----------
     90687

10:00:32 SQL> select count(*) from flash3;

  COUNT(*)
----------
     90687
取消闪回结果，恢复到闪回前状态的方法：
0:00:45 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:01:22 SQL> startup mount
ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size            2928968 bytes
Variable Size          524291768 bytes
Database Buffers      222298112 bytes
Redo Buffers            5455872 bytes
Database mounted.
10:01:35 SQL> recover database;
Media recovery complete.
10:01:44 SQL> alter database open;

Database altered.

10:01:52 SQL> select count(*) from flash1;

  COUNT(*)
----------
     90687

10:02:04 SQL> select count(*) from flash2;

  COUNT(*)
----------
     0

10:02:14 SQL> select count(*) from flash3;
select count(*) from flash3
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
可见，通过上面的recover后，数据库恢复到了闪回前的状态。
5）闪回后修复数据库两种方式之二：open resetlogs
通过open resetlogs方式打开数据库后，很显然，闪回到时间点之后的数据将全部丢失，慎用！
10:02:21 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:03:04 SQL> startup mount
ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size            2928968 bytes
Variable Size          524291768 bytes
Database Buffers      222298112 bytes
Redo Buffers            5455872 bytes
Database mounted.
10:05:05 SQL> flashback database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));

Flashback complete.

10:06:01 SQL> alter database open resetlogs;

Database altered.

10:06:25 SQL> select count(*) from flash1;

  COUNT(*)
----------
     90687


10:06:48 SQL> select count(*) from flash2;

  COUNT(*)
----------
     90687

10:06:55 SQL> select count(*) from flash3;

  COUNT(*)
----------
     90687

5.小结
这里对Flashback Database闪回数据库的语法进行总结。闪回数据库可以在SQL*Plus环境和RMAN环境下使用。
  基于时间戳进行闪回数据库操作方法：
Flashback Database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback Database to timestamp(sysdate-1/24);
  基于SCN进行闪回数据库操作方法：
Flashback Database to 1321427;