Incomplete Recovery   «Prev  Next»

Lesson 5 Reviewing the alert log
Objective Describe how to monitor the recovery progress.

Monitor Recovery Progress in Oracle and Reviewing 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 series of images demonstrates how to use the alert log file to find information about a recovery.

Checking Recovery information with alert log

1) The DBA sets the ORACLE_SID to ensure the correct database is connected.
1)
  1. The DBA sets the ORACLE_SID to ensure the correct database is connected.
  2. Then the DBA access the Server Manager(2) and connects as a sysdba.
  3. The default password is 'manager'. For security purposes, you do not see the user's entry.

2) The location information for an alert log is stored in the BACKGROUND DUMP DES parameter.
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). The result shows that the location of the log file is in the ORACLE_HOME\RDBMS80\trace folder in the operating system (6)
The default file name is AlertSID.ora (AlertORCL.ora for this database. In Windows Explorer, you can use Notepad to open the file.

3) This image shows the beginning of the alert log file recording.
3) This image shows the beginning of the alert log file recording. If the alert file is not cleared periodically, you many to scroll down the file for quite a ways to find the record that you need.

4) You can locate the information you need using the data and time (7)
4) You can locate the information you need using the data and time (7). Here you can see that the database failed to start up due to a file recovery needed error (8), and that the DBA initiated a recovery session (9)

5) The alert log also records an archived log file used at a particular stage of recovery.
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 and the redo log sequence number is set to 1
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). The alert log also records the Incomplete recovery method you used (12).

  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
View the Code 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: 
SQL> 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'
SQL>

In the code below you can see that the DBA initiated a recovery session: 
SQL> 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}
auto
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.
SQL>

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

SEMrush Software