Oracle online Log File Corruption scenarios and recovery methods

Oracle online log files record the logs of data block changes during database operation. After the database becomes corrupted or fails abnormally, you must use online logs (or archive logs, repeat the changes in the database.
 
When the log file itself is damaged (lost), the database may not be able to open normally. In this case, the recovery test is conducted (for reference only ):

Change redo log size)

Oracle ONLINE redo LOG FILE)

Restoration of Oracle online redo log loss

Oracle online redo log file

Troubleshooting of version inconsistencies in Oracle redo log files

[Backup and recovery] restores damaged online redo log files.

I. Classification of Log File Corruption:
 
1. inactive status (no data loss)
 
2. active and current status (usually causing data loss)
 
View method:
 
SQL> select group #, thread #, archived, status from v $ log;
 
GROUP # THREAD # ARC STATUS
---------------------------------------
1 1 YES INACTIVE
2 1 NO CURRENT
3 1 YES INACTIVE

Ii. test environment:
 
• OS: Linux xxxxxxxx 2.6.18-238. el5 #1 SMP Thu Jan 13 15:51:15 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
• DB: oracle 11.2.0.1.6 (non-RAC)
 

Iii. Test the recovery of corrupted inactive log files:
 
Error Log when startup:
 
SQL> startup
ORACLE instance started.
 

Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 29499
The Session ID is 2273 Serial number: 5.
 

Alert Error Log:
 
Errors in file/u01/oracle/diag/rdbms/test/trace/test_lgwr_29457.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/test/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file/u01/oracle/diag/rdbms/test/trace/test_lgwr_29457.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/test/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file/u01/oracle/diag/rdbms/test/trace/test_ora_29499.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/test/redo01.log'
USER (ospid: 29499): terminating the instance due to error 313
Instance terminated by USER, pid = 29499
 

In this case, you only need to delete the active log group and start up as follows:
 
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database open;
Database altered.

4. Damaged active and current log files:
 
Startup error:
 
SQL> startup
ORACLE instance started.
Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select group #, thread #, archived, status from v $ log;
GROUP # THREAD # ARC STATUS
---------------------------------------
1 1 NO CURRENT
3 1 YES ACTIVE
2 1 YES INACTIVE

Alert Log error:
 
Completed: ALTER DATABASE MOUNT
Fri Apr 25 16:49:21 2014
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Parallel recovery started with 2 processes
Started redo scan
Errors in file/u01/oracle/diag/rdbms/test/trace/test_ora_29862.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Aborting crash recovery due to error 313
Errors in file/u01/oracle/diag/rdbms/test/trace/test_ora_29862.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file/u01/oracle/diag/rdbms/test/trace/test_ora_29862.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-313 signalled during: alter database open...
Fri Apr 25 16:49:21 2014
Errors in file/u01/oracle/diag/rdbms/test/trace/test_m000_29874.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 2 new persistent data failures

In this case, the active online log cannot be deleted, and an error will be reported when you try to delete the log as follows:
 
SQL> alter database drop logfile group 3;
Alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance test (thread 1)
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
SQL> alter database clear logfile group 3;
Alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance test (thread 1)
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'
SQL> alter database clear unarchived logfile group 3;
Alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance test (thread 1)
ORA-00312: online log 3 thread 1: '/u01/test/redo03.log'

 

For more details, please continue to read the highlights on the next page:

1 2 Next Page