REDO LOG RECOVERY

Original Post: https://jtechspot.com/recovering-after-the-loss-of-online-redo-log-files-scenarios/

SQL> startup

ORA-00314: log 1 of   thread 1 ,  expected sequence # does not   match 
ORA-00312:   online   log 1   thread 1: '/home/app/oracle/oradata/jazdb/redo01.log'

Check the status of Online redo log (whether current or inactive)

SQL> select group#, sequence#, archived, status from v$log

GROUP#	SEQUENCE#	ARCHIVED	STATUS
1	331	YES	INACTIVE
2	332	NO	CURRENT
3	330	NO	INACTIVE

scenario1:

If the damaged online log file is inactive and archive is YES

use clear command to clean up the file

SQL> alter database clear logfile group 1;

scenario2:

If the damaged online log file is inactive and archive is NO

use clear command to clean up the file

SQL> alter database clear unarchived logfile group 3;

open database and take backup

scenario3:

If the damaged or deleted online log file is current

When the database goes down

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/home/app/oracle/oradata/jazdb/redo02.log'

ORA-27041: unable to open file

SQL> startup mount

When try to clear logfile

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance jazdb (thread 1)

ORA-00312: online log 2 thread 1: '/home/app/oracle/oradata/jazdb/redo02.log'

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

SQL> recover database until cancel

type cancel

SQL> alter database open resetlogs;

After database successfully open do a full backup