Problem:
How do I recover until cancel with Oracle when doing a roll forward?
Can someone explain why it is necessary to perform "recover database until cancel"
before using "alter database open resetlogs" when you are restoring a cold
backup without a backup of the redo log files ?
I perform the following steps, which I thought would be
very simple, however I get an error on opening the database.
SHUTDOWN
IMMEDIATE
Restore all backup datafiles and controlfiles (OS copy)
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
So I tried using:
RECOVER DATABASE UNTIL CANCEL
ALTER DATABASE OPEN RESETLOGS
And this works, the database is opened and the new redo
logs are created.
I got a trace of my controlfile and found the following..
CREATE
CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVEMODE .....
I think it is no NORESETLOGS that is causing the problem as if I re-create the
control file with RESETLOGS specified it works fine.
Solution:
The Oracle documentation notes:
"In cancel-based recovery, recovery proceeds by prompting you with
the suggested filenames of archived redo log files. Recovery stops when you
specify CANCEL
instead of a filename or when all redo has been
applied to the datafiles.
Cancel-based recovery is better than change-based or time-based
recovery if you want to control which archived log terminates recovery. For
example, you may know that you have lost all logs past sequence 1234, so you
want to cancel recovery after log 1233 is applied.
You should perform cancel-based media recovery in these stages:
- Prepare for recovery
by backing up the database and correct any media failures as described in
"Preparing for Incomplete Recovery".
- Restore backup
datafiles as described in
"Restoring Datafiles Before Performing Incomplete Recovery". If you have
a current control file, then do not restore a backup control file.
- Perform media
recovery on the restored database backup as described in the following
procedure.
To perform cancel-based recovery:
- Start SQL*Plus and
connect to Oracle with administrator privileges. For example, enter:
% sqlplus '/ AS SYSDBA'
- Start a new instance
and mount the database:
STARTUP MOUNT
- Begin cancel-based
recovery by issuing the following command: