Oracle 12c – Logically There Is A Redo Group But Not Physically – Database Can’t Open

I have an Oracle 12c Database for my enterprise manager.

I could not connect to enterprise manager because the database was closed.

I opened listener. However, the database remained mounted mode. When I want to open the database in read & write mode, I get the error as follows:

SQL> alter database open;

ERROR at line 1:

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

ORA-00312: online log 3 thread 1:
'/path/of/redo/redo03.log'

ORA-27037: unable to
obtain file status

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

When I go to the file location of redo03, there is no redo03.

So it's logically, but not physically.

There are some queries and results:

SQL> col member format a50
SQL> select group#, type, member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         3 ONLINE  /path/of/redo/redo03.log
         2 ONLINE  /path/of/redo/redo02.log
         1 ONLINE  /path/of/redo/redo01.log
         4 ONLINE  /path/of/redo/redo04.log

SQL> select group#, thread#, sequence#, bytes/1024/1024, members, status from v$log;

    GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024    MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
         1          1      61525              50          1 INACTIVE
         4          1          0              50          1 UNUSED
         3          1      61527              50          1 CURRENT
         2          1      61526              50          1 INACTIVE

I tried doing this: create redo group 4. Drop redo group 3 and recreate redo group 3.

There was no problem creating redo group 4. I used this command below:

alter database add logfile thread 1 group 4 '/path/of/redo/redo04.log' size 50m;

But when I want to drop group 3, it gives error as follows:

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 3
*

ERROR at line 1:

ORA-01623: log 3 is current log for instance dbname (thread 1) –
cannot drop

ORA-00312: online log 3 thread 1: '/path/of/redo/redo03.log'

I wanted to try disable the thread that redo group is connected to and after that I wanted to drop redo group 3. But it give error as follows:

SQL> select thread#, status, enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC


SQL> alter database disable thread 1;
alter database disable thread 1
*

ERROR at line 1:

ORA-01109: database not open

When I try to switch logfile, it gives error as follow:

SQL> alter system switch logfile;
alter system switch logfile
*

ERROR at line 1:

ORA-01109: database not open

I tried to clear and drop the logfile group. It gives error as follow:

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 dbname (thread
1)

ORA-00312: online log 3 thread 1:
'/path/of/redo/redo03.log'

And I tried to recover database and after that open with below command:

rman taget /

recover database;

Starting recover at 09-JUL-20

using channel ORA_DISK_1

starting media recovery

RMAN-08187: WARNING: media recovery until SCN <scn_number> complete
Finished recover at 09-JUL-20

    exit;
    
    sqlplus / as sysdba
    
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/path/of/file/system01.dbf'

exit;

rman target /

recover datafile 1;

Starting recover at 09-JUL-20 using target database control file
instead of recovery catalog allocated channel: ORA_DISK_1 channel
ORA_DISK_1: SID=<sid_number> device type=DISK

starting media recovery media recovery failed

RMAN-00571:

===========================================================

RMAN-00569:

=============== ERROR MESSAGE STACK FOLLOWS
===============

RMAN-00571:
===========================================================

RMAN-03002: failure of recover command at <time_of_today>

ORA-00283: recovery session canceled due to errors RMAN-11003: failure
during parse/execution of SQL statement: alter database recover if
needed datafile 1

ORA-00283: recovery session canceled due to errors

ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/path/of/file/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory Additional
information: 3

As a result; there is a redo group 3 logically but there is no physically file. I am sure that nobody has deleted the file because I checked it with the history command on linux system. I can't open the database because redo group 3 cannot find. I can't logfile switch, I can't disable thead, I can't delete redo group 3 and recreate it. Please can you help with this?

Best regards,