ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用户误操作的恢复之FLASHBACK

用户误操作的恢复之FLASHBACK

原创 Linux操作系统 作者:DBA_oracle_java 时间:2013-08-08 10:28:26 0 删除 编辑

用户误操作的恢复之FLASHBACK

Oracle中难免会遇到用户的误操作,如误删除/修改大量的数据并且已经提交。没有提交可以在事物一级使用rollback来回滚未提交的事物。

恢复用户的误操作的一般方式有如下几种:

1.flashback

flashback最早是oracle 9i开始引入的,好处多多。

1.1 flashback database

Flashback database的功能从oracle 10g开始引入。默认是关闭的如需启动,需将数据库置入归档模式,在mount状态下修改。

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

flashback database能闪回的时间长度受DB_FLASHBACK_RETENTION_TARGET控制,其默认值为1400(min),正好是一天。

DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far

back in time the database may be flashed back. How far back one can flashback a

database depends on how much flashback data Oracle has kept in the fast recovery

area.

该参数来控制flashback log的存在与否的,当然需要配合DB_RECOVERY_FILE_DEST_SIZE参数来衡量。如果产生的flashback log的大小大于DB_RECOVERY_FILE_DEST_SIZE所指定的值,那么oracle可以保证的是你可以恢复到DB_FLASHBACK_RETENTION_TARGET所指定的时间点,因为他要删除文件了。换句话说,oracle可以保证你的数据至少可以恢复到DB_FLASHBACK_RETENTION_TARGET参数所指定的时间内。

 

SQL> SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET

 

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer     1440

 

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=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> FLASHBACK DATABASE TO TIMESTAMP TO_DATE('2013-08-02 21:23:41','YYYY-MM-DD HH24:MI:SS');

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

     72733

flashback database在如下情况下不可以闪回数据库:

Drop tablesapce           

Shrink data file

Re-create control file

 

1.2. Flashback drop

可以使用flashback drop来实现删除表的恢复

使用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

 

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

1.3Flashback table

Flashback table功能允许我们将数据表恢复到过去的某个点,不需要进行数据库一级别的不完全恢复

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语句

1.4Flashback Query

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 1001455;

----创建中间表T_HISTORY插入SCN1001455时刻的数据

1.5Flashback Version

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,VERSIONS_OPERATION

FROM SYS.ZGH VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

 

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V

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

          1026142                 060005001B030000 D

 

 

 

                          1026142

 

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

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

 

1.6Flashback Transaction

SQL> set autotrace on

SQL> SELECT f.xid,f.operation,f.undo_sql

  2  FROM FLASHBACK_TRANSACTION_QUERY f

  3  WHERE F.xid='060005001B030000';

 

 

XID              OPERATION

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

UNDO_SQL

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

060005001B030000 DELETE

insert into "SYS"."ZGH"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA

_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPOR

ARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME") values ('SYS','UNDO$',N

ULL,'15','15','TABLE',TO_DATE('18-SEP-11', 'DD-MON-RR'),TO_DATE('18-SEP-11', 'DD

-MON-RR'),'2011-09-18:17:33:48','VALID','N','N','N','1',NULL);

 

060005001B030000 BEGIN

 

 

XID              OPERATION

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

UNDO_SQL

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

 

 

 

Execution Plan

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

Plan hash value: 1115820779

 

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

| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |           |     1 |  2026 |     0   (0)| 00:00:01 |

|*  1 |  FIXED TABLE FULL| X$KTUQQRY |     1 |  2026 |     0   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter(RAWTOHEX("XID")='060005001B030000')

 

 

Statistics

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

      21330  recursive calls

         10  db block gets

     176167  consistent gets

          2  physical reads

          0  redo size

        992  bytes sent via SQL*Net to client

        419  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

        374  sorts (memory)

          0  sorts (disk)

          2  rows processed

---查询XID060005001B030000UNDO SQL,执行返回的结果可以恢复数据。只是耗时比较长,该表没有走索引- -

 

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

上一篇: DG SWITCH OVER
请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    31
  • 访问量
    117123