Handling methods for lost or corrupted Oracle online log files

Oracle


Experience Summary:

The online logs are divided into current and non current online logs, and the corruption of the non-current online log is relatively simple, and the problem can be resolved by the clear command.

Corrupt non-current online log:
1, start the database, encountered ORA-00312 or ORA-00313 error, such as:
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 '
From here we know that log Group 1 data files are corrupted or missing
More detailed information can be seen from the alert file
2, view V$log view:
Sql> select Group#,sequence#,archived,status from V$log;

group# sequence# ARC STATUS

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

1 YES INACTIVE

2 NO Current

3 YES INACTIVE


As you can see, the group is out of the current state and has been archived.
3. Rebuild the log file with clear command
Sql>alter Database Clear logfile Group 3;
If the log group is not yet archived, you need to use the
Sql>alter Database Clear unarchived logfile Group 3;
4, open the database, back up the database
Sql>alter database open;
Description
1, if the damage is not the current online log file, you typically only need clear to rebuild the log file, but you need to force clear if the database is in an archived state but the log is not archived.
2), recommend clear, especially after forced clear, make a full backup of the database.
3), this method is suitable for archiving and non-archive databases.

Corrupt current online log:

There are two cases of corruption of the current log in archive mode.
First, the database is shut down normally, no pending transactions in the log file require instance recovery, and the current log group corruption can be rebuilt directly with ALTER DATABASE clear unarchived logfile Group N.
Two, is the log group has the activity the transaction, the database needs the media to restore, the log group needs to use for the synchronization, there are both kinds of remedial methods
A. The best approach is to ensure consistency of the database through incomplete recovery, but this approach requires an archiving approach with available backup
B. Through mandatory recovery, but may cause database inconsistencies.
The following are used to illustrate the two methods of recovery
5.1.2.1 Recovery via Backup
1, open the database, you will encounter a similar error
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 could not find the specified file

2, view V$log, found is 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. Found clear not successful
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 valid database full backup, not fully restore the database
Can be recovered using the until SCN with a method of obtaining the nearest SCN or until Cnacel
Recover database until Cancel
Select Auto, try to restore the available archive logs, and then
Recover database until Cancel
This time the input cancel, completes does not complete restores, namely restores two times.
Such as:
sql> recover database until cancel;
Auto
......
sql> recover database until cancel;
Cancel;
5. Open the database using ALTER DATABASE open Resetlogs
Description
1, this approach to restore the database is consistent incomplete recovery, will lose the current online log in the transaction data
2, this method is suitable for archiving the database and has available database full backups.
3, after the restoration of success, remember to do a full backup of the database.
4. It is recommended that online log files must realize mirror phase on different disk, to avoid this situation, because any loss of data for production is not allowed.

If there is no backup, make mandatory recovery
1, open the database, you will encounter a similar error
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 could not find the specified file

2, view V$log, found is 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. Found clear not successful
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, the database down
Sql>shutdown Immediate

5, add the following parameters in the Init<sid>.ora
_allow_resetlogs_corruption=true

6, restart the database, using until cancel recovery
Sql>recover database until cancel;
Cancel
If you make a mistake, stop ignoring it and send out
Sql>alter database open resetlogs;

7. After the database is opened, execute a full export immediately.

8, Shutdown database, remove _all_resetlogs_corrupt parameters

9. Rebuild the Library

10. Import and complete recovery

11, the proposal executes analyze TABLE ... VALIDATE STRUCTURE CASCADE;
Description
1, the recovery method is no way to recover after the method, generally recommended not to use, because this method may lead to inconsistent database
2, the method also loses data, but the missing data is not more than one method of data, mainly is not written to the data file submitted or uncommitted data.
3, the recommendation of the successful implementation of the above 7 to 11 steps to complete the database inspection and analysis
4, all completed after the full backup of the database
5. It is recommended that online log files must realize mirror phase on different disk, to avoid this situation, because any loss of data for production is not allowed.