SQL> select NAME,FLASHBACK_ON from v$database;

NAME      FLASHBACK_ON

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

SZSCDB    NO

oracle@rac1:/home/oracle>srvctl stop database -d szscdb

oracle@rac1:/home/oracle>srvctl start database -d szscdb -o mount

SQL> ALTER DATABASE ARCHIVELOG;

oracle@rac1:/home/oracle>srvctl stop database -d szscdb

oracle@rac1:/home/oracle>srvctl start database -d szscdb -o mount

SQL> ALTER DATABASE FLASHBACK ON;

ALTER DATABASE FLASHBACK ON

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38709: Recovery Area is not enabled

oracle@rac2:/home/oracle>oerr ora 38709

38709, 00000, "Recovery Area is not enabled."

// *Cause:  An ALTER DATABASE FLASHBACK ON command failed because the

//          Recovery Area was not enabled.

// *Action: Set DB_RECOVERY_FILE_DEST to a location and retry.

SQL>  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G SCOPE=BOTH SID='*';

System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+DATA' SCOPE=BOTH SID='*';  

System altered.

SQL> ALTER DATABASE FLASHBACK ON;

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

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

YES

FROM ALERT.log

ALTER DATABASE FLASHBACK ON

Starting background process RVWR

Fri Aug 02 21:08:06 2013

RVWR started with pid=21, OS id=2912

Allocated 3981204 bytes in shared pool for flashback generation buffer

Flashback Database Enabled at SCN 958062

Completed: ALTER DATABASE FLASHBACK ON

SQL> SELECT * FROM v$sgastat s

  2  WHERE s.POOL='shared pool' AND s.BYTES='3981204';

POOL         NAME                            BYTES

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

shared pool  KCB Table Scan Buffer         3981204

shared pool  flashback generation buff     3981204

SQL> ALTER SYSTEM SET =3 SCOPE=BOTH;

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') CURRENT_TIME FROM DUAL;

CURRENT_TIME

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

2013-08-02 21:23:41

Wait three mins….

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') CURRENT_TIME FROM DUAL;

CURRENT_TIME

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

2013-08-02 21:28:56

SQL> TRUNCATE TABLE T;

SQL> shutdown immediate;

SQL> startup mount;

SQL>

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

     72733

使用flashback drop需要开启recyclebin功能，该功能在10g中是默认开启的

本次试验同时验证flashback drop后，索引，约束是否还在

SYSTEM表空间对象不支持flashback功能

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /RECO/arch/szscdb

Oldest online log sequence     2

Current log sequence           4

----回收站的功能已经开启

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

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

recyclebin                           string      on

SQL> conn hr/oracle

-----创建表，创建主键及索引

CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

ALTER  TABLE T ADD CONSTRAINT T_PK_OBJID primary key (OBJECT_ID);

CREATE INDEX T_OBJNAME ON T(OBJECT_NAME);

SQL> SELECT i.INDEX_NAME,i.TABLE_NAME

    FROM USER_INDEXES i

    WHERE i.TABLE_NAME='T';

INDEX_NAME                     TABLE_NAME

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

T_OBJNAME                      T

T_PK_OBJID                     T

---删除表T

SQL> DROP TABLE T;

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> SELECT r.object_name,r.original_name,r.droptime

    FROM DBA_RECYCLEBIN r

    WHERE r.owner='HR';

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$41s39ZfTC5jgQwEAAH+pHg==$0 T_OBJNAME                        2013-08-07:19:55:32

BIN$41s39ZfUC5jgQwEAAH+pHg==$0 T_PK_OBJID                       2013-08-07:19:55:32

BIN$41s39ZfVC5jgQwEAAH+pHg==$0 T                                2013-08-07:19:55:32

SQL> SELECT COUNT(*) FROM "BIN$41s39ZfVC5jgQwEAAH+pHg==$0";

  COUNT(*)

----------

     56323

SQL> FLASHBACK TABLE T TO BEFORE DROP;

Flashback complete.

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

     56323

SQL> SELECT r.object_name,r.original_name,r.droptime

  2      FROM DBA_RECYCLEBIN r

  3      WHERE r.owner='HR';

no rows selected

SQL> SELECT i.INDEX_NAME,i.TABLE_NAME

  2      FROM USER_INDEXES i

  3      WHERE i.TABLE_NAME='T';

INDEX_NAME                     TABLE_NAME

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

BIN$41s39ZfTC5jgQwEAAH+pHg==$0 T

BIN$41s39ZfUC5jgQwEAAH+pHg==$0 T

-------表和索引及主键都被闪回了，名字有变化不影响使用

SQL> show user

USER is "HR"

CREATE TABLE T (ID INT,NAME VARCHAR2(20));

SQL> SELECT dbms_flashback.get_system_change_number SCN,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "DATE" FROM DUAL;

       SCN DATE

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

   1001369 2013-08-07 20:46:25

--插入测试数据

INSERT INTO T VALUES(1,'SZSCKJ');

INSERT INTO T VALUES(2,'SZSCKJ');

INSERT INTO T VALUES(3,'SZSCKJ');

INSERT INTO T VALUES(4,'SZSCKJ');

INSERT INTO T VALUES(5,'SZSCKJ');

COMMIT;

SQL> SELECT dbms_flashback.get_system_change_number SCN,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "DATE" FROM DUAL;

       SCN DATE

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

   1001455 2013-08-07 20:46:50

--删除测试数据

DELETE FROM T

WHERE ID>3;

COMMIT;

SQL> SELECT dbms_flashback.get_system_change_number SCN,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "DATE" FROM DUAL;

       SCN DATE

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

   1001469 2013-08-07 20:47:12

---执行flashback table

SQL> FLASHBACK TABLE T TO SCN  1001455;

FLASHBACK TABLE T TO SCN  1001455

                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

SQL> ALTER TABLE T ENABLE ROW MOVEMENT;

SQL> FLASHBACK TABLE T TO SCN  1001455;

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

         5

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

         3

SQL> FLASHBACK TABLE T TO TIMESTAMP TO_DATE('2013-08-07 20:46:25','YYYY-MM-DD HH24:MI:SS');

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

         0

----可以看到在特定的时间内可以随便闪

----flashback table 不支持sys用户的表，也不支持跨DDL语句

SQL> SELECT * FROM T AS OF SCN 1001455;

        ID NAME

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

         1 SZSCKJ

         2 SZSCKJ

         3 SZSCKJ

         4 SZSCKJ

         5 SZSCKJ

SQL> CREATE TABLE T_HISTORY AS SELECT * FROM T AS OF SCN ;

----创建中间表T_HISTORY插入SCN为1001455时刻的数据

SQL> alter database add supplemental log data;

SQL> CREATE TABLE ZGH AS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<6;

SQL> DELETE FROM ZGH WHERE OBJECT_ID<20;

Commit complete.

SELECT VERSIONS_STARTSCN,VERSIONS_ENDSCN,VERSIONS_XID,

FROM SYS.ZGH VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V

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

          1026142                 D

                          1026142

通过该视图可以查询到作用于该表T的事物，并且可以看到事物的开始SCN，结束SCN以及事物的操作类型VERSIONS_OPERATION。可以通过获得的XID，利用flashback transaction来闪回该事物。

注：在oracle 11g中需要开启supplemental log data才可以得到undo SQL;