浅谈ORACLE数据库闪回

Oracle闪回技术包括闪回删除和闪回数据库。闪回删除主要是关注用户误删除表、索引的数据库对象；闪回数据库是一种快速的数据库恢复方案，这种恢复是基于用户的逻辑错误，比如对表中的数据做了错误的修改、插入了大量的错误数据。

闪回删除：

[oracle@localhost ~]$ sqlplus test/test@orcl      

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 9 16:50:00 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>show parameter recyclebin;

NAME             TYPE         VALUE
----------------  -----------   ------------ 

recyclebin    string        on

SQL> drop table student;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME     RECYCLEBIN NAME                         OBJECT TYPE     DROP TIME
---------------- -       -----------------------------                        ------------          -------------------
STUDENT   BIN$+2ROU1DktpLgQKjAkwBc6A==$0       TABLE              2014-06-09:16:50:41

SQL> select object_name,original_name,operation from recyclebin;

OBJECT_NAME                                           ORIGINAL_NAME          OPERATION
-------------------------------------------------          ---------------               ---------------
BIN$+2ROU1DltpLgQKjAkwBc6A==$0            STUDENT                 DROP

SQL> select * from tab;

TNAME                                                              TABTYPE              CLUSTERID
---------------------------------------------            ----------------          -------------------
BIN$+2ROU1DltpLgQKjAkwBc6A==$0         TABLE

SQL> flashback table student to before drop;

Flashback complete.

SQL> select * from tab;

TNAME                         TABTYPE           CLUSTERID
-------------------         -----------------         ---------------------
STUDENT                      TABLE

SQL> select * from student;

       SNO          SNAME
----------      ---------------
  1                  xxx
 11                aa

 

 也可以执行下面语句进行闪回：

flashback table"BIN$+2ROU1DktpLgQKjAkwBc6A==$0" to before drop rename to student;

 

 

闪回数据库：

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 9 16:50:00 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

查看数据库是否开启闪回
SQL> select flashback_on from v$database;

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

开启数据库闪回报错，提示需要在mount下开启
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
FixedSize                 1218412 bytes
VariableSize             83888276 bytes
Database Buffers          96468992 bytes
RedoBuffers               2973696 bytes
Database mounted.

数据库在mount状态下再次尝试开启数据库的闪回功能，又返回错误信息，需要开启归档。
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

开启归档
SQL> alter database archivelog;

Database altered.
SQL> archive log list
Database logmode             Archive Mode
Automaticarchival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     59
Next log sequence to archive   61
Current logsequence           61

成功开启数据库闪回

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

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

查看闪回区信息，默认安装大小为2G，根据数据库的具体情况作修改。
SQL> show parameter recover_file_dest

NAME                                            TYPE                          VALUE
------------------------------------     -----------                 ------------------------------
db_recovery_file_dest               string                 /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size       big integer                20G

数据库可以恢复到多少分钟以前，默认1440分钟（一天）
SQL> show parameter flash

NAME                                                     TYPE        VALUE
------------------------------------             -----------    --------------
db_flashback_retention_target       integer     1440

SQL> alter database open;

Database altered.

查看数据库能恢复到最早的scn和最早的时间
SQL> select oldest_flashback_scn,oldest_flashback_time fromv $flashback_database_log;

OLDEST_FLASHBACK_SCN       OLDEST_FLASHBACK_TIME
--------------------                            ---------------------
            1021372                           20101130 23:07:18

SQL> select * from student;

    SNO         SNAME
----------    ---------------
  1                xxx
 11               aa

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    6312445

SQL> insert into student values(22,'nn');

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    6312458

通过v$flashback_database_log和v$flashback_database_stat视图查询相关闪回数据库信息

SQL> selectoldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN               OLDEST_FLASHBACK_TIME
--------------------                                   ---------------
      6259583                                       09-JUN-14

SQL> select * from v$flashback_database_stat;

BEGIN_TIME                   END_TIME      FLASHBACK_DATA     DB_DATA     REDO_DATA      ESTIMATED_FLASHBACK_SIZE
---------------                     ---------------          --------------                 ----------        ----------                  ------------------------
10-JUN-14 10-JUN-14                                 5529600                  7790592         2463744

关闭数据库并启动到mount状态，执行闪回，并以resetlogs打开数据库

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

Total System Global Area 1224736768 bytes
Fixed Size      2083560 bytes
Variable Size    335545624 bytes
Database Buffers   872415232 bytes
Redo Buffers     14692352 bytes
Database mounted.
SQL> flashback database to scn 6312445;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

备注：

1、无论是OS认证还是密码认证，以sysdba登陆默认用户是sys，sys用户的默认表空间是system，system与sysaux表空间无法做回收站的操作

2、执行表闪回删除后，与表相关联的对象也会被闪回，但是对象名已经发生变化，需要重命名或者重建对象

3、只要启动了闪回数据库特性，数据库的永久表空间都会受到闪回数据库保护，如果不希望某个表空间受闪回数据库保护，可以禁用对某个表空间的闪回特性

alter tablespace users flashback off;

4、在闪回数据库时，往往要用到具体的时间或者scn，我们可以定义容易记忆的复原点（scn的别名）来执行闪回，步骤如下

     a. 创建一个复原点

 SQL> create restore point rp0;

Restore point created.

    b. 创建有保证的复原点

SQL> create restore point rp1 guarantee flashbackdatabase;

Restore point created.

     c. 查询复原点信息

SQL> selectname,scn,storage_size,guarantee_flashback_database from v$restore_point;

NAME      SCN       STORAGE_SIZE    GUARANTEE
---------- ------------    ---------------------      ---------------------
RP1       6316026     31883264                    YES

RP0       6322551         0                               NO