Recovering Database with Inactive Redo Logs

Let us say you just spent 2 hours fixing the problems in your database and put it in ARCHIVELOG mode. Before you make a backup of this database, you decide to test the automatic creation of the archived log function. You issue the

ALTER SYSTEM SWITCH LOGFILE

command. The second time you issue the command you get the following error message:

SQL> alter system switch logfile; 
ORA-00740: LGWR process terminated with error

ORA-00740 is still a valid error for Oracle RDBMS. It indicates that the log writer (LGWR) process has terminated abnormally with an unexpected error.
This can happen for a variety of reasons, such as

1. disk I/O problems,
2. memory corruption, or
3. software bugs.

The LGWR process is responsible for writing redo log files to disk. Redo log files are used to recover the database if it crashes or if it needs to be restored to a previous point in time. If the LGWR process terminates abnormally, the database may not be able to write redo log files, which can lead to data corruption or loss. There are a number of things that can cause the LGWR process to terminate abnormally, including:

1. Disk I/O problems: If the disk where the redo log files are stored is experiencing problems, such as a full disk or a disk failure, the LGWR process may not be able to write redo log files to disk and will terminate abnormally.
2. Memory corruption: If the LGWR process encounters memory corruption, it may terminate abnormally.
3. Software bugs: There may be bugs in the LGWR process or in the Oracle database software that can cause the LGWR process to terminate abnormally.

If you encounter ORA-00740, you should first try to identify the cause of the problem. If the problem is caused by disk I/O problems, you will need to resolve the disk I/O problems. If the problem is caused by memory corruption, you may need to restart the Oracle database. If you are unable to resolve the problem, you may need to contact Oracle support for assistance. Here are some of the steps you can take to troubleshoot ORA-00740:

1. Check the Oracle alert log for any error messages that may be related to the LGWR process.
2. Check the operating system event log for any error messages that may be related to disk I/O problems.
3. Use the Oracle Enterprise Manager to monitor the health of the Oracle database.

As an experienced DBA, you immediately know that a background process is abnormally terminated and the Oracle instance is aborted. Since you do not have a backup of the fixed database and you do not want to spend another two hours fixing those problems, what can you do?

In this situation, the only option you have is to recover the database with "inactive redo logs". The folowing series of images below describes the steps for "Recovering Database with Inactive Redo Log File".

The code and all the command lines are displayed below.

SQL> STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA'
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-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: 'C:\ORANT\DATABASE\LOG1ORCL.ORA'
SQL> SELECT * FROM V$LOG; 
GROUP# THREAD# ... MEMBERS    ARC   STATUS     FIRST_CHAN FIRST_TIM
------ -------   -------    ---   ---------- ---------- ---------
     1       1         1     NO    CURRENT      6402439 28-FEB-00
     2       1         1    YES    INACTIVE     6382423 28-FEB-00
     3       1         1    YES    INACTIVE     6322223 25-FEB-00
     4       1         1    YES    INACTIVE     6342407 26-FEB-00
4 rows selected.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4; 
Statement processed.
SQL> ALTER DATABASE OPEN; 
Statement processed.
SQL>

"ORA-00313" is still a valid error for the Oracle RDBMS. It indicates that the online redo log file cannot be opened. This error can occur for a variety of reasons, such as a corrupt redo log file, a missing redo log file, or a permissions issue.
The online redo log file is a critical component of the Oracle database. It is used to track changes made to the database so that those changes can be recovered in the event of a system failure or other disaster. If the online redo log file cannot be opened, the database cannot be opened. There are a number of things that can cause the error ORA-00313, including:

• Corrupt redo log file: If the redo log file is corrupt, it cannot be opened and the error ORA-00313 will occur.
• Missing redo log file: If the redo log file is missing, it cannot be opened and the error ORA-00313 will occur.
• Permissions issue: If the user attempting to open the redo log file does not have the necessary permissions, the error ORA-00313 will occur.

If you encounter the error ORA-00313, you should first try to identify the cause of the problem. If the problem is caused by a corrupt redo log file, you will need to restore the database from a backup. If the problem is caused by a missing redo log file, you will need to create a new redo log file. If the problem is caused by a permissions issue, you will need to grant the necessary permissions to the user attempting to open the redo log file.
Here are some of the steps you can take to troubleshoot ORA-00313:

• Check the Oracle alert log for any error messages that may be related to the online redo log file.
• Use the V$LOGFILE view to check the status of the online redo log file.
• Use the RMAN recovery tool to restore the database from a backup.

If you are still unable to resolve the problem, you may need to contact Oracle support for assistance.

When an online redo log file is lost or damaged due to media failure, it becomes almost impossible to perform a complete recovery. However, you may find that there is no data loss in the following situations:

1. The damaged or lost online redo log is not current
2. This online redo log is already archived
3. The database has mirrored online redo log files

In these situations, incomplete database recovery is unnecessary. You can recover the database by getting rid of the damaged online redo log and creating a new one. The next lesson demonstrates how to recreate an online redo log file.

Click the Quiz link below to review your understanding of database recovery with inactive redo logs.
Recovering Database with Inactive RedoLogs - Quiz