DisclaimerThe following steps are intended for a quick recovery of the database. Use them at your own risk.
SymptomOracle mounts the database, but refuses to open the database with an
ORA-01113: file x needs media recovery
Error.
% sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 24 09:29:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size 1981064 bytes
Variable Size 436209016 bytes
Database Buffers 2231369728 bytes
Redo Buffers 14794752 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/OraData/SIAMST/siamst_system.dbf'
% oerr ora 01113
01113, 00000, "file %s needs media recovery"
// *Cause: An attempt was made to online or open a database with a file that
// is in need of media recovery.
// *Action: First apply media recovery to the file.
Resolution- Startup the database with the
mount
option
SQL> startup mount
ORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size 1981064 bytes
Variable Size 436209016 bytes
Database Buffers 2231369728 bytes
Redo Buffers 14794752 bytes
Database mounted.
- Find the name of the redo log file which belongs to the active group
SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP#
2 AND G.STATUS = 'CURRENT';
MEMBER
--------------------------------------------------------------------------------
/OraRedo/RedoLogFiles/siamst_log01.dbf
- Using a backup control file, start the cancel based recovery. The system may suggest a non-existing archive log -- ignore it and specify the log file name {with full path} that you got in the above step
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 21375038 generated at 09/21/2007 20:12:47 needed for thread 1
ORA-00289: suggestion : /export/pspp/oracle/dbs/arch1_4_633901491.dbf
ORA-00280: change 21375038 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
/OraRedo/RedoLogFiles/siamst_log01.dbf
Log applied.
Media recovery complete.
- Finally open the database in
RESETLOGS
mode. It is recommended to reset the online logs after incomplete recovery or recovery with a backup control file
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Take a backup of the database as soon as the recovery is done.
See Also:_______________
Technorati Tags:
Oracle |
Troubleshooting