Loading Consent Dialog

Incomplete Recovery   «Prev  Next»

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

  1. If the database is open, shut it down with the IMMEDIATE or the NORMAL option.
  2. Perform a whole closed database backup.
  3. Restore all the datafiles from the most recent backup (not the one you made in Step 2).
  4. Open the database in the mount mode.
  5. 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.
  6. To synchronize datafiles with control file and redo logs, open the database using the RESETLOGS option via the 
    ALTER DATABASE OPEN RESETLOGS;
    statement.
  7. To make sure the database is recovered to the state you expected, check whether you got the objects back.
  8. 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.