I observe corrupted online log file. It is Oracle 11g Express Edition Release 11.2.0.2.0 - 64bit (Windows 10 home)
Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_5164.trc (incident=7402):
ORA-00353: log corruption near block 75108 change 925201 time 07/15/2020 10:31:10
ORA-00334: archived log: 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG'
Incident details in: C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\incident\incdir_7402\xe_ora_5164_i7402.trc
Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_5164.trc (incident=7403):
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 75108 change 925201 time 07/15/2020 10:31:10
ORA-00334: archived log: 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG'
Incident details in: C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\incident\incdir_7403\xe_ora_5164_i7403.trc
Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_5164.trc:
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 75108 change 925201 time 07/15/2020 10:31:10
ORA-00334: archived log: 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG'
Wed Jul 15 10:41:56 2020
I tried with, "recover database using backup controlfile;" but with no success. And it is true that, I am not having complete knowledge on my approach. Can you please let me know what option I have to recover the DB from this issue. I have still kept it in this state without issuing any further commands. Please advise. Many thanks in advance.
SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 780141488 bytes
Database Buffers 281018368 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance xe (thread 1)
ORA-00312: online log 2 thread 1: 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG'
SQL> select l.status, member from v$logfile inner join v$log l using (group#);
STATUS MEMBER
----------------
CURRENT C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_HH18M861_.LOG
ACTIVE C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG
SQL> recover database using backup controlfile;
ORA-00279: change 884769 generated at 07/11/2020 17:59:46 needed for thread 1
ORA-00289: suggestion : C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC
ORA-00280: change 884769 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> recover database using backup controlfile;
ORA-00279: change 884769 generated at 07/11/2020 17:59:46 needed for thread 1
ORA-00289: suggestion : C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC
ORA-00280: change 884769 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_HH18M861_.LOG
ORA-00310: archived log contains sequence 27; sequence 26 required
ORA-00334: archived log: 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_HH18M861_.LOG'
SQL> recover database using backup controlfile;
ORA-00279: change 884769 generated at 07/11/2020 17:59:46 needed for thread 1
ORA-00289: suggestion : C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC
ORA-00280: change 884769 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG
ORA-00283: recovery session canceled due to errors
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 75108 change 925201 time 07/15/2020 10:31:10
ORA-00334: archived log: 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_HH18M8L7_.LOG'
ORA-01112: media recovery not started
SQL> recover database using backup controlfile;
ORA-00279: change 918834 generated at 07/11/2020 17:59:46 needed for thread 1
ORA-00289: suggestion : C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC
ORA-00280: change 918834 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00308: cannot open archived log 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2020_07_15\O1_MF_1_26_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>