Applies to:

Purpose

This note is intended to list the common reasons & solutions for the ORA-1113 error

Troubleshooting Steps

An ORA-1113 occurs when a datafile needs recovery.

Definition of ORA-01113:

The ORA-01113 error is usually followed with an ORA-01110 error which indicates
the name of the datafile that needs media recovery.

This error message indicates that a datafile that is not up-to-date with
respect to the controlfile and other datafiles.

   Oracle's architecture is tightly coupled in the sense that all database
   files i.e., datafiles, redolog files, and controlfiles -- must be in sync
   when the database is opened or at the end of a checkpoint.
       
   This implies that the checkpoint SCN (System Commit Number) of all datafiles
   must be the same. If that is not the case for a particular datafile, an
   ORA-1113 error will be generated.

   For example, when you put a tablespace in hot backup mode, the checkpoint  
   SCN of all its datafiles is frozen at the current value until you issue the  
   corresponding end backup. If the database crashes during a hot backup and
   you try to restart it without doing recovery, you will likely get ORA-1113
   for at least one of the datafiles in the tablespace that was being backed up,
   since its SCN will probably be lower than that of the controlfile and the
   datafiles in other tablespaces.

   Likewise, offlining a datafile causes its checkpoint SCN to freeze.
   If you simply attempt to online the file without recovering it first, its
   SCN will likely be much older than that of the online datafiles, and thus an
   ORA-1113 will result.

Before Starting these actions do the following:
Query the V$LOG and V$LOGFILE.

1. If the database is down, you need to mount it first.
 
     SQL> STARTUP MOUNT;

2. Then connect internal Server Manager and issue the query:
 
     SQL> connect / as sysdba

     SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#  
            FROM V$LOG V1, V$LOGFILE V2
            WHERE V1.GROUP# = V2.GROUP# ;  
 
This will list all your online redolog files and their respective sequence and
first change numbers.

The steps to take next depend on the scenario in which the ORA-1113 was issued.
This is discussed in the following sections.
 

POSSIBLE CAUSES AND SOLUTIONS SUMMARY:


I.   AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
II.  AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
IV.  WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
 

I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
**********************************************************
 
   1. Mount the database.
 
          SQL> STARTUP MOUNT;

   2. Find out which datafiles were in hot backup mode when the database crashed or
      was shutdown abort or the machine was rebooted by running the query:
 
         SQL> SELECT V1.FILE#, NAME
                FROM V$BACKUP V1, V$DATAFILE V2
                WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ;
   
   3. For each of the files returned by the above query, issue the command:
 
          SQL> ALTER DATABASE DATAFILE '' END BACKUP;
 
   4. Open the database.

          SQL> ALTER DATABASE OPEN;
 
 
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP  
*********************************************************************
 
A. WITH THE DATABASE IN ARCHIVELOG MODE
 
   1. Mount the database.

           SQL> STARTUP MOUNT;

   2. Recover the datafile:
 
          SQL> RECOVER DATAFILE '' ;
 
      If recovering more than one datafile in a tablespace issue a

           SQL> RECOVER TABLESPACE ;

      If recovering more than one tablespace issue a  
 
           SQL> RECOVER DATABASE;
 

   3. Confirm each of the archived logs that you are prompted for until you
      receive the message "Media recovery complete".

      If you are prompted for an archived log that does not exist, Oracle probably
      needs one or more of the online logs to proceed with the recovery. Compare
      the sequence number referenced in the ORA-280 message with the sequence
      numbers of your online logs. Then enter the full path name of one of the
      members of the redo group whose sequence number matches the one you are
      being asked for.
 
   4. Open the database.
          
       SQL> ALTER DATABASE OPEN;

 
B. WITH THE DATABASE IN NOARCHIVELOG MODE
 
   In this case, you will only succeed in recovering the datafile or tablespace
   if the redo to be applied to it is within the range of your online logs.
   Issue the query:
 
        SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
 
   Compare the change number you obtain with the FIRST_CHANGE# of your online logs.
 
      If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs,
      the datafile can be recovered. In this case, the procedure to be followed
      is analogous to that of scenario II.A above, except that you must always
      enter the appropriate online log when prompted, until recovery is finished.
 
   If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file
   cannot be recovered.Your options at this point include:
 
      - If the datafile is in a temporary or index tablespace, you may drop
        it with an ALTER DATABASE DATAFILE '' OFFLINE DROP
        statement and then open the database. Once the database is up, you
        must drop the tablespace to which the datafile belongs and recreate it.
 
      - If the datafile is in the SYSTEM or in a rollback tablespace, restore
        an up-to-date copy of the datafile (if available) or your most recent
        full backup.In case you do not have either of this, then it might not
        be possible to recover the database fully. For more details or to
        assist you in your decision, please contact Oracle Customer Support.
 
  For all other cases in this scenario, you must weigh the cost of going to a
  backup versus the cost of recreating the tablespace involved, as described in
  the two previous cases.For more details or to assist you in your decision,
  please contact Oracle Customer Support.
 
 
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
**********************************************
 
   1. Recover the datafile:
 
           SQL> RECOVER DATAFILE '' ;
 
      If recovering a tablespace, do
 
           SQL> RECOVER TABLESPACE ;

      If recovering a database, do
         
           SQL> RECOVER DATABASE;

      -- Note: With recover database, offline datafiles will not be recovered.
 
   2. Confirm each of the archived logs that you are prompted for until you
      receive the message "Media recovery complete".

      If you are prompted for an archived log that does not exist, Oracle probably
      needs one or more of the online logs to proceed with the recovery. Compare
      the sequence number referenced in the ORA-280 message with the sequence
      numbers of your online logs. Then enter the full path name of one of the
      members of the redo group whose sequence number matches the one you are
      being asked for.

   3. Open the database.
          
       SQL> ALTER DATABASE OPEN;


 

IV. WHEN RECOVERING 'USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
*******************************************************************************

  If the database is recovered with the
  "RECOVER DATABASE USING BACKUP CONTROLFILE;" option without specifying the
  "UNTIL CANCEL" option, then upon "ALTER DATABASE OPEN RESETLOGS;" you will
  encounter the ORA-1113 error.
        
  Steps to workaround this issue:

    1. Recover database again using:

        SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

    2. Cancel recovery by issuing the "CANCEL" command.
 
    3. Open the database using:

        SQL> ALTER DATABASE OPEN RESETLOGS;


#