Database recovery lost online redo log File recovery

Online redo log files are used to cycle through all operations of the Oracle database and are almost always read-write, so it is meaningless to back up the online redo log files at some point in time, and to restore them at all. The Rman backup does not have the ability to back up the online redo log at all, and not only Rman, but all backup software does not have the idea of backing up the online redo log file. Therefore, the recovery of the database after the loss of the online redo log is also not available to Rman.

If an Oracle database is found to be missing an online redo log file at startup, an error is made directly. Oracle ensures the security of online redo log files through file redundancy. That is, each set of online redo logs is created
Multiple files, at least two, each file path is individual.
The In-service Oracle database locks the online redo log files and cannot be removed through the operating system commands. Therefore, most of the lost files are caused by disk corruption.
The online redo log file is broadly divided into two states: currently being written and not currently in writing.
=================================
Online redo log File information query:

Sql> SELECT GROUP#,thread#,sequence#,members,archived,status fromv$LOG;GROUP# thread# sequence# members archived STATUS---------- ---------- ---------- ---------- -------- ----------------1 1 1 1YES INACTIVE2 1 2 1NO Current3 1 0 1YES UNUSED4 1 0 1YES UNUSED

The status of the online redo log file can be seen from the SQL statement query results above.
There are 6 selectable values of status:
UNUSED: Indicates that the online redo log file is a state that has never been used before, usually just created or open resetlogs opened.
Active: Represents the active. This is not the current state, but it may be in use or being used. This state may exist when crash recovery.
Clearing: The log is being emptied. This state occurs when the alter DATABASE CLEAR logfile statement is executed. After execution, the status changes to unused.
Clearing_current: The log is being emptied. The log is emptied and the log is in this state if an error occurs while emptying, causing the cleanup to not complete successfully.
INACTIVE: Inactive, indicating that the contents of the group log have been archived or successfully written to the data file, and that the group of logs can be continued to be used.

Sql> SELECT GROUP#,status,type,member fromV$logfileORDER  by GROUP#;GROUP# STATUS TYPE MEMBER---------- ------- ------- --------------------------------------------1ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG2ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG3ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG4ONLINE D:\TESTADDLOG.LOG

As can be seen from the above query results, the current Oracle database has a total of 4 sets of online redo logs, each group is a member file.
GROUP2 is currently being used. GROUP2 the corresponding member D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02. Log, which is the current online redo log file. The other 3 groups are non-current online redo log files.
=================================
1. Loss of non-current online redo log files
1.1 Delete the non-current online redo log file and restore it.
sql> shutdown immediate;--First shutdown the database and then deletes the file.
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> host del D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01. Log-Deletes the member file of the GROUP1 that is not the current online redo log file.

1.2 Startup database

Sql>The startuporacle routine has been started. Total System Global Area647204864bytesfixed Size2178536bytesvariable Size478151192bytesDatabaseBuffers159383552Bytesredo Buffers7491584The bytes database is loaded. ORA-03113: The file end of the communication channel process ID:5896Session ID:9Serial number:3------------have an error, the database is not started properly. The database is in Mount state at this time. 

1.3 Repairing lost online redo log files

 sql>   startup Mount; SQL  >  conn sys/ Rusky1234 @orcl  as   Sysdba; connected. SQL  >  alter  database  Clear logfile group  1 ; --   >  alter  database  open  ; the database has changed. 

----------------------------------------
Description: For non-current online redo log file corruption, the repair process is very simple, and the operation is safe and does not cause data loss.
The loss or corruption of a non-current online redo log file during a database run does not necessarily cause the database to crash because non-current files are not being used and the database is functioning properly. If the database is cut
If you change to a damaged online redo log file, you will get an error.
========================================

2. Loss of the current online redo log file
Loss of the current online redo log file, even if there is a backup, will certainly lose data.
2.1 Delete the current online redo log file and boot to the Mount state.

Sql> shutdownimmediate; SQL>Host del D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG --Delete the member files that are not the current online redo log file GROUP2. Sql>The startuporacle routine has been started. Total System Global Area647204864bytesfixed Size2178536bytesvariable Size478151192bytesDatabaseBuffers159383552Bytesredo Buffers7491584The bytes database is loaded. ORA-03113: The file end of the communication channel process ID:1008Session ID:162Serial number:5SQL>The startup Mount;oracle routine has been started. Total System Global Area647204864bytesfixed Size2178536bytesvariable Size478151192bytesDatabaseBuffers159383552Bytesredo Buffers7491584The bytes database is loaded. SQL>Conn SYS/rusky1234@orcl  assysdba; connected. 

2.2 Attempt to repair online redo log files directly

Sql> Alter DatabaseClear logfileGroup 2;Alter DatabaseClear logfileGroup 2*Section1line error: ORA-00350: Log2(Instance ORCL log, thread1) need to archive Ora-00312: Online Log2Thread1:'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02. LOG'

---------------have an error, the missing redo log file contains the necessary redo information and cannot be clear.

2.3 Performing incomplete restores
If it is in archive mode and has a backup, it can be backed up with incomplete recovery and, normally, only the data in the current online redo log file is lost.
If you do not have a backup, you can only force recovery. You must modify one of the hidden initialization parameters:

Sql> ALTERSYSTEMSET"_allow_resetlogs_corruption"=TRUE SCOPE=SPFILE;--When the parameter value is set to True, Oracle skips some consistency checks when open. The system has changed. SQL> SelectStatus fromv$instance; STATUS------------Mountedsql>RecoverDatabaseuntil cancel; complete media recovery. SQL> Alter Database Openresetlogs; The database has changed. 

2.4 Aftermath Treatment
If there is no backup, the above treatment is also the most feasible method. This repair is also likely to result in inconsistent data for the database. If the submitted data is not written to the data file, the uncommitted data
Was written to the data file. Even though the database can be opened normally and can be accessed normally, its alarm log file also has many error messages. At this point, it is best to export the logic to perform a full export
, then create a new database, and then import the contents of the exported file.

Database recovery lost online redo log File recovery