How to handle Cancel-Based Recovery in Oracle?

1. "cancel-based" or
2. "until cancel recovery"

still exists in Oracle, even in the most recent versions. While it's less commonly used in favor of more precise recovery techniques, it remains a viable option for certain scenarios.
Here's a summary of how it works:

1. Initiate Recovery:
   • Use the command `RECOVER DATABASE UNTIL CANCEL` to start the process.
2. Interactive Prompts:
   • Oracle will prompt you for archived redo log files sequentially.
   • You have two choices at each prompt:
     
     1. Type the filename of the next log to apply.
     2. Type `CANCEL` to stop the recovery process.
3. Recovery Stops:
   • Recovery terminates when you type `CANCEL` or when all available redo logs have been applied.

Key Points:

• Flexibility: Useful when you're uncertain about the exact point in time to recover to.
• Manual Intervention: Requires manual input for each log file, making it less suitable for automated recovery.
• Partial Recovery: Results in incomplete recovery, necessitating a reset of the database logs using `RESETLOGS`.

Common Use Cases:

• Restoring a table dropped accidentally without a specific time for recovery.
• Recovering from media failure when exact recovery time is unknown.

Alternative Recovery Methods:

• Time-Based Recovery: Recovers to a specific point in time using `RECOVER DATABASE UNTIL TIME 'timestamp'`.
• Change-Based Recovery: Recovers up to a specific system change number (SCN) using `RECOVER DATABASE UNTIL CHANGE 'SCN'`.

General Recommendation:

• Prefer precise methods: Time-based or change-based recovery are generally preferred for their accuracy and automation potential.
• Use cancel-based recovery judiciously: Employ it when uncertainty about the recovery point exists or when manual control is necessary.

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.

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

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

SQL> 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    
SQL>

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.

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.