Flashback Database

Flashback Database命令可以在RMAN和SQL*PLUS里面执行：

[root@linux5 ~]# su - oracle
[oracle@linux5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 2 00:18:06 2014

Copyright (c) 1982,2005,Oracle.? All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area? 167772160 bytes
Fixed Size????????????????? 1218316 bytes
Variable Size????????????? 75499764 bytes
Database Buffers?????????? 88080384 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
Database opened.
SQL> host clear

查看是否启动Flash Recovery Area（分配充足，容纳数据文件、控制文件、重做日志文件、备份文件、增量备份文集、尚未备份的归档文件、Flashback Logs……）
SQL> show parameter db_recover

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest??????????????? string????? /u01/app/oracle/flash_recovery
???????????????????????????????????????????????? _area
db_recovery_file_dest_size?????????? big integer 2G

检查是否启用归档：
SQL> archive log list
Database log mode????????????? Archive Mode
Automatic archival???????????? Enabled
Archive destination??????????? /u01/arch_orcl
Oldest online log sequence???? 57
Next log sequence to archive?? 59
Current log sequence?????????? 59

检查是否启用Flashback Database和Force Logging
SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON?????? FOR
------------------ ---
NO???????????????? YES

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? 167772160 bytes
Fixed Size????????????????? 1218316 bytes
Variable Size????????????? 75499764 bytes
Database Buffers?????????? 88080384 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON?????? FOR
------------------ ---
YES??????????????? YES

SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
?????? *
ERROR at line 1:
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

SQL> alter database open;

Database altered.

查询当前的SCN

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
???????????????? 1249964

SQL> conn scott/oracle
Connected.
SQL> drop table temp purge;

Table dropped.

SQL> drop table flash_tbl purge;

Table dropped.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area? 167772160 bytes
Fixed Size????????????????? 1218316 bytes
Variable Size????????????? 75499764 bytes
Database Buffers?????????? 88080384 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.

恢复数据库：
SQL> flashback database to SCN 1249964;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

执行完Flashback Database命令之后，有两种打开方式：open resetlogs和read only。

open resetlogs：指定SCN之后的数据都会丢失。

read only ：打开数据库之后，通过逻辑导出误操作的表的数据，再执行recover database命令以重新应用数据库产生的REDO，将数据库修复到Flashback Database操作前的状态，然后通过逻辑导入的方式，将之前误操作的表重新导入，这样就不会有数据丢失了。
SQL> alter database open resetlogs;

Database altered.

SQL> conn scott/oracle
Connected.
SQL> select * from tab where tname in('flash_tbl','temp');

no rows selected

SQL> select * from tab where tname in('flash_tbl','temp');

no rows selected

SQL> select * from tab where tname in('FLASH_TBL','TEMP');

TNAME????????????????????????? TABTYPE? CLUSTERID
------------------------------ ------- ----------
FLASH_TBL????????????????????? TABLE
TEMP?????????????????????????? TABLE

?恢复操作成功……