Oracle data block corruption error in ebs database

[raiq1]

Dear Erman,

I have applied the patch in ebs r12.1.3 database but the patch has failed due to below error.

AD_TABLE_INDEX_INFO table dropped.

AutoPatch error:
The following ORACLE error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 352, block # 110463)
ORA-01110: data file 352: '/u01/oracle/PROD/db/apps_st/data/system09.dbf'


occurred while executing the SQL statement:

CREATE SYNONYM AD_TABLE_INDEX_INFO FOR APPLSYS.AD_TABLE_INDEX_INFO

AutoPatch error:
adpxcl3: Error calling adprdcCreateTableIndexInfoTabfor Running SQL and EXEC commands.


You should check the file
/u01/oracle/PROD/apps/apps_st/appl/admin/PROD/log/adpatch.log

for errors.

After that I have resize the datafile for below command but the issue is not resolve.

SQL> alter database datafile '/u01/oracle/PROD/db/apps_st/data/system09.dbf' resize 1000M;

Database altered.


In the alert log file it has showing below error.
Thu Sep 09 18:49:32 2021
Hex dump of (file 352, block 110463) in trace file /u01/oracle/PROD/db/tech_st/11.2.0.4/admin/PROD_oel67/diag/rdbms/prod/PROD/trace/PROD_ora_7237.trc
Corrupt block relative dba: 0x5801af7f (file 352, block 110463)
Completely zero block found during buffer read
Reading datafile '/u01/oracle/PROD/db/apps_st/data/system09.dbf' for corruption at rdba: 0x5801af7f (file 352, block 110463)
Reread (file 352, block 110463) found same corrupt data (no logical check)
Thu Sep 09 18:49:32 2021
Corrupt Block Found
         TSN = 0, TSNAME = SYSTEM
         RFN = 352, BLK = 110463, RDBA = 1476505471
         OBJN = 525491, OBJD = 525491, OBJECT = I_OBJ4, SUBOBJECT =
         SEGMENT OWNER = SYS, SEGMENT TYPE = Index Segment
Errors in file /u01/oracle/PROD/db/tech_st/11.2.0.4/admin/PROD_oel67/diag/rdbms/prod/PROD/trace/PROD_ora_7237.trc  (incident=16161):
ORA-01578: ORACLE data block corrupted (file # 352, block # 110463)
ORA-01110: data file 352: '/u01/oracle/PROD/db/apps_st/data/system09.dbf'
Thu Sep 09 18:49:32 2021
Sweep [inc][16161]: completed


Kindly help me to solve the issue .

Regards,
Raiq

[raiq1]

Dear Erman,

Plz reply.

Regards,

[ErmanArslansOracleBlog]

Find the object that is sitting on block 110463 of file 352 and then we will check what we can do about it..

execute the following query :

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = <file#>
and <block#> between block_id AND block_id + blocks - 1;

file# should be 352
block# should be 110463 , so the necessary replacement in the above sql and execute it.. What is the result?

[raiq1]

Dear Erman sir,

I have used below command


SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
       352     110336        104                  0 ALL ZERO
       352     110440          1                  0 CORRUPT
       352     110441         23                  0 ALL ZERO
       352     165656      26473                  0 ALL ZERO


SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2  FROM dba_extents
  3  WHERE file_id =352 and 110440 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYSTEM                         INDEX              SYS
I_OBJ4

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2  FROM dba_extents
  3   WHERE file_id =352 and 110441 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYSTEM                         INDEX              SYS
I_OBJ4


SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2  FROM dba_extents
  3  WHERE file_id =352 and 110336  between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYSTEM                         INDEX              SYS
I_OBJ4


I have done dbv command to system09 tablespace and also resize the datafile system09 but still same issue are persist and not be able to removed corrupted block.

Kindly advice us.

Regards,

[ErmanArslansOracleBlog]

Okay. There is corrupt index I_OBJ4..

I would recommend you taking a backup and then dropping and recreating the index (when the database is in restricted mode) normally, but this is an internal index. . It is risky and you must not take that risk.
I would recommend using bbed and do some modifications manually , but again this is an internal index.
Backup restore may be a good idea, but it requies effort.

I don't know how you end up with this.. But! this may be consequence of a recent upgrade.. It seems utlmmig.sql includes the creation of this index.. So that script may also be your friend in this situation..
But as the object is an internal one  -> *** contact Oracle Support for this.

[raiq1]

Dear Erman,

Thanks for your reply, I have found one note that are similar to my issue.



More than 20000 Blocks Corrupted in system09.dbf After Fresh Installation of E-Business Suite R12.1.1 on Linux x86_64 Platform [ID 840963.1]

kindly check the note and advice.

Regards,

[ErmanArslansOracleBlog]

You already took the action suggested in that note ->  "alter database datafile '/u01/oracle/PROD/db/apps_st/data/system09.dbf' resize 1000M;" and as I see, it didn't solve your issue, right?. So that note won't help in this case. So, consider the things that I suggested in my previous update.