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
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.
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:
- Begin cancel-based recovery by issuing the following command:RECOVER DATABASE UNTIL CANCEL
If you are using a backup control file with this incomplete recovery, then specify theUSING
BACKUP
CONTROLFILE
option in theRECOVER
command.
- Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find from
LOG_ARCHIVE_DEST_1
and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.
- Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
CANCEL
Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get anORA-1113
error if more recovery is necessary for the file. You can queryV$RECOVER_FILE
to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.
No comments:
Post a Comment