测试目的：通过实验，初次体会闪回技术能做些什么

1.闪回查询（select …… as of）

1.1环境准备

 [oracle@secdb1 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 30 16:48:01 2012

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

 Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

 SQL> create user xiao identified by xiao;

 User created.

 SQL> grant dba to xiao;

 Grant succeeded.

 SQL> conn xiao/xiao

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL> create table xiao(id int,name varchar2(20));

 Table created.

SQL> begin

  2  for i in 1..10 loop

  3  insert into xiao values(i,'xiao');

  4  end loop;

  5  commit;

  6  end;

  7  /

 PL/SQL procedure successfully completed.

 SQL> select * from xiao;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 Session altered.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-30 17:12:20

 SQL> select dbms_flashback.get_system_change_number from dual;

 GET_SYSTEM_CHANGE_NUMBER

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

                  255936

SQL>

 1.2基于时间闪回误删除的数据

SQL> delete from xiao where id<3;

 2 rows deleted.

 SQL> commit;

Commit complete.

 SQL> select * from xiao;

         ID NAME

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

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 8 rows selected.

 SQL> SELECT * FROM xiao AS OF TIMESTAMP

  2  TO_TIMESTAMP('2012-12-30 17:12:20', 'YYYY-MM-DD HH24:MI:SS');

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL> insert into xiao

  2  (select * from xiao as of timestamp

  3  to_timestamp('2012-12-30 17:12:20','yyyy-mm-dd hh24:mi:ss')

  4  where id<3);

 2 rows created.

SQL> select * from xiao;

         ID NAME

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

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

         1 xiao

         2 xiao

 10 rows selected.

 SQL> commit;

 Commit complete.

 SQL>

1.3基于SCN闪回误删除的数据

SQL> select * from xiao;

         ID NAME

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

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 

8 rows selected.

 SQL> select * from xiao as of scn 255936;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

SQL> insert into xiao

  2  (select * from xiao as of scn 255936 where id<3);

 2 rows created.

 SQL> select * from xiao;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL> commit;

 Commit complete.

 SQL>

2.闪回版本查询

2.1基于时间的闪回版本查询

SQL> select * from xiao;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-30 21:49:46

 SQL> select dbms_flashback.get_system_change_number from dual;

 GET_SYSTEM_CHANGE_NUMBER

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

                  263253

SQL> update xiao set name='hai' where id=1;

 1 row updated.

 SQL> commit;

 Commit complete.

 SQL> insert into xiao values(11,'xiao');

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> delete from xiao where id=11;

 1 row deleted.

 SQL> commit;

 Commit complete.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-30 21:57:05

 SQL> select dbms_flashback.get_system_change_number from dual;

 GET_SYSTEM_CHANGE_NUMBER

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

                  263408

 SQL> set pages 999 linesize 300

SQL> col VERSIONS_STARTTIME format a25

SQL> col VERSIONS_ENDTIME format a25

SQL> SELECT versions_startscn, versions_starttime,

  2  versions_endscn, versions_endtime,

  3  versions_xid, versions_operation,

  4  id,name

  5  FROM xiao

  6  VERSIONS BETWEEN TIMESTAMP

  7  TO_TIMESTAMP('2012-12-30 21:49:46', 'YYYY-MM-DD HH24:MI:SS')

  8  AND TO_TIMESTAMP('2012-12-30 21:57:05', 'YYYY-MM-DD HH24:MI:SS');

2.2基于SCN的闪回版本查询

SQL> SELECT versions_startscn, versions_starttime,

  2  versions_endscn, versions_endtime,

  3  versions_xid, versions_operation,

  4  id,name

  5  FROM xiao

  6  VERSIONS BETWEEN scn

  7  minvalue and maxvalue;

 

3.闪回事务查询

3.1基于闪回事务进行恢复

SQL> select * from xiao;

 

        ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 

10 rows selected.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-30 22:36:26

 SQL> select dbms_flashback.get_system_change_number from dual;

 GET_SYSTEM_CHANGE_NUMBER

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

                  264562

SQL> update xiao set name='hai' where id=2;

 1 row updated.

 SQL> insert into xiao values(11,'xiao');

 1 row created.

 SQL> delete from xiao where id=11;

 1 row deleted.

 SQL> commit;

 Commit complete.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-30 22:41:28

 SQL> select dbms_flashback.get_system_change_number from dual;

 GET_SYSTEM_CHANGE_NUMBER

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

                  264678

 SQL> SELECT versions_startscn, versions_starttime,

  2  versions_endscn, versions_endtime,

  3  versions_xid, versions_operation,

  4  id,name

  5  FROM xiao

  6  VERSIONS BETWEEN scn

  7  minvalue and maxvalue;

SQL> insert into "XIAO"."XIAO"("ID","NAME") values ('11','xiao');

 1 row created.

 SQL> delete from "XIAO"."XIAO" where ROWID = 'AAACZtAAHAAAAAPAAL';

 0 rows deleted.

 SQL> update "XIAO"."XIAO" set "NAME" = 'xiao' where ROWID = 'AAACZtAAHAAAAAPAAB';

 1 row updated.

 SQL> commit;

 Commit complete.

 SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

        11 xiao

 

11 rows selected.

 

SQL>

4.闪回表

4.1基于时间的闪回表

SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

        11 xiao

 11 rows selected.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-31 17:17:27

 SQL> delete from xiao where id=11;

 1 row deleted.

 SQL> commit;

 Commit complete.

 SQL> update xiao set name='hai' where id=4;

 1 row updated.

 SQL> commit;

 Commit complete.

 SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 hai

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL> alter table xiao enable row movement;

 Table altered.

 SQL> flashback table xiao to timestamp to_timestamp('2012-12-31 17:17:27','yyyy-mm-dd hh24:mi:ss');

 Flashback complete.

 SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

        11 xiao

 11 rows selected.

 SQL>

4.2基于SCN的闪回表

SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

        11 xiao

 11 rows selected.

 SQL> select dbms_flashback.get_system_change_number from dual;

 GET_SYSTEM_CHANGE_NUMBER

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

                  266708

 SQL> delete from xiao where id=11;

 1 row deleted.

 SQL> commit;

 Commit complete.

 SQL> update xiao set name='hai' where id=2;

 1 row updated.

 SQL> commit;

 Commit complete.

 SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 hai

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL> alter table xiao enable row movement;

 Table altered.

 SQL> flashback table xiao to scn 266708;

 Flashback complete.

 SQL> select * from xiao;

         ID NAME

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

         1 hai

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

        11 xiao

 11 rows selected.

 SQL>

5.闪回数据库

5.1配置闪回数据库

[oracle@secdb2 ~]$ mkdir -p /home/oracle/flash

[oracle@secdb2 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 31 18:14:24 2012

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

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

 SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch

Oldest online log sequence     21

Current log sequence           23

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 SQL> alter system set db_recovery_file_dest_size=1g;

 System altered.

 SQL> alter system set db_recovery_file_dest='/home/oracle/flash';

 System altered.

 SQL> alter system set db_flashback_retention_target=2880;

 System altered.

 SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     21

Current log sequence           23

 SQL> alter database archivelog;

 Database altered.

 SQL> alter database flashback on;

 Database altered.

 SQL> alter database open;

 Database altered.

 SQL> select flashback_on from v$database;

 FLASHBACK_ON

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

YES

 SQL>

5.2基于时间的闪回数据库恢复表

SQL> select * from xiao;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 

10 rows selected.

 SQL> select sysdate from dual;

 SYSDATE

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

2012-12-31 19:12:37

SQL> truncate table xiao;

 Table truncated.

 SQL> select * from xiao;

 no rows selected

 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  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size             125830736 bytes

Database Buffers          155189248 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> flashback database to timestamp to_timestamp('2012-12-31 19:12:37','yyyy-mm-dd hh24:mi:ss');

 Flashback complete.

 SQL> alter database open read only;

 Database altered.

 SQL> select * from xiao.xiao;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL>

 

5.3基于SCN的闪回数据库恢复表

SQL> select * from xiao;

 

        ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 

10 rows selected.

 

SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

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

                  413154

 SQL> truncate table xiao;

 Table truncated.

 SQL> select * from xiao;

 no rows selected

 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  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size             125830736 bytes

Database Buffers          155189248 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> flashback database to scn 413154;

 Flashback complete.

 SQL> alter database open read only;

 Database altered.

 SQL> select * from xiao.xiao;

         ID NAME

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

         1 xiao

         2 xiao

         3 xiao

         4 xiao

         5 xiao

         6 xiao

         7 xiao

         8 xiao

         9 xiao

        10 xiao

 10 rows selected.

 SQL>

6闪回删除