Oracle EBS 12.1.3: Gather Schema statistics request failed with object_name=APPLSYS.WF_LOCAL_ROLES***ORA-01578: ORACLE data block corrupted (file # 87, block # 2545378) ***
DB alert log showing below error details:
ORA-01578: ORACLE data block corrupted (file # 87, block # 2545378)
ORA-01110: data file 87: '/db19c/prodebs/APPS_TS_TX_DATA11.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Find details of corrupted blocks using below queries:
select * from v$database_block_corruption; (use this for database versions below 12c)
select * from v$nonlogged_block; (use this for Database 12c/19c and later , run this under CDB &PDB)
SQL> SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = 87
AND 2545378 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
------- --------------- --------------------------
APPLSYS WF_LOCAL_ROLES TABLE PARTITION
SQL> select owner, segment_name, tablespace_name from dba_extents where FILE_ID=97 and 2545678 between BLOCK_ID and BLOCK_ID+BLOCKS;
OWNER SEGMENT_NAME TABLESPACE_NAME
----------- ------------------- ---------------------
APPLSYS WF_LOCAL_ROLES APPS_TS_TX_DATA
In my case above object under APPLSYS so its always safe to submit SR with oracle for PROD and for non-prod with proper backup you can try below workaround and see if it works.
1) Run dbv for the datafile showing corrupted.
Connect oracle user on database server and run below command
oracle@db: dbv file=/db19c/prodebs/APPS_TS_TX_DATA11.dbf
output:
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 2101240
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 818182
Total Pages Failing (Index): 0
Total Pages Processed (Lob) : 17153
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 989090
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6308
Total Pages Marked Corrupt : 187
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 38139909423844 (8880.599835364)
2) connect rman and recover if you have proper previous backup
rman target /
#connect catalog if you have catalog setup
RMAN> BLOCKRECOVER DATAFILE 87 BLOCK 2545378;
BLOCKRECOVER DATAFILE 97 BLOCK 2545678;
Starting recover at 09-DEC-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2085 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=192 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-DEC-25
RMAN>
3) Run dbv again to make sure no corrupted blocks for the datafile showing corrupted.
Connect oracle user on database server and run below command
oracle@db: dbv file=/db19c/prodebs/APPS_TS_TX_DATA11.dbf
0 Comments