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
- 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.
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;
Comments (4)
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
Posted by sandarsh | July 5, 2007 4:47 AM
Posted on July 5, 2007 04:47
alter database end backup works in 9i also.
had faced a similar situation and then I did alter database end backup;
:-)
Enjoy
Posted by Abhi | July 9, 2007 6:48 AM
Posted on July 9, 2007 06:48
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
Posted by Ahmed Khan | July 16, 2007 8:04 AM
Posted on July 16, 2007 08:04
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
Posted by Mehul Patel | July 27, 2008 1:47 AM
Posted on July 27, 2008 01:47