浅谈ORACLE数据库闪回
oracle 闪回技术包括闪回删除和闪回数据库。闪回删除主要是关注用户误删除表、索引的数据库对象；闪回数据库是一种快速的数据库恢复方案，这种恢复是基于用户的逻辑错误，比如对表中的数据做了错误的修改、插入了大量的错误数据。 闪回删除： [oracle@localh
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 NAMEOBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
STUDENT BIN$+2ROU1DktpLgQKjAkwBc6A==$0 TABLE 2014-06-09:16:50:41
SQL> select object_name,original_name,operationfrom 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
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> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
开启归档，默认的归档目录：USE_DB_RECOVERY_FILE_DEST。
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> 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
查看数据库能恢复到最早的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_FLASHBAC
-------------------- ---------------
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、使用sysdba身份登陆，通过dba_recyclebin可以查询回收站里所有的对象，也可以指定条件查询
2、无论是OS认证还是密码认证，以sysdba登陆默认用户是sys，sys用户的默认表空间是system，system与sysaux表空间无法做回收站的操作
3、执行表闪回后，与表相关联的对象也会被闪回，但是对象名已经发生变化，需要重命名或者重建对象
4、只要启动了闪回数据库特性，数据库的永久表空间都会受到闪回数据库保护，如果不希望某个表空间受闪回数据库保护，可以禁用对某个表空间的闪回特性
alter tablespace users flashback off;
5、在闪回数据库时，往往要用到具体的时间或者scn，我们可以定义容易记忆的复原点（scn的别名）来执行闪回，步骤如下
5.1、创建一个复原点
SQL> create restore point rp0;
Restore point created.
5.2、创建有保证的复原点
SQL> create restore point rp1 guarantee flashbackdatabase;
Restore point created.
5.3、查询复原点信息
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