hi folks I'm currently managing an Oracle Database, and while my SQL skills are solid, my experience as a DBA is rather limited. Despite this, I possess sufficient knowledge to initiate the Oracle database and carry out fundamental operations. Unfortunately, we recently encountered a scenario where the database unexpectedly shut down. Upon reviewing the logs, I discovered errors indicative of a media failure:
KCF: read, write or open error, block=0x2c95 online=1
file=3 '/datafiles/CDB1/datafile/o1_mf_sysaux_l6363dv1_.dbf'
error=27072 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 11413
Additional information: 4294967295'
2024-02-22T21:51:09.453691+03:00
Errors in file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_dbw0_2796.trc:
2024-02-22T21:51:09.497128+03:00
Errors in file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_dbw0_2796.trc:
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 3 (block # 11413)
ORA-01110: data file 3: '/datafiles/CDB1/datafile/o1_mf_sysaux_l6363dv1_.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 11413
Additional information: 4294967295
Errors in file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_dbw0_2796.trc (incident=326564) (PDBNAME=CDB$ROOT):
ORA-63999 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/cdb1/cdb1/incident/incdir_326564/cdb1_dbw0_2796_i326564.trc
USER (ospid: 2796): terminating the instance due to ORA error 63999
2024-02-22T21:51:10.218387+03:00
System state dump requested by (instance=1, osid=2796 (DBW0)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_diag_2781.trc
2024-02-22T21:51:12.210735+03:00
Dumping diagnostic data in directory=[cdmp_20240222215110], requested by (instance=1, osid=2796 (DBW0)), summary=[abnormal instance termination].
2024-02-22T21:51:13.414069+03:00
Instance terminated by USER, pid = 2796
These errors led to the termination of the instance due to an ORA error 63999, prompting a system state dump and the dumping of diagnostic data.
Initially attributing the media error to a full SYSAUX tablespace, I attempted to resolve the issue by adding a new SYSAUX datafile. However, this action did not remedy the problem. Further investigation revealed that the actual cause was server-related, specifically a lack of attention from the system administrator leading to insufficient storage. Once the storage issue was addressed and expanded, the database resumed normal operations.
Subsequently, when inspecting the logs, I encountered a new error related to data corruption in the newly added SYSAUX datafile:
Completely zero block found during validation
Reread of blocknum=28018, file=/datafiles/CDB1/AE692245E72461FDE053150A10AC24F1/datafile/o1_mf_sysaux02_l636qq5r_.dbf
. found same corrupt data
Reread of blocknum=28018, file=/datafiles/CDB1/AE692245E72461FDE053150A10AC24F1/datafile/o1_mf_sysaux02_l636qq5r_.dbf
. found same corrupt data
Reread of blocknum=28018, file=/datafiles/CDB1/AE692245E72461FDE053150A10AC24F1/datafile/o1_mf_sysaux02_l636qq5r_.dbf
. found same corrupt data
Reread of blocknum=28018, file=/datafiles/CDB1/AE692245E72461FDE053150A10AC24F1/datafile/o1_mf_sysaux02_l636qq5r_.dbf
. found same corrupt data
Reread of blocknum=28018, file=/datafiles/CDB1/AE692245E72461FDE053150A10AC24F1/datafile/o1_mf_sysaux02_l636qq5r_.dbf
. found same corrupt data
2024-03-13T22:14:48.105487+03:00
Corrupt block relative dba: 0x06006d72 (file 24, block 28018)
Given that the database is currently operational, I am contemplating whether it is feasible to remove this newly added, and now corrupted, SYSAUX datafile. I seek advice on the best course of action in this situation. How should I proceed with handling the corrupted datafile, considering the current state of the database? Your expertise and recommendations would be greatly appreciated.
Note the database is pdbs and the corrupted file is pluggable database (PDB) or a root container.
db version 19.3
os oralce linux 7.9