[20250501]truncate table恢复实战.txt

来源：这里教程网时间：2026-03-03 21:54:09 作者：

[20250501]truncate table恢复实战.txt --//别人数据库不小心truncate table，需要恢复，本以为3-4个小时可以完成，结果几乎使用1倍的工作时间。 --//往往是节前最容易出错的时候。 --//这类事情虽然以前做过练习，实战的情况几乎没有，在测试环境重新模拟整个的恢复过程。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试环境建立： --//drop table t purge ; SCOTT@book01p> create table t as select * from all_objects; Table created. SCOTT@book01p> create table t_bak as select * from t ; Table created. COTT@book01p> select count(*) from t; COUNT(*) ---------- 69881 SCOTT@book01p> select count(*) from t_bak; COUNT(*) ---------- 69881 SCOTT@book01p> truncate table t ; Table truncated. SCOTT@book01p> insert into t select * from t_bak where owner='SCOTT'; 63 rows created. SCOTT@book01p> commit ; Commit complete. SCOTT@book01p> select rowid from t; ROWID ------------------ AAAeg8AAMAAAACuAAA AAAeg8AAMAAAACuAAB AAAeg8AAMAAAACuAAC ... AAAeg8AAMAAAACvAAA AAAeg8AAMAAAACvAAB 63 rows selected. SCOTT@book01p> @ rowid AAAeg8AAMAAAACuAAA DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT -------------- ---------- ---------- ---------- -------------------- -------------------- -------------------------------------------------- 124988 12 174 0 0x30000AE 12,174 alter system dump datafile 12 block 174 ; --//实际上已经插入数据块dba=12,174以及dba=12，175. --//注：先把这部分数据建立新表保存在别的表空间里面步骤略。 --//注：真实的环境实际上打开归档日志，但是没有rman备份，事发前几天做了expdp的导出。在truncate后已经存在dml操作，覆盖小部 --//分数据块信息,这样比较真实的模拟现实的情况。 3.恢复： --//确定truncate前数据段号data_object_id. SYS@book01p> @ o2 scott.t SYS@book01p> @ pr ============================== O_OWNER : SCOTT O_OBJECT_NAME : T O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 124986 D_OID : 124988 CREATED : 2025-05-01 10:08:27 LAST_DDL_TIME : 2025-05-01 10:15:30 PL/SQL procedure successfully completed. --//truncate后已经是data_object_id=124988。 SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,NAME s '' '' obj#=124986 VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V OBJ# DATAOBJ# NAME -------------------- -------------------- ----------------- --------------- ---------------- - ---------- ---------- ------------------------------ 2025-05-01 10:08:27. 2025-05-01 10:15:28. 36631539 36632608 080009009F1E0000 I 124986 124986 T 2025-05-01 10:15:28. 36632608 0A000300331E0000 U 124986 124988 T --//可以确定truncate后data_object_id=124986。一般以前没有move或者truncate，object_id=data_object_id. --//也许还有许多情况两者不相等。 --//顺便提一下tab$,seg$表不同通过版本查询，可能原因是cluster table的一部分。 SYS@book01p> @ versions tab$ OBJ#,DATAOBJ#,TS# s '' '' obj#=124986 SELECT versions_starttime * ERROR at line 1: ORA-00600: internal error code, arguments: [ktrvGetChildRwsIdxCtx_not_found], [], [], [], [], [], [], [], [], [], [], [] --//注：可以使用as of scn|timestamp 查询获得以前的记录信息。 --//恢复的方法是通过扫描数据文件，通过rowid读取对应数据块。前提先还原obj$,tab$,seg$为原来的状态。 --//实际上仅仅还原obj$就可以了。 SYS@book01p> select * from obj$ where obj#=124986 2 @ pr ============================== OBJ# : 124986 DATAOBJ# : 124988 OWNER# : 109 NAME : T NAMESPACE : 1 SUBNAME : TYPE# : 2 CTIME : 2025-05-01 10:08:27 MTIME : 2025-05-01 10:15:30 STIME : 2025-05-01 10:08:27 STATUS : 1 REMOTEOWNER : LINKNAME : FLAGS : 0 OID$ : SPARE1 : 6 SPARE2 : 1 SPARE3 : 109 SPARE4 : SPARE5 : SPARE6 : SIGNATURE : 570DD59CAB4634BF17253AE92B1920B0 SPARE7 : 134233583 SPARE8 : 0 SPARE9 : 0 DFLCOLLID : 16382 CREAPPID : CREVERID : CREPATCHID : MODAPPID : MODVERID : MODPATCHID : SPARE10 : SPARE11 : SPARE12 : SPARE13 : SPARE14 : PL/SQL procedure successfully completed. SYS@book01p> update (select * from obj$ where obj#=124986 and DATAOBJ#=124988) set DATAOBJ#=124986; 1 row updated. SYS@book01p> commit ; Commit complete. --//确定扫描数据文件最大块号。 SCOTT@book01p> select * from dba_DATA_FILES where file_id=12 2 @ pr ============================== FILE_NAME : /u01/oradata/BOOK/book01p/users01.dbf FILE_ID : 12 TABLESPACE_NAME : USERS BYTES : 267386880 BLOCKS : 32640 STATUS : AVAILABLE RELATIVE_FNO : 12 AUTOEXTENSIBLE : YES MAXBYTES : 34359721984 MAXBLOCKS : 4194302 INCREMENT_BY : 160 USER_BYTES : 266338304 USER_BLOCKS : 32512 ONLINE_STATUS : ONLINE LOST_WRITE_PROTECT : OFF PL/SQL procedure successfully completed. --//最大块号 32640。 --//$ cd bbed ; --//$ rm log.bbd --//我的bbed取了别名并且定义为函数rlbbed。在parfile=bbed.par加入spool=Y，这样执行的输出记录在log.bbd文件里面。 $ type rlbbed rlbbed is a function rlbbed () { cd /home/oracle/bbed; $RLWRAP -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed $BBED parfile=bbed.par cmdfile=cmd.par } $ seq 128 1 32640 | xargs -IQ echo p /d dba 12,Q ktbbhsid.ktbbhsg1 | rlbbed > /dev/null --//输出有点长。 --//确定那些数据块的段号等于124986。 $ grep -B1 " 124986$" log.bbd | grep ktbbhsid.ktbbhsg1 | head -4 BBED> p /d dba 12,176 ktbbhsid.ktbbhsg1 BBED> p /d dba 12,177 ktbbhsid.ktbbhsg1 BBED> p /d dba 12,178 ktbbhsid.ktbbhsg1 BBED> p /d dba 12,179 ktbbhsid.ktbbhsg1 --//将需要扫描的数据块保存在文本scan.txt文件中。 $ grep -B1 124986$ log.bbd | grep ktbbhsid.ktbbhsg1 > scan.txt SCOTT@book01p> @ seg2 t '' SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ---------- 0 SCOTT T TABLE USERS 8 12 170 SCOTT@book01p> select * from dba_extents where segment_name='T' 2 @ pr ============================== OWNER : SCOTT SEGMENT_NAME : T PARTITION_NAME : SEGMENT_TYPE : TABLE TABLESPACE_NAME : USERS EXTENT_ID : 0 FILE_ID : 12 BLOCK_ID : 168 BYTES : 65536 BLOCKS : 8 RELATIVE_FNO : 12 PL/SQL procedure successfully completed. --//段头在12，170.而实际上truncate的插入已经导致前面数据块做个格式化，数据块168到175已经标识为段号124988。 --//通过查看log.bbd文件内容也可以确定. BBED> p /d dba 12,168 ktbbhsid.ktbbhsg1 BBED-00400: invalid blocktype (32) BBED> p /d dba 12,169 ktbbhsid.ktbbhsg1 BBED-00400: invalid blocktype (33) BBED> p /d dba 12,170 ktbbhsid.ktbbhsg1 BBED-00400: invalid blocktype (35) BBED> p /d dba 12,171 ktbbhsid.ktbbhsg1 ub4 ktbbhsg1 @24 124988 BBED> p /d dba 12,172 ktbbhsid.ktbbhsg1 ub4 ktbbhsg1 @24 124988 BBED> p /d dba 12,173 ktbbhsid.ktbbhsg1 ub4 ktbbhsg1 @24 124988 BBED> p /d dba 12,174 ktbbhsid.ktbbhsg1 ub4 ktbbhsg1 @24 124988 BBED> p /d dba 12,175 ktbbhsid.ktbbhsg1 ub4 ktbbhsg1 @24 124988 BBED> p /d dba 12,176 ktbbhsid.ktbbhsg1 ub4 ktbbhsg1 @24 124986 --//尝试通过rowid访问数据块看看。 SCOTT@book01p> select OWNER,OBJECT_NAME from t where rowid = dbms_rowid.rowid_create(1,124986,12,176,0); OWNER OBJECT_NAME ------------------------------ ------------------------------ SYS SQLOBJ$PLAN --//OK,说明通过rowid方式取数据没有问题。 SYS@book01p> create table scott.scanblock ( file_id number,block_id number ) tablespace TSP_AUDIT; Table created. SYS@book01p> create table scott.bak_t tablespace TSP_AUDIT as select * from scott.t where 0=1; Table created. --//注意建立的新表一定不能使用原来的表空间，避免覆盖。 $ awk '{print $5}' scan.txt | sed 's/^/insert into scanblock values (/;s/$/);/' > scan1.txt $ head -2 scan1.txt ; tail -2 scan1.txt insert into scanblock values (12,176); insert into scanblock values (12,177); insert into scanblock values (12,24614); insert into scanblock values (12,24615); --//执行@scan1.txt.注意提交。 --//从网上找的脚本我修改仅仅扫描scott.scanblock表。实际上8k数据块最多736条记录,对应这样表取200行号已经足够，不然扫描有点 --//慢，正常不会遗漏。 $ cat truncT.txt declare v_fno number; v_s_bno number; v_e_bno number; v_rowid rowid; v_owner varchar2(100):='&&1'; v_table varchar2(100):='&&2'; v_o_owner varchar2(100):='&&3'; v_o_table varchar2(100):='&&4'; v_dataobj number; v_sql varchar2(4000); v_tablespace varchar2(100); nrows number; begin nrows:=0; select data_object_id into v_dataobj from dba_objects where owner=v_owner and object_name=v_table; -- select tablespace_name into v_tablespace from dba_tables where owner=v_owner and table_name=v_table; for i in (select file_id,block_id from scott.scanblock) loop v_fno:=i.file_id; v_s_bno:=i.block_id; v_e_bno:=i.block_id+1-1; for j in v_s_bno .. v_e_bno loop begin for x in 0 .. 200 loop v_rowid:=dbms_rowid.rowid_create(1,v_dataobj,v_fno,j,x); v_sql:='insert into '||v_o_owner||'.'||v_o_table||' select * from '||v_owner||'.'||v_table||' where rowid=:1'; execute immediate v_sql using v_rowid; if sql%rowcount = 1 then nrows:=nrows+1; end if; if (mod(nrows,10000)=0) then commit; end if; end loop; exception when others then null; end; commit; end loop; end loop; end; / SYS@book01p> @ truncT.txt SCOTT T SCOTT BAK_T PL/SQL procedure successfully completed. --//注意大写owner，表名。 SCOTT@book01p> select count(*) from bak_t ; COUNT(*) ---------- 69567 --//丢失了69881-69567 = 314。 SCOTT@book01p> select * from bak_t minus select * from t_bak; no rows selected --//说明恢复的数据没有任何问题。 4.还原现场： SYS@book01p> update (select * from obj$ where obj#=124986 and DATAOBJ#=124986) set DATAOBJ#=124988; 1 row updated. SYS@book01p> commit ; Commit complete. SYS@book01p> alter system flush shared_pool; System altered. SYS@book01p> select count(*) from scott.t; COUNT(*) ---------- 63 --//顺便提一下数据块171到175已经标识为段号124988，已经做了格式化，虽然插入仅仅在块174，175，但是其他数据块里面的数据已经 --//无法看到。 SCOTT@book01p> @ bbvi 12 171 BVI_COMMAND ------------------------------------------------------------------------------------------------------------------------ bvi -b 1400832 -s 8192 /u01/oradata/BOOK/book01p/users01.dbf xxd -c16 -g 2 -s 1400832 -l 8192 /u01/oradata/BOOK/book01p/users01.dbf dd if=/u01/oradata/BOOK/book01p/users01.dbf bs=8192 skip=171 count=1 of=12_171.dd conv=notrunc 2>/dev/null od -j 1400832 -N 8192 -t x1 -v /u01/oradata/BOOK/book01p/users01.dbf hexdump -s 1400832 -n 8192 -C -v /u01/oradata/BOOK/book01p/users01.dbf alter system dump datafile '/u01/oradata/BOOK/book01p/users01.dbf' block 171; alter session set events 'immediate trace name set_tsn_p1 level 6'; alter session set events 'immediate trace name buffer level 50331819'; 9 rows selected. $ xxd -a -c16 -g 2 -s 1400832 -l 8192 /u01/oradata/BOOK/book01p/users01.dbf 0156000: 06a2 0000 ab00 0003 f5f8 2e02 0000 0104 ................ 0156010: 7139 0000 0100 0000 3ce8 0100 f5f8 2e02 q9......<....... 0156020: 0080 0000 0200 3200 a800 0003 0000 0000 ......2......... 0156030: 0000 0000 0000 0000 0000 0000 0000 0000 ................ * 0156060: 0000 0000 0000 0000 ffff 0e00 981f 8a1f ................ 0156070: 8a1f 0000 0000 0000 0000 0000 0000 0000 ................ 0156080: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0156090: a900 0003 1000 0000 0000 0000 0000 0000 ................ 01560a0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 01560b0: 0000 0000 0000 0000 0000 0000 a002 0000 ................ 01560c0: 3ae8 0100 55f3 2e02 0000 0000 0003 0003 :...U........... 01560d0: 4000 0000 0000 0000 0000 0000 0000 0000 @............... 01560e0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ * 0156180: 0000 0000 0000 0000 0000 0000 1111 1111 ................ 0156190: 1111 1111 1111 1111 1111 1111 1111 1111 ................ 01561a0: 1111 1111 1111 1111 1111 1111 0000 0000 ................ 01561b0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ * 0156390: 0000 0000 d3f3 2e02 0000 0000 0000 0000 ................ 01563a0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ * 01567a0: 0000 0000 0000 0000 0000 0000 4141 4141 ............AAAA 01567b0: 4141 4141 4141 4141 4141 4141 4141 4141 AAAAAAAAAAAAAAAA 01567c0: 4141 4141 4141 4141 4141 4141 4141 4141 AAAAAAAAAAAAAAAA 01567d0: 4141 4141 4141 4141 4141 4141 4141 4141 AAAAAAAAAAAAAAAA 01567e0: 4141 4141 4141 4141 4141 4141 0000 0000 AAAAAAAAAAAA.... 01567f0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ * 0157ff0: 0000 0000 0000 0000 0000 0000 0106 f5f8 ................ --//原始的数据信息完成清除了。通过这样的方式已经无法恢复，即使我修改段号等于124986。 5.顺便贴上原始的恢复脚本： --//链接来自：http://www.minniebaby.tech/2021/10/25/truncate-table%e6%81%a2%e5%a4%8d-%e8%84%9a%e6%9c%ac/，做一些修改。 --//顺便贴上原始的恢复脚本，使用它扫描范围有点大，实际的环境非常慢，不如我先确定扫描那些块要快一些。 --//行号取到999有点多,可以根据需要修改。另外我加入mod(nrows,10000)=0提交，我扫描时打开另外窗口发现实际上记录增加并不是按 --//照10000量增加，也许pl/sql有点不同，有点多余。 SCOTT@book01p> select count(*) from bak_t ; COUNT(*) ---------- 46647 SCOTT@book01p> select count(*) from bak_t ; COUNT(*) ---------- 47168 SCOTT@book01p> select count(*) from bak_t ; COUNT(*) ---------- 47771 $ cat trunc.txt declare v_fno number; v_s_bno number; v_e_bno number; v_rowid rowid; v_owner varchar2(100):='&&1'; v_table varchar2(100):='&&2'; v_o_owner varchar2(100):='&&3'; v_o_table varchar2(100):='&&4'; v_dataobj number; v_sql varchar2(4000); v_tablespace varchar2(100); nrows number; begin nrows:=0; select data_object_id into v_dataobj from dba_objects where owner=v_owner and object_name=v_table; select tablespace_name into v_tablespace from dba_tables where owner=v_owner and table_name=v_table; for i in (select relative_fno,block_id,blocks from dba_extents where owner=v_owner and segment_name=v_table and extent_id=0 union all select relative_fno,block_id,blocks from dba_free_space where tablespace_name=v_tablespace union all select relative_fno,block_id,blocks from ( select relative_fno,block_id,blocks,row_number()over(partition by owner,segment_name,partition_name order by extent_id desc) rn from dba_extents where tablespace_name=v_tablespace and extent_id>0) where rn=1) loop v_fno:=i.relative_fno; v_s_bno:=i.block_id; v_e_bno:=i.block_id+i.blocks-1; for j in v_s_bno .. v_e_bno loop begin for x in 0 .. 999 loop v_rowid:=dbms_rowid.rowid_create(1,v_dataobj,v_fno,j,x); v_sql:='insert into '||v_o_owner||'.'||v_o_table||' select * from '||v_owner||'.'||v_table||' where rowid=:1'; execute immediate v_sql using v_rowid; if sql%rowcount = 1 then nrows:=nrows+1; end if; if (mod(nrows,10000)=0) then commit; end if; end loop; exception when others then null; end; commit; end loop; end loop; end; / $ cat versions.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. ------------------------------------------------------------------------------------------------------------ -- -- File name: versions.sql -- Purpose: display table record of dml history -- -- Author: lfree -- -- Usage: -- @ versions <table_name> <col1,..,colN> <scn|s|t|time|timestamp> <scn1|time1> <scn2|time2> <filter> -- ------------------------------------------------------------------------------------------------------------- set term off column 2 new_value 2 column 3 new_value 3 column 4 new_value 4 column 5 new_value 5 column 6 new_value 6 column cols new_value v_cols column st new_value v_st column s1 new_value v_s1 column s2 new_value v_s2 column filter new_value v_filter select null "2" , null "3" , null "4" , null "5" ,null "6" from dual where 1=2; select decode('&2',NULL,'&1..*','*','&1..*','&2') cols ,decode(lower('&3'),null,'scn','s','scn','scn','scn','t','timestamp','time','timestamp','timestamp','timestamp','scn') st ,decode('&4',null,'minvalue','&4') s1 ,decode('&5',null,'maxvalue','&5') s2 from dual ; select decode('&6',null,'1=1','&6') "6" from dual ; set term on --set echo on verify on SELECT versions_starttime ,versions_endtime ,versions_startscn ,versions_endscn ,versions_xid ,versions_operation ,&v_cols FROM &1 VERSIONS BETWEEN &v_st &v_s1 AND &v_s2 where ( &6 ) ORDER BY VERSIONS_STARTSCN nulls first; -- FROM &1 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE -- FROM &1 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE -- FROM &1 VERSIONS BETWEEN SCN &3 AND &4 -- FROM &1 VERSIONS BETWEEN TIMESTAMP &&3 and &4 set echo off verify off

编辑推荐：

返回列表

[20250501]truncate table恢复实战.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推