------------------
ORA-01113: "file %s needs media recovery"
This error is usually followed with ORA-1110 error which will indicate the
name of the datafile that needs media recovery.Like,
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\BDMS\DRSYS01.DBF'
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.
Ensure what you need to do?
-----------
1.startup mount;
2.conn / as sysdba
3.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 Corresponding Solutions:
----------------------------------------------
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.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:
SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ;
3.alter database datafile 'file name' end backup;
4.alter database open;
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
-------------------------
A)Database in Archivelog Mode
1.STARTUP MOUNT;
2.RECOVER DATAFILE 'file name';
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. ALTER DATABASE OPEN;
B)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:
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 '
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.
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
--------------------------------------
1.RECOVER DATAFILE 'file name';
2. Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".
3.ALTER DATABASE OPEN;
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
-------------------------------
1.RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
2. Cancel recovery by issuing the "CANCEL" command.
3. ALTER DATABASE OPEN RESETLOGS;
Related Documents
Really good explained, you save my job tonigth.
ReplyDeleteRegards.