This discussion is archived
7 Replies Latest reply: Apr 27, 2008 11:01 PM by 446099 RSS

DATAFILE RECOVERY

450109 Newbie
Currently Being Moderated
Hi all,

I have taken export of one of the production database and it gave the following error.

When I check the tablespace it gave me tablespace in recover state.

Can any one advice me how to recover this particular database.

Regards,
Umair

EXP-00056: ORACLE error 376 encountered
ORA-00376: file 76 cannot be read at this time
ORA-01111: name for data file 76 is unknown - rename to correct file
ORA-01110: data file 76: '/oracle/product/9.2.0/dbs/MISSING00076'
. . exporting table PROC_AUD_PIPE_LINE_T 0 rows exported
. . exporting table PROC_AUD_SCE_BLINK_T 0 rows exported
. . exporting table PROC_AUD_SCE_FLINK_T
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 76 cannot be read at this time
ORA-01111: name for data file 76 is unknown - rename to correct file
ORA-01110: data file 76: '/oracle/product/9.2.0/dbs/MISSING00076'
. . exporting table PROC_AUD_T
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 76 cannot be read at this time
ORA-01111: name for data file 76 is unknown - rename to correct file
ORA-01110: data file 76: '/oracle/product/9.2.0/dbs/MISSING00076'
  • 1. Re: DATAFILE RECOVERY
    376714 Newbie
    Currently Being Moderated
    Hi,

    First of all, let me say this one more time: Exporting data is not a valid backup method.

    What seems to happen is that you've lost a datafile.
    I sincerely hope you have a valid backup, because what you need is a datafile recovery.

    Some questions:
    . Oracle version?
    . Database mode (archivelog/noarchivelog)?
    . Backup procedure (rman, hot backup, cold backup, ...)?
    . O.S. is Unix. Which version?

    Regards,

    Yoann.
  • 2. Re: DATAFILE RECOVERY
    454606 Newbie
    Currently Being Moderated
    your export is trying to read data from file number 76, which appears to have been deleted.

    you need to recover the datafile (if the database is in archivelog mode) or recover the entire database.

    If the db is in archivelog mode, you should be able to restore the datafile back and RECOVER DATABASE DATAFILE 'name', which will prompt for the required archivelogs.
  • 3. Re: DATAFILE RECOVERY
    445130 Newbie
    Currently Being Moderated
    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;
  • 4. Re: DATAFILE RECOVERY
    453427 Newbie
    Currently Being Moderated
    Good .
    I have learned more . Thanks
  • 5. Re: DATAFILE RECOVERY
    464312 Newbie
    Currently Being Moderated
    Rcube. Awesome explination - Well done!
  • 6. Re: DATAFILE RECOVERY
    219405 Newbie
    Currently Being Moderated
    Hello All,
    I have following error in my Oracle 7.3 database.
    Errors in file /opt/oracle/admin/TBSS/bdump/smon_73718_tbss.trc:
    ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [],

    Database is able to open but it continuously spiting this error.
    I have seen the status of rollback segments but it is not showing any message
    "NEEDS RECOVER" ,it is showing FULL.

    What are the options for me
    1) Comment out the rollback in init.ora
    2) Start with OFFLINEROLLBACK_SEGMENTS (In this case there is the chance of logical corruption)
    3) Restore rbs01.dbf file and apply archive logs.This option looks more viable but what Oracle does in this case?Do it write to database?
    Which one is the best?
    Thanks,
    S. Mann
  • 7. Re: DATAFILE RECOVERY
    446099 Newbie
    Currently Being Moderated
    rCube,
    thanks, it helped a lot :-)