oracle创建数据表kcb,【案例】Oracle遇到bootstrap$基表坏块的解决办法

56 阅读 0 评论 37 点赞

我是靠谱客的博主传统钥匙，最近开发中收集的这篇文章主要介绍oracle创建数据表kcb,【案例】Oracle遇到bootstrap$基表坏块的解决办法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

【案例】Oracle遇到bootstrap$基表坏块的解决办法

时间:2016-11-03 10:16 来源:Oracle研究中心作者:HTZ 点击:

次

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oracle数据库无法启动，发现遇到基表bootstrap$存在坏块，使用BBED工具进行修复。recover bootstrap$ corrupt block with dd tool

在11G中ORACLE提供DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP直接切换boostrap表到用户指定的表，但是前提数据库必须启动到upgrade状态，如果bootstrap块损坏，数据库都不能启动，当然也不能使用DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP。DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP这个工具还是有很多作用的，将在后面会测试。

下面是测试直接使用dd清除bootstrap$表的块，再使用dd其它数据库的块来恢复bootstrap$ge 。

注意下面测试平台，请非在生产环境测试。

1 环境介绍

是在linux平台，ORACLE 11.2.0.3环境下，其它的环境没有做测试

oracleplus.net> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

oracleplus.net> !uname -a

Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

2 BOOTSTRAP$表

oracleplus.net> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

oracleplus.net> select name from v$dbfile;

NAME

--------------------------------------------------------------------------------

/oracle/app/oracle/oradata/orcl1123/system01.dbf

/oracle/app/oracle/oradata/orcl1123/sysaux01.dbf

/oracle/app/oracle/oradata/orcl1123/undo1.dbf

/oracle/app/oracle/oradata/orcl1123/user02.dbf

/oracle/app/oracle/oradata/orcl1123/user01.dbf

这里可以看到BOOTSTRAP$表使用的块是520到527

oracleplus.net> @extent.sql

Enter value for owner: sys

Enter value for segment_name: bootstrap$

Enter value for tablespace_name:

FILE BLOCK

OWNER:SEGMENT_NAME ID FNO EXTENT_ID BEGIN_END

------------------------ ----- ----- ---------- -------------------------

SYS.BOOTSTRAP$ 1 1 0 520~527

****************************** Oracle о

Total:

oracleplus.net> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

3 dd数据文件

这里dd数据的时候，千万要记住在conv=notrunc增加上，不然后果就悲剧了

oracleplus.net> !dd if=/dev/zero of=/oracle/app/oracle/oradata/orcl1123/system01.dbf bs=8192 seek=520 count=8 conv=notrunc

8+0 records in

8+0 records out

4 数据库报坏块

oracleplus.net> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 1493174472 bytes

Database Buffers 637534208 bytes

Redo Buffers 4947968 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-01578: ORACLE data block corrupted (file # 1, block # 520)

ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl1123/system01.dbf'

Process ID: 14852

Session ID: 1 Serial number: 5

这里可以看到数据库启动的时候报520是坏块的错误

下面来看看10046生成的TRACE文件信息

oracleplus.net> oradebug setmypid

Statement processed.

oracleplus.net> oradebug event 10046 trace name context forever,level 12;

Statement processed.

oracleplus.net> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_15095.trc

WAIT #182936778792: nam='instance state change' ela= 874 layer=2 value=1 waited=1 obj#=-1 tim=1416486056350145

WAIT #182936778792: nam='db file sequential read' ela= 9 file#=1 block#=520 blocks=1 obj#=-1 tim=1416486056350361

Hex dump of (file 1, block 520)

这里可以看到dump 520这个块的内容，从这里可以看到块完全是一个空块

Dump of memory from 0x00000000B2F66000 to 0x00000000B2F68000

0B2F66000 0000A200 00400208 00000000 01010000 [......@.........]

0B2F66010 00000000 00000000 00000000 00000000 [................]

Repeat 509 times

0B2F67FF0 00000000 00000000 00000000 00000001 [................]

Corrupt block relative dba: 0x00400208 (file 1, block 520)

Completely zero block found during buffer read

Reading datafile '/oracle/app/oracle/oradata/orcl1123/system01.dbf' for corruption at rdba: 0x00400208 (file 1, block 520)

WAIT #182936778792: nam='Disk file Mirror Read' ela= 4 fileno=1 blkno=520 filetype=0 obj#=-1 tim=1416486056351002

Reread (file 1, block 520) found same corrupt data (no logical check)

...............

Byte offset to file# 1 block# 520 is 4259840

Incident 464209 created, dump file: /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/incident/incdir_464209/orcl1123_ora_15095_i464209.trc

ORA-01578: ORACLE data block corrupted (file # 1, block # 520)

ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl1123/system01.dbf'

ORA-00704: bootstrap process failure

ORA-01578: ORACLE data block corrupted (file # 1, block # 520)

ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl1123/system01.dbf'

ORA-00704: bootstrap process failure

ORA-01578: ORACLE data block corrupted (file # 1, block # 520)

ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl1123/system01.dbf'

*** 2014-11-20 20:20:58.149

USER (ospid: 15095): terminating the instance due to error 704

EXEC #182936778792:c=726890,e=1999361,p=1,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1416486058194414

ERROR #182936778792:err=1092 tim=1416486058194441

5 DD复制其它数据库块

oracleplus.net> !dd if=/oracle/app/oracle/oradata/test/system01.dbf of=/oracle/app/oracle/oradata/orcl1123/system01.dbf bs=8192 count=8 seek=520 skip=520 conv=notrunc

8+0 records in

8+0 records out

这里直接使用dd复制其它数据库的块过来，因为bootstrap$在相同的版本内容都是一致的，其实基本上不会发生变化的。其实使用DD复制还可以使用system undo段的。

[oracle@www.htz.pw sql]$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 20 20:23:16 2014

Connected to an idle instance.

oracleplus.net> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 1493174472 bytes

Database Buffers 637534208 bytes

Redo Buffers 4947968 bytes

Database mounted.

Database opened.

这里看到数据库已经正常启动

下面查看一下块的kcbh的信息

oracleplus.net> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

orcl1123 OPEN

BBED> set block 520

BLOCK# 520

BBED> map

File: /oracle/app/oracle/oradata/orcl1123/system01.dbf (0)

Block: 520 Dba:0x00000000

------------------------------------------------------------

Unlimited Data Segment Header

struct kcbh, 20 bytes @0

struct ktech, 72 bytes @20

struct ktemh, 16 bytes @92

struct ktetb[1], 8 bytes @108

struct ktshc, 8 bytes @4148

struct ktsfs_seg[1], 20 bytes @4156

struct ktsfs_txn[16], 320 bytes @4176

ub4 tailchk @8188

BBED> p kcbh

struct kcbh, 20 bytes @0

ub1 type_kcbh @0 0x10

ub1 frmt_kcbh @1 0xa2

ub1 spare1_kcbh @2 0x00

ub1 spare2_kcbh @3 0x00

ub4 rdba_kcbh @4 0x00400208

ub4 bas_kcbh @8 0x00000252

ub2 wrp_kcbh @12 0x0000

ub1 seq_kcbh @14 0x01

ub1 flg_kcbh @15 0x04 (KCBHFCKV)

ub2 chkval_kcbh @16 0xe443

ub2 spare3_kcbh @18 0x0000

BBED> set block 519

BLOCK# 519

BBED> map

File: /oracle/app/oracle/oradata/orcl1123/system01.dbf (0)

Block: 519 Dba:0x00000000

------------------------------------------------------------

KTB Data Block (Index Leaf)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdxle, 32 bytes @92

sb2 kd_off[359] @124

ub1 freespace[234] @842

ub1 rowdata[7044] @1076

ub4 tailchk @8188

BBED> p kcbh

struct kcbh, 20 bytes @0

ub1 type_kcbh @0 0x06

ub1 frmt_kcbh @1 0xa2

ub1 spare1_kcbh @2 0x00

ub1 spare2_kcbh @3 0x00

ub4 rdba_kcbh @4 0x00400207

ub4 bas_kcbh @8 0x005cea63

ub2 wrp_kcbh @12 0x0000

ub1 seq_kcbh @14 0x01

ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)

ub2 chkval_kcbh @16 0x8f8a

ub2 spare3_kcbh @18 0x0000

这里发现2个块的SCN值完全不一样，怀疑ORACLE在启动的时候没有去验证BOOTSTRAP$块的SCN值的情况，后面再去测试一下

6 DBV验证数据文件

oracleplus.net> !dbv file=/oracle/app/oracle/oradata/orcl1123/system01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Nov 20 22:32:26 2014

DBVERIFY - Verification starting : FILE = /oracle/app/oracle/oradata/orcl1123/system01.dbf

Page 392 is marked corrupt

Corrupt block relative dba: 0x00400188 (file 1, block 392)