UNDO段头块损坏测试与修复,本次案例通过BBED工具模拟UNDO段头坏块，并在没有备份情况下启动数据库；
1 查看UNDO段头块位置
select header_file, header_block
from dba_segments
where segment_name like '_SYSSMU%'
order by 2;
2 通过BBED工具，破坏UNDO某一段的段头块(file=3 block=280)
破坏的方式是直接将其他的数据块覆盖段头块
[Oracle11@primary ~]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat Jul 30 18:00:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> copy dba 1,1 to dba 3,280
BBED> sum apply;
Check value for File 3, Block 280:
current = 0x599e, required = 0x599e
BBED> verify
DBVERIFY - Verification starting
FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf
BLOCK = 280
Block 280 is corrupt
Corrupt block relative dba: 0x00400118 (file 0, block 280)
Bad header found during verification
Data in bad block:
type: 11 format: 2 rdba: 0x00400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0xc8c7
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
---通过BBED和DBV检查结果都是file3,block 280损坏
[oracle11@primary orcl11]$ dbv file=undotbs01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Sat Jul 30 18:01:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf
Page 280 is marked corrupt
Corrupt block relative dba: 0x00c00118 (file 3, block 280)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x00400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0xc8c7
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 392
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 45
Total Pages Processed (Seg) : 23
Total Pages Failing (Seg) : 0
Total Pages Empty : 346
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1283208 (0.1283208)
---模拟异常断电
SQL> shutdown abort
ORACLE instance shut down.
---启动数据库，报错ORA-01578
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01578: ORACLE data block corrupted (file # 3, block # 280)
ORA-01110: data file 3: '/u02/app/oracle/oradata/orcl11/undotbs01.dbf'
Process ID: 8265
Session ID: 1 Serial number: 5
通常UNDO损坏，在没有备份的情况下，可以通过以下方式启动数据库
#*.undo_tablespace='UNDOTBS1' ----注释原UNDO表空间
#*.undo_management=AUTO ----UNDO管理方式改为手动
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM' ---将UNDO表空间改成SYSTEM
*._corrupted_rollback_segments=损坏的回滚段 ---屏蔽损坏的UNDO段
创建新的回滚段:
create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;
删除旧的回滚段:
drop tablespace UNDOTBS1 including contents and datafiles;
*.undo_tablespace='UNDOTBS2'
*.undo_management=AUTO
但是在mount状态下无法查询(创建或删除)回滚段
SQL> select * from v$rollname;
select * from v$rollname
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
无法创建新的UNDO表空间
SQL> create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;
create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on
*
ERROR at line 1:
ORA-01109: database not open
无法删除旧的UNDO表空间
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open
在数据库不能OPEN情况下，有两种方式可以查询数据库部分信息；
1：strings命令可以查询所有的UNDO回滚段名，包括已经删除的回滚段；
[oracle11@primary orcl11]$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU
[oracle11@primary orcl11]$ vim listSMU
_SYSSMU20_3293637928$
_SYSSMU20_379396250$
_SYSSMU20_379396250$
_SYSSMU13_811223436$
........
2：BBED工具也可以查询UNDO段名；
BBED> set file 1 block 225 -----Oracle 11g版本，undo$表信息一般位于1号文件第225个数据块中
FILE# 1
BLOCK# 225
BBED> map
File: /u02/app/oracle/oradata/orcl11/system01.dbf (1)
Block: 225 Dba:0x004000e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 48 bytes @20
struct kdbh, 14 bytes @68
struct kdbt[1], 4 bytes @82
sb2 kdbr[25] @86 -------含有25个UNDO段
ub1 freespace[6402] @136
ub1 rowdata[1650] @6538
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @86 8078
sb2 kdbr[1] @88 8011
sb2 kdbr[2] @90 7944
......
sb2 kdbr[22] @130 6603
sb2 kdbr[23] @132 6537
sb2 kdbr[24] @134 6470
BBED> x /rnc *kdbr[0] ----查看0号UNDO段名称
col 1[6] @8151: SYSTEM
BBED> x /rnc *kdbr[1] ----查看1号UNDO段名称
col 1[20] @8085: _SYSSMU1_4115952380$
如果UNDO段特别多，可以通过EXECL，自动生成多个x /rnc *kdbr[0]......*kdbr[n]命令，再将命令复制粘贴到BBED中，同时获取多个UNDO段名；
x /rnc *kdbr[0]
x /rnc *kdbr[1]
x /rnc *kdbr[2]
x /rnc *kdbr[3]
......
x /rnc *kdbr[24]
如果不能判断具体哪个回滚段出现问题，可以跳过所有的回滚段