Incomplete Recovery   «Prev  Next»

Lesson 9 Recovering without the current redo log
Objective Demonstrate how to recover when the current redo log is lost.

Recovering without current redo log when log is corrupt or lost

Sometimes, a current redo log is corrupt or lost due to a media failure. When the current redo log is lost, your database can be in either of the following states:
  1. The database is open, yet is in a "hung" state
  2. The database is closed because of the media failure or a terminated background process

The first situation involves more troubleshooting than recovery. In the second situation, you need to perform an incomplete recovery.

Database open yet hung

If your database is still open, yet in a "hung" state, this indicates that the current redo log[1] is either damaged or lost. You need to find the current log file and replace it as shown in the steps below: 

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

  1. Determine which log file group is the current one by querying V$LOG view. Click the View Code button.

The log file group 2 is the current log file
  1. Get rid of the damaged current log file by issuing the 
    ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;statement.

Your database should function properly now. The log file will be overwritten if it is corrupt or recreated if it is lost. Remember, you need to make a whole database backup immediately in case your database needs another recovery.

Database closed

The following SlideShow shows you the steps to perform a recovery without the current redo log in a situation where the database is closed due to media failure or a terminated background process. You may also preview the steps below.

Performing a recovery without the current redo log

If a database is closed because of media failure, you should perform the following steps to recover it.
  1. Try to open the database. You will get the information about the current damaged redo log group.

In a situation where a database is closed, the damaged online redo log file will not be archived. Thus, the command CLEAR LOGFILE will not clear the damaged file and a cancel-based recovery is required. You can only recover the database to the time before the current redo log is applied.
  1. In order to perform the cancel-based recovery, you must find the sequence number for the current redo log via the 
    SELECT * FROM V$LOG;
    statement.
  2. Restore all datafiles from a previous backup.
  3. Recover using the RECOVER UNTIL CANCEL command and stop before the current redo log is applied.
  4. Open the database with the RESETLOGS option.
  5. If the media failure is not corrected, you can recreate the log files onto another disk using the ALTER DATABASE DROP LOGFILE GROUP and ALATER DATABASE ADD LOG GROUP; statements to drop and recreate the log files to a new location.
  6. Perform a whole closed database backup.


Performing recovery without the current Redo Log

1) The DBA is informed that the database is not available.
1) The DBA is informed that the database is not available. The DBA accesses the Server Manager (1) and connects to the database as sysdba (2)

2)The DBA tries to open the database(3) but fails. The message says that something is wrong with the online redo log file LOG3ORCL.ORA (4) which belongs to log group 2.
2) The DBA tries to open the database(3) but fails. The message says that something is wrong with the online redo log file LOG3ORCL.ORA (4) which belongs to log group 2.

3) The DBA immediately queries the V$LOG view and finds out the problematic log file in group 2 is the current redo log.
3) The DBA immediately queries the V$LOG view (5) and finds out the problematic log file in group 2 is the current redo log with the sequence number 482(6). This means the recovery has to be complete when log 481 is reached. The DBA then queries V$LOGFILE view (7) to confirm the information (8).

4) The DBA shuts down the database(9) to restore all the datafiles(10)
4) The DBA shuts down the database(9) to restore all the datafiles(10). 
SQL>SHUTDOWN IMMEDIATE

5) The DBA starts to recover the database using cancel-based recovery (11) and when prompted for the log to apply, the DBA just hits Enter to continue (12).
5) The DBA starts to recover the database using cancel-based recovery (11) and when prompted for the log to apply, the DBA just hits Enter to continue (12).

6) When log 482 is prompted, the DBA enters CANCEL to complete the recovery
6) When log 482 is prompted, the DBA enters CANCEL to complete the recovery (13). Then the DBA opens the database with the RESETLOGS option (14). The DBA has successfully recovered the database without the current redo log.

  1. The DBA is informed that the database is not available
  2. The DBA tries to open the database(3) but fails. The message says that something is wrong with the online redo log file
  3. The DBA immediately queries the V$LOG view (5) and finds out the problematic log file in group 2 is the current redo log with the sequence number 482(6).
  4. The DBA shuts down the database(9) to restore all the datafiles(10).
  5. The DBA starts to recover the database using cancel-based recovery (11) and when prompted for the log to apply
  6. When log 482 is prompted, the DBA enters CANCEL to complete the recovery (13).

The next lesson demonstrates recovery through RESETLOGS.

Recovery Through ControlFile - Quiz

Click the quiz link below to review your understanding of recovery without current redo logs and recovery through the control file.
Recovery Through Control File - Quiz

[1]current redo log: A redo log is a crucial structure for recovery operations in an Oracle database. It is made up of two or more preallocated files that store all changes made to the database as they happen.

SEMrush Software