oracle误删除表数据后的恢复详解
?
一、undo_retention参数的查询、修改：
用show parameter undo;命令查看当时的数据库参数undo_retention设置。
显示如下：
undo_management?????????????????????? string?????? AUTO
undo_retention????????????????????????????? integer?????10800
undo_suppress_errors????????????????? boolean????? FALSE
undo_tablespace?????????????????????????? string?????? UNDOTBS1
undo_retention(保持力)，10800单位是秒。即3个小时。
修改默认的undo_retention参数设置：
ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
二、oracle误删除表数据后的的快速恢复功能方法：
【方法一】：
通过oracle提供的回闪功能：
exec dbms_flashback.enable_at_time(to_date('2007-07-23 10:21:00','yyyy-mm-dd hh24:mi:ss'));
set serveroutput on
DECLARE r_temp hr.job_history%ROWTYPE;
CURSOR c_temp IS SELECT * FROM hr.job_history;
BEGIN
OPEN c_temp;
dbms_flashback.disable;
LOOP
FETCH c_temp INTO r_temp;
EXIT WHEN c_temp%NOTFOUND;
insert into hr.job_history(EMPLOYEE_ID,JOB_ID,START_DATE,END_DATE) values (r_temp.EMPLOYEE_ID,r_temp.JOB_ID,r_temp.START_DATE,r_temp.END_DATE);
commit;
END LOOP;?
CLOSE c_temp;?
END;
这种办法可以将删除的数据恢复到对应的表中，首先要保证该用户有执行dbms_flashback包的权限
【方法二】：
insert into hr.job_history
select * from hr.job_history as of timestamp to_timestamp('2007-07-23 10:20:00', 'yyyy-mm-dd hh24:mi:ss');
这种方法简单，容易掌握，功能和上面的一样时间为你误操作之前的时间，最好是离误操作比较近的，因为oracle保存在回滚保持段里的数据时间有一定的时间限制由undo_retention 这个参数值决定。
?
SQL> col fscn for 9999999999999999999SQL> col nscn for 9999999999999999999SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;...................NAME FSCN NSCN FIRST_TIME------------------------------ -------------------- -------------------- -------------------/mwarch/oracle/1_52413.dbf 12929941968 12929942881 2005-06-22 14:38:28/mwarch/oracle/1_52414.dbf 12929942881 12929943706 2005-06-22 14:38:32/mwarch/oracle/1_52415.dbf 12929943706 12929944623 2005-06-22 14:38:35/mwarch/oracle/1_52416.dbf 12929944623 12929945392 2005-06-22 14:38:38/mwarch/oracle/1_52417.dbf 12929945392 12929945888 2005-06-22 14:38:41/mwarch/oracle/1_52418.dbf 12929945888 12929945965 2005-06-22 14:38:44/mwarch/oracle/1_52419.dbf 12929945965 12929948945 2005-06-22 14:38:45/mwarch/oracle/1_52420.dbf 12929948945 12929949904 2005-06-22 14:46:05/mwarch/oracle/1_52421.dbf 12929949904 12929950854 2005-06-22 14:46:08/mwarch/oracle/1_52422.dbf 12929950854 12929951751 2005-06-22 14:46:11/mwarch/oracle/1_52423.dbf 12929951751 12929952587 2005-06-22 14:46:14.................../mwarch/oracle/1_52498.dbf 12930138975 12930139212 2005-06-22 15:55:57/mwarch/oracle/1_52499.dbf 12930139212 12930139446 2005-06-22 15:55:59/mwarch/oracle/1_52500.dbf 12930139446 12930139682 2005-06-22 15:56:00NAME FSCN NSCN FIRST_TIME------------------------------ -------------------- -------------------- -------------------/mwarch/oracle/1_52501.dbf 12930139682 12930139915 2005-06-22 15:56:02/mwarch/oracle/1_52502.dbf 12930139915 12930140149 2005-06-22 15:56:03/mwarch/oracle/1_52503.dbf 12930140149 12930140379 2005-06-22 15:56:05/mwarch/oracle/1_52504.dbf 12930140379 12930140610 2005-06-22 15:56:05/mwarch/oracle/1_52505.dbf 12930140610 12930140845 2005-06-22 15:56:0714811 rows selected.
SQL> select dbms_flashback.get_system_change_number fscn from dual; FSCN-------------------- 12930142214
SQL> connect username/passwordConnected.
SQL> select count(*) from hs_passport; COUNT(*)---------- 851998创建恢复表
SQL> create table hs_passport_recov as select * from hs_passport where 1=0;Table created.
SQL> select count(*) from hs_passport as of scn 12929970422; COUNT(*)---------- 861686
SQL> select count(*) from hs_passport as of scn &scn;Enter value for scn: 12929941968old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12929941968 COUNT(*)---------- 861684SQL> /Enter value for scn: 12927633776old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12927633776select count(*) from hs_passport as of scn 12927633776 *ERROR at line 1:ORA-01466: unable to read data - table definition has changedSQL> /Enter value for scn: 12929928784old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12929928784 COUNT(*)---------- 825110SQL> /Enter value for scn: 12928000000old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12928000000select count(*) from hs_passport as of scn 12928000000 *ERROR at line 1:ORA-01466: unable to read data - table definition has changed
SQL> insert into hs_passport_recov select * from hs_passport as of scn 12929941968;861684 rows created.SQL> commit;Commit complete.
?
?
数据恢复简单例子
?
简介?
在过去，如果用户误删/更新了数据后，作为用户并没有什么直接的方法来进行恢复，他们必须求助DBA来对数据库进行恢复，到了Oracle9i，这一个难堪局面有所改善。Oracle 9i中提供了一项新的技术手段--闪回查询，用户使用闪回查询可以及时取得误操作前的数据，并可以针对错误进行相应的恢复措施，而这一切都无需DBA干预。?
下面我们通过一个例子来具体说明闪回查询的用法。?
二、示例?
1、使用闪回查询前必须确定下面两个参数：?
UNDO_MANAGEMENT = AUTO?
undo_retention = 1200; #这个时间可以随便设，他表示在系统中保留提交了的UNDO信息的时间，1200就是保留20分钟。?
2、使用闪回查询?
SQL> conn sys/sys as sysdba?
Connected.?
SQL> create user flash identified by flash;?
User created.?
SQL> grant connect, resource to flash;?
Grant succeeded.?
SQL> grant execute on dbms_flashback to flash;?
Grant succeeded.?
SQL> conn flash/flash?
Connected.?
SQL> set echo on?
SQL> create table t (a varchar2(10));?
Table created.?
SQL> insert into t values('gototop');?
1 row created.?
SQL> insert into t values('www.ncn.cn');?
1 row created.?
SQL> /?
SQL> select * from t;?
A?
----------?
gototop?
www.ncn.cn?
www.ncn.cn?
SQL> set time on?
15:00:22 SQL>?
15:00:22 SQL>?
15:00:23 SQL> delete t where a='gototop';?
1 row deleted.?
15:00:35 SQL> commit;?
Commit complete.?
15:00:38 SQL> select * from t;?
A?
----------?
www.ncn.cn?
www.ncn.cn?
15:08:22 SQL> execute DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2003-05-16 15:00:00'));?
PL/SQL procedure successfully completed.?
15:09:02 SQL> select * from t;?
A?
----------?
gototop?
www.ncn.cn?
www.ncn.cn?
15:16:43 SQL> execute DBMS_FLASHBACK.DISABLE;?
PL/SQL procedure successfully completed.?
15:17:05 SQL> select * from t;?
A?
----------?
www.ncn.cn?
www.ncn.cn?
3、使用闪回查询恢复数据：?
15:36:55 SQL> select * from t;?
no rows selected?
15:36:57 SQL> insert into t values('www.ncn.cn');?
1 row created.?
15:37:15 SQL> /?
1 row created.?
15:37:16 SQL> /?
1 row created.?
15:37:17 SQL> /?
1 row created.?
15:37:18 SQL> /?
1 row created.?
15:37:19 SQL> commit;?
Commit complete.?
15:37:21 SQL> /?
Commit complete.?
15:37:22 SQL> /?
Commit complete.?
15:37:23 SQL> select * from t;?
A?
----------?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
6 rows selected.?
15:37:28 SQL>?
15:38:44 SQL> delete t;?
6 rows deleted.?
15:38:50 SQL> commit;?
Commit complete.?
15:38:53 SQL> commit;?
Commit complete.?
15:38:54 SQL> /?
Commit complete.?
15:38:57 SQL> declare?
15:39:28 2 cursor flash_recover is?
15:39:28 3 select * from t;?
15:39:28 4 t_recode t%rowtype;?
15:39:28 5 begin?
15:39:28 6 DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2003-05-16 15:37:23'));?
15:39:28 7 open FLASH_RECOVER;?
15:39:28 8 DBMS_FLASHBACK.DISABLE;?
15:39:28 9 loop?
15:39:28 10 FETCH FLASH_RECOVER INTO t_recode;?
15:39:28 11 EXIT WHEN FLASH_RECOVER%NOTFOUND;?
15:39:28 12 insert into t values (t_recode.a);?
15:39:28 13 end loop;?
15:39:28 14 CLOSE FLASH_RECOVER;?
15:39:28 15 commit;?
15:39:28 16 end;?
15:39:28 17 /?
PL/SQL procedure successfully completed.?
15:39:28 SQL> 15:39:28 SQL>?
15:39:31 SQL> select * from t;?
A?
----------?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
www.ncn.cn?
15:39:35 SQL>?
我们可以已经恢复了5条纪录，但我们要恢复的6条纪录，为什么会少一条呢？原因就在下面。?
三、局限性?
1、闪回查询是基于SCN的，虽然我执行的是：?
DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2003-05-16 15:37:23'));?
但Oracle并不会精确的这个时间点，而是ROUND DOWN到最近的一次SCN，然后从这个SCN开始进行恢复。而Oracle 9i是每五分钟记录一次SCN的，并将SCN和对应时间的映射做个纪录。?
这正是上面我们进行恢复时少了一条的原因。因此如果使用DBMS_FLASHBACK.ENABLE_AT_TIME来进行恢复，为了避免恢复失败，我可以先等5分钟，然后再进行恢复。?
使用DBMS_FLASHBACK.ENABLE_AT_TIME进行恢复还有一个缺点，那就是在Oracle 9i中SCN和对应时间的映射信息只会保留5天，因此我们无法通过DBMS_FLASHBACK.ENABLE_AT_TIME来恢复5天前的数据。如果你想使用闪回查询来恢复5天前的数据，你必须自己来确定需要恢复的SCN，然后使用?
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(SCN_NUMBER); 来定位你的恢复时间点，下面是使用方法：?
15:58:58 SQL> VARIABLE SCN_SAVE NUMBER;?
16:13:43 SQL> EXECUTE :SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;?
PL/SQL procedure successfully completed.?
16:13:50 SQL> print SCN_SAVE;?
SCN_SAVE?
----------?
2.1202E+11?
16:28:34 SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE);?
PL/SQL procedure successfully completed.?
另外，在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必须设定你的NLS_DATE_FORMAT的精确程度，Oracle默认的是精确到天，如果你不设定，像上面的例子你不会得到预期结果。?
2、如果你使用sysdate和DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER来获取时间点或者SCN值，你必须注意它们取得都是当前的时间点和SCN值。?
3、你只能在事务开始时进入闪回查询模式，如果之前有DML操作，则必须COMMIT。?
4、闪回查询无法恢复到表结构改变之前，因为闪回查询使用的当前的数据字典。 ?
?
附老陈的例子
?
Using Flashback Queries: Example The following statements show a current value from the sample table hr.employees and then change the value. The intervals used in these examples are very short for demonstration purposes. Time intervals in your own environment are likely to be larger.
?
SELECT salary FROM employees
?? WHERE last_name = 'Chung';
?
?? ?SALARY
----------
?? ? ?3800
?
UPDATE employees SET salary = 4000
?? WHERE last_name = 'Chung';
?
1 row updated.
?
SELECT salary FROM employees
?? WHERE last_name = 'Chung';
?
?? ?SALARY
----------
?? ? ?4000
?
To learn what the value was before the update, you can use the following Flashback Query:
?
SELECT salary FROM employees
?? AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE)
?? WHERE last_name = 'Chung';
?
?? ?SALARY
----------
?? ? ?3800
?
To learn what the values were during a particular time period, you can use a version Flashback Query:
?
SELECT salary FROM employees
??VERSIONS BETWEEN TIMESTAMP
?? ?SYSTIMESTAMP - INTERVAL '10' MINUTE AND
?? ?SYSTIMESTAMP - INTERVAL '1' MINUTE
??WHERE last_name = 'Chung';
?
To revert to the earlier value, use the Flashback Query as the subquery of another UPDATE statement:
?
UPDATE employees SET salary = ? ? ?
?? (SELECT salary FROM employees
?? AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE)
?? WHERE last_name = 'Chung')
?? WHERE last_name = 'Chung';
1 row updated.
?
SELECT salary FROM employees
?? WHERE last_name = 'Chung';
?
?? ?SALARY
----------
?? ? ?3800
?
?
?
