模拟undo数据块损坏处理
一、检查数据文件和undo段
idle> select owner,segment_name,tablespace_name from dba_segments where tablespace_name='UNDOTBS1';
OWNER SEGMENT_NAME TABLESPACE_NAME
--------------- ------------------------- ------------------------------
SYS _SYSSMU1_1261223759$ UNDOTBS1
SYS _SYSSMU2_27624015$ UNDOTBS1
SYS _SYSSMU3_2421748942$ UNDOTBS1
SYS _SYSSMU4_625702278$ UNDOTBS1
SYS _SYSSMU5_2101348960$ UNDOTBS1
SYS _SYSSMU6_813816332$ UNDOTBS1
SYS _SYSSMU7_2329891355$ UNDOTBS1
SYS _SYSSMU8_399776867$ UNDOTBS1
SYS _SYSSMU9_1692468413$ UNDOTBS1
SYS _SYSSMU10_930580995$ UNDOTBS1
10 rows selected.
idle> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/t1.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
二、损坏undo
BBED> copy file 4 block 273 to file 4 block 272
File: /u01/app/oracle/oradata/ORCL/undotbs01.dbf (4)
Block: 272 Offsets: 0 to 511 Dba:0x01000110
------------------------------------------------------------------------
02a20000 11010001 c042b802 00005304 24310000 0a000c00 c8950000 39015252
0000e81f 901f041f ac1e201e c81d3c1d e41c581c 001c741b 1c1b901a 381af419
9c195819 0019bc18 64182018 c8178417 2c17e816 90164c16 f415b015 58151415
bc147814 2014dc13 84134013 e812a412 4c120812 b0116c11 1411d010 78103410
dc0f980f 400ffc0e a40e600e 080e7c0d 240d980c 400cb40b 5c0bd00a 780aec09
94090809 b0082408 cc074007 e8065c06 04067805 20059404 3c04b003 5803cc02
7402e801 90010401 00000000 00000000 00000000 00000000 00000000 08001800
10001400 42000000 40000000 00000000 00000000 0b011050 0000ac95 020d0200
01000300 11010001 2f014f00 cfc04100 80024000 ff120301 16001800 10003100
02000200 03000200 00000200 02000000 42000000 40000000 00000000 00000000
0b010c51 0000c895 020d9c0c 9d7f0000 11010001 39015000 a0c24100 80024000
ff120201 02000000 6c000700 fe7f0000 9bac5912 00000000 02000000 00000000
18007c00 01100000 28000000 c10c0000 c104104e c2361195 c1540200 c1030300
c1540001 0a001800 10001400 0d004000 43000000 43000000 00000000 00000000
0a160c50 0000c895 020d1400 18001000 11010001 39014f00 05020100 60024000
80bd4100 03000000 00000000 02c14502 c10c0600 41c2a000 7bc24100 16001800
<32 bytes per line>
BBED> sum apply;
Check value for File 4, Block 272:
current = 0x3124, required = 0x3124
三、重启
idle> shutdown abort
ORACLE instance shut down.
idle> startup
ORACLE instance started.
Total System Global Area 1056964480 bytes
Fixed Size 8904576 bytes
Variable Size 310378496 bytes
Database Buffers 729808896 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01578: ORACLE data block corrupted (file # 4, block # 272)
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/undotbs01.dbf'
Process ID: 23168
Session ID: 17 Serial number: 55028
四、dbv检查坏块
[oracle@19c01:/u01/app/oracle/product/19.0.0/db_1/dbs]$dbv file='/u01/app/oracle/oradata/ORCL/undotbs01.dbf'
DBVERIFY: Release 19.0.0.0.0 - Production on Mon Sep 19 05:32:36 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ORCL/undotbs01.dbf
Page 272 is marked corrupt
Corrupt block relative dba: 0x01000110 (file 4, block 272)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x01000111
last change scn: 0x0000.0000.02b842c0 seq: 0x53 flg: 0x04
spare3: 0x0
consistency value in tail: 0x42c00253
check value in block header: 0x3124
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 463360
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 454599
Total Pages Processed (Seg) : 9
Total Pages Failing (Seg) : 0
Total Pages Empty : 8760
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 45659789 (0.45659789)
五、rman检查坏块
RMAN> backup validate check logical database;
Starting backup at 2022-09-19 05:35:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/t1.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 20769 519687 45859854
File Name: /u01/app/oracle/oradata/ORCL/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 439458
Index 0 54367
Other 0 5086
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 22703 85760 45659789
File Name: /u01/app/oracle/oradata/ORCL/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 10933
Index 0 9982
Other 0 42142
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 8760 463360 45659789
File Name: /u01/app/oracle/oradata/ORCL/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 1 454600
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1149 1280 45563073
File Name: /u01/app/oracle/oradata/ORCL/t1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1
Index 0 0
Other 0 130
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 69 642 45586320
File Name: /u01/app/oracle/oradata/ORCL/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81
Index 0 17
Other 0 473
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25379.trc for details
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 646
Finished backup at 2022-09-19 05:36:17
六、重建undo
idle> alter database datafile 4 offline drop;
Database altered.
idle> alter database open;
Database altered.
idle> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/t1.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
idle> !rm -rf /u01/app/oracle/oradata/ORCL/undotbs01.dbf
idle> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/ORCL/undotbs02.dbf' size 50m;
Tablespace created.
idle> alter system set undo_tablespace=undotbs2 scope=spfile;
System altered.
idle> startup force
ORACLE instance started.
七、检查alert
Undo initialization recovery: err:0 start: 3197076 end: 3197092 diff: 16 ms (0.0 seconds)
[27743] Successfully onlined Undo Tablespace 5.
Undo initialization online undo segments: err:0 start: 3197092 end: 3197112 diff: 20 ms (0.0 seconds)
Undo initialization finished serial:0 start:3197076 end:3197116 diff:40 ms (0.0 seconds)
2022-09-19T05:58:19.202312+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_28084.trc:
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/undotbs01.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORCL/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
八、删除旧的undo
idle> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU10_930580995$' found, terminate dropping tablespace
idle> alter system set "_offline_rollback_segments"=('_SYSSMU10_930580995$') scope=spfile;
alter system set "_offline_rollback_segments"=('_SYSSMU10_930580995$') scope=spfile
idle> create pfile='/home/oracle/pfile' from spfile;
File created.
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
_offline_rollback_segments=(_SYSSMU10_930580995$)
idle> startup pfile='/home/oracle/pfile';
ORACLE instance started.
Total System Global Area 1056964480 bytes
Fixed Size 8904576 bytes
Variable Size 310378496 bytes
Database Buffers 729808896 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
idle> DROP ROLLBACK SEGMENT "_SYSSMU10_930580995$";
Rollback segment dropped.
idle> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
最后修改时间：2022-09-18 22:52:00
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
