Lesson 7 | Performing cancel-based recovery |
Objective | Demonstrate how to handle cancel-based or until cancel recovery. |
How to handle Cancel-Based Recovery in Oracle?
Scenario
Suppose the current time is 12:00 PM on 18-JAN-2020. One of your junior DBAs tells you that he dropped the CUSTOMERS table while he was trying to fix some damaged blocks in the datafile. As an experienced DBA, you are more concerned about the block corruption that resulted from the earlier disk failure. You also know that redo logs are contained on the same disk and they are not mirrored. At this time, the database transaction is minimal because most staff members are at lunch. What should you do to get the table back?
Solution
Before you perform any recovery, you have to know how much data loss may occur. You should immediately check the status of the redo logs and the archive logs.
View the code below to see the statement you should use.
SVRMGR> select * from V$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------
1 C:\ORANT\DATABASE\LOG4ORCL.ORA
2 C:\ORANT\DATABASE\LOG3ORCL.ORA
......
View the code below to see the the location of all your redo logs.
SVRMGR> select * from V$log;
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS ... FIRST_TIME
------ --------- ------ ------- --- -------- ---------------
1... 74 1048576 1 NO INACTIVE ...18-JAN-00:11:50
2... 75 1048576 1 NO CURRENT ...18-JAN-00:11:55
......
From the above result, you find that the redo log LOG4ORCL.ORA has not been archived. By further searching through data dictionary views,
you notice that you cannot locate this file. Therefore, you cannot recover past this time. You will lose at least 10 minutes of data input.
You query the data dictionary view V$LOG_HISTORY to confirm the absence of archive log seq 74 (LOG4ORCL.ORA).
View the code below to see the statement you should use.
SVRMGR> select * from V$log_history;
RECID STAMP FIRST_CHAN FIRST_TIM
----- ---------- ---------- --------------------
72 389609594 5741087 18-JAN-00:10:57
73 389610040 5761097 18-JAN-00:11:35
SVRMGR>
You are now sure that redo log #74 is not archived.
Since you know the recovery should be stopped before log #74 is applied, you decide to use cancel-based recovery.
Steps to perform a cancel-based recovery
- If the database is open, shut it down with the
IMMEDIATE
or the NORMAL
option.
- Perform a whole closed database backup.
- Restore all the datafiles from the most recent backup (not the one you made in Step 2).
- Open the database in the mount mode.
- Recover the database by issuing the
RECOVER DATABASE UNTIL CANCEL;
statement. Press Enter when you are prompted with the suggestion of which log you need to apply. Enter CANCEL
when you are prompted with the log number to complete the
recovery.
- To synchronize datafiles with control file and redo logs, open the database using the
RESETLOGS
option via the
ALTER DATABASE OPEN RESETLOGS;
statement.
- To make sure the database is recovered to the state you expected, check whether you got the objects back.
- Perform a whole closed database backup and notify the user that any data entered after the recovery time should be manually re-entered.
The next lesson shows how to perform a backup control file recovery.