帮客户检查环境时，发现sysaux表空间的数据文件有坏块，8月25日发生的，备份保留3个月，直接恢复处理。
rman备份log报错如下
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/14/2024 02:31:29
ORA-19566: exceeded limit of 0 corrupt blocks for file /data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf
Recovery Manager complete.
查看alert_orcl.log日志报错如下
2024-08-25T21:08:19.404068+08:00
ARC3 (PID:2213402): Archived Log entry 4886 added for T-1.S-4891 ID 0x3724ce74 LAD:1
2024-08-25T21:31:19.231170+08:00
KILL SESSION for sid=(1867, 64225):
Reason = profile limit idle_time
Mode = KILL SOFT -/-/NO_REPLAY
Requestor = PMON (orapid = 2, ospid = 2213246, inst = 1)
Owner = Process: USER (orapid = 525, ospid = 2355342)
Result = ORA-0
2024-08-25T22:10:22.273914+08:00
Hex dump of (file 3, block 152588) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_575427.trc
Corrupt block relative dba: 0x00c2540c (file 3, block 152588)
Fractured block found during multiblock buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x00c2540c
last change scn: 0x0000.0000.1a9d4078 seq: 0x1 flg: 0x06
spare3: 0x0
consistency value in tail: 0x656c7564
check value in block header: 0xdcc0
computed block checksum: 0xf829
Reading datafile '/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf' for corrupt data at rdba: 0x00c2540c (file 3, block 152588)
Reread (file 3, block 152588) found same corrupt data (no logical check)
Hex dump of (file 3, block 152589) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_575427.trc
Corrupt block relative dba: 0x00c2540d (file 3, block 152589)
Bad header found during multiblock buffer read
Data in bad block:
type: 1 format: 2 rdba: 0x00165d58
last change scn: 0x0000.80a4.0000130c seq: 0x62 flg: 0x47
spare3: 0x672d
consistency value in tail: 0x00012a58
check value in block header: 0x3272
computed block checksum: 0x0
Reading datafile '/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf' for corrupt data at rdba: 0x00c2540d (file 3, block 152589)
2024-08-25T22:10:22.282345+08:00
Corrupt Block Found
2024-08-25T22:10:22.282400+08:00
Reread (file 3, block 152589) found same corrupt data (no logical check)
TIME STAMP (GMT) = 08/25/2024 22:10:21
CONT = 0, TSN = 1, TSNAME = SYSAUX
RFN = 3, BLK = 152588, RDBA = 12735500
OBJN = 8706, OBJD = 8706, OBJECT = WRI$_ADV_OBJECTS, SUBOBJECT =
SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
2024-08-25T22:10:22.286419+08:00
Corrupt Block Found
TIME STAMP (GMT) = 08/25/2024 22:10:21
CONT = 0, TSN = 1, TSNAME = SYSAUX
RFN = 3, BLK = 152589, RDBA = 12735501
OBJN = 8706, OBJD = 8706, OBJECT = WRI$_ADV_OBJECTS, SUBOBJECT =
SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_575427.trc (incident=296740):
ORA-01578: ORACLE data block corrupted (file # 3, block # 152588)
ORA-01110: data file 3: '/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf'
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_296740/orcl_j000_575427_i296740.trc
2024-08-25T22:10:30.325869+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2024-08-25T22:10:34.631225+08:00
Hex dump of (file 3, block 152588) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_296740/orcl_mz00_575442_i296740_a.trc
Corrupt block relative dba: 0x00c2540c (file 3, block 152588)
Fractured block found during validation
Data in bad block:
type: 6 format: 2 rdba: 0x00c2540c
last change scn: 0x0000.0000.1a9d4078 seq: 0x1 flg: 0x06
spare3: 0x0
consistency value in tail: 0x656c7564
check value in block header: 0xdcc0
computed block checksum: 0xf829
Reread of blocknum=152588, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152588, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152588, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152588, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152588, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Hex dump of (file 3, block 152589) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_296740/orcl_mz00_575442_i296740_a.trc
Corrupt block relative dba: 0x00c2540d (file 3, block 152589)
Bad header found during validation
Data in bad block:
type: 1 format: 2 rdba: 0x00165d58
last change scn: 0x0000.80a4.0000130c seq: 0x62 flg: 0x47
spare3: 0x672d
consistency value in tail: 0x00012a58
check value in block header: 0x3272
computed block checksum: 0x0
Reread of blocknum=152589, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152589, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152589, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152589, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
Reread of blocknum=152589, file=/data/ORCL/datafile/o1_mf_sysaux_l1r6o7xr_.dbf. found same corrupt data
2024-08-26T00:05:41.474833+08:00
TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P16634 (5351) VALUES LESS THAN (TO_DATE(' 2024-08-26 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2024-08-26T02:00:00.119203+08:00
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
2024-08-26T02:30:05.025770+08:00
ALTER SYSTEM ARCHIVE LOG
SYS@orcl> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
3 152588 1 0 FRACTURED 0
3 152589 1 0 CORRUPT 0
RMAN> blockrecover datafile 3 block 152588,152589 ;
Starting recover at 23-OCT-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3698 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00003
channel ORA_DISK_1: reading from backup piece /bak/rman/FULLBAK_ORCL_20240817_1172_1
channel ORA_DISK_1: piece handle=/bak/rman/FULLBAK_ORCL_20240817_1172_1 tag=TAG20240817T023005
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:15:15
starting media recovery
archived log for thread 1 with sequence 5191 is already on disk as file /archlog/orcl/1_5191_1132248310.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4854
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4855
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240817_1174_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240817_1174_1 tag=TAG20240817T034954
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4856
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4857
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4858
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4859
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240818_1177_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240818_1177_1 tag=TAG20240818T023007
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4860
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4861
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4862
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4863
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240819_1179_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240819_1179_1 tag=TAG20240819T023007
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4864
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4865
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4866
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4867
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4868
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240820_1181_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240820_1181_1 tag=TAG20240820T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:45
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4869
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4870
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4871
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4872
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4873
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240821_1183_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240821_1183_1 tag=TAG20240821T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:36
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4874
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4875
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4876
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4877
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4878
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240822_1185_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240822_1185_1 tag=TAG20240822T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4879
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4880
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4881
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4882
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240823_1188_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240823_1188_1 tag=TAG20240823T023007
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4883
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4884
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4885
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4886
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4887
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4888
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4889
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240825_1203_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240825_1203_1 tag=TAG20240825T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:35
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4890
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4891
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4892
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4893
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240826_1212_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240826_1212_1 tag=TAG20240826T023007
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4894
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4895
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4896
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4897
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4898
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240827_1217_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240827_1217_1 tag=TAG20240827T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4899
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4900
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4901
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4902
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240828_1222_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240828_1222_1 tag=TAG20240828T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4903
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4904
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4905
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4906
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4907
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4908
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240829_1227_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240829_1227_1 tag=TAG20240829T023007
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:05
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4909
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4910
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4911
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4912
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4913
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240830_1232_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240830_1232_1 tag=TAG20240830T023006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:05
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4914
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4915
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4916
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4917
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4918
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4919
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240901_1248_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:05
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4920
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4921
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4922
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4923
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4924
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4925
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240901_1249_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240901_1249_1 tag=TAG20240901T023007
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:45
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4926
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4927
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4928
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4929
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240902_1258_1
channel ORA_DISK_1: piece handle=/bak/rman/ARCHBAK_ORCL_20240902_1258_1 tag=TAG20240902T023018
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4930
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4931
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4932
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4933
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4934
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4935
channel ORA_DISK_1: reading from backup piece /bak/rman/ARCHBAK_ORCL_20240903_1263_1
。。。。。。。。。。。。
。。。。。。。。。。。。
。。。。。。。。。。。。
channel ORA DISK 1:restore complete, elapsed time: 00:00:55
media recovery complete, elapsed time: 00:04:51
Finished recoverat 24-0CT-24
SYS@orcl> select * from v$database_block_corruption;
no rows selected
SYS@orcl>
