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...
- 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.
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;