[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;
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
2.首先看看truncate後執行什麼指令:
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
,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;
SYS@test01p> UPDATE tab$ set dataobj#=107699 where obj#=107699;
1 row updated.
SYS@test01p> UPDATE seg$ set hwmincr=107699 where hwmincr=107701;
SYS@test01p> update obj$ set dataobj#=107699 where obj#=107699;
SYS@test01p> commit ;
Commit complete.
4.檢查是否能正常顯示:
SCOTT@book> alter system flush buffer_cache;
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 ;
--//檢查轉儲:
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 :
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;
--//網上找到的腳本,參考連結: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
----------------- -----------------------------------
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
b3a40100 f6f4a501 0000
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)
Block: 186 Offsets: 104 to 113 Dba:0x024000ba
b5a40100 01000000 0000
BBED> f
BBED> modify /x b3a4 offset 104
b3a40100 01000000 0000
BBED> sum apply dba 9,186
Check value for File 9, Block 186:
current = 0x4215, required = 0x4215
--//修改dba=9,186
BBED> set dba 9,187
DBA 0x024000bb (37748923 9,187)
Block: 187 Offsets: 272 to 281 Dba:0x024000bb
b5a40100 00000010 b800
BBED> modify /x b3a4 offset 272
b3a40100 00000010 b800
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;
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;
SCOTT@test01p> select * from t2 minus select * from t1new;
SCOTT@test01p> select count(*) from t1new;
COUNT(*)
----------
89859
--//OK沒有丢失資料.
6.總結:
--//這樣方法不适合一般的恢複,僅僅作為探究與學習.
--//從測試可以看出做好備份才是關鍵.
--//另外出現問題,如果真是那種沒有備份沒有歸檔的資料庫,如果真出現這樣的問題.
--//最好的方法停庫,做一個冷備份.如果不行,直接拷貝對應表空間的資料檔案也是一個可行的方法.
--//或者先給truncate的表改名,建立同名表在不同表空間,避免覆寫,最大程度恢複truncate的資料.
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)