bbed基本操作篇
bbed破坏数据文件
bbed修改数据内容
bbed恢复删除数据
bbed修改ASM中数据
bbed 找回被删除数据
bbed 删除普通表记录
通过bbed查看数据库结构
bbed 删除 cluster table 记录
bbed修改undo$(回滚段)状态
利用bbed找回ORACLE更新前值
bbed晋级提升篇
bbed解决ORA-01190
bbed 修改datafile header
重现ORA-600[4000]异常
bbed 使用实现 drop index 操作
使用bbed修复损坏datafile header
使用bbed解决ORA-00600[2662]
bbed 恢复 GLOBAL_NAME 为空故障
通过bbed解决ORA-00600[4000]案例
ORA-00600 [ktbdchk1: bad dscn] 解决
使用bbed让rac中的sysaux数据文件online
通过bbed修改回滚段状态解决ORA-00704故障
通过bbed模拟ORA-00607/ORA-00600[4194]故障
使用bbed解决ORA-00607/ORA-00600[4194]故障
Daily Archives: 2012 年 09 月 01 日
bbed 删除普通表记录
有朋友和我说我的bbed系列缺少一篇普通表使用bbed删除记录的文章,月底现场保证回来没睡意,完善这篇文章,也算是对bbed系列的一个终结.
创建模拟记录
SQL> create table t_xifenfei tablespace users
2 as
3 select * from dba_tables where rownum<10;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select table_name,owner,rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei;
TABLE_NAME OWNER ROWID REL_FNO BLOCKNO ROWNO
------------------------------ ------------------------------ ------------------ ---------- ---------- ----------
CON$ SYS AAAM9UAAEAAACA0AAA 4 8244 0
UNDO$ SYS AAAM9UAAEAAACA0AAB 4 8244 1
CDEF$ SYS AAAM9UAAEAAACA0AAC 4 8244 2
CCOL$ SYS AAAM9UAAEAAACA0AAD 4 8244 3
PROXY_ROLE_DATA$ SYS AAAM9UAAEAAACA0AAE 4 8244 4
FILE$ SYS AAAM9UAAEAAACA0AAF 4 8244 5
FET$ SYS AAAM9UAAEAAACA0AAG 4 8244 6
TS$ SYS AAAM9UAAEAAACA0AAH 4 8244 7
PROXY_DATA$ SYS AAAM9UAAEAAACA0AAI 4 8244 8
9 rows selected.
SQL> select dump('FILE$',16) from dual;
DUMP('FILE$',16)
----------------------------
Typ=96 Len=5: 46,49,4c,45,24
SQL> select dump('SYS',16) FROM DUAL;
DUMP('SYS',16)
----------------------
Typ=96 Len=3: 53,59,53
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
这里创建一张测试表,有9条记录,计划使用bbed删除file$的记录
bbed删除表记录
[oracle@xifenfei ~]$ bbed listfile=bbedfile
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat Sep 1 10:28:57 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/oradata/orcl/system01.dbf 0
3 /u01/oradata/orcl/sysaux01.dbf 0
4 /u01/oradata/orcl/users01.dbf 0
5 /u01/oradata/orcl/GS_ORADB_001.dbf 0
6 /u01/oradata/orcl/GS_ORADB_IDX_001.dbf 0
7 /u01/oradata/orcl/undo01.dbf 0
BBED> set file 4 block 8244
FILE# 4
BLOCK# 8244
BBED> map
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Dba:0x01002034
------------------------------------------------------------
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[9] @142
ub1 freespace[6137] @160
ub1 rowdata[1891] @6297
ub4 tailchk @8188
BBED> set count 32
COUNT 32
--查找对应值,估算起位置
BBED> find /x 494c4524
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6929 to 6960 Dba:0x01002034
------------------------------------------------------------------------
494c4524 06535953 54454dff ff055641 4c494402 c10b02c1 2902c102 03c20338
<32 bytes per line>
BBED> p *kdbr[7]
rowdata[209]
------------
ub1 rowdata[209] @6506 0x2c
--6506肯定不是在这个位置
BBED> p *kdbr[5]
rowdata[623]
------------
ub1 rowdata[623] @6920 0x2c
--6920包含了6929,可以确定在该位置
--查看对应值
BBED> x /rccc
rowdata[623] @6920
------------
flag@6920: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6921: 0x00
cols@6922: 49
col 0[3] @6923: SYS
col 1[5] @6927: FILE$
col 2[6] @6933: SYSTEM
col 3[0] @6940: *NULL*
col 4[0] @6941: *NULL*
col 5[5] @6942: VALID
col 6[2] @6948: ..
col 7[2] @6951: .)
col 8[2] @6954: ..
col 9[3] @6957: ..8
col 10[4] @6961: ..8%
col 11[0] @6966: *NULL*
col 12[2] @6967: ..
col 13[6] @6970: ..01%.
col 14[0] @6977: *NULL*
col 15[2] @6978: ..
col 16[2] @6981: ..
col 17[3] @6984: YES
col 18[1] @6988: N
col 19[2] @6990: ..
col 20[2] @6993: ..
col 21[1] @6996: .
col 22[1] @6998: .
col 23[1] @7000: .
col 24[2] @7002: .(
col 25[1] @7005: .
col 26[1] @7007: .
col 27[10] @7009: 1
col 28[10] @7020: 1
col 29[5] @7031: N
col 30[7] @7037: ENABLED
col 31[2] @7045: ..
col 32[7] @7048: xp....!
col 33[2] @7056: NO
col 34[0] @7059: *NULL*
col 35[1] @7060: N
col 36[1] @7062: N
col 37[2] @7064: NO
col 38[7] @7067: DEFAULT
col 39[8] @7075: DISABLED
col 40[3] @7084: YES
col 41[2] @7088: NO
col 42[0] @7091: *NULL*
col 43[8] @7092: DISABLED
col 44[3] @7101: YES
col 45[0] @7105: *NULL*
col 46[8] @7106: DISABLED
col 47[8] @7115: DISABLED
col 48[2] @7124: NO
BBED> d
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6920 to 6951 Dba:0x01002034
------------------------------------------------------------------------
2c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02
<32 bytes per line>
BBED> set mode edit
MODE Edit
--修改为delete状态
BBED> m /x 3c
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6920 to 6951 Dba:0x01002034
------------------------------------------------------------------------
3c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274
--验证不通过,因为空闲空间不正确(删除了数据还是以前的值当然不正确)
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x7f0a75d0327c
kdbchk: the amount of space used is not equal to block size
used=1722 fsc=0 avsp=6137 dtl=8064
Block 8244 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
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 9
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 36
sb2 kdbhfseo @132 6173
b2 kdbhavsp @134 6137
b2 kdbhtosp @136 6137
BBED> m /x c618 offset 134
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 134 to 165 Dba:0x01002034
------------------------------------------------------------------------
c618f917 00000900 b01ee11d 0a1d311c 5b1b8c1a be19ee18 1d180000 00000000
<32 bytes per line>
BBED> m /x c618 offset 136
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 136 to 167 Dba:0x01002034
------------------------------------------------------------------------
c6180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x13ef07c
kdbchk: space available on commit is incorrect
tosp=6342 fsc=0 stb=2 avsp=6342
Block 8244 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
BBED> m /x c8 offset 136
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 136 to 167 Dba:0x01002034
------------------------------------------------------------------------
c8180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000
<32 bytes per line>
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 9
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 36
sb2 kdbhfseo @132 6173
b2 kdbhavsp @134 6342
b2 kdbhtosp @136 6344
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa27a, required = 0xa27a
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
启动数据库测试
SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 2019320 bytes Variable Size 75497480 bytes Database Buffers 88080384 bytes Redo Buffers 2174976 bytes Database mounted. Database opened. SQL> set lines 150 SQL> select table_name,owner,rowid, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from t_xifenfei; TABLE_NAME OWNER ROWID REL_FNO BLOCKNO ROWNO ------------------------------ ------------------------------ ------------------ ---------- ---------- ---------- CON$ SYS AAAM9UAAEAAACA0AAA 4 8244 0 UNDO$ SYS AAAM9UAAEAAACA0AAB 4 8244 1 CDEF$ SYS AAAM9UAAEAAACA0AAC 4 8244 2 CCOL$ SYS AAAM9UAAEAAACA0AAD 4 8244 3 PROXY_ROLE_DATA$ SYS AAAM9UAAEAAACA0AAE 4 8244 4 FET$ SYS AAAM9UAAEAAACA0AAG 4 8244 6 TS$ SYS AAAM9UAAEAAACA0AAH 4 8244 7 PROXY_DATA$ SYS AAAM9UAAEAAACA0AAI 4 8244 8 8 rows selected.
可以看到file$这条记录已经被删除,证明bbed操作普通表删除成功
相关文章:
1.bbed 删除 cluster table 记录
2.bbed 找回被删除数据
3.利用bbed找回ORACLE更新前值
ORA-607/ORA-600[4194]不一定是重大灾难
以前解决过ORA-607/ORA-600[4194]和模拟过ORA-607/ORA-600[4194]错误,所以固定思维任务ORA-607/ORA-600[4194]可能就是重大灾难,通过这个案例来说明ORA-607/ORA-600[4194]可能也就是一个常规的不能再常规的错误:有一网友数据库因意外关闭电源导致启动过程出现ORA-00607/ORA-00600[4194]/ORA-00600[4097]的错误,使得数据库启动失败.
SMON: enabling tx recovery Fri Aug 31 23:14:08 2012 Database Characterset is ZHS16GBK replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=19, OS id=15619 Fri Aug 31 23:14:10 2012 Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc: ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], [] Fri Aug 31 23:14:12 2012 Non-fatal internal error happenned while SMON was doing logging scn->time mapping. SMON encountered 1 out of maximum 100 non-fatal internal errors. Fri Aug 31 23:14:12 2012 Completed: alter database open Fri Aug 31 23:14:14 2012 Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc: ORA-01595: error freeing extent (2) of rollback segment (4)) ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], [] Fri Aug 31 23:29:41 2012 Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc: ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [17], [10], [], [], [], [], [] Fri Aug 31 23:29:43 2012 Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc: ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], [] Fri Aug 31 23:29:44 2012 Errors in file /u01/oradata/orcl/bdump/orcl_pmon_15577.trc: ORA-00474: SMON process terminated with error Fri Aug 31 23:29:44 2012 PMON: terminating instance due to error 474 Instance terminated by PMON, pid = 15577
通过alert日志可以定位到SMON_SCN_TIME表或者其回滚操作可能异常,结合alert和trace分析,发现这次错误的操作主要sql语句为:
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Current SQL statement for this session:
UPDATE SYS.COL_USAGE$
SET EQUALITY_PREDS = EQUALITY_PREDS +
DECODE(BITAND(:FLAG, 1), 0, 0, 1),
EQUIJOIN_PREDS = EQUIJOIN_PREDS +
DECODE(BITAND(:FLAG, 2), 0, 0, 1),
NONEQUIJOIN_PREDS = NONEQUIJOIN_PREDS +
DECODE(BITAND(:FLAG, 4), 0, 0, 1),
RANGE_PREDS = RANGE_PREDS + DECODE(BITAND(:FLAG, 8), 0, 0, 1),
LIKE_PREDS = LIKE_PREDS + DECODE(BITAND(:FLAG, 16), 0, 0, 1),
NULL_PREDS = NULL_PREDS + DECODE(BITAND(:FLAG, 32), 0, 0, 1),
TIMESTAMP = :TIME
WHERE OBJ# = :OBJN
AND INTCOL# = :COLN
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [17], [10], [], [], [], [], []
Current SQL statement for this session:
UPDATE SYS.MON_MODS$
SET INSERTS = INSERTS + :INS,
UPDATES = UPDATES + :UPD,
DELETES = DELETES + :DEL,
FLAGS =
(DECODE(BITAND(FLAGS, :FLAG), :FLAG, FLAGS, FLAGS + :FLAG)),
DROP_SEGMENTS = DROP_SEGMENTS + :DROPSEG,
TIMESTAMP = :TIME
WHERE OBJ# = :OBJN
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO SMON_SCN_TIME
(THREAD,
TIME_MP,
TIME_DP,
SCN,
SCN_WRP,
SCN_BAS,
NUM_MAPPINGS,
TIM_SCN_MAP)
VALUES
(0, :1, :2, :3, :4, :5, :6, :7)
这里主要涉及到对oracle的三张表的操作
COL_USAGE$:主要是在收集统计信息的时候作为是否需要收集列直方图信息参考
MON_MODS$:Oracle主要利用该表来记录那些表的数据发生改变,方便收集统计信息
SMON_SCN_TIME:记录SCN和TIME的对应关系
通过这里的分析可以确定这三张表中的数据对于数据库来说不是致命的基表信息,在数据库运行过程中可以清理掉这些信息,最多就是因为数据库性能的下降或者SCN和TIME互转功能不完善.
解决思路
完整的undo异常处理顺序
1.从alert中可以看出来数据库是在open之后由于SMON回滚到上述几条sql异常导致数据库down,所以可以尝试使用system回滚段启动数据库,看看是否可以屏蔽相关问题
2.如果方法1不可行,那使用event屏蔽smon对回滚段的相关操作,使得数据库正常启动
3.如果由于存在特殊事务,event无法屏蔽,尝试使用隐含参数处理该问题
4.如果隐含参数尚无法解决给问题考虑使用bbed
5.如果bbed不能解决,那只能选择dul或者其类似工具处理
这个案例中我们明确的看到是因为上面的三条sql回滚异常出现问题导致,对于这样的问题,经过测试使用方法1和2都能够顺利解决问题(open库之后需要重建undo,删除有问题undo表空间,修改参数[可能包括event],切换undo表空间).因为遇到几次ORA-607/ORA-600[4194]是因为system rollback损坏导致,所以这次开始也认为是一次比较复杂的恢复,最后证明这次是一种非常常规的恢复.对于ORACLE的数据库恢复有经验可能会比较快的定位问题,但是如果按照固定的思路去想可能会让自己走进死胡同.
