[20180630]truncate table的另类恢复2.txt
[20180630]truncate table的另类恢复2.txt
--//上个星期做了truncate table的另类恢复,通过修改数据块的段号,再通过rowid定位收集数据,达到修复的目的.
--//实际上不能作为恢复的手段,主要不实用,不过通过学习了解oracle一些基本概念.
--//链接:blog.itpub.net/267265/viewspace-2156936/
--//而且上次涉及的块很少,这次我们反向思考,不修改块的段号,而修改数据字典,来恢复truncate表.
--//注意truncate还是使用reuse storage选项,存储空间不会回收.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t1 as select * from all_objects;
Table created.
SCOTT@test01p> create table t2 as select * from t1;
Table created.
SCOTT@test01p> select rowid,t1.* from t1 where rownum=1;
ROWID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O
------------------ ----- ----------- -------------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------ - - - ---------- ------------ ------- - -
AAAaSzAAJAAAAC7AAA SYS I_OBJ1 36 36 INDEX 2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID N N N ########## NONE Y
SCOTT@test01p> @ rowid AAAaSzAAJAAAAC7AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- ------- ----------------------------------------
107699 9 187 0 0x24000BB 9,187 alter system dump datafile 9 block 187 ;
SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
107699 107699
SCOTT@test01p> column PARTITION_NAME noprint
SCOTT@test01p> select * from dba_extents where owner=user and segment_name='T1';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- --------- ------- -------- ------- ------ ------------
SCOTT T1 TABLE USERS 0 9 184 65536 8 9
SCOTT T1 TABLE USERS 1 9 200 65536 8 9
SCOTT T1 TABLE USERS 2 9 208 65536 8 9
SCOTT T1 TABLE USERS 3 9 232 65536 8 9
SCOTT T1 TABLE USERS 4 9 240 65536 8 9
SCOTT T1 TABLE USERS 5 9 248 65536 8 9
SCOTT T1 TABLE USERS 6 9 256 65536 8 9
SCOTT T1 TABLE USERS 7 9 264 65536 8 9
SCOTT T1 TABLE USERS 8 9 272 65536 8 9
SCOTT T1 TABLE USERS 9 9 280 65536 8 9
SCOTT T1 TABLE USERS 10 9 288 65536 8 9
SCOTT T1 TABLE USERS 11 9 320 65536 8 9
SCOTT T1 TABLE USERS 12 9 328 65536 8 9
SCOTT T1 TABLE USERS 13 9 336 65536 8 9
SCOTT T1 TABLE USERS 14 9 344 65536 8 9
SCOTT T1 TABLE USERS 15 9 352 65536 8 9
SCOTT T1 TABLE USERS 16 9 384 1048576 128 9
SCOTT T1 TABLE USERS 17 9 512 1048576 128 9
SCOTT T1 TABLE USERS 18 9 640 1048576 128 9
SCOTT T1 TABLE USERS 19 9 768 1048576 128 9
SCOTT T1 TABLE USERS 20 9 896 1048576 128 9
SCOTT T1 TABLE USERS 21 9 1024 1048576 128 9
SCOTT T1 TABLE USERS 22 9 1152 1048576 128 9
SCOTT T1 TABLE USERS 23 9 1280 1048576 128 9
SCOTT T1 TABLE USERS 24 9 1408 1048576 128 9
SCOTT T1 TABLE USERS 25 9 1536 1048576 128 9
SCOTT T1 TABLE USERS 26 9 1664 1048576 128 9
SCOTT T1 TABLE USERS 27 9 1920 1048576 128 9
28 rows selected.
SCOTT@test01p> @ 10046on 12
Session altered.
SCOTT@test01p> truncate table t1 reuse storage;
Table truncated.
SCOTT@test01p> @ 10046off
Session altered.
2.首先看看truncate后执行什么命令:
SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
107699 107701
--//可以发现truncate后data_object_id=107701发生变化.
SCOTT@book> alter system checkpoint;
System altered.
D:\> egrep -i "^insert|^update|^delete" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_6320.trc > b.txt
--//显示b.txt内容看看:
delete from compression_stat$ where dataobj# = :1 and ts# = :2
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1
UPDATE tab$
SET ts# = :2
,file# = :3
,block# = :4
,bobj# = DECODE (:5, 0, NULL, :5)
,tab# = DECODE (:6, 0, NULL, :6)
,intcols = :7
,kernelcols = :8
,clucols = DECODE (:9, 0, NULL, :9)
,audit$ = :10
,flags = :11
,pctfree$ = :12
,pctused$ = :13
,initrans = :14
,maxtrans = :15
,rowcnt = :16
,blkcnt = :17
,empcnt = :18
,avgspc = :19
,chncnt = :20
,avgrln = :21
,analyzetime = :22
,samplesize = :23
,cols = :24
,property = :25
,degree = DECODE (:26, 1, NULL, :26)
,instances = DECODE (:27, 1, NULL, :27)
,dataobj# = :28
,avgspc_flb = :29
,flbcnt = :30
,trigflag = :31
,spare1 = :32
,spare2 = DECODE (:33, 0, NULL, :33)
,spare4 = :34
,spare6 = :35
WHERE obj# = :1
UPDATE seg$
SET type# = :4
,blocks = :5
,extents = :6
,minexts = :7
,maxexts = :8
,extsize = :9
,extpct = :10
,user# = :11
,iniexts = :12
,lists = DECODE (:13, 65535, NULL, :13)
,groups = DECODE (:14, 65535, NULL, :14)
,cachehint = :15
,hwmincr = :16
,spare1 = DECODE (:17, 0, NULL, :17)
,scanhint = :18
,bitmapranges = :19
WHERE ts# = :1 AND file# = :2 AND block# = :3
UPDATE obj$
SET obj# = :4
,type# = :5
,ctime = :6
,mtime = :7
,stime = :8
,status = :9
,dataobj# = :10
,flags = :11
,oid$ = :12
,spare1 = :13
,spare2 = :14
,spare3 = :15
,signature = :16
,spare7 = :17
,spare8 = :18
,spare9 = :19
WHERE owner# = :1
AND name = :2
AND namespace = :3
AND remoteowner IS NULL
AND linkname IS NULL
AND subname IS NULL
--//为了显示的需要,我格式化sql语句,这样显示更好看一些.
--//只要是对tab$,seg$,obj$进行了修改.
3.通过修改数据字典看看:
--//首先确定原来的段号,一般如果表没有move或者truncate,开始object_id=data_object_id.也可以转储块看看.
--//使用bbed查看也是一样的.
BBED> p /d dba 9,188 ktbbh.ktbbhsid.
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 107699
ub4 ktbbhod1 @24 107699
--//数据段号=107699
UPDATE tab$ set dataobj#=107699 where obj#=107699;
UPDATE seg$ set hwmincr=107699 where hwmincr=107701;
--//好像seg$记录段头,没有注意这个表以前.
SCOTT@test01p> @ pt2 "select * from sys.seg$ where hwmincr=107701"
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ---------- -------------------- ------------
1 1 FILE_x0023_ 9
2 BLOCK_x0023_ 186
3 TYPE_x0023_ 5
4 TS_x0023_ 3
5 BLOCKS 1664
6 EXTENTS 28
7 INIEXTS 8
8 MINEXTS 1
9 MAXEXTS 2147483645
10 EXTSIZE 128
11 EXTPCT 0
12 USER_x0023_ 109
13 LISTS 0
14 GROUPS 0
15 BITMAPRANGES 2147483645
16 CACHEHINT 0
17 SCANHINT 0
18 HWMINCR 107701
19 SPARE1 4194561
19 rows selected.
--//
update obj$ set dataobj#=107699 where obj#=107699;
--//执行以上3条update命令以sys用户执行.还原回来原来的数据段号.
SYS@test01p> alter system flush shared_pool;
System altered.
SYS@test01p> alter system flush shared_pool;
System altered.
SYS@test01p> UPDATE tab$ set dataobj#=107699 where obj#=107699;
1 row updated.
SYS@test01p> UPDATE seg$ set hwmincr=107699 where hwmincr=107701;
1 row updated.
SYS@test01p> update obj$ set dataobj#=107699 where obj#=107699;
1 row updated.
SYS@test01p> commit ;
Commit complete.
SYS@test01p> alter system flush shared_pool;
System altered.
4.检查是否能正常显示:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@test01p> select rowid,t1.* from t1 where rowid='AAAaSzAAJAAAAC7AAA';
ROWID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O
------------------ -------------------- -------------------- -------------------- ---------- -------------- -------------------- ------------------- ------------------- ------------------- -------------------- - - - ---------- -------------------- ------------- - -
AAAaSzAAJAAAAC7AAA SYS I_OBJ1 36 36 INDEX 2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID N N N ########## NONE Y
--//可以发现使用rowid可以查询对应记录.如果写成如下:
SCOTT@test01p> select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB';
select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB'
*
ERROR at line 1:
ORA-08103: object no longer exists
D:\tools\bbed>oerr ora 8103
08103, 00000, "object no longer exists"
// *Cause: The object has been deleted by another user since the operation
// began, or a prior incomplete recovery restored the database to
// a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
// recovery.
--//你可以发现报错ora-08103,因为数据段头的段号我没有修改,现在是107701.做一个转储就能确定:
SCOTT@test01p> alter system dump datafile 9 block 186 ;
System altered.
--//检查转储:
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 28 #blocks: 1664
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x024000bb ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
*** 2018-06-30 21:01:17.135
Disk Lock:: Locked by xid: 0x0004.00c.00006195
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x024000bb ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x024000b8
Level 1 BMB for Low HWM block: 0x024000b8
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x024000b9
Last Level 1 BMB: 0x02400781
Last Level II BMB: 0x024000b9
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 28 obj#: 107701 flag: 0x10000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ===>obj#: 107701
Inc # 0
Extent Map
-----------------------------------------------------------------
0x024000b8 length: 8
0x024000c8 length: 8
0x024000d0 length: 8
0x024000e8 length: 8
0x024000f0 length: 8
0x024000f8 length: 8
0x02400100 length: 8
0x02400108 length: 8
0x02400110 length: 8
0x02400118 length: 8
0x02400120 length: 8
0x02400140 length: 8
0x02400148 length: 8
0x02400150 length: 8
0x02400158 length: 8
0x02400160 length: 8
0x02400180 length: 128
0x02400200 length: 128
0x02400280 length: 128
0x02400300 length: 128
0x02400380 length: 128
0x02400400 length: 128
0x02400480 length: 128
0x02400500 length: 128
0x02400580 length: 128
0x02400600 length: 128
0x02400680 length: 128
0x02400780 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x024000b8 Data dba: 0x024000bb
Extent 1 : L1 dba: 0x024000b8 Data dba: 0x024000c8
Extent 2 : L1 dba: 0x024000d0 Data dba: 0x024000d1
Extent 3 : L1 dba: 0x024000d0 Data dba: 0x024000e8
Extent 4 : L1 dba: 0x024000f0 Data dba: 0x024000f1
Extent 5 : L1 dba: 0x024000f0 Data dba: 0x024000f8
Extent 6 : L1 dba: 0x02400100 Data dba: 0x02400101
Extent 7 : L1 dba: 0x02400100 Data dba: 0x02400108
Extent 8 : L1 dba: 0x02400110 Data dba: 0x02400111
Extent 9 : L1 dba: 0x02400110 Data dba: 0x02400118
Extent 10 : L1 dba: 0x02400120 Data dba: 0x02400121
Extent 11 : L1 dba: 0x02400120 Data dba: 0x02400140
Extent 12 : L1 dba: 0x02400148 Data dba: 0x02400149
Extent 13 : L1 dba: 0x02400148 Data dba: 0x02400150
Extent 14 : L1 dba: 0x02400158 Data dba: 0x02400159
Extent 15 : L1 dba: 0x02400158 Data dba: 0x02400160
Extent 16 : L1 dba: 0x02400180 Data dba: 0x02400182
Extent 17 : L1 dba: 0x02400200 Data dba: 0x02400202
Extent 18 : L1 dba: 0x02400280 Data dba: 0x02400282
Extent 19 : L1 dba: 0x02400300 Data dba: 0x02400302
Extent 20 : L1 dba: 0x02400380 Data dba: 0x02400382
Extent 21 : L1 dba: 0x02400400 Data dba: 0x02400402
Extent 22 : L1 dba: 0x02400480 Data dba: 0x02400482
Extent 23 : L1 dba: 0x02400500 Data dba: 0x02400502
Extent 24 : L1 dba: 0x02400580 Data dba: 0x02400582
Extent 25 : L1 dba: 0x02400600 Data dba: 0x02400602
Extent 26 : L1 dba: 0x02400680 Data dba: 0x02400682
Extent 27 : L1 dba: 0x02400780 Data dba: 0x02400782
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x024000b9
End dump data blocks tsn: 3 file#: 9 minblk 186 maxblk 186
--//注意看下划线内容.
--//我前面提到执行计划TABLE ACCESS BY ROWID RANGE,注意范围这个关键字.这样要访问段头,因为是truncate表后
--//降低了高水位标识,并且我没有修改数据段号,即使我修改数据段号=107699.
--//oracle认为要访问的数据块在高水位之上,一样没有结果集.
--//而前面的执行计划是TABLE ACCESS BY USER ROWID.直接通过rowid访问块.越过了数据段头的访问.
5.有了以上思路,应该可以恢复全部记录.
--//实际上就是当段头损坏.
SCOTT@test01p> CREATE TABLE t1new tablespace lfree AS SELECT * FROM t1 where 1=0;
Table created.
--//网上找到的脚本,参考链接:http://www.xifenfei.com/3898.html
--//修改owner以及对应表名.
set serveroutput on
set concat off
DECLARE
nrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number;
BEGIN
ROWSPERBLOCK:=736; --估算最大的一个块中记录条数,8K最多736条记录1块.
nrows:=0;
select data_object_id into dobj
from dba_objects
where owner = 'SCOTT'
and object_name = 'T1'
-- and subobject_name = '<table partition>' Add this condition if table is partitioned
;
for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner = 'SCOTT'
and segment_name = 'T1'
-- and partition_name = '<table partition>' Add this condition if table is partitioned
-- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376 (A)
order by extent_id)
loop
for br in i.block_id..i.totblocks loop
for j in 1..ROWSPERBLOCK loop
begin
rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
insert into t1new
select /*+ ROWID(A) */ *
from t1 A
where rowid = rid;
if sql%rowcount = 1 then nrows:=nrows+1; end if;
if (mod(nrows,10000)=0) then commit; end if;
exception when others then null;
end;
end loop;
end loop;
end loop;
COMMIT;
dbms_output.put_line('Total rows:'||to_char(nrows));
END;
/
--//再执行前必须修改dba=9,184 到 9,186块中的段号=107699,不然
--//select * from dba_extents where owner=user and segment_name='T1';没有显示.无法恢复.
SCOTT@test01p> @ 10to16 107701
10 to 16 HEX REVERSE16
----------------- ----------------------------------
000000000001a4b5 0xb5a40100-00000000
SCOTT@test01p> @ 10to16 107699
10 to 16 HEX REVERSE16
----------------- -----------------------------------
000000000001a4b3 0xb3a40100-00000000
--//修改0xb5a40100 => 0xb3a40100
--//修改dba=9,184
BBED> set dba 9,185
DBA 0x024000b9 (37748921 9,185)
--//注:我使用windwos的bbed,修改块存在1个数据块偏移.后面不再说明.实际上修改9,184.
BBED> set offset 0
OFFSET 0
BBED> find /x b5a40100 top
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 185 Offsets: 192 to 201 Dba:0x024000b9
------------------------------------------------------------------------------------------------------------------------------------------------
b5a40100 f6f4a501 0000
<64 bytes per line>
BBED> find
BBED-00212: search string not found
BBED> modify /x b3a4 offset 192
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 185 Offsets: 192 to 201 Dba:0x024000b9
------------------------------------------------------------------------------------------------------------------------------------------------
b3a40100 f6f4a501 0000
<64 bytes per line>
BBED> sum apply dba 9,185
Check value for File 9, Block 185:
current = 0xa553, required = 0xa553
--//修改dba=9,185
BBED> set dba 9,186
DBA 0x024000ba (37748922 9,186)
BBED> set offset 0
OFFSET 0
BBED> find /x b5a40100 top
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 186 Offsets: 104 to 113 Dba:0x024000ba
------------------------------------------------------------------------------------------------------------------------------------------------
b5a40100 01000000 0000
<64 bytes per line>
BBED> f
BBED-00212: search string not found
BBED> modify /x b3a4 offset 104
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 186 Offsets: 104 to 113 Dba:0x024000ba
------------------------------------------------------------------------------------------------------------------------------------------------
b3a40100 01000000 0000
<64 bytes per line>
BBED> sum apply dba 9,186
Check value for File 9, Block 186:
current = 0x4215, required = 0x4215
--//修改dba=9,186
BBED> sum apply dba 9,186
Check value for File 9, Block 186:
current = 0x4215, required = 0x4215
BBED> set dba 9,187
DBA 0x024000bb (37748923 9,187)
BBED> set offset 0
OFFSET 0
BBED> find /x b5a40100 top
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 187 Offsets: 272 to 281 Dba:0x024000bb
------------------------------------------------------------------------------------------------------------------------------------------------
b5a40100 00000010 b800
<64 bytes per line>
BBED> f
BBED-00212: search string not found
BBED> modify /x b3a4 offset 272
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 187 Offsets: 272 to 281 Dba:0x024000bb
------------------------------------------------------------------------------------------------------------------------------------------------
b3a40100 00000010 b800
<64 bytes per line>
BBED> sum apply dba 9,187
Check value for File 9, Block 187:
current = 0x6ac0, required = 0x6ac0
--//OK现在修改完成.select * from dba_extents where owner=user and segment_name='T1';也能正常显示.
--//执行修复脚本,等,脚本很慢,主要块有点多,而且不管什么块都做这样的操作,
--//另外每块假设的记录值736有点高,估计200比较合适.对于当前的表.
SCOTT@test01p> alter system flush shared_pool;
System altered.
SCOTT@test01p> select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB';
no rows selected
--//rowid range 查询一样没有结果,验证了我前面的判断.
SCOTT@test01p> @ aa.txt
Total rows:89859
PL/SQL procedure successfully completed.
--//我这里大约5分钟,有点慢...^_^.
SCOTT@test01p> select * from t1new minus select * from t2;
no rows selected
SCOTT@test01p> select * from t2 minus select * from t1new;
no rows selected
SCOTT@test01p> select count(*) from t1new;
COUNT(*)
----------
89859
--//OK没有丢失数据.
6.总结:
--//这样方法不适合一般的恢复,仅仅作为探究与学习.
--//从测试可以看出做好备份才是关键.
--//另外出现问题,如果真是那种没有备份没有归档的数据库,如果真出现这样的问题.
--//最好的方法停库,做一个冷备份.如果不行,直接拷贝对应表空间的数据文件也是一个可行的方法.
--//或者先给truncate的表改名,建立同名表在不同表空间,避免覆盖,最大程度恢复truncate的数据.
