ITPub博客

首页 > 数据库 > Oracle > 闪回技术功能测试

闪回技术功能测试

原创 Oracle 作者:fengpinDBA 时间:2013-11-22 11:32:06 0 删除 编辑
测试体会:在学习一门新技术之前,对于这门技术的初次感观体验是非常重要的,这里的感观体验是指亲自尝试一门新技术所能实现的功能和达到的效果,这样你才能更加直观的了解这是什么,能干什么。
测试目的:通过实验,初次体会闪回技术能做些什么

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闪回删除

 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> drop table xiao.xiao;

Table dropped.

 SQL> drop table xiao.xiao;

select * from xiao.xiao
                               *
ERROR at line 1:
ORA-00942:table or view does not exist

SQL> flashback table xiao.xiao to before drop rename to xiao.X;

Flashback complete.

 SQL> select * from xiao.X;

         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.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29339097/viewspace-777311/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-11-22

  • 博文量
    22
  • 访问量
    923059