[20190226]删除tab$记录的恢复6.txt
--//春节前几天做了删除tan$记录的测试,链接:
http://blog.itpub.net/267265/viewspace-2565245/=> [20190130]删除tab$记录的恢复.txt
http://blog.itpub.net/267265/viewspace-2565250/=> [20190130]删除tab$记录的恢复2.txt
--//我这样恢复后,仅仅能读,不能建表等操作,必须解决bbed verify报6110,6111,6112之类的错误问题.链接:
http://blog.itpub.net/267265/viewspace-2629866/=> [20190212]删除tab$记录的恢复3.txt
--//当时这些恢复,我tab$的索引i_tab1是禁用的.今天尝试恢复索引看看.
--//做了这个测试:链接https://www.cnblogs.com/lfree/p/10438177.html
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> select rowid,a.* from SYS.BOOTSTRAP$ a where a.sql_text like '%I_TAB1%';
ROWID LINE# OBJ# SQL_TEXT
------------------ ---------- ---------- ------------------------------------------------------------
AAAAA7AABAAAAILAAJ 33 33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAX
TRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAX
EXTENTS 2147483645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BL
OCK 312))
SYS@book> select count(*) from tab$ where BOBJ# is not null ;
COUNT(*)
----------
139
--//可以发现索引的键值仅仅139个,也就是索引很小.可以尝试使用bbed解决这个问题.
--//索引段头在dba =1,312,这样索引root节点在dba =1,313.
2.先观察没有删除的情况:
BBED> info all
File# Name Size(blks)
----- ---- ----------
1 /mnt/ramdisk/book/system01.dbf 0
101 /home/oracle/backup/book_20190122_good/system01.dbf 0
201 /home/oracle/backup/book_20190122_bad/system01.dbf 0
BBED> set dba 1,313
DBA 0x00400139 (4194617 1,313)
BBED> p kd_off
sb2 kd_off[0] @124 8032
sb2 kd_off[1] @126 0
sb2 kd_off[2] @128 8020
sb2 kd_off[3] @130 7996
sb2 kd_off[4] @132 7972
sb2 kd_off[5] @134 7960
sb2 kd_off[6] @136 7900
sb2 kd_off[7] @138 7864
sb2 kd_off[8] @140 7852
sb2 kd_off[9] @142 7840
sb2 kd_off[10] @144 7828
sb2 kd_off[11] @146 7816
...
sb2 kd_off[134] @392 6260
sb2 kd_off[135] @394 6218
sb2 kd_off[136] @396 6232
sb2 kd_off[137] @398 6190
sb2 kd_off[138] @400 6204
--//正好139个行目录.在一个块里面修复相对简单.
--//注:bbed看索引块存在问题,实际上kd_off[0], kd_off[1]记录的偏移量不对.实际上从kd_off[2]开始.
BBED> x /rnx dba 1,313 *kd_off[2]
rowdata[2002] @8112
-------------
flag@8112: 0x00 (NONE)
lock@8113: 0x00
data key:
col 0[2] @8115: 2
col 1[6] @8118: 0x00 0x40 0x00 0x91 0x00 0x00
BBED> x /rnx dba 201,313 *kd_off[2]
rowdata[2002] @8112
-------------
flag@8112: 0x01 (KDXRDEL)
lock@8113: 0x02
data key:
col 0[2] @8115: 2
col 1[6] @8118: 0x00 0x40 0x00 0x91 0x00 0x00
--//注:201是删除tab$的对应的数据文件.可以发现删除键值的flag=0x01.
--//这样恢复就简单了.但是注意这样遗漏2条索引键值记录.
$ seq 2 138 | xargs -I{} echo 'x /rnx dba 201,313 *kd_off[{}]' | rlbbed | grep -B1 "^lock@.*:*0x02$" | grep "^flag@.*:.*0x01"|cut -d: -f1 | cut -d@ -f2 | xargs -I{} echo assign dba 1,313 offset {}=0x00 > index_bbed.txt
--//grep 过滤lock的相关信息你的测试可能与我的不同(可能使用不同itl槽处理事务),根据实际需要修改.
--//注意:我写的脚本坏文件号为201.
$ head -3 index_bbed.txt
assign dba 1,313 offset 8112=0x00
assign dba 1,313 offset 8088=0x00
assign dba 1,313 offset 8064=0x00
$ tail -3 index_bbed.txt
assign dba 1,313 offset 6324=0x00
assign dba 1,313 offset 6282=0x00
assign dba 1,313 offset 6296=0x00
$ wc index_bbed.txt
137 685 4658 index_bbed.txt
--//137条,还差2条.
BBED> p kd_off[138]
sb2 kd_off[138] @400 6204
BBED> dump offset 402 count 4
File: /mnt/ramdisk/book/system01.dbf (1)
Block: 313 Offsets: 402 to 405 Dba:0x00400139
---------------------------------------------------------
94178617
<64 bytes per line>
--//说明: kd_off[138]的偏移量记录在偏移400,下面2条记录的偏移记录从402算起.
--// 9417 颠倒过来就是 0x1794=6036, 记录相对偏移从kdxle算起(当前是92,通过map命令可以确定kdxle的偏移), 6036+92 = 6128.
--// 8617 颠倒过来就是 0x1786=6022, 记录相对偏移从kdxle算起(当前是92,通过map命令可以确定kdxle的偏移), 6022+92 = 6114.
BBED> x /rnx dba 201,313 offset 6128
rowdata[18] @6128
-----------
flag@6128: 0x01 (KDXRDEL)
lock@6129: 0x02
data key:
col 0[4] @6131: 90426
col 1[6] @6136: 0x00 0x41 0x70 0x1e 0x00 0x01
BBED> x /rnx dba 201,313 offset 6114
rowdata[4] @6114
----------
flag@6114: 0x01 (KDXRDEL)
lock@6115: 0x02
data key:
col 0[4] @6117: 90426
col 1[6] @6122: 0x00 0x41 0x70 0x1e 0x00 0x02
--//追加如下2行到index_bbed.txt
echo assign dba 1,313 offset 6128=0x00 >> index_bbed.txt
echo assign dba 1,313 offset 6114=0x00 >> index_bbed.txt
3.开始尝试索引恢复:
--//先关闭数据库.恢复坏的数据库.
$ /bin/cp -r /home/oracle/backup/book_20190122_bad/* /mnt/ramdisk/book
*/
--//执行前面测试生成的脚本:
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/scan3_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/scan4k_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/clearout_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/notclearout_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/scan5_bbed.txt
--//修复索引标识标本.
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/index_bbed.txt
--//以下是修复一些错误.
--/OK
,现在修复了.
4.启动检查数据库情况:
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> select * from sys.tab$ minus select * from orachk001;
no rows selected
SYS@book> select * from orachk001 minus select * from sys.tab$;
no rows selected
SYS@book> ANALYZE TABLE sys.tab$ VALIDATE STRUCTURE CASCADE;
Table analyzed.
SYS@book> select count(*) from sys.tab$ where BOBJ# is not null ;
COUNT(*)
----------
139
SYS@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7nkaks3h1mmd2, child number 0
-------------------------------------
select count(*) from sys.tab$ where BOBJ# is not null
Plan hash value: 3621218687
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX FULL SCAN| I_TAB1 | 139 | 278 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TAB$@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BOBJ#" IS NOT NULL)
--//可以正常读取索引,到此删除tab$的恢复包括索引已经完美完成.累...........