Catatan kerja

Database beberapa permasalahan yang terjadi di tempat kerja

Oracle: Fixing ORA-01113: file x needs media recovery Error October 28, 2008

Filed under: Oracle — aderahman @ 4:00 am

Disclaimer

The following steps are intended for a quick recovery of the database. Use them at your own risk.

Symptom

Oracle 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

  1. 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.
  2. 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
  3. 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.
  4. 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.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s