[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
京ICP备14020293号-4