Hi,

Following is a checklist to solve your problems. Best of Luck.

Cheers..rCube

An ORA-376 occurs when Oracle knows a datafile, but Oracle cannot read it. Most likely the file is offline.

In order to correct it , 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 datafilePossible 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 <tablespace_name> online;

- If the datafile in a tablespace is offline, you can bring it online by:
SQL> alter database datafile <full_path_datafile_name> 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/v920/nar.dbf'

For doing the recovery, the following commands can be used:

SQL> recover datafile '<full_path_of_datafile>';
SQL> alter database datafile <full_path_datafile_name> 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 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 '<datafile_name>' OFFLINE DROP;

- Drop the tablespace

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

- Recreate the tablespace

SQL> CREATE TABLESPACE <tablespace_name> DATAFILE '<datafile_name>'
SIZE <required_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 <number>


For the Bourne or Korn shell:
-----------------------------

$ ulimit -f <number>


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 '<full_path_file_name>' ONLINE;

4. Perform media recovery on the datafile.

SQL> RECOVER DATAFILE '<full_path_file_name>';

5. Open the database.

SQL> ALTER DATABASE OPEN;

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<SID>.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 '<full path filename>';

- 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 '<full path filename>' 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 <RBS NAME> 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 init<SID>ora 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;