How to deal with Oracle online Log File loss or damage

Experience summary:

Online logs are classified into current online logs and non-current online logs. the corruption of Non-current online logs is relatively simple. Generally, you can solve the problem by using the clear command.

Corrupt non-current online logs:
1. Start the database, encountered a ORA-00312 or ORA-00313 error, such:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 3 thread 1: '/opt/Oracle/db04/oradata/ORCL/redo03.log'
Here we know that the data file in log group 1 is corrupted or lost.
You can see more detailed information from the alarm File
2. view the V $ log view:
SQL>; select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARC STATUS

---------------------------------------

1 54 YES INACTIVE

2 55 NO CURRENT

3 53 YES INACTIVE

The Group is not in the current status and has been archived.
3. Use the CLEAR command to recreate the log file.
SQL>; alter database clear logfile group 3;
If the log group has not been archived, use
SQL>; alter database clear unarchived logfile group 3;
4. Open the database and back up the database again.
SQL>; alter database open;
Note:
1) if the damaged online log file is not the current one, you can rebuild the log file only by clear. However, if the database is archived but the log is not archived

Clear is required.
2) clear is recommended, especially after forcible clear, for a full backup of the database.
3) This method is applicable to archive and non-archive databases.

Damage current online log:

There are two types of log corruption in archive mode,
1. The database is shut down normally, and no pending transactions in the log file need to be recovered by the instance. If the current log group is damaged, you can directly use alter database clear unarchived

Logfile group n.
2. There are active transactions in the log group, the database needs to be restored by media, and the log group needs to be used for synchronization. There are two ways to remedy this problem.
A. The best way is to ensure Database Consistency through Incomplete recovery. However, this method requires that the archive method and available backup be available.
B. Forced recovery may cause Database Inconsistency.
The following describes the two restoration methods.
5.1.2.1 restore through backup
1. A similar error occurs when you open the database.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) the system cannot find the specified file.

2. view the V $ log and find the current log.
SQL>; select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARCHIVED STATUS
--------------------------------------------
1 1 NO CURRENT
2 2

YES INACTIVE
3 3 YES INACTIVE

3. failed to find clear
SQL>; alter database clear unarchived logfile group 1;
Alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG'

4. Copy the full backup of a valid database and do not completely restore the database.
Until SCN recovery or until cnacel recovery can be used to obtain the nearest scn.
Recover database until cancel
Select auto first, restore the available archived logs as much as possible, and then try again
Recover database until cancel
The input cancel is incomplete, that is, it is restored twice.
For example:
SQL>; recover database until cancel;
Auto
......
SQL>; recover database until cancel;
Cancel;
5. Use alter database open resetlogs to open a database
Note:
1. The recovered database is consistent and incomplete, and transaction data in the current online log will be lost.
2. This method is suitable for archiving databases and full backup of available databases.
3. After the recovery is successful, remember to make a full backup of the database again.
4. It is recommended that online log files be stored on different disks to avoid this situation, because any data loss is not allowed in production.

If no backup is available, perform forced recovery.
1. A similar error occurs when you open the database.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) the system cannot find the specified file.

2. view the V $ log and find the current log.
SQL>; select group #, sequence #, archived, status from v $ log;

GROUP # SEQUENCE # ARCHIVED STATUS
--------------------------------------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE

3. failed to find clear
SQL>; alter database clear unarchived logfile group 1;
Alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG'

4. Drop the database
SQL>; shutdown immediate

5. Add the following parameters to init <sid>;. ora:
_ Allow_resetlogs_upload uption = TRUE

6. Restart the database and use until cancel to restore the database.
SQL>; recover database until cancel;
Cancel
If an error occurs, ignore it and send it.
SQL>; alter database open resetlogs;

7. Execute a full export immediately after the database is opened.

8. Shut down the database and remove the _ all_resetlogs_upt parameter.

9. Rebuilding the database

10. import and restore

11. Perform analyze table... validate structure cascade;

Note:
1. There is no way to restore the database. We recommend that you do not use this method in general, because this method may cause Database Inconsistency.
2. This method also loses data, but the lost data does not have much data in the previous method, mainly because the submitted or not submitted data that has not been written to the data file.
3. We recommend that you strictly perform the above 7 to 11 steps after successful database check and analysis.
4. Make a full backup of the database after completing all the tasks.
5. It is recommended that online log files be stored on different disks to avoid this situation, because any data loss is not allowed in production.