[20231020]rename IDL_UB1$後使用bbed的恢復.txt
[20231020]rename IDL_UB1$後使用bbed的恢復.txt
--//繼續前面的測試：
--//參考連結： [20231019]rename IDL_UB1$的恢復測試前準備.txt
1.環境：
SYS@book> @ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.開始測試:
SYS@book> rename IDL_UB1$ to IDL_UB1X;
Table renamed.
SYS@book> alter system checkpoint;
System altered.
alter system checkpoint;
alter system checkpoint;
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1X'| awk '{print int($1/8192), " ",$1-int($1/8192)*8192, " " ,$2}'
351 3983 IDL_UB1X
375 3983 IDL_UB1X
86182 3867 IDL_UB1X
--//為了測試方便,我貼上前面的測試結果(刪除多餘部分),可以發現偏移都不對.
--//其中的86182對應一定是obj$.居然沒有就地修改,位置發生了變動,包括索引.
--//如果關閉資料庫,資料庫可能無法啟動.(注：我後面的測試說明可以正常啟動，僅僅一些涉及包的命令無法正常使用)
--//原來的結果如下.
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1\$'| awk '{print int($1/8192), " ",$1-int($1/8192)*8192, " " ,$2}'
243 6325 IDL_UB1$
351 5692 IDL_UB1$
375 5692 IDL_UB1$
3.對比分析:
--//dba 1,243
BBED> x /rnnncncntttnccnxnnncct dba 1,243 *kdbr[23]
rowdata[5215] @6311
-------------
flag@6311: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@6312: 0x01
cols@6313: 0
--//可以發現記錄已經做了刪除標識，KDRHFD表示刪除標識。
BBED> x /rnnncncntttnccnxnnncct dba 1,86182 *kdbr[6]
rowdata[0] @3853
----------
flag@3853: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3854: 0x01
cols@3855: 18
col 0[3] @3856: 225
col 1[3] @3860: 225
col 2[1] @3864: 0
col 3[8] @3866: IDL_UB1X
col 4[2] @3875: 1
col 5[0] @3878: *NULL*
col 6[2] @3879: 2
col 7[7] @3882: 2013-08-24 11:37:39
col 8[7] @3890: 2023-10-20 10:48:44
col 9[7] @3898: 2023-10-20 10:48:44
col 10[2] @3906: 1
col 11[0] @3909: *NULL*
col 12[0] @3910: *NULL*
col 13[1] @3911: 0
col 14[0] @3913: *NULL*
col 15[1] @3914: 0
col 16[2] @3916: 2
~~~~~~~~~~~~~~~~~~~
col 17[1] @3919: 0
--//正常應該是0x2c。
--//原來的dba =1,243的樣子:
BBED> x /rnnncncntttnccnxnnncct dba 1,243 *kdbr[23]
rowdata[5215] @6311
-------------
flag@6311: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6312: 0x00
cols@6313: 18
col 0[3] @6314: 225
col 1[3] @6318: 225
col 2[1] @6322: 0
col 3[8] @6324: IDL_UB1$
col 4[2] @6333: 1
col 5[0] @6336: *NULL*
col 6[2] @6337: 2
col 7[7] @6340: 2013-08-24 11:37:39
col 8[7] @6348: 2013-08-24 11:37:39
col 9[7] @6356: 2013-08-24 11:37:39
col 10[2] @6364: 1
col 11[0] @6367: *NULL*
col 12[0] @6368: *NULL*
col 13[1] @6369: 0
col 14[0] @6371: *NULL*
col 15[1] @6372: 0
col 16[2] @6374: 1
~~~~~~~~~~~~~~~~~~~~
col 17[1] @6377: 0
--//長度並沒有變化,col 16值不同(事後檢查spare1),可以猜測rename實際上先delete原來記錄,然後在插入一條.並不是就地修改.導致
--//索引的位置也發生了不再原來位置,索引我僅僅改動最後1個字元,應該還在原來的塊中.
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1$
col 2[2] @5701: 1
col 3[0] @5704: *NULL*
col 4[0] @5705: *NULL*
col 5[0] @5706: *NULL*
col 6[2] @5707: 2
col 7[1] @5710: 0
col 8[3] @5712: 225
--//現在flag@5681: 0x01 (KDXRDEL),標識刪除.
BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x06
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1X
col 2[2] @3992: 1
col 3[0] @3995: *NULL*
col 4[0] @3996: *NULL*
col 5[0] @3997: *NULL*
col 6[2] @3998: 2
col 7[1] @4001: 0
col 8[3] @4003: 225
--//索引的恢復很簡單設定flag對調就ok了.
BBED> x /rncnnnnccn dba 1,375 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1$
col 2[2] @5701: 1
col 3[2] @5704: 2
col 4[1] @5707: 0
col 5[0] @5709: *NULL*
col 6[0] @5710: *NULL*
col 7[0] @5711: *NULL*
col 8[3] @5712: 225
--//現在flag@5681: 0x01 (KDXRDEL),標識刪除.
BBED> x /rncnnnnccn dba 1,375 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x06
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1X
col 2[2] @3992: 1
col 3[2] @3995: 2
col 4[1] @3998: 0
col 5[0] @4000: *NULL*
col 6[0] @4001: *NULL*
col 7[0] @4002: *NULL*
col 8[3] @4003: 225
--//有了以上相關資訊恢復就很簡單了.
--//恢復對應資料塊243塊號,在86182塊做刪除標識，恢復原來的索引指向(塊號351,375).
4.關閉資料庫看看,開始bbed恢復測試:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
--//實際上資料庫open一點問題都沒有.
SYS@book> @ ddl scott.dept
BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY',true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//實際一些包會報錯.也就是實際上 select * from obj$ where name ='IDL_UB1X';輸出正常.
--//但是如果你呼叫一些包就出現問題.
SYS@book> @ rowid AAAYD0AAIAAMqfEAAF
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('AAAYD0AAIAAMqfEAAF')
*
ERROR at line 13:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//實際上恢復就變得很簡單了.直接修改資料欄位obj$欄位就ok了.但是我的目的是使用bbed完成恢復，繼續操作。
--//在bbed下執行執行如下操作。
assign dba 1,243 offset 6311 = 0x2c
assign dba 1,86182 offset 3853 = 0x3c
assign dba 1,351 offset 5681 =0x0
assign dba 1,351 offset 3972 =0x1
assign dba 1,375 offset 5681 =0x0
assign dba 1,375 offset 3972 =0x1
5.修復資料塊的一致性.
--//然後開始修復資料塊的一致性.
--//dba 1,243
BBED> set dba 1,243
DBA 0x004000f3 (4194547 1,243)
BBED> sum apply
Check value for File 1, Block 243:
current = 0x73b6, required = 0x73b6
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 243
Block Checking: DBA = 4194547, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: the amount of space used is not equal to block size
used=7298 fsc=66 avsp=822 dtl=8120
Block 243 failed with check code 6110
--//簡單公式:
--//code 6110
--//avsp = dtl-used-fsc
--//code 6111
--//tosp = avsp+stb+fsc
--//avsp = dtl-used-fsc
--//avsp= 8120-7298-66 = 756
BBED> assign kdbh.kdbhavsp=756
sb2 kdbhavsp @78 756
BBED> sum apply
Check value for File 1, Block 243:
current = 0x7274, required = 0x7274
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 243
Block Checking: DBA = 4194547, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: space available on commit is incorrect
tosp=890 fsc=66 stb=0 avsp=756
Block 243 failed with check code 6111
--//tosp = avsp+stb+fsc
--//tosp= 756+0+66 = 822
BBED> assign kdbh.kdbhtosp=822
sb2 kdbhtosp @80 822
BBED> sum apply
Check value for File 1, Block 243:
current = 0x7238, required = 0x7238
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 243
--//其它塊基本類似.不再具體說明.
--//dba 1,86182
BBED> set dba 1,86182
DBA 0x004150a6 (4280486 1,86182)
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x84ea, required = 0x84ea
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
Block Checking: DBA = 4280486, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: the amount of space used is not equal to block size
used=3098 fsc=0 avsp=4956 dtl=8120
Block 86182 failed with check code 6110
--//avsp =8120-3098-0 = 5022
BBED> assign kdbh.kdbhavsp=5022
sb2 kdbhavsp @78 5022
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x8428, required = 0x8428
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
Block Checking: DBA = 4280486, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: avsp(5022) > tosp(4956)
Block 86182 failed with check code 6128
BBED> assign kdbh.kdbhtosp=5022
sb2 kdbhtosp @80 5022
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x84ea, required = 0x84ea
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
Block Checking: DBA = 4280486, Block Type = KTB-managed data block
data header at 0x8ec644
kdbchk: space available on commit is incorrect
tosp=5022 fsc=0 stb=2 avsp=5022
Block 86182 failed with check code 6111
--//tosp=5022+0+2=5024.
BBED> assign kdbh.kdbhtosp=5024
sb2 kdbhtosp @80 5024
BBED> sum apply
Check value for File 1, Block 86182:
current = 0x84d4, required = 0x84d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 86182
--//dba 1,351
BBED> set dba 1,351
DBA 0x0040015f (4194655 1,351)
BBED> sum apply
Check value for File 1, Block 351:
current = 0xa100, required = 0xa100
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 351
--//dba 1,375
BBED> set dba 1,375
DBA 0x00400177 (4194679 1,375)
BBED> sum apply
Check value for File 1, Block 375:
current = 0x1923, required = 0x1923
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 375
--//索引不需要恢復.很好理解長度都沒有變化。
6.看看恢復情況:
SYS@book> startup
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
SYS@book> @ ddl scott.dept
C300
------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
SYS@book> @ rowid AAAYD0AAIAAMqfEAAF
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
98548 8 3319748 5 0x232A7C4 8,3319748 alter system dump datafile 8 block 3319748 ;
--//沒有任何問題.檢檢視看:
SYS@book> validate index i_obj2;
Index analyzed.
SYS@book> validate index i_obj5;
Index analyzed.
SYS@book> select * from obj$ where name ='IDL_UB1$'
2 @ pr
==============================
OBJ# : 225
DATAOBJ# : 225
OWNER# : 0
NAME : IDL_UB1$
NAMESPACE : 1
SUBNAME :
TYPE# : 2
CTIME : 2013-08-24 11:37:39
MTIME : 2013-08-24 11:37:39
STIME : 2013-08-24 11:37:39
STATUS : 1
REMOTEOWNER :
LINKNAME :
FLAGS : 0
OID$ :
SPARE1 : 0
SPARE2 : 1
SPARE3 : 0
SPARE4 :
SPARE5 :
SPARE6 :
PL/SQL procedure successfully completed.
SYS@book> analyze table IDL_UB1$ validate structure cascade;
Table analyzed.
--//OK.實際上最簡單還是直接修改資料字典obj$,我的測試可以關閉資料庫重啟在修改資料欄位應該沒有任何問題,bbed恢復純粹為了練
--//習.
$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Oct 20 11:52:54 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 97280
Total Pages Processed (Data) : 64260
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13456
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4206
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15358
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 392188896 (3.392188896)
7.總結：
--//如果rename後修改長度不等長，情況要複雜一點，不過鑑於資料庫能正常重啟並open，修改資料字典也許更加簡單。
--//後續rename後修改長度不等長的情況測試就不作了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2990585/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20231103]rename IDL_UB1$後使用bbed的恢復的後遺症.txt
- [20231019]rename IDL_UB1$的恢復測試前準備.txt
- [20181031]truncate IDL_UB1$恢復.txt
- [20210930]bbed恢復刪除的資料.txt
- [20210401]使用bbed讀取資料塊恢復注意6.txt
- [20190213]學習bbed-恢復刪除的資料.txt
- [20220909]bbed關於刪除記錄恢復的問題.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- [20181227]bbed的使用問題.txt
- [20231020]增加欄位的問題.txt
- [20210901]cygwin下使用bbed.txt
- [20210303]bbed使用小問題.txt
- 6_Oracle truncate異常恢復之bbed修復Oracle
- 【BBED】丟失歸檔檔案情況下的恢復
- Oracle delete誤運算元據恢復(BBED)Oracledelete
- 28_bbed實戰(1)_delete操作恢復delete
- [20210920]bbed的assign命令.txt
- [20210304]bbed的assign命令.txt
- [20190428]恢復oraInventory.txtAI
- oracle drop table purge無備份bbed恢復(3/3)Oracle
- oracle drop table purge無備份bbed恢復(1/3)Oracle
- oracle drop table purge無備份bbed恢復(2/3)Oracle
- 深入解析：段頭塊損壞bbed異常恢復
- [20180627]truncate table的另類恢復.txt
- [20210803]刪除user$的恢復準備.txt
- [20201221]KTFB Bitmapped File Space Header的恢復.txtAPPHeader
- [20201222]KTFB Bitmapped File Space Bitmap的恢復.txtAPP
- [20190130]刪除tab$記錄的恢復.txt
- [20181212]truncate的另類恢復5.txt
- [20231008]bbed探究lob段.txt
- [20220223]bbed ktbbh.ktbbhict.txt
- [20181204]bbed修改問題.txt
- [20180619]bbed verify問題.txt
- [20231025]跟蹤rename操作2.txt
- [20210906]bbed讀取資料塊(bbed-wrap.sh).txt
- DM7使用DMRAMN對多次故障恢復後使用不同資料庫的歸檔進行恢復資料庫