Corrupted Redo log Files
The unplanned power outage, my development database was not starting up because of Redo log files corrupted.
Let’s have a look redo log file corrupt scenario:
Sql>alter database mount;
Mounted.
Sql>alter database open;
Error at line 1:
ORA-16038: log 3 sequence# 52428800 cannot be archived
ORA-19504: failed to create file “”
ORA-00312: online log 3 thread 1: ‘+DATA/prod/onlinelog/group_3.6.52428800’
ORA-00312: online log 3 thread 1: ‘+FLASH/prod/onlinelog/group_3.6.52428800’
I checked the all ASM disk groups are mounted or not, but ASM disk groups are mounted.
———————————————————————————————
The corrupted redo logs related error messages from my experience:
Error type 1:
If header corrupted:
Sql>alter system switch logfile;
ORA-00366: log 3 of thread 1, checksum error in the file header
ORA-00312: online log 3 threads 1: ‘+DATA/prod/onlinelog/group_3.6.52428800’
ORA-00312: online log 3 threads 1: ‘+FLASH/prod/onlinelog/group_3.6.52428800’
The archived log process hung because it could not archive the log 52428800 which got corrupted, the solution was to clear the redo log and manually switch the logs to test the fix.
1- Archived log hung because the online redo log was corrupted and could not be archived.
Error type 2:
Sql>startup
ORA-16014: LOG 4 sequence# 36 not archived, no available destination
ORA-00312: online log 4 theread 1: ‘c: \oracle\product\10.2.0\gov\redo04.log’
The above both errors, are indicating the currupted redo log files, so I followed the below following steps.
————————————————————-
Step1:
check the status of redo log groups
Sql> Select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,STATUS FROM V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS STATUS
——– ———- ———- ———- ———- ———-
1 1 7 52428800 1 INACTIVE
3 1 6 52428800 1 UNUSED
Step2:
The below command, reinitialize the redo log group without archiving the redo log.
Sql>Alter database clear unarchived logfile group 3;
Note: If the above command give the following error…below steps..otherwise we can open database.
ERROR at line 1:
ORA-00393: log 3 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 3 thread 1: ‘+DATA/prod/onlinelog/group_3.6.52428800’
ORA-00312: online log 3 thread 1: ‘+FLASH/prod/onlinelog/group_3.6.52428800’
ORA-01110: data file 281: ‘+DATA/prod/data/test1.dbf’
Sql>Alter database Clear Unarchived logfile group 3 unrecoverable datafile;
If there is an offline datafile that requires the cleared log to bring it back , the keyword is “unrecoverable datafile”.
Now check the status:
Sql>Select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,STATUS FROM V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- ———- ————
1 1 7 52428800 1 INACTIVE
3 1 0 52428800 1 UNUSED
2 1 8 52428800 1 CURRENT
Step3:
Now open the database
Sql> alter database open;
Successfully opened.
Now try to switch the redologs manually.
Sql> alter system switch logfile;
/
Sql>alter system switch logfile;
Note: Switch the manually until unless group 3 will become current.
Select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,STATUS FROM V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- ———- ————–
1 1 7 52428800 1 INACTIVE
2 1 8 52428800 1 INACTIVE
3 1 9 52428800 1 CURRENT
Now finally check the log group 3 was CURRENT….
Its good Idea,If we take Immediate current backup of hole database.
