用户误操作的恢复之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插入SCN为1001455时刻的数据 |
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
---查询XID为060005001B030000的UNDO SQL，执行返回的结果可以恢复数据。只是耗时比较长，该表没有走索引- -！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26169542/viewspace-768009/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26169542/viewspace-768009/
256
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
