首页

   技术服务

   维保服务

   解决方案

   成功案例

   新闻中心

   关于我们
 
 
新闻中心
Oracle-truncate误删数据恢复
 
前言： truncate操作误删数据之后想恢复数据通常比较困难，因为truncate操作属于ddl操作无法使用直接undo闪回查询方式恢复数据，并且由于空间大小、备份时间以及变更操作不规范原因，往往在执行操作之前没有对表进行CTAS备份或者其他有效的逻辑备份导致恢复truncate的误删数据困难。

本文接下来将介绍常用的5种truncate误删数据的恢复办法，希望对读者有所借鉴或是启发。

数据恢复介绍：

在进行数据恢复之前，我们需要先获取一个非常关键的数据，就是truncate操作的时间点，这个时间点的准确性对数据的恢复非常关键，因为会影响数据恢复的完整性，时间点可以从以下途径获取

1执行人员的大脑记忆，获取的时间点相对模糊

2数据库的审计，操作记录日志，获取的时间点准确

3dba_objects视图的last_ddl_time，获取时间点准确，前提是truncate是最近一次的DDL操作

4从redo日志里面获取，获取的时间点准确

5从历史相关视图获取v$sql,v$active_session_history获取的时间点准确，单不一定有记录

方式一：通过基于时间点的rman备份恢复这种方式是相对稳妥的方式，但缺点也比较明显全量数据恢复时间相对较长，需要一倍空间以及恢复环境，而Oracle也推出更加简便快速的不用恢复全量数据的恢复方式，在Oracle10G以上可以使用TSPITR基于时间点恢复表空间的方式恢复数据，具体操作步骤可以参考Oracle-表空间基于时间点恢复(TSPITR)，而在Oracle12c版本以上可以使用基于时间点的recovertable的方式直接从备份里面基于时间快速恢复表或者是表分区，具体操作步骤可以参考Oracle-12c新功能基于时间点recovertable 方式二:通过expdp,exp等逻辑备份恢复

可以这种方式恢复数据简单，灵活，但是逻辑备份通常没有增量的方式，都是单次的固定时间点执行全量备份，所以备份恢复的数据数据点与删除时间点可能会出现相差较多的问题，导致恢复的数据出现不完整的情况

方式三：flashback闪回数据库

flashback闪回数据库，将整个数据库数据闪回到误删数据前的时间点，这种方式的缺点非常明显就是闪回是将整个数据库的数据一起闪回，而这样的恢复方式在生产主库环境操作几乎不可能，因为不可能为了一张表的数据，将整个数据库的数据都闪回到之前的时间点，因此这种方式合适在备库环境进行，在备库开启了flashback的功能情况下，我们可以选择在备库上进行闪回恢复数据

具体操作步骤如下

--查看备库闪回日志的最老时间点，要低于误删数据的操作时间点SQL>select*SQL>fromv$flashback_database_log;OLDEST_FLASHBACK_SCNOLDEST_FLASHBACK_TIRETENTION_TARGETFLASHBACK_SIZEESTIMATED_FLASHBACK_SIZE---------------------------------------------------------------------------------------------172390072023-10-1117:42:3114402097152000--将备库启动到mountSQL>shutdownimmediate;SQL>startupmount;--备库闪回到truncate之前的时间点SQL>flashbackdatabasetotimestampto_timestamp('2023-10-1117:45:00','yyyy-mm-ddhh24:mi:ss');--只读打开数据库，确认是要恢复的数据SQL>alterdatabaseopenreadonly;SQL>selectcount(*)fromtest.testdebug;COUNT(*)----------172864--备库开启到快照库模式导出表数据SQL>alterdatabaseconverttosnapshotstandby;SQL>Alterdatabaseopen;--expdp导出表数据expdp'userid="/assysdba"'dumpfile=test.dmptables=test.testdebugdirectory=expdirlogfile=test.log--转为物理备库恢复同步SQL>shutdownimmediate;SQL>startupmount;SQL>alterdatabaseconverttophysicalstandby;--重新恢复同步SQL>shutdownimmediate;SQL>startup;SQL>alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectfromsession; 方式四：延迟备库恢复

通过ADG延迟备库功能恢复数据，延迟备库是通过主动设置备库日志的应用延时，从而避免主库的操作在备库被立即应用，这样在主库发生误删数据时，备库的数据可以保证在延迟时间期限内未被删除，这样我们可以从延迟备库中恢复数据

具体操作步骤如下

--备库立即取消日志应用SQL>alterdatabaserecovermanagedstandbydatabasecancel;--主库切换日志SQL>altersystemswitchlogfile;--备库启动到mount状态SQL>shutdownimmediate;startupmount--备库收到recover恢复到truncate之前的时间点run{setuntiltime"to_date('2023-10-1116:45:00','yyyy-mm-ddhh24:mi:ss')";recoverdatabase;}--只读打开备库SQL>alterdatabaseopenreadonly;--确认是要恢复的数据SQL>selectcount(*)fromtest.testdebug;COUNT(*)----------86432--备库开启到快照库模式导出表数据SQL>alterdatabaseconverttosnapshotstandby;SQL>Alterdatabaseopen;--expdp导出表数据expdp'userid="/assysdba"'dumpfile=test.dmptables=test.testdebugdirectory=expdirlogfile=test.log--转为物理备库重新恢复同步SQL>shutdownimmediate;SQL>startupmount;SQL>alterdatabaseconverttophysicalstandby;--重新恢复同步SQL>shutdownimmediate;SQL>startup;SQL>alterdatabaserecovermanagedstandbydatabasedelay120disconnectfromsession; 方式五：通过数据挖掘方式恢复数据

在数据库没有开启闪回功能，没有有效备份以及延迟备库环境的情况下，想恢复truncate误删数据方法只剩下从已有的数据块里面挖掘恢复数据，前提是误删的数据块还未被覆盖，如果数据块已经被覆盖，那么数据可能就再也找不回来了

所以，在没有有效的恢复方式的情况下，应尽可能的避免数据块被覆盖导致数据丢失，最好将数据所在的表空间设置为只读保留误删环境，如果有备库环境，可以立即取消日志应用然后将备库激活为快照库作为数据挖掘的环境

altertablespacetest1readonly; 接下来进行数据挖掘，truncate表操作我们可以通过10046以及redo日志内容大概了解到truncate包含了以下的步骤

--100461将truncate表加上独占锁LOCKTABLE"TESTDEBUG"INEXCLUSIVEMODENOWAIT2执行truncatetruncatetabletestdebug3更新统计信息监控的数据updatesys.mon_mods$setinserts=inserts+:ins,updates=updates+:upd,deletes=deletes+:del,flags=(decode(bitand(flags,:flag),:flag,flags,flags+:flag)),drop_segments=drop_segments+:dropseg,timestamp=:timewhereobj#=881854删除基本superobj$下的信息deletefromsuperobj$wheresubobj#=881855删除基表统计信息deletefromtab_stats$whereobj#=881856更新基表tab$的信息，包含更新data_object_id为新的值，文件块的信息updatetab$7删除基表索引统计信息deletefromind_stats$whereobj#=:18更新基表索引对象信息,包含更新data_object_id为新的值,文件块的信息updateind$set9更新基表seg$信息,包含更新extent的信息updateseg$10更新基表obj$信息，包含dataobj--从redo日志里面，我们还可以看到truncate期间直接更新的块信息1segmentheader(dataobj#、LHWM、HHWM、extentmap、auxmap以及extents个数)2L1,L2块(dataobj#,extentmap) 从中我们可以发现，truncate操作涉及修改的基表、块信息较为复杂，想直接通过修改回原来的块信息，基表信息的恢复方式难度较大，因此我们选择直接从块里面挖掘数据，关键点如下

1.使用rowid的方式直接访问误删数据所在的数据块，避免访问segmentheader,L1,L2块

2.误删数据可能包含在这以下3种块

空闲块(dba_free_space)

被分配给其他段但还没被使用覆盖(也就是每个段最后申请的一个extent)

当前对象所在段的**个extent

3.obj$基表的data_object_id要跟挖掘原来数据的rowid里面的data_object_id一致，不然访问会出现无效rowid的报错

4.根据误删数据可能所在所在块的RELATIVE_file_id,block_id以及data_object_id，使用dbms_rowid.rowid_create函数构造误删数据的rowid

数据挖掘步骤如下: 1.找到被truncate表上一次的data_object_id

2更新误删表在obj$基表上的data_object_id为原来的删除前的值

3.通过dba_extents,dba_free_space获取误删数据可能所在的块

4.通过dbms_rowid.rowid_create来创建误删数据的的rowid

5.利用rowid来挖掘抽取数据

6.验证挖掘的数据

7将误删表在obj$基表上的data_object_id修改为删除后的值

8.将数据插入原表

具体操作步骤如下:假设误删了表test.testdebug数据 truncatetabletest.testdebug; 通过redo查找truncate之前的data_object_id --dump出删除操作期间的redoaltersystemdumplogfile'+DATADG/db/onlinelog/group_2.262.1132512359';--搜索关键字Newobjd,找出修改之前的dataobj#RedoonLevel1BitmapBlockChangeobjdNewobjd:184880CHANGE#2CON_ID:0TYP:0CLS:8AFN:7DBA:0x01c000c8OBJ:184880SCN:0x000000000178875cSEQ:1OP:13.22ENC:0RBL:0FLG:0x0000RedoonLevel1BitmapBlockChangeobjdNewobjd:184890 更新误删表在obj$基表上的data_object_id为原来的删除前的值 updateobj$setdataobj#=184880whereobj#=184880;commit;altersystemflushshared_pool;altersystemflushbuffer_cache; 通过dba_extents,dba_free_space获取误删数据可能所在的块,并生成rowid

--存储过程执行用户权限SQL>grantdba,resource,connectto<username>;SQL>grantselectondba_extentsto<username>;SQL>grantselectondba_free_spaceto<username>;SQL>grantselectondba_tablesto<username>;SQL>grantcreatetableto<username>;--存储过程get_data_rowid参数1.p_old_ownertruncate表的用户名2.p_tabletruncate的表名3.p_old_data_object_id表truncate前的data_object_id4.p_block_row单个块最多包含的行数，根据表的列数量进行调整5.p_parallel挖掘数据的并行度--创建存储过程get_data_rowidcreateorreplaceprocedureget_data_rowid(p_old_ownerinvarchar2,p_tableinvarchar2,p_old_data_object_idnumber,p_block_rownumber,p_parallelnumber)isv_fnonumber;v_s_bnonumber;v_e_bnonumber;v_rowidrowid;v_ownervarchar2(50);--truncate表用户名v_tablevarchar2(100);--truncate表名v_data_objnumber;--truncate前dba_objects.data_object_idv_block_rownumber;--理论上每个块最多存放接近680行数据v_parallelnumber;v_rowid_tablevarchar2(40);--存放rowid的表v_table_seqnumber;v_sql1varchar(1000);v_sql2varchar(1000);beginv_owner:=p_old_owner;v_table:=p_table;v_data_obj:=p_old_data_object_id;v_block_row:=p_block_row;v_parallel:=p_parallel;beginfortin1..v_parallelloopv_rowid_table:='rowid_data_table_'||t;v_sql1:='createtable'||v_rowid_table||'(BLOCK_IDNUMBER,ROW_IDrowid)';executeimmediatev_sql1;endloop;EXCEPTIONwhenOTHERSthenraise;commit;end;foriin(selectrelative_fno,block_id,blocks--通过dba_extents,dba_free_space获取误删数据可能所在的块fromdba_extentswhereowner=v_ownerandsegment_name=v_tableandextent_id=0unionallselectrelative_fno,block_id,blocksfromdba_free_spacewheretablespace_namein(selecttablespace_namefromdba_tableswhereowner=v_ownerandtable_name=v_table)unionallselectrelative_fno,block_id,blocksfrom(selectrelative_fno,block_id,blocks,row_number()over(partitionbyowner,segment_name,PARTITION_NAMEorderbyextent_iddesc)rnfromdba_extentswheretablespace_namein(selecttablespace_namefromdba_tableswhereowner=v_ownerandtable_name=v_table)andextent_id>0)wherern=1)loopv_fno:=i.relative_fno;v_s_bno:=i.block_id;v_e_bno:=i.block_id+i.blocks-1;forjinv_s_bno..v_e_bnoloopbeginforxin0..v_block_rowloop--通过dbms_rowid.rowid_create来创建潜在的rowidv_rowid:=dbms_rowid.rowid_create(1,v_data_obj,v_fno,j,x);v_table_seq:=mod(j,v_parallel)+1;v_rowid_table:='rowid_data_table_'||v_table_seq;v_sql2:='insertinto'||v_rowid_table||'values(:1,:2)';executeimmediatev_sql2usingj,v_rowid;endloop;exceptionwhenothersthenraise;end;commit;endloop;endloop;end;/--执行存储过程生成要挖掘的rowidexecget_data_rowid('TEST','TESTDEBUG',184880,100,16);--这里使用了16个并行，所以rowid会分布在rowid_data_table_1-16表里面 利用rowid来挖掘抽取数据

--创建存放挖掘数据的备份表createtabletest.testdebug_baktablespaceusersasselect*fromTEST.testdebugwhere1=2;--存储过程insert_data_by_rowid参数1.p_old_ownertruncate表的用户名2.p_tabletruncate的表名3.p_bak_owner备份表的用户名4.p_bak_table备份表的表名5.p_slave并行的子进程号--创建存储过程insert_data_by_rowidcreateorreplaceprocedureinsert_data_by_rowid(p_old_ownerinvarchar2,p_tableinvarchar2,p_bak_ownerinvarchar2,p_bak_tableinvarchar2,p_slavenumber)isv_ownervarchar2(50);v_tablevarchar2(100);v_bak_ownervarchar2(50);v_bak_tablevarchar2(100);v_sqlvarchar2(2000);v_slavenumber;v_err_codevarchar2(50);v_err_msgvarchar2(500);v_rowid_tableVARCHAR(40);c_rowidsys_refcursor;v_rowiddbms_sql.urowid_table;v_sql1varchar2(1000);v_sql2varchar2(1000);beginv_owner:=p_old_owner;v_table:=p_table;v_bak_owner:=p_bak_owner;v_bak_table:=p_bak_table;v_slave:=p_slave;v_rowid_table:='rowid_data_table_'||v_slave;v_sql1:='selectrow_idfrom'||v_rowid_table;v_sql2:='insertinto'||v_bak_owner||'.'||v_bak_table||'select*from'||v_owner||'.'||v_table||'whererowid=:1';openc_rowidforv_sql1;LOOPfetchc_rowidbulkcollectintov_rowidlimit10000;exitwhenv_rowid.count=0;beginforalliinv_rowid.first..v_rowid.lastSAVEEXCEPTIONSexecuteimmediatev_sql2usingv_rowid(i);EXCEPTIONwhenOTHERSthen--DBMS_OUTPUT.put_line(SQLERRM);--FORindxIN1..SQL%BULK_EXCEPTIONS.COUNT--LOOP--DBMS_OUTPUT.put_line('Oracleerroris'||SQLERRM(-1*SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));--ENDLOOP;null;end;commit;endloop;closec_rowid;end;/--16个窗口并行挖掘，注意会消耗大量的PGA进程内存以及产生大量单块读IOsetserveroutputonsettimingonexecinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',1);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',2);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',3);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',4);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',5);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',6);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',7);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',8);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',9);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',10);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',11);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',12);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',13);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',14);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',15);execinsert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',16); 验证挖掘的数据，将数据插回原表

--从表的字段逻辑，业务数据逻辑，表数据量验证数据selectcount(*)fromtest.testdebug_bak;selectsum(SESSIONID+ENTRYID+STATEMENT)fromtest.testdebug_bak;--将误删表在obj$基表上的data_object_id修改为删除后的值updateobj$setdataobj#=184890whereobj#=184880;commit;altersystemflushshared_pool;altersystemflushbuffer_cache;--表空间设置为读写altertablespacetest1readwrite;--将数据插回原表，完成数据挖掘工作insertintotest.testdebugselect*fromtest.testdebug_bak; 总结：

5种恢复方式综合来看，延迟备库恢复以及闪回备库从恢复时间、恢复难度以及恢复数据完整性来看整体最好，是恢复truncate误删数据的首选，这也是为什么对于重要的核心数据库通常建议配置一主两备的原因，RAC（主）+实时备库(flashbackon8小时)+延迟备库(delay24小时)+合理有效的rman备份策略的配置可以最大限度的保障数据安全
返回上一页
天津金网智信息技术有限公司
电话：022-87783825
Email：400@gn-service.cn
地址：天津市红桥区青年路85号青年创业园A座105室
 
拨打电话
QQ 咨询
联系我们