[20190531]ORA-600 kokasgi1故障模擬與恢復(後續).txt
[20190531]ORA-600 kokasgi1故障模擬與恢復(後續).txt
--//http://blog.itpub.net/267265/viewspace-2646340/=>[20190531]ORA-600 kokasgi1故障模擬與恢復.txt
--//後續有一些恢復沒做,補充測試看看.
--//先更正連結http://blog.itpub.net/267265/viewspace-2646340/的一些錯誤:
--//1.前面做壞塊恢復時,少寫了執行步驟:.
BBED> assign kcbh.seq_kcbh = 0x01
--//2.使用system使用者登入時:
SYS@book> connect system/oracle
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [209], [6110], [], [], [], [], [], [], [], []
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
--//主要原因是口令不對(注:我恢復最原始的口令),導致要更新user$相應記錄,而塊沒有完全恢復,所以報錯.如果口令正確,不會出現以
--//上錯誤.
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
SYS@book> @ hide _db_always_check_system_ts
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------- ------------------------------------------------------------- ------------- ------------- ------------
_db_always_check_system_ts Always perform block check and checksum for System tablespace FALSE FALSE FALSE
2.修復塊dba=1,209看看.
--//user$的使用者SYS,SYSTEM改名.修正回來後dba 1,209存在問題.
BBED> set dba 1,209
DBA 0x004000d1 (4194513 1,209)
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 209
Block Checking: DBA = 4194513, Block Type = KTB-managed data block
data header at 0x7fa51592825c
kdbchk: the amount of space used is not equal to block size
used=2548 fsc=1 avsp=5515 dtl=8096
Block 209 failed with check code 6110
--//修改口令看看.
SYS@book> password system
Changing password for system
New password:
Retype new password:
Password changed
--//這也證明_db_always_check_system_ts=false,塊有一些瑕疵不會報錯.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
BBED> verify dba 1,209
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 209
Block Checking: DBA = 4194513, Block Type = KTB-managed data block
data header at 0x7ffe7f88e25c
kdbchk: the amount of space used is not equal to block size
used=2548 fsc=0 avsp=5516 dtl=8096
Block 209 failed with check code 6110
--//還是存在.連續執行2次sqlplus system/aaa,這樣裡面不成功登入計數改變,會導致記錄長度變化,看看.
BBED> x /rcncnnttttncnnnnnccnnncct *kdbr[27]
rowdata[0] @2113
----------
flag@2113: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@2114: 0x02
cols@2115: 22
ckix@2116: 5
col 0[6] @2117: SYSTEM
col 1[2] @2124: 1
col 2[16] @2127: 2D594E86F93B17A1
col 3[1] @2144: 0
col 4[2] @2146: 3
col 5[7] @2149: 2013-08-24 11:37:40
col 6[7] @2157: 2019-05-31 11:43:38
col 7[0] @2165: *NULL*
col 8[0] @2166: *NULL*
col 9[1] @2167: 0
col 10[0] @2169: *NULL*
col 11[2] @2170: 1
col 12[0] @2173: *NULL*
col 13[0] @2174: *NULL*
col 14[1] @2175: 0
col 15[2] @2177: 2
--// 2次不成功登入.
col 16[22] @2180: DEFAULT_CONSUMER_GROUP
col 17[0] @2203: *NULL*
col 18[1] @2204: 0
col 19[0] @2206: *NULL*
col 20[0] @2207: *NULL*
col 21[62] @2208: S:0CDF21806AF97030971BEB57BB609CA72A6DB6B1989178BC1CBF5A82C39C
BBED> verify dba 1,209
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 209
Block Checking: DBA = 4194513, Block Type = KTB-managed data block
data header at 0x7f4cc456b25c
kdbchk: the amount of space used is not equal to block size
used=2549 fsc=0 avsp=5515 dtl=8096
Block 209 failed with check code 6110
--//對比前面fsc=0已經發現變化.開始修復:
--//公式 dtl= used+fsc+avsp
--//dtl-used-fsc= 8096 - 2549 - 0 = 5547
BBED> p dba 1,209 kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 2
sb2 kdbhnrow @94 43
sb2 kdbhfrre @96 -1
sb2 kdbhfsbo @98 108
sb2 kdbhfseo @100 2021
sb2 kdbhavsp @102 5515
sb2 kdbhtosp @104 5515
--//assign kdbh.kdbhavsp=5547
BBED> assign kdbh.kdbhavsp=5547
sb2 kdbhavsp @102 5547
BBED> sum apply
Check value for File 1, Block 209:
current = 0x3ccd, required = 0x3ccd
BBED> verify dba 1,209
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 209
Block Checking: DBA = 4194513, Block Type = KTB-managed data block
data header at 0x18e105c
kdbchk: avsp(5547) > tosp(5515)
Block 209 failed with check code 6128
--//assign kdbh.kdbhtosp = kdbh.kdbhavsp
BBED> assign kdbh.kdbhtosp = kdbh.kdbhavsp
sb2 kdbhtosp @104 5547
BBED> sum apply
Check value for File 1, Block 209:
current = 0x3ced, required = 0x3ced
BBED> verify dba 1,209
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 209
--//OK dba=1,209已經恢復.
2.修復索引I_user1看看.
--//根據前面的修復,該索引在dba = 1,417 .
--//相關資訊可以參考:http://blog.itpub.net/267265/viewspace-2637037/=>[20190226]刪除tab$記錄的恢復6.txt
--//參考:https://www.cnblogs.com/lfree/p/10438177.html=>[20190226]測試使用bbed恢復索引.txt
SYS@book> @ bbvi 1 417
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 3416064 -s 8192 /mnt/ramdisk/book/system01.dbf
xxd -c16 -g 2 -s 3416064 -l 8192 /mnt/ramdisk/book/system01.dbf
dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=417 count=1 of=1_417.dd conv=notrunc 2>/dev/null
od -j 3416064 -N 8192 -t x1 -v /mnt/ramdisk/book/system01.dbf
hexdump -s 3416064 -n 8192 -C -v /mnt/ramdisk/book/system01.dbf
alter system dump datafile '/mnt/ramdisk/book/system01.dbf' block 417;
alter session set events 'immediate trace name set_tsn_p1 level 1';
alter session set events 'immediate trace name buffer level 4194721';
9 rows selected.
$ dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=417 count=1 of=1_417.dd conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000106025 seconds, 77.3 MB/s
$ strings -td -3 1_417.dd | egrep SYS
5864 SYSDW
5878 SYSTEMDW
6005 JAVASYSPRIV
6375 OWBSYS_AUDIT
6396 OWBSYS
6544 SYSMAN
6780 OLAPSYS
6835 MDSYS
6911 ORDSYS
7148 CTXSYS
7163 EXFSYS
7275 JAVASYSPRIV
7357 WMSYS
7370 APPQOSSYS
7627 GATHER_SYSTEM_STATISTICS
8027 SYSTEM
8102 SYS
--//也就是修復指向原來的位置. 減去-9 對應 kd_off的偏移.
--//8102-9 = 8093
--//8027-9 = 8018
BBED> x /rc *kd_off[86]
rowdata[2242] @8093
-------------
flag@8093: 0x01 (KDXRDEL)
lock@8094: 0x02
keydata[6]: 0x00 0x40 0x00 0xd1 0x00 0x01
data key:
col 0[3] @8102: SYS
BBED> x /rc *kd_off[87]
rowdata[4] @5855
----------
flag@5855: 0x00 (NONE)
lock@5856: 0x02
keydata[6]: 0x00 0x40 0x00 0xd1 0x00 0x01
data key:
col 0[5] @5864: SYSDW
BBED> x /rc *kd_off[88]
rowdata[684] @6535
------------
flag@6535: 0x00 (NONE)
lock@6536: 0x00
keydata[6]: 0x00 0x40 0x00 0xd4 0x00 0x0b
data key:
col 0[6] @6544: SYSMAN
BBED> x /rc *kd_off[89]
rowdata[2167] @8018
-------------
flag@8018: 0x01 (KDXRDEL)
lock@8019: 0x02
keydata[6]: 0x00 0x40 0x00 0xd1 0x00 0x06
data key:
col 0[6] @8027: SYSTEM
BBED> x /rc *kd_off[90]
rowdata[18] @5869
-----------
flag@5869: 0x00 (NONE)
lock@5870: 0x02
keydata[6]: 0x00 0x40 0x00 0xd1 0x00 0x06
data key:
col 0[8] @5878: SYSTEMDW
--//注意看flag標識,刪除標識是0x01 (KDXRDEL).對應rowid並沒有變化(keydata).
--//另外注意索引的特點是塊內無序,塊間有序.可以看出修改並不改變行目錄的順序.
--//注意鍵值一定按照行目錄排序的，oracle插入索引鍵值應該透過行目錄二分法定位，然後行目錄對應記錄後移。
assign offset 8093 = 0x0;
assign offset 5855 = 0x1;
assign offset 8018 = 0x0;
assign offset 5869 = 0x1;
BBED> sum apply
Check value for File 1, Block 417:
current = 0x049d, required = 0x049d
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 417
Block Checking: DBA = 4194721, Block Type = KTB-managed data block
**** actual free space credit for itl 2 = 35 != # in trans. hdr = 31
---- end index block validation
Block 417 failed with check code 6401
BBED> p /d ktbbh.ktbbhitl[1]._ktbitun
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 31
ub2 _ktbitwrp @86 31
BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=35
sb2 _ktbitfsc @86 35
BBED> p /d ktbbh.ktbbhitl[1]._ktbitun
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 35
ub2 _ktbitwrp @86 35
BBED> sum apply
Check value for File 1, Block 417:
current = 0x04a1, required = 0x04a1
--//OK.
3.修復禁用的索引.
BBED> set dba 1,522
DBA 0x0040020a (4194826 1,522)
BBED> x /rnnc *kdbr[19]
rowdata[228] @1754
------------
flag@1754: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@1755: 0x01
cols@1756: 0
BBED> assign offset 1754=0x2c;
ub1 rowdata[0] @1754 0x2c
BBED> x /rnnc *kdbr[19]
rowdata[228] @1754
------------
flag@1754: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1755: 0x01
cols@1756: 3
col 0[2] @1757: 46
col 1[2] @1760: 46
col 2[197] @1763: CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 OBJNO 46 EXTENTS (FILE 1 BLOCK 416))
BBED> sum apply
Check value for File 1, Block 522:
current = 0x1ee8, required = 0x1ee8
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 522
--//OK,現在全部修復.重啟資料庫
SYS@book> startup [150/10484]
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> select /*+ full(a) */ name from user$ a minus select name from user$ ;
no rows selected
SYS@book> select name from user$ minus select /*+ full(a) */ name from user$ a;
no rows selected
SYS@book> validate index i_user1;
Index analyzed.
--//ok,已經全部修復.
4.收尾:
--//修改引數_db_always_check_system_ts=true.
--//alter system reset "_db_always_check_system_ts";
--//重啟資料庫略.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2646419/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190531]ORA-600 kokasgi1故障模擬與恢復.txt
- ORA-00600 [kokasgi1]資料庫無法啟動的模擬與恢復資料庫
- 模擬一則ORA-600 [4194][][]故障並處理
- [20190531]建立job與commit.txtMIT
- [20190531]如何實現與執行.txt
- [20190531]Timestamp Oddity.txt
- vsan儲存資料恢復過程—虛擬機器故障恢復過程資料恢復虛擬機
- [20181105]ORA-00600[4000] 模擬故障(10g).txt
- postgreSQL 恢復至故障點 精準恢復SQL
- 紅警教育陸續推出多套資料恢復國賽模擬考題資料恢復
- Oracle 不完全恢復遇到的ORA-600錯誤Oracle
- oracle ora-600[2662]問題分析及異常恢復Oracle
- MySQL資料庫故障恢復MySql資料庫
- SQLServer異常故障恢復(二)SQLServer
- 【故障處理】ORA-600:[13013],[5001]故障處理
- [20190428]恢復oraInventory.txtAI
- mysql GTID主從複製故障後不停機恢復同步流程MySql
- [20231020]rename IDL_UB1$後使用bbed的恢復.txt
- 【伺服器資料恢復】HP EVA虛擬化磁碟陣列常見故障和資料恢復方案伺服器資料恢復陣列
- [20190531]lob型別pctversion 和 retention.txt型別
- 伺服器資料恢復—伺服器發生故障後怎麼恢復伺服器資料？伺服器資料恢復
- 【北亞資料恢復】硬碟壞道故障如何恢復資料？資料恢復硬碟
- 【伺服器資料恢復】VSAN故障導致虛擬機器無法訪問的資料恢復案例伺服器資料恢復虛擬機
- 【伺服器資料恢復】Vsan節點虛擬機器磁碟元件出現故障的資料恢復案例伺服器資料恢復虛擬機元件
- 一次rman恢復引起的nologging問題模擬
- [20231103]rename IDL_UB1$後使用bbed的恢復的後遺症.txt
- DM7使用DMRAMN對多次故障恢復後使用不同資料庫的歸檔進行恢復資料庫
- 「分散式技術專題」故障恢復分散式
- redis cluster 叢集故障恢復操作思路Redis
- 解析ESX SERVER故障資料恢復方法Server資料恢復
- 儲存硬碟故障後強制上線恢復所有資料過程硬碟
- 線上故障突突突？如何緊急診斷、排查與恢復
- 分散式資料庫事務故障恢復的原理與實踐分散式資料庫
- OracleORA-03113 ORA-600 [4193]故障處理Oracle
- [20181123]模擬ora-01555.txt
- 【伺服器資料恢復】RAID5崩潰後強制上線導致故障的資料恢復案例伺服器資料恢復AI
- 【伺服器資料恢復】Raid陣列更換故障硬碟後資料同步失敗的資料恢復案例伺服器資料恢復AI陣列硬碟
- MySQL 組複製故障恢復的有效策略MySql