issue :
we recently faced issue with oracle database version 11204 running on AIX server which throwed the error and DB crashed
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 205 (block # 2089728)
ORA-01110: data file 205: '/apporadb/oradata/DB1.temp/temp05.dbf'
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 24576
DBW0 (ospid: 50593896): terminating the instance due to error 63999
Fri Apr 06 22:02:39 2018
Dumping diagnostic data in directory=[cdmp_20180406220239], requested by (instance=1, osid=50593896 (DBW0)), summary=[abnormal instance termination].
Instance terminated by DBW0, pid = 50593896
cause:
when we try to look into the log initially nothing reported and later it got updated on the log when the DB crashed.
Solution:
when I checked on the server one of the temp file eaten up the space on the file system and it grown up and filled the file system to 100% used ,so incoming DB connection unable to do thier operation and crashed the DB and looks like it was bug on the system.
Its not even allow to resize the temp file.
SQL> alter database tempfile '/appmktp/oradata/oradb.temp/temp05.dbf' resize 20000m;
alter database tempfile '/appmktp/oradata/oradb.temp/temp05.dbf' resize 20000m
*
ERROR at line 1:
ORA-00376: file 205 cannot be read at this time
ORA-01110: data file 205: '/appmktp/oradata/oradb.temp/temp05.dbf'
But we are able to resize other tempfiles.
moreover it does not showing the auto extend status
SQL> select sum(bytes/1024/1024) ,file_name,AUTOEXTENSIBLE from dba_temp_files group by file_name,AUTOEXTENSIBLE ;
SUM(BYTES/1024/1024)
--------------------
FILE_NAME
----------------------------------------------------------------------------------------------------
AUT
---
/appmktp/oradata/oradbtemp/temp05.dbf
27000
/appmktp/oradata/oradbtemp/temp01.dbf
NO
15360
/appmktp/oradata/oradbtemp/temp03.dbf
NO
30720
/appmktp/oradata/oradbtemp/temp04.dbf
NO
23552
/appmktp/oradata/oradb01/temp02.dbf
NO
SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp04.dbf' resize 25000m;
Database altered.
even I rebooted the DB and its NOT allowed me to resize the temp file.
However I can dropped the temp file and recreated one.
SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' autoextend off;
alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' autoextend off
*
ERROR at line 1:
ORA-00376: file 205 cannot be read at this time
ORA-01110: data file 205: '/appmktp/oradata/oradbtemp/temp05.dbf'
SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' drop including datafiles;
Database altered.
Then the issue got resolved and application able to run their query without any issues.
No comments:
Post a Comment