**Checked for relevance on 26-Mar-2012***
PURPOSE
-------
To consolidate the common reasons & solutions for the ORA-376 error.
SCOPE & APPLICATION
-------------------
Customers facing ORA-376 or analysts requiring information on known issues
with ORA-376 errors.
ORA-376
=======
An ORA-376 occurs when Oracle knows a datafile, but Oracle cannot read it.
Error Explanation:
-----------------
Error: ORA 376 file %s cannot be read at this time
Cause: attempting to read from a file that is not readable. Most likely
the file is offline.
Action: Check the state of the file. Bring it online
As mentioned in the error text, the common reason for this error is due to
Oracle failing to read a specific datafile. This error will usually be
accompanied by ORA-1110, which will give the name of the file that could
not be read by Oracle.
For example:
ORA-00376: file 28 cannot be read at this time
ORA-01110: data file 28: '/h04/usupport/app/oracle/oradata/v817/test.dbf'
In this case, test.dbf is the name of the datafile that Oracle is failing to
read.
Possible causes and solutions summary:
=====================================
A. Tablespace or Datafile offline.
B. Datafile does not exist at the OS level.
C. Datafile locked by Backup Software.
D. Incorrectly set ULIMIT on UNIX.
E. Rollback Segment with active transactions is unavailable
F. Possible Other Causes.
A. Tablespace or the datafile in a tablespace being offline.
***********************************************************
- Use the following query to find out the status of the tablespaces:
SQL> select tablespace_name,status from dba_tablespaces;
- Use the following query to find out the status of the datafiles.
SQL> select file#,name,status,enabled from v$datafile;
- If the tablespace is offline, you can bring it online by :
SQL> alter tablespace online;
- If the datafile in a tablespace is offline, you can bring it online
by:
SQL> alter database datafile online;
In some cases, the status of the datafile might be 'recover'.
In that case, media recovery must be done bringing the datafile online.
Else, ORA-1113 will be encountered:
For example:
ORA-01113: file 28 needs media recovery
ORA-01110: data file 28: '/h04/app/oracle/oradata/v817/nar.dbf'
For doing the recovery, the following commands can be used:
SQL> recover datafile '';
SQL> alter database datafile online;
In some cases, it might be possible to recover the datafile
from the online redologs only.
B. Datafile does not exist at the OS level.
******************************************
In this case, you can drop the datafile and recreate the tablespace.
But, this is feasible for non-system (including SYSAUX) and non-rollback tablespaces only.
The steps in brief would be:
- Log into Server Manager and connect internal.
- Offline drop the other datafiles associated to the tablespace
SQL> ALTER DATABASE DATAFILE '' OFFLINE DROP;
- Drop the tablespace
SQL> DROP TABLESPACE INCLUDING CONTENTS;
- Recreate the tablespace
SQL> CREATE TABLESPACE DATAFILE ''
SIZE ;
C. Due to the Backup software locking the file.
**********************************************
In some platforms, the backup software might be locking the datafiles,
preventing Oracle from reading the datafiles.
Check if there are any backup software running and stop them, thereby
releasing the locks and try starting up the database again.
D. On Unix, if the ulimit is not set properly.
**********************************************
The following error might result, if the ulimit parameter is not set properly.
This is possible especially in Oracle Parallel Server (OPS) instances, where
the node switch might happen.
For Example:
ORA-00376: file 29 cannot be read at this time
ORA-01110: data file 29: '/db/GICORP_4/axix01.dbf'
Error: 27: File too large
The problem is that on the new node the filesize limit is lower than it used to
be on the older node and lower than the datafile size.
In these situations, the resolution would be to increase the
Unix ulimit filesize as explained below.
For the C shell:
----------------
% limit filesize
For the Bourne or Korn shell:
-----------------------------
$ ulimit -f
Once the ulimit is increased, the database can be restarted after
bringing the datafiles online ( if they were offline ).
E. If the rollback segment containing active transactions is unavailable.
************************************************************************
ORA-376 error might also result in the following scenario:
1. Database was down because of shutdown abort or system crash.
2. A datafile in a rollback segment tablespace is lost because the disk
is bad, controller problems etc.
3. The database was subsequently started up after removing the entries
in the rollback_segments parameter.
4. The rollback datafile was offline dropped.
5. Database open command is issued.
The reason for ORA-376 in this case is :
Oracle performs recovery automatically to bring all database files to a
consistent state. For that, it needs information from both the redolog and
rollback segments.
If a datafile containing rollback segment extents is needed in this process
but is found to be offline, Oracle will issue the error.
The solution in this case would be:
If the rollback datafile can be made available again, then
1. Reinclude the rollback segments in the "init.ora" file.
2. Mount the database.
SQL> STARTUP MOUNT;
3. Bring the datafile back online.
SQL> ALTER DATABASE DATAFILE '' ONLINE;
4. Perform. media recovery on the datafile.
SQL> RECOVER DATAFILE '';
5. Open the database.
SQL> ALTER DATABASE OPEN;
If step 4 fails and the file cannot be recovered OR the rollback
datafile is physically lost, then refer to Note:1013221.6.
In this case the solution would depend on whether the database was last
cleanly shut down or not.
F. Some other cases where the error ORA-376 was resolved include:
****************************************************************
1. Rollback segment datafile is present, but Oracle complains about
ORA-376 error during startup of the database.
The database is in NOARCHIVE log mode and there is no cold backup.
Solution:
One option available is to fake a recovery to see what file(s) Oracle
requires to recover the database. If Oracle only requires the online
redologs then database can be recovered and opened with no data loss.
From SQL*Plus or SVRMGL:
Run the following query to determine sequence number of online redologs:
SQL> select v1.group#,member,sequence#,first_change#
from v$log v1, v$logfile v2
where v1.group# = v2.group#;
Note the sequence number and member columns are of importance.
Next, attempt to recover database to see what file Oracle requires:
SQL> recover database until cancel;
Again note the sequence number. Ignore the suggested logfile name.
The name will appear in the form. of an archived log, but this is really
the name of the online redolog not yet archived.
If the lowest sequence number from the query results is the same as the
sequence number Oracle requires to recover database, then database can
be recovered.
Simply copy the exact path and filename from the member column as the
filename required by the RECOVER DATABASE command.
Repeat the process for each online redolog.
Oracle will return the message "Recovery completed." at the end of
recovery.
At this point you can issue:
SQL> ALTER DATABASE OPEN;
If ORA-1589 "must use RESETLOGS or NORESETLOGS option for database open"
occurs:
SQL> ALTER DATABASE OPEN NORESETLOGS;
NOTE: The open may take a while to process.
2. Shutdown abort followed by server reboot.
-------------------------------------------
Database startup fails with the following errors:
ORA-01545: rollback segment '%s' specified not available
ORA-01595: error freeing extent (%s) of rollback segment (%s))
ORA-00376: file %s cannot be read at this time
The Database in archive log mode was shutdown abort before the server
was rebooted.
The cause of this problem is still undetermined, but the following
solution worked:
- Shutdown the database immediate:
SQL>shutdown immediate;
- Edit the init.ora file and remove or comment out the problem
rollback segment from the list of rollback segments in the
ROLLBACK_SEGMENTS parameter.
- Startup the database.
SQL> STARTUP MOUNT;
- Find out which files need recovery by running the following statement:
SQL> select * from v$recover_file;
Alternatively, you can also query v$datafile.
- Find out which rollback segments need recovery by running the
following statement;
SQL> select usn,status from v$rollstat where status != 'ONLINE';
- Recover the datafiles that need recovery:
SQL> recover datafile '';
- Find out which datafiles are offline by running the following
statement:
SQL> select name,status
from v$datafile
where status != 'ONLINE';
- Bring the datafiles online:
SQL> alter database datafile '' online;
- Verify that all datafiles are online:
SQL> select file#, name, status
from v$datafile;
- Find out which rollback segments are offline by running the following
statement:
SQL> select usn,status from v$rollstat where status != 'ONLINE';
- Bring all rollback segments online:
SQL> alter rollback segment online;
- Verify that all rollback segments are now online:
SQL> select usn,status from v$rollstat ;
- Shutdown the database immediate:
SQL> shutdown immediate;
- Edit the initora file and add or uncomment the problem rollback
segment back to the list of rollback segments in the ROLLBACK_SEGMENTS
parameter.
- Startup the database.
SQL> STARTUP MOUNT;
Some other notes related to the ORA-376 errors includes:
*******************************************************
Note:105758.1 -- "How to Automate Controlfile Backup at Database
Startup" Bug:1297939, fixed in 9i only.
ORA-376 possible when using 'before shutdown'
database-level trigger
Note:113923.1 -- "AIX: ORA-376 ORA-27063 IBM/AIX ERROR 11"
Asynch IO problem on AIX might cause ORA-376
Note:104646.1 -- "ORA-604 ORA-376 ORA-1110 doing ALTER TABLESPACE.."
Bug:661649 not resolved, the problem is related
to SYS.FILEXT$ not being created in system
tablespace
Note:1071385.6 -- "MULTIPLE ERRORS WHEN ATTEMPTING TO CREATE DATABASE"
Wrong DDL syntax used
Note:1024128.6 -- "VMS: CREATING SECOND DATABASE FAILS WITH ORA-376
ORA-0110" VMS specific issue due to illegal
characters used for database name
Bug:563510 Fixed in 8.0.5
Accidental use of CREATE DATABASE or CREATE
CONTROLFILE while a database was open
Note:182648.1 -- "How To Recover from Loss of a READONLY Table
Partition When No Backup Exists" ORA-376 possible
due to lost partition
Note: 427801.1 ORA-00376 ORA-01110 After Changing Undo Tablespace in Automatic Undo Management
Note: 283846.1 ORA-00376 Error 376 encountered while recovering transaction after partial restore and open resetlogs.
Note: 963102.1 ORA-376/ORA-1110 Encountered While Dropping Old Undo Tablespace / Undo Segments
Note: 419980.1 ORA-00376 with 'ODM ERROR V-41-4-3-261-12 Not enough space
Note: 270532.1 ORA-376, ORA-1110 while creating a table in a dictionary managed tablespace
Note: 257801.1 Exchange Of Partitioned Data Fails With ORA-00376 When Different Schemas Are Used
References
NOTE:427801.1 - ORA-00376 ORA-01110 After Changing Undo Tablespace in Automatic Undo Management
NOTE:283846.1 - Messages in the alert.log (ORA-00376 Error 376 encountered while recovering transaction) after partial restore and open resetlogs.
NOTE:963102.1 - ORA-376/ORA-1110 Encountered While Dropping Old Undo Tablespace / Undo Segments
NOTE:419980.1 - ORA-00376 with 'ODM ERROR V-41-4-3-261-12 Not enough space'
NOTE:270532.1 - ORA-376, ORA-1110 while creating a table in a dictionary managed tablespace
NOTE:257801.1 - Exchange Of Partitioned Data Fails With ORA-00376 When Different Schemas Are Used
