Oracle误删除表数据后的数据恢复详解
Oracle误删除表数据后的恢复详解
测试环境：
SYSTEM:IBMAIX5L OracleVersion:10gR2
1.undo_retention参数的查询与修改
使用showparameterundo命令查看当前的数据库参数undo_retention设置。
显示如下：
SQL>showparameterundo
NAME TYPE VALUE
-----------------------------------------------------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
undo_retention(保持力)，900单位是秒，即15分钟。
修改默认的undo_retention参数设置：
SQL>ALTERSYSTEMSETundo_retention=10800SCOPE=BOTH;
Systemaltered.
SQL>showparameterundo
NAME TYPE VALUE
-----------------------------------------------------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS2
undo_retention10800，单位秒，即3小时。
2.oracle误删除表数据后的的快速恢复功能方法
2.1方法一
通过oracle提供的回闪功能
execdbms_flashback.enable_at_time(to_date('2011-04-1508:21:00','yyyy-mm-ddhh24:mi:ss'));
setserveroutputon
DECLAREr_temphr.job_history%ROWTYPE;
CURSORc_tempISSELECT*FROMhr.job_history;
BEGIN
OPENc_temp;
dbms_flashback.disable;
LOOP
FETCHc_tempINTOr_temp;
EXITWHENc_temp%NOTFOUND;
insertintohr.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;
ENDLOOP;
CLOSEc_temp;
END;
这种办法可以将删除的数据恢复到对应的表中，首先要保证该用户有执行dbms_flashback包的权限。
2.2方法二
insertintohr.job_history
select*fromhr.job_historyasoftimestampto_timestamp('2011-04-1508:20:00','yyyy-mm-ddhh24:mi:ss');
这种方法简单，容易掌握，功能和上面的一样，此处的时间为你误操作之前的时间，最好是离误操作比较近的，因为oracle保存在回滚保持段里的数据时间有一定的时间限制，这个限制由undo_retention这个参数值决定。
查看FIRST_CHANGE#,NEXT_CHANGE#,FIRST_TIME
SQL>setpagesize9999
SQL>colfscnfor999999999
SQL>colnscnfor999999999
SQL>selectname,FIRST_CHANGE#fscn,NEXT_CHANGE#nscn,FIRST_TIMEfromv$archived_log;
当前的SCN为:
SQL>selectdbms_flashback.get_system_change_numberfscnfromdual;
FSCN
----------
3435958
使用应用用户尝试闪回
SQL>connectusername/password
Connected.
现有数据:
SQL>selectcount(*)fromhs_passport;
COUNT(*)
----------
851998
创建恢复表：
SQL>createtablehs_passport_recovasselect*fromhs_passportwhere1=0;
Tablecreated.
选择SCN向前恢复：
SQL>selectcount(*)fromhs_passportasofscn12929970422;
COUNT(*)
----------
861686
尝试多个SCN，获取最佳值（如果能得知具体时间，那么可以获得准确的数据闪回）
SQL>selectcount(*)fromhs_passportasofscn&scn; Entervalueforscn:12929941968 old1:selectcount(*)fromhs_passportasofscn&scn new1:selectcount(*)fromhs_passportasofscn12929941968 COUNT(*) ---------- 861684 SQL>/ Entervalueforscn:12927633776 old1:selectcount(*)fromhs_passportasofscn&scn new1:selectcount(*)fromhs_passportasofscn12927633776 selectcount(*)fromhs_passportasofscn12927633776 * ERRORatline1: ORA-01466:unabletoreaddata-tabledefinitionhaschanged SQL>/ Entervalueforscn:12929928784 old1:selectcount(*)fromhs_passportasofscn&scn new1:selectcount(*)fromhs_passportasofscn12929928784 COUNT(*) ---------- 825110 SQL>/ Entervalueforscn:12928000000 old1:selectcount(*)fromhs_passportasofscn&scn new1:selectcount(*)fromhs_passportasofscn12928000000 selectcount(*)fromhs_passportasofscn12928000000 * ERRORatline1: ORA-01466:unabletoreaddata-tabledefinitionhaschanged
最后选择恢复到SCN为12929941968的时间点
SQL>insertintohs_passport_recovselect*fromhs_passportasofscn12929941968;
861684rowscreated.
SQL>commit;
Commitcomplete.
数据恢复简单例子
在过去，如果用户误删/更新了数据后，作为用户并没有什么直接的方法来进行恢复，他们必须求助DBA来对数据库进行恢复，到了Oracle9i，这一难堪的局面有所改善。Oracle9i中提供了一项新的技术手段--闪回查询，用户使用闪回查询可以及时取得误操作前的数据，并可以针对错误进行相应的恢复措施，而这一切都无需DBA干预。
3.下面我们通过一个例子来具体说明闪回查询的用法
示例
3.1使用闪回查询前必须确定下面两个参数：
UNDO_MANAGEMENT=AUTO
undo_retention=10800;
这个时间可以随便设，它表示在系统中保留提交了的UNDO信息的时间，10800就是保留3小时，即180分钟。
3.2使用闪回查询
SQL>conn/assysdba
Connected.
SQL>dropuserlsfcascade;
Userdropped.
SQL>createuserlsfidentifiedbylsf;
Usercreated.
SQL>grantconnect,resourcetolsf;
Grantsucceeded.
SQL>grantexecuteondbms_flashbacktolsf;
Grantsucceeded.
SQL>connlsf/lsf
Connected.
SQL>createtableT(idint,namevarchar2(20));
Tablecreated.
SQL>insertintoTvalues(1,'lsf');
1rowcreated.
SQL>insertintoTvalues(2,'lsf');
1rowcreated.
SQL>insertintoTvalues(3,'lsf');
1rowcreated.
SQL>commit;
Commitcomplete.
SQL>select*fromT;
IDNAME
----------------------------------------------------------------------
1lsf
2lsf
3lsf
SQL>settimeon
10:12:50SQL>deletefromTwhereid=1;
1rowdeleted.
10:13:02SQL>commit;
Commitcomplete.
10:13:10SQL>select*fromT;
IDNAME
----------------------------------------------------------------------
2lsf
3lsf
10:13:18SQL>executeDBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2011-04-1510:12:50','YYYY-MM-DDHH24:MI:SS'));
PL/SQLproceduresuccessfullycompleted.
10:13:50SQL>select*fromT;
IDNAME
----------------------------------------------------------------------
1lsf
2lsf
3lsf
10:13:57SQL>executeDBMS_FLASHBACK.DISABLE;
PL/SQLproceduresuccessfullycompleted.
10:15:48SQL>select*fromT;
IDNAME
----------------------------------------------------------------------
2lsf
3lsf
3.3使用闪回查询恢复数据
10:16:59SQL>truncatetableT;
Tabletruncated.
10:18:15SQL>select*fromT;
norowsselected
10:18:22SQL>insertintoTvalues(1,'lsf');
1rowcreated.
10:19:42SQL>insertintoTvalues(2,'lsf');
1rowcreated.
10:19:48SQL>insertintoTvalues(3,'lsf');
1rowcreated.
10:19:55SQL>insertintoTvalues(4,'lsf');
1rowcreated.
10:20:07SQL>insertintoTvalues(5,'lsf');
1rowcreated.
10:20:15SQL>insertintoTvalues(6,'lsf');
1rowcreated.
10:20:21SQL>commit;
Commitcomplete.
10:20:26SQL>select*fromT;
IDNAME
----------------------------------------------------------------------
1lsf
2lsf
3lsf
4lsf
5lsf
6lsf
6rowsselected.
10:20:56SQL>deleteT;
6rowsdeleted.
10:21:27SQL>commit;
Commitcomplete.
10:21:40SQL>declare
10:22:292cursorflash_recoveris
10:22:433select*fromT;
10:22:504t_recodeT%rowtype;
10:23:115begin
10:23:146DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2011-04-1510:20:56','YYYY-MM-DDHH24:MI:SS'));
10:24:227openflash_recover;
10:24:398DBMS_FLASHBACK.DISABLE;
10:24:599loop
10:25:0510FETCHflash_recoverintot_recode;
10:25:2411EXITWHENflash_recover%NOTFOUND;
10:25:4512insertintoTvalues(t_recode.id,t_recode.name);
10:26:3513endloop;
10:26:3914CLOSEFLASH_RECOVER;
10:26:5015commit;
10:26:5616end;
10:26:5817/
PL/SQLproceduresuccessfullycompleted.
10:27:00SQL>select*fromT;
IDNAME
----------------------------------------------------------------------
1lsf
2lsf
3lsf
4lsf
5lsf
6lsf
6rowsselected.
我们可以已经恢复了所有的6条纪录，但是由于闪回查询的局限性，有可能不能恢复所有的6条记录，原因就在下面。
4.局限性
4.1闪回查询是基于SCN的，虽然我们执行的是：
DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2011-04-1510:20:56','YYYY-MM-DDHH24:MI:SS'));
但Oracle并不会精确的这个时间点，而是ROUNDDOWN到最近的一次SCN，然后从这个SCN开始进行恢复。而Oracle9i是每五分钟记录一次SCN的，并将SCN和对应时间的映射做个纪录。
因此如果使用DBMS_FLASHBACK.ENABLE_AT_TIME来进行恢复，为了避免恢复失败，我们可以先等5分钟，然后再进行恢复。
使用DBMS_FLASHBACK.ENABLE_AT_TIME进行恢复还有一个缺点，那就是在Oracle9i中SCN和对应时间的映射信息只会保留5天，因此我们无法通过DBMS_FLASHBACK.ENABLE_AT_TIME来恢复5天前的数据。如果你想使用闪回查询来恢复5天前的数据，你必须自己来确定需要恢复的SCN，然后使用DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(SCN_NUMBER);来定位你的恢复时间点，下面是使用方法：
10:27:27SQL>VARIABLESCN_SAVENUMBER; 10:32:47SQL>EXECUTE:SCN_SAVE:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; PL/SQLproceduresuccessfullycompleted. 10:33:24SQL>printSCN_SAVE; SCN_SAVE ---------- 3438420 10:33:41SQL>executeDBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE); PL/SQLproceduresuccessfullycompleted. 10:34:31SQL>select*fromT; IDNAME ---------------------------------------------------------------------- 1lsf 2lsf 3lsf 4lsf 5lsf 6lsf 6rowsselected.
另外，在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必须设定你的NLS_DATE_FORMAT的精确程度，Oracle默认的是精确到天，如果你不设定，像上面的例子你不会得到预期结果。
4.2如果你使用sysdate和DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER来获取时间点或者SCN值，你必须注意它们取得都是当前的时间点和SCN值。
4.3你只能在事务开始时进入闪回查询模式，如果之前有DML操作，则必须COMMIT。
4.4闪回查询无法恢复到表结构改变之前，因为闪回查询使用的当前的数据字典。