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闪回查询无法恢复到表结构改变之前,因为闪回查询使用的当前的数据字典。