Monday, July 2, 2012

Handling ORA-0376 (ORA-376) Errors

ORA-376 means at some point in time a datafile made unavailiable on the database. Usually when you come across this error with the database opened, unless SYSTEM tablespace has been unavailiable. Most of the times and depending on the cases you can bypass this error by just recovering the offended datafiles with the database in open or mounted state.


 1. Check the alert.log of the instance and find the file and the tablespace affected when the error generated. You can also check the corresponding tracefile for further info.

Mon Jul 02 01:01:53 2012
KCF: write/open error block=0x13e5e online=1
     error=27070 txt: 'OSD-04016: Message 4016 not found; No message file for product=RDBMS, facility=SOSD
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service.'
Mon Jul 02 01:01:53 2012
ARC0: Failed to archive thread 1 sequence 84025 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Jul 02 01:01:53 2012
Automatic datafile offline due to write error on

2. Find out the status of the offened tablespacetablespaces:

select status,count(*) from dba_tablespaces group by status;

3. Find out the status of the datafiles using the number you found out before.

select file#,name,status,enabled from v$datafile where file#=3;

a. Check that the name of the datafile is matching the one from alert.log and the status column. If status is offline then you can bring the tablespace / datafile online and you have finished.
alter tablespace [TABLESPACE_NAME] online;
alter database datafile [FULL_PATH_DATAFILE_NAME] online;

b. If the status is recover then you must try media recovery for the datafile and then try to bring it online. If the affected tablespace is SYSTEM, SYSAUX, UNDOTBS then you must cleanly shutdown the database (if its still open) and perform the following steps using sqlplus:
shutdown immediate;

startup mount

recover datafile [FULL_PATH_DATAFILE_NAME];
Use AUTO if you prompeted for an Archive Log name to continue recovery using the online redo.logs

alter database datafile [FULL_PATH_DATAFILE_NAME] online;

alter database open;
If database opens without an error then restart the DB.

shutdown immediate;


Its advisable to take a new backup for the affected tablespaces.

1. References

Oracle Support Document ID(s): 183327.1, 1013912.6, 

No comments:

Post a Comment