RelationalDBDesign RelationalDBDesign

Incomplete Recovery   «Prev  Next»
Lesson 5 Reviewing the alert log
Objective Describe how to monitor the recovery progress.

Reviewing the Alert Log

The alert log file is a special trace file for a database. It chronologically records messages and errors about database operations, such as internal errors, block corruption errors, and information about administration operations, including database recovery.
Information about each recovery, either complete or incomplete, is recorded in the alert log file. As a DBA, you should check the alert log, before and after the recovery, to find any recovery errors, hints, and the SCN (system changed number). With this information, you can decide which recovery method you should use and you can monitor the recovery process. If a recovery fails, you may find the reason for the failure in the alert log file.
The following SlideShow demonstrates how to use the alert log file to find information about a recovery.

  1. The DBA sets the ORACLE_SID to ensure the correct database is connected.
  2. The location information for an alert log is stored in the BACKGROUND DUMP DES parameter. In SQL* Plus, issue the SHOW PARAMETER DEST command(5).
  3. This image shows the beginning of the alert log file recording.
  4. You can locate the information you need using the data and time (7).
  5. The alert log also records an archived log file used at a particular stage of recovery(10).
  6. The alert log records that you reset the redo log sequence number when you opened the database(11) and the redo log sequence number is set to 1 (13).

Checking Recovery Information using alert Log
Click the View Code link below to view the codes that cause the recordings in the alert log.

In the code below, you can see that the database failed to start up due to a "file recovery needed" error:
SVRMGR> startup
ORACLE instance started.
Total System Global Area                         11710464 bytes
Fixed Size                                          49152 bytes
Variable Size                                    11177984 bytes
Database Buffers                                   409600 bytes
Redo Buffers                                        73728 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'C:\ORANT\DATABASE\TMP1ORCL.ORA'

In the code below you can see that the DBA initiated a recovery session:
SVRMGR> recover database
ORA-00279: change 5621029 generated at 01/11/2013 16:03:17 
needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC442.1
ORA-00280: change 5621029 for thread 1 is in sequence #442
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
ORA-00279: change 5641036 generated at 01/12/2013 11:45:17 
needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC443.1
ORA-00280: change 5641036 for thread 1 is in sequence #443
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC442.1' 
no longer needed for this recovery
 Log applied.
Media recovery complete.

The next lesson shows you how to perform a time-based recovery.

Incomplete Recovery Concepts - Quiz

Click the Quiz link below to review your understanding of some basic concepts of incomplete recovery.
Incomplete Recovery Concepts - Quiz