20180614刪除bootstrap$記錄無法啟動3補充
[20180614][20180614]刪除bootstrap$記錄無法啟動3(補充).txt
--//昨天測試刪除bootstrap$記錄,導致資料庫重啟出現問題的修復方法:
--//連結:
http://blog.itpub.net/267265/viewspace-2156144/=>[20180612]刪除bootstrap$記錄無法啟動.txt
http://blog.itpub.net/267265/viewspace-2156149/=>[20180614]刪除bootstrap$記錄無法啟動2.txt
--//有網友問的問題,就是使用bbed修復記錄後,修改flag從0x3c=>0x2c後,如果執行verify會報錯.
--//從我個人認為如果select正常,這些問題可以忽略.真要修復實際對於不經常使用bbed的使用者還是難度的.
--//實際上這個問題可以參考連結:
http://blog.itpub.net/267265/viewspace-2137082/=>[20170412]bbed恢復修改記錄(不等長).txt.
--//我喜歡透過例子講解問題:
1.環境:
SCOTT@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
SCOTT@book> create table t as select * from dept ;
Table created.
SCOTT@book> select rowid,t.* from t ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEIAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEIAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEIAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEIAAEAAAAIjAAD 40 OPERATIONS BOSTON
SCOTT@book> @ &r/rowid AAAWEIAAEAAAAIjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90376 4 547 0 0x1000223 4,547 alter system dump datafile 4 block 547 ;
SCOTT@book> delete from t where deptno in (10,30);
2 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
2.使用bbed修復:
BBED> set dba 4,547
DBA 0x01000223 (16777763 4,547)
BBED> set count 10
COUNT 10
--//注意在使用find前設定count不要太大,否則查詢字元0x3c可能跳過.
--//補充一點: 0x3c對應asicc碼是'<',0x2c對應asicc碼是','.
BBED> map
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[7946] @150
ub1 rowdata[92] @8096
ub4 tailchk @8188
BBED> set offset 8096
OFFSET 8096
BBED> find /x 0x3c curr
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8120 to 8129 Dba:0x01000223
----------------------------------------------------------------
3c020302 c11f0553 414c
<64 bytes per line>
BBED> x /rncc offset 8120
rowdata[24] @8120
-----------
flag@8120: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8121: 0x02
cols@8122: 0
BBED> find
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8162 to 8171 Dba:0x01000223
-----------------------------------------------------------------
3c020302 c10b0a41 4343
<64 bytes per line>
--//可以發現刪除記錄偏移在8120,8162.執行如下:
assign dba 4,547 offset 8120 = 0x2c
assign dba 4,547 offset 8162 = 0x2c
--//檢查記錄情況.
BBED> x /4rncc *kdbr[3]
rowdata[0] @8096
----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x00
cols@8098: 3
col 0[2] @8099: 40
col 1[10] @8102: OPERATIONS
col 2[6] @8113: BOSTON
rowdata[24] @8120
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x02
cols@8122: 3
col 0[2] @8123: 30
col 1[5] @8126: SALES
col 2[7] @8132: CHICAGO
rowdata[44] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x00
cols@8142: 3
col 0[2] @8143: 20
col 1[8] @8146: RESEARCH
col 2[6] @8155: DALLAS
rowdata[66] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x02
cols@8164: 3
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
col 2[8] @8179: NEW YORK
--//檢查:
BBED> sum apply dba 4,547
Check value for File 4, Block 547:
current = 0xdc32, required = 0xdc32
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7ff1765ed27c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=42 avsp=7946 dtl=8064
Block 547 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--//可以發現verify報錯,實際上fsc儲存了刪除記錄時儲存的長度.
SCOTT@book> select sum(length(dname)+length(deptno)+length(loc)) n10 from dept where deptno in (10,30);
N10
---------------------
34
--//加上刪除時每個記錄前的長度指示器佔1個位元組(3個欄位),3個位元組.以及cols欄位佔1個位元組(注意計算不包括flag,lock的長度)
--//34+4*2 = 42.也就是fsc記錄刪除記錄時回收的長度.
3.檢查:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select rowid,t.* from t ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEIAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEIAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEIAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEIAAEAAAAIjAAD 40 OPERATIONS BOSTON
--//可以發現即使verify錯誤,問題不大,顯示已經正常.不必計較這個問題.
--//如果在該塊以後有事務發生,itl槽被覆蓋.可以執行多次.
select * from t where rownum=1 for update;
commit ;
alter system flush buffer_cache;
--//錯誤變成如下:
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x21d8e7c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=0 avsp=7988 dtl=8064
Block 547 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--//當然顯示沒有任何問題:
SCOTT@book> select rowid,t.* from t ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEIAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEIAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEIAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEIAAEAAAAIjAAD 40 OPERATIONS BOSTON
--//如果真要修復,其實是很繁瑣的.
--//參考http://blog.itpub.net/267265/viewspace-2137082/=>[20170412]bbed恢復修改記錄(不等長).txt.
--//公式: dtl-used+fsc=avsp.
--//8064-118+0 = 7946,修改avsp=7946就ok了.
BBED> p dba 4,547 kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 4
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 26
sb2 kdbhfseo @132 7972
sb2 kdbhavsp @134 7988
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kdbhtosp @136 7992
BBED> assign dba 4,547 kdbh.kdbhavsp=7946
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbhavsp @134 7946
BBED> sum apply dba 4,547
Check value for File 4, Block 547:
current = 0x6371, required = 0x6371
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7f60bb6a527c
kdbchk: space available on commit is incorrect
tosp=7992 fsc=0 stb=0 avsp=7946
Block 547 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--//avsp+fsc+stb=tops,7946+0+0=7975,fsc=0.要修改kdbh.kdbhtosp=kdbh.kdbhavsp。
BBED> assign dba 4,547 kdbh.kdbhtosp= dba 4,547 kdbh.kdbhavsp
sb2 kdbhtosp @136 7946
BBED> p dba 4,547 kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 4
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 26
sb2 kdbhfseo @132 7972
sb2 kdbhavsp @134 7946
sb2 kdbhtosp @136 7946
BBED> sum apply dba 4,547
Check value for File 4, Block 547:
current = 0x6343, required = 0x6343
BBED> verify dba 4,547
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
--//總之,剩下的修復很煩,自己不經常做也會搞暈.而且在修改前一定要做好備份,特別是生產系統!!
--//實際上就是2步:(在itl槽覆蓋後,不覆蓋要修改itl槽的對於資訊設定fsc=0)
1.dtl-used+fsc=avsp.
used=118 fsc=0 avsp=7988 dtl=8064
8064-118+0 = 7946
assign dba 4,547 kdbh.kdbhavsp=7946
2.assign dba 4,547 kdbh.kdbhtosp= dba 4,547 kdbh.kdbhavsp
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2156206/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180614]刪除bootstrap$記錄無法啟動2boot
- [20180612]刪除bootstrap$記錄無法啟動.txtboot
- 刪除redo所有日誌,資料庫無法啟動資料庫
- 刪除Chrome位址列記錄中自動補全的網址Chrome
- win10任務檢視記錄無法刪除的解決辦法Win10
- SAP RETAIL 事務程式碼WRDL - 刪除自動補貨執行記錄AI
- 刪除檔案或目錄提示"檔案或目錄無法刪除"的解決方法！
- Elastic Beanstalk-自動生成的S3無法刪除問題ASTBeanS3
- win10任務檢視記錄無法刪除怎麼辦_win10任務檢視記錄刪除不掉解決方法Win10
- 移動端除錯方法補充除錯
- 怎麼刪除電腦中開啟的檔案記錄？刪除電腦中開啟的檔案記錄方法教程
- [Kubernetes]node節點pod無法啟動/節點刪除網路重置
- mysql刪除一條記錄MySql
- 刪除Oracle重複記錄Oracle
- oracle刪除重複記錄Oracle
- dbca無法刪除資料庫資料庫
- 轉載：關於oracle無法啟動儲存，記錄一下Oracle
- 360打補丁後系統無法啟動的解決辦法
- LnixuAS3Mysql無法啟動問題S3MySql
- winxp 刪除輸入法; msconfig 配置啟動程式
- 用“稽核物件”功能記錄檔案刪除記錄物件
- windows10更新記錄刪除_怎樣刪除win10更新歷史記錄WindowsWin10
- 刪除mac啟動臺launchpad中的無效圖示，重置啟動臺Mac
- 刪除不掉的資料記錄
- Mongodb原始碼分析--刪除記錄MongoDB原始碼
- win10 1803系統中時間線無法刪除記錄如何解決Win10
- AIX 檔案過多無法刪除AI
- 資料夾刪除不掉怎麼辦？資料夾無法刪除
- oracle 快速刪除大批量資料方法（全部刪除，條件刪除，刪除大量重複記錄）Oracle
- VS2005無法啟動除錯問題除錯
- 刪除EFI系統分割槽（ESP）後Windows無法啟動，重建引導分割槽並修復啟動的過程Windows
- mysql表刪除重複記錄方法MySql
- DB2 刪除重複記錄DB2
- 找回Oracle中Delete刪除的記錄Oracledelete
- Linux--/tmp目錄檔案重啟後自動刪除Linux
- Windows上的資料夾無法刪除Windows
- 修改daemon.json重新載入後docker無法啟動問題記錄JSONDocker
- oracle 快速刪除大批量資料方法（全部刪除，條件刪除，刪除大量重複記錄） 轉Oracle