Oracle Online log file loss or corruption processing method (RPM)

Experience Summary:

The online log is divided into the current online log and non-current online logs, the damage of non-current online logs is relatively simple, generally through the clear command to solve the problem.

Corruption of non-current online logs:
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 alarm 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 non-current and has been archived.
3. Rebuild the log file with the clear command
Sql>alter Database Clear logfile Group 3;
If the log group has not yet been archived, you need to use the
Sql>alter Database Clear unarchived logfile Group 3;
4. Open the database and back up the database
Sql>alter database open;
Description
1), if the damage is not the current online log file, generally only need to clear to rebuild the log file, but if the database is in an archive state but the log has not been archived, you need to forcibly clear.
2), the proposed clear, in particular, forcibly clear the database after the full backup.
3), this method is suitable for archiving and non-archive databases.

Corruption of the current online log:

There are two types of corruption in the current log in archive mode,
First, the database is normal shutdown, no pending transactions in the log file need instance recovery, the current log group corruption can be directly with ALTER DATABASE clear unarchived logfile Group N to rebuild.
Second, there are active transactions in the log group, the database needs media recovery, the log group needs to be used for synchronization, there are two ways to remedy
A. The best approach is to ensure database consistency through incomplete recovery, but this approach requires an archive and available backup
B. Through mandatory recovery, but may cause database inconsistencies.
The following are respectively used to illustrate the two methods of recovery
5.1.2.1 to restore by 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:oracleoradatatestredo01. 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 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 that the clear is unsuccessful
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:oracleoradatatestredo01. LOG '

4, copy the full backup of the valid database, do not completely restore the database
Can be used to obtain the nearest SCN with until SCN recovery or with until Cnacel recovery
Recover database until Cancel
Select Auto, try to restore the archive log that you can use, and then re-
Recover database until Cancel
This time the input cancel completes the incomplete recovery, that is to say, recover two times.
Such as:
sql> recover database until cancel;
Auto
......
sql> recover database until cancel;
Cancel;
5. Open the database with ALTER DATABASE open Resetlogs
Description
1, this method restores the database is consistent incomplete recovery, will lose the current online log transaction data
2. This method is suitable for archiving the database and has a full backup of the database available.
3, after the successful recovery, remember to do a full backup of the database.
4, it is recommended that the online log file must be mirrored on different disks, to avoid this situation, because any loss of data for production is not allowed.

If there is no backup, make a 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:oracleoradatatestredo01. 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 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 that the clear is unsuccessful
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:oracleoradatatestredo01. LOG '

4. Drop the database down
Sql>shutdown Immediate

5. Add the following parameters to the Init<sid>.ora
_allow_resetlogs_corruption=true

6. Restart the database and use until Cancel to recover
Sql>recover database until cancel;
Cancel
If something goes wrong, ignore it and issue
Sql>alter database open resetlogs;

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

8, Shutdown database, remove _all_resetlogs_corrupt parameters

9. Rebuilding the Library

10, Import and complete recovery

11, the proposed implementation of analyze TABLE ... VALIDATE STRUCTURE CASCADE;
Description
1, the recovery method is no way to recover after the method, generally recommended not to adopt, because the method may lead to inconsistent database
2, the method also lost data, but the lost data is not the last method of data, mainly is not written to the data file submitted or uncommitted data.
3, the proposed success after strict implementation of the above 7 to 11 steps to complete the database inspection and analysis
4, complete the database after the full backup
5, it is recommended that the online log file must be mirrored on different disks, to avoid this situation, because any loss of data for production is not allowed.

Ext.: http://www.cnblogs.com/rootq/archive/2009/08/04/1538165.html

Oracle Online log file loss or corruption processing method (RPM)