1.开启10g的数据库闪回特征
a.确定当前的模式：
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/product/10.2.0/db_1/fl
ash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
b.启用flash database选项：
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 1279120 bytes
Variable Size 104860528 bytes
Database Buffers 58720256 bytes
Redo Buffers 2912256 bytes
Database mounted.
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.
--可以看到，flashback还必须要归档的保证
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Current log sequence 19
SQL> alter database archivelog;
Database altered.
--9i还要设置Automatic archival，10g简化了。
SQL> alter database flashback on;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
SQL> alter database open;
Database altered.
2.使用Flashback database功能
a.背景知识
启动Flashback Database之后，oracle定期将已发生变化的块写入闪回日志的日志文件中。这些日志不是由传统的Log Writer (LGWR) 过程写入，而是由一种称作Recovery Writer (RVWR)的新过程写入。这是Oracle10g的新增进程，如下：
# ps -ef|grep rvwr|grep -v grep
oracle 6028 1 0 16:42:48 ? 0:00 ora_rvwr_sjh10g
与常规的重做日志（redo logs）不同，回闪日志既不需要由DBA创建，也不需要由他们维护；它们由Oracle Managed Files（OMF）自动在闪回恢复区域所指定的目录中创建。这些文件不会归档，所以，如果在该目录发生介质故障后就不可能再进行恢复。
# pwd
/oracle/product/10.2.0/db_1/flash_recovery_area/SJH10G/flashback
# ls
o1_mf_4wmvn7f0_.flb --闪回区日志。
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
-------------------- -------------------
1383302 2009-03-25 16:42:48
--通过以上SQL我们可以知道，可以前滚恢复到的最早的SCN与时间点是多少，如果没有确保flashback database，该视图将没有查询结果。
b.创建测试数据
SQL> select sysdate from dual;
SYSDATE
-------------------
2009-03-25 17:01:55
SQL> create table sjh0 as select * from dba_users;
Table created.
SQL> select count(*) from dba_users;
COUNT(*)
----------
22
SQL> select sysdate from dual;
SYSDATE
-------------------
2009-03-25 17:03:15
SQL> create table sjh1 as select * from dba_users;
Table created.
SQL> select sysdate from dual;
SYSDATE
-------------------
2009-03-25 17:03:55
c.进行闪回操作
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 1279120 bytes
Variable Size 104860528 bytes
Database Buffers 58720256 bytes
Redo Buffers 2912256 bytes
Database mounted.
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
-------------------- -------------------
1383302 2009-03-25 16:42:48
SQL> flashback database to timestamp to_timestamp('2009-03-25 17:01:55','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> desc sjh0;
ERROR:
ORA-04043: object sjh0 does not exist
--显然在2009-03-25 17:01:55这个时间点sjh0还没有创建。所以对象不存在。
SQL> alter database close;
Database altered.
SQL> flashback database to timestamp to_timestamp('2009-03-25 17:03:18','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
SQL> alter database dismount;
Database altered.
SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1279120 bytes
Variable Size 104860528 bytes
Database Buffers 58720256 bytes
Redo Buffers 2912256 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> desc sjh0
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> desc sjh1
ERROR:
ORA-04043: object sjh1 does not exist
--恢复的时间点选在sjh1创建之前。一旦resetlogs之后，将不能再flashback的resetlogs之前的时间点。
3.使用flashback drop 功能
a.背景知识：
Oracle10g提供了flashback drop的功能。而在以前的版本中，通常只能进行不完全恢复。
Oracle 10g的flashback drop功能，允许你从当前数据库中恢复一个被drop了的对象，在执行drop操作时，现在Oracle不是真正删除它，而是将该对象自动将放入回收站。对于一个对象的删除，其实仅仅就是简单的重命名操作。所谓的回收站，是一个虚拟的容器，用于存放所有被删除的对象。在回收站中，被删除的对象将占用创建时的同样的空间，你甚至还可以对已经删除的表查询，也可以利用flashback功能来恢复它， 这个就是flashback drop功能。回收站内的相关信息可以从recyclebin，user_recyclebin，dba_recyclebin等视图中获取，或者通过SQL*Plus的show recyclebin 命令查看。
b.测试
SQL> create table sjh0 as select * from dba_users;
Table created.
SQL> select count(*) from sjh0;
COUNT(*)
----------
22
SQL> drop table sjh0;
Table dropped.
SQL> show recyclebin;
--sysdba的drop操作不会被记录。
SQL> conn sjh/sjh
Connected.
SQL> create table sjh0 as select * from dba_users;
Table created.
SQL> select count(*) from sjh0;
COUNT(*)
----------
22
SQL> drop table sjh0;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
SJH0 BIN$ZfALsiRTGCLgRAAEdupMww==$0 TABLE 2009-03-25:18:34:34
SQL> flashback table sjh0 to before drop;
Flashback complete.
SQL> desc sjh0
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> select count(*) from sjh0;
COUNT(*)
----------
22
SQL> show recyclebin;
--对象没有了，被恢复了。
SQL> drop table sjh0;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
SJH0 BIN$ZfALsiRbGCLgRAAEdupMww==$0 TABLE 2009-03-25:18:38:03
SQL> purge table sjh0; --从回收站清除。
Table purged.
SQL> show recyclebin;
--没有对象了。
SQL> create table sjh0 as select * from dba_users;
Table created.
SQL> select count(*) from sjh0;
COUNT(*)
----------
22
SQL> drop table sjh0 purge; --彻底删除一个表，不让进入回收站。
Table dropped.
SQL> show recyclebin;
--没有对象。
4.对DML操作的闪回查询
a.测试
SQL> create table sjh0 as select * from dba_users;
Table created.
SQL> select count(*) from dba_users;
COUNT(*)
----------
22
SQL> delete from sjh0;
22 rows deleted.
SQL> select count(*) from sjh0;
COUNT(*)
----------
0
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: : invalid identifier
SQL> conn sys/sys as sysdba
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1388195
SQL> select count(*) from sjh0 as of scn 1388190;
select count(*) from sjh0 as of scn 1388190
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn sjh/sjh
Connected.
SQL> select count(*) from sjh0 as of scn 1388193;
COUNT(*)
----------
0
SQL> select count(*) from sjh0 as of scn 1388190;
COUNT(*)
----------
22
--在scn 1388190的时刻数据都还在。
SQL> insert into sjh0 select * from sjh0 as of scn 1388190;
22 rows created.
SQL> select count(*) from sjh0;
COUNT(*)
----------
22
5.使用Flashback version Query
闪回版本查询的测试：
SQL> create table sjh0 as select username,user_id from dba_users;
Table created.
SQL> desc sjh0;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
SQL> select * from sjh0;
USERNAME USER_ID
------------------------------ ----------
MGMT_VIEW 53
SYS 0
SYSTEM 5
DBSNMP 24
SYSMAN 51
SJH 55
OUTLN 11
MDSYS 46
ORDSYS 43
CTXSYS 36
ANONYMOUS 39
USERNAME USER_ID
------------------------------ ----------
EXFSYS 34
DMSYS 35
WMSYS 25
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
MDDATA 50
DIP 19
SCOTT 54
TSMSYS 21
22 rows selected.
--以下执行一些DML语句：
SQL> delete from sjh0 where username='SYS';
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> DELETE FROM sjh0 where username='SYSTEM';
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> UPDATE sjh0 set user_id=0 where username='SJH';
1 row updated.
SQL> commit;
Commit complete.
SQL> update sjh0 set user_id=1 where username='SCOTT';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from sjh0;
USERNAME USER_ID
------------------------------ ----------
MGMT_VIEW 53
DBSNMP 24
SYSMAN 51
SJH 0
OUTLN 11
MDSYS 46
ORDSYS 43
CTXSYS 36
ANONYMOUS 39
EXFSYS 34
DMSYS 35
USERNAME USER_ID
------------------------------ ----------
WMSYS 25
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
MDDATA 50
DIP 19
SCOTT 1
TSMSYS 21
20 rows selected.
--执行Flashback version Query
SQL> select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, username,user_id from sjh0 versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME USER_ID
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ------------------------------ ----------
26-MAR-09 01.58.03 PM 02001B0010030000 U SCOTT 1
26-MAR-09 01.57.21 PM 01002600CF020000 U SJH 0
26-MAR-09 01.56.12 PM 07000F00C5020000 D SYSTEM 5
26-MAR-09 01.55.48 PM 0A001700C0020000 D SYS 0
MGMT_VIEW 53
26-MAR-09 01.55.48 PM SYS 0
26-MAR-09 01.56.12 PM
--为了不显得格式凌乱，后面的记录省略。。。
--下面我们利用VERSIONS_XID来查询undo语句，需要用到sys/system用户。最后利用这些undo来撤销不同版本的事物。
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='0A001700C0020000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYS','0');
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='07000F00C5020000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYSTEM','5');
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='01002600CF020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SJH"."SJH0" set "USER_ID" = '55' where ROWID = 'AAAM3JAAEAAAABMAAF';
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='02001B0010030000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SJH"."SJH0" set "USER_ID" = '54' where ROWID = 'AAAM3JAAEAAAABMAAU';
SQL> insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYS','0');
1 row created.
SQL> insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYSTEM','5');
1 row created.
SQL> update "SJH"."SJH0" set "USER_ID" = '55' where ROWID = 'AAAM3JAAEAAAABMAAF';
1 row updated.
SQL> update "SJH"."SJH0" set "USER_ID" = '54' where ROWID = 'AAAM3JAAEAAAABMAAU';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from sjh0;
USERNAME USER_ID
------------------------------ ----------
MGMT_VIEW 53
DBSNMP 24
SYSMAN 51
SJH 55
OUTLN 11
MDSYS 46
ORDSYS 43
CTXSYS 36
ANONYMOUS 39
EXFSYS 34
DMSYS 35
USERNAME USER_ID
------------------------------ ----------
WMSYS 25
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
MDDATA 50
DIP 19
SCOTT 54
TSMSYS 21
SYS 0
SYSTEM 5
22 rows selected.
--完成事务的撤销。
--Flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图。这个视图来源于x$ktuqqry表且xid字段上不存在索引，所以走的是全表扫描。x$ktuqqry的查询非常耗时，所以请注意评估你的恢复成本。
