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

Question

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,

Answer 1

... there is a redo group 3 logically but there is no physically file.

Not a good place to be.

I am sure that nobody has deleted the file because I checked it with the history command on linux system.

And yet here you are, with a missing file that Oracle has previously written redo data to and, indeed, is demanding back in order to do Instance Recovery as part of startup. Clearly you are not the only one that can "get at" this database.

Try opening the database and resetting the online redo logs.

From the MOUNTED state:

alter database open resetlogs ; 

Oracle should recreate the missing redo log file for itself.

Answer 2

I am sure that nobody has deleted the file because I checked it with the history command on linux system.

That does not guarantee anything.

You lost your current logfile group. The steps for recovery you can find here:

Recovering After Losing All Members of an Online Redo Log Group

Specifically this part:

Losing an Active Online Redo Log Group

Keep in mind, this requires you to have a backup of the database.