《课程笔记 | Oracle DBA武林秘籍》-UNDO坏块导致的数据库宕机

一.处理方法

1.数据库是open状态

创建新的UNDO ，当前UNDO切换到新创建的UNDO ，删除旧的 UNDO 就可以解决；

2.数据库不是open状态

这时可以使用两个隐含参数"_CORRUPTED_ROLLBACK_SEGMENTS","_offline_rollback_segments", 两个参数效果很相似。

(1)_CORRUPTED_ROLLBACK_SEGMENTS

表示数据库启动时跳过损坏的回滚段，如果无法确认具体哪个回滚段损坏，会选择跳过所有回滚段，具体用法是在参数文件中增加参数，

_CORRUPTED_ROLLBACK_SEGMENTS=(SYSSMU1$, _SYSSMU2$, _SYSSMU3$, ...etc)

具体回滚段名称需要提前查出来。

(2)_offline_rollback_segments

表示在数据库启动时，可以指定部分回滚段offline;

3.查询undo段

3.1.使用操作系统命令strings

strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU
Add the following parameter in init.ora:
UNDO_MANAGEMENT=MANUAL
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS=(SYSSMU1$, _SYSSMU2$, _SYSSMU3$, …etc)
Startup the database using pfile:
startup pfile=‘init.ora’
show parameter corrupt;
Recover and open the database:
RECOVER DATABASE UNTIL CANCEL;
Cancel
ALTER DATABASE OPEN RESETLOGS;

3.2.bbed 工具
可以在数据库不启动情况下，直接查询或修改数据块部分信息，当然也可以查询 UNDO 段名称；
11g 开始已经没有这个工具了，需要单独下载；
11g 版本，undo$ 表信息一般位于1号文件第225个数据块中。

set file 1 block 225
map
x /rnc *kdbr[0]
x /rnc *kdbr[1]

二.故障模拟

环境描述：
CentOS Linux release 7.9.2009
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.1.查看undo段头块位置

SQL> set line 200
SQL> col segment_name for a80
SQL> select header_file,header_block,segment_name from dba_segments where segment_name like '_SYSSMU%';

HEADER_FILE HEADER_BLOCK SEGMENT_NAME
----------- ------------ --------------------------------------------------------------------------------
	  3	     128 _SYSSMU1_3724004606$
	  3	     144 _SYSSMU2_2996391332$
	  3	     160 _SYSSMU3_1723003836$
	  3	     176 _SYSSMU4_1254879796$
	  3	     192 _SYSSMU5_898567397$
	  3	     208 _SYSSMU6_1263032392$
	  3	     224 _SYSSMU7_2070203016$
	  3	     240 _SYSSMU8_517538920$
	  3	     256 _SYSSMU9_1650507775$
	  3	     272 _SYSSMU10_1197734989$

10 rows selected.

2.2.通过BBED 工具，破坏UNDO某一段的段头块(file=3 block=128)

破坏的方式是直接将其他的数据块覆盖段头块

SQL> col name for a60
SQL> select  FILE#,NAME from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
	 1 /data/app/oracle/oradata/orcl/system01.dbf
	 2 /data/app/oracle/oradata/orcl/sysaux01.dbf
	 3 /data/app/oracle/oradata/orcl/undotbs01.dbf
	 4 /data/app/oracle/oradata/orcl/users01.dbf
	 5 /data/app/oracle/oradata/orcl/example01.dbf

SQL> 
[oracle@single ~]$ cat listfile.txt 
1 /data/app/oracle/oradata/orcl/system01.dbf
2 /data/app/oracle/oradata/orcl/sysaux01.dbf
3 /data/app/oracle/oradata/orcl/undotbs01.dbf
4 /data/app/oracle/oradata/orcl/users01.dbf
5 /data/app/oracle/oradata/orcl/example01.dbf
[oracle@single ~]$ 
[oracle@single bbed]$ bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/home/oracle/listfile.txt

BBED: Release 2.0.0.0.0 - Limited Production on Tue Aug 23 14:21:50 2022

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,128
 File: /data/app/oracle/oradata/orcl/undotbs01.dbf (3)
 Block: 272              Offsets:    0 to  511           Dba:0x00c00110
------------------------------------------------------------------------
 0ba20000 01004000 00000000 00000104 60420000 00000000 0004200b 7b820661 
 4f52434c 00000000 5f7ce441 007c0100 00200000 01000300 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 08024000 07000000 00000000 7dc92131 fd369141 06200e00 00000000 00000000 
 00000000 00000000 00000420 cd72e441 86f14342 cc72e441 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 06005359 5354454d 00000000 00000000 
 00000000 00000000 00000000 00000000 01000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000a00 
 7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 61d65800 00000000 514d5e42 0100c05d 76000000 02000000 10000000 
 <32 bytes per line>
BBED> sum apply; 
Check value for File 3, Block 128:
current = 0x137f, required = 0x137f
BBED> verify
DBVERIFY - Verification starting
FILE = /data/app/oracle/oradata/orcl/undotbs01.dbf
BLOCK = 128

Block 128 is corrupt
Corrupt block relative dba: 0x00400080 (file 0, block 128)
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: 0x137f
 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> 

2.3.模拟异常断电

SQL> shutdown abort;
ORACLE instance shut down

2.4.启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2253624 bytes
Variable Size		  989859016 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7393280 bytes
Database mounted.
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01578: ORACLE data block corrupted (file # 3, block # 128)
ORA-01110: data file 3: '/data/app/oracle/oradata/orcl/undotbs01.dbf'
Process ID: 4457
Session ID: 191 Serial number: 3
SQL> 

2.5.找到损坏的undo段

SQL> create pfile='/home/oracle/pfile.ora' from spfile;
File created

[oracle@single ~]$ strings /data/app/oracle/oradata/orcl/system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU
[oracle@single ~]$ cat listSMU 
	<9    ' and substr(drs.segment_name,1,7) != ''_SYSSMU''');
 and substr(drs.segment_name,1,7) != '_SYSSMU'
 and substr(drs.segment_name,1,7) != '_SYSSMU' 
	c;    ' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' ||
_SYSSMU10_1197734989
_SYSSMU10_3470984480
_SYSSMU11_894599432
_SYSSMU12_1573055333
_SYSSMU1_2603659607
_SYSSMU13_3860906822
_SYSSMU1_3724004606
_SYSSMU14_3319140121
_SYSSMU15_1436577151
_SYSSMU16_1689093467
_SYSSMU17_1049158485
_SYSSMU18_1557221903
_SYSSMU19_2284825117
_SYSSMU20_2312497597
_SYSSMU2_2996391332
_SYSSMU2_73114111
_SYSSMU3_1723003836
_SYSSMU3_596277271
_SYSSMU4_1254879796
_SYSSMU4_2523322691
_SYSSMU5_4008018903
_SYSSMU5_898567397
_SYSSMU6_1263032392
_SYSSMU6_4235600416
_SYSSMU7_2070203016
_SYSSMU7_2271882308
_SYSSMU8_517538920
_SYSSMU8_854328387
_SYSSMU9_1650507775
_SYSSMU9_508477954
[oracle@single ~]$

2.6.利用pfile启动数据库

#*.undo_tablespace='UNDOTBS1'
*.UNDO_MANAGEMENT=MANUAL
*._ALLOW_RESETLOGS_CORRUPTION = TRUE
*._CORRUPTED_ROLLBACK_SEGMENTS = '_SYSSMU1_3724004606$'

SQL> startup pfile='/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size		    2253624 bytes
Variable Size		  989859016 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7393280 bytes
Database mounted.
Database opened.
SQL>

需要注意的是，用strings这种方式得到的段名后面需要加$.