The following errors appear in alert log:
Thu Sep 23 20:06:34 2021
Hex dump of (file 23, block 774506) in trace file /u01/app/oracle/diag/rdbms/testdb/TETSDB/trace/TETSDB_ora_7558.trc
Corrupt block relative dba: 0x000bd16a (file 23, block 774506)
Bad check value found during backing up datafile
Data in bad block:
type: 6 format: 2 rdba: 0x000bd16a
last change scn: 0x071e.81e8b798 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xb7980602
check value in block header: 0xe202
computed block checksum: 0x82fe
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/ctl_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
SOLUTION
The corrupted block can be fixed online without offline datafile or tablespace required.
double confirm the data corrution
Run dbverify utility
$dbv blocksize=8192 file=/u05/oradata/TETSDB/cdr_01.dbf
...
..
.
Corrupt block relative dba: 0x000bd16a (file 23, block 774506)
...
..
.
Total Pages Marked Corrupt : 1
Run RMAN utility
RMAN> connect target /
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup validate check logical database;
close channel c1;
close channel c2;
close channel c3;
close channel c4
}
OR
Just check specified file and blocks:
RMAN> validate check logical datafile 23 block 774505 to 774507;
Check block corruption from database view
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
23 774506 1 0 CHECKSUM
Check the impacted object
SQL> select
relative_fno, owner, segment_name, segment_type
from
dba_extents
where
file_id = 23
and
774506 between block_id and block_id + blocks - 1;
RELATIVE_FNO OWNER SEGMENT_NAME SEGMENT_TYPE
------------ ----------- ------------------ ------------------
1024 TEST CDR_IDX1 INDEX
fix the corruption
If it is an object like index, just rebuilt it even online rebuilding the index.
SQL> alter index TEST.CDR_IDX1 rebuild online parallel 8; Index altered. SQL> alter index TEST.CDR_IDX1 parallel 1; Index altered.
$ rman target / catalog rman/passwd@rman
connected to target database: TESTDB (DBID=1234567890)
connected to recovery catalog database
RMAN> run {
allocate channel ch1 TYPE 'SBT_TAPE';
SEND 'NSR_ENV=(NSR_SERVER=BKP_SERVER,NSR_CLIENT=DB_SERVER)';
BLOCKRECOVER DATAFILE 23 block 774506;
}
...
..
.
archived log for thread 1 with sequence 51992 is already on disk as file /fra/TESTDB/archivelog/2021_09_23/o1_mf_1_51992_jnrmdprf_.arc
archived log for thread 1 with sequence 51993 is already on disk as file /fra/TESTDB/archivelog/2021_09_23/o1_mf_1_51993_jnrpkppq_.arc
media recovery complete, elapsed time: 00:05:08
Finished recover at 23-SEP-21
released channel: ch1
RMAN>
Check the progressing:
SQL> select
username,sid, opname, target, sofar, totalwork, units,
to_char(start_time,'YYYYMMDD-HH24:MI:SS') StartTime,
time_remaining, message
from
V$SESSION_LONGOPS
where
time_remaining>60;
Finally check and confirm the corrupted block(s) have been fixed.
SQL> select * from v$database_block_corruption;
no rows selected
For some database versions, the record in v$database_block_corruption still exists , even there is no corrupted blocks.
To clear v$database_block_corruption orphan records:
RMAN> validate check logical datafile 23 block 774505to 774507; Starting validate at 04-OCT-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=413 device type=DISK channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00023 name=/u05/oradata/TESTDB/CDR_01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 23 OK 0 0 3 8013155974853 File Name: /u05/oradata/TESTDB/CDR_01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 3 Other 0 0 Finished validate at 04-OCT-21
The check v$database_block_corruption is cleared successfully.
SQL> select * from v$database_block_corruption; no rows selected
Make DBA Life Easy 