X

Alejandro Vargas' Blog

  • July 3, 2007

Corrupted Archived Log While Performing Recovery of a Crashed Instance

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

When there is no more than the actual backup, and the database serves a critical online production facility, and the facility virtually stops because of the problem, then the last thing you want to meet is  a corrupted archived log!

It started early in the morning, a very top industry had its ERP database down, a colleague was all the night working on the case, but the database was still down...

Facts were:

  • Database was performing a hot backup,
  • While being backed up crashed
  • Startup normal does not work
  • Recovery fails with oracle pointing to a corrupted archived log
Fortunately the problem was not that complicated:
  • The crash happened while the database was in backup mode.
  • Select status from v$backup revealed that several datafiles were still on backup mode.
  • To perform recovery you must finish backup.
  • Recover again failed with the corruption being reported on the same archived log as before, but we noticed that the sequence of this archived log was on the range of the online logs.
  • We did recovery again and applied the matching online log instead of the corrupted archived log, and the following sequences until the current, recovery finished successfully.
  • Before opening the database with resetlogs option we did backup the online logs, to replace the corrupted one.
  • The database opened with resetlogs successfully.
Note:

To end backup mode in 10g you can use:

    alter database end backup.

In this case we were working with a 9i database so we used :

    alter tablespace <tbsname> end backup;

A script like this can be used to match datafiles to its tablespaces:

set echo off  head off
SELECT     'alter tablespace '||name||' end backup;'
from     V$TABLESPACE WHERE TS# IN (
    SELECT TS# FROM V$DATAFILE WHERE FILE# IN (
        SELECT FILE# FROM V$BACKUP WHERE STATUS='ACTIVE'))
/

alter tablespace USERS end backup;
alter tablespace PERFSTAT end backup;

Join the discussion

Comments ( 4 )
  • sandarsh Thursday, July 5, 2007
    Hey Alejandro,
    Can you also explain How the Files were bought back online?.
    Was that 10G or 9i?
    In 10G you have alter database end backup;
    and 9i alter tablespace .....
    Which one did you use?.
    Thanks,
    Sandarsh
  • Abhi Monday, July 9, 2007
    alter database end backup works in 9i also.
    had faced a similar situation and then I did alter database end backup;
    :-)
    Enjoy
  • Ahmed Khan Monday, July 16, 2007
    A good practical blog.
    >Recover again failed with the corruption being reported on the same archived log as before, but we noticed that the sequence of this archived log was on the range of the online logs.
    ----------------------------------------
    I was wondering what would have happened if the corrupted archived log file did not belong to one of the online log files? What would have happened if the archived log sequence which was corrupted was less than the online log sequences? Will complete recovery be possible in such a scenario ?
    Thanks for the posts, they all impart practical knowledge.
    Regards,
    Ahmed Khan
  • Mehul Patel Sunday, July 27, 2008
    Hi,
    I had also same situation for corrupted archive log but i had something more than that.
    DB was up and running, suddenly something happend on archive file system, storage admin got to know they copied entire file system which was holding online redolog file which was being archived. when after two days db crashed i was doing recovery and suddenly recovery thrown error saying corrupted archive log i was wondering how come this happend. When i asked storage admin he convayed that they have copied entire file system to other place. i tried all the online redolog which was copied at that time, one of them applied successfully and asked next sequence to continue recovery. and fortunately all rest archive logs were safe and able to do complete recovery.
    so Hurray.
    Mehul Patel
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.