Monday, January 29, 2018

ORA-1114 For An Unknown File Id

Most of the time DBAs get issues on ORA-1114 but the file id mentioned in error message becomes an unknown file id. Error message looks like :-
ORA-01114: IO error writing block to file 201 (block # 458754)
SVR4 Error: 28: No space left on device
What Oracle Doc says on this error :-
Cause:
The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space.

But in my case file id reported by error is unknown file id. First we'll check "DBA_DATA_FILES" to find out if issue is with any datafile.
SYS@racdb1> select file_id, RELATIVE_FNO, file_name from dba_data_files where file_id='201';
no rows selected
SYS@racdb1> select file_id, RELATIVE_FNO, file_name from dba_data_files where RELATIVE_FNO=201;
no rows selected

This error is being reported on a temp file. For TEMPFILES, the file numbering starts after the value of db_files. Run the following query to get the file name.
select file#, name from v$tempfile where file#=<file id reported in ora-1114 error >-<value of db_files>;

In this case :-
SYS@racdb1> show parameter db_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200
SYS@racdb1> select file#, name from v$tempfile where file#=(201-200);
FILE# NAME
---------- --------------------------------------------------
1 +DATA/RACDB/TEMPFILE/temp.287.963776695
So i got issue with my tempfile 1. We need to check this tempfile and take actions accordingly.

No comments:

Post a Comment