1

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,

2 Answers 2

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.

3
  • Hi sir. I recovered the database while in mounted mode before you comment my post. After that, when I try to alter database open resetlogs; command, it gives this error: ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/path/of/file/system01.dbf'. And I tried to recover datafile 1 from rman with using this command: RMAN> recover datafile 1;
    – jrdba
    Jul 9, 2020 at 13:46
  • But it says: starting media recovery media recovery failed 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/redo/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
    – jrdba
    Jul 9, 2020 at 13:47
  • 1
    Next option would be to restore the database using "RECOVER DATABASE UNTIL CANCEL". It will ask for a log file. Press enter instead, which will end the recovery at that point, then try to open the database with RESETLOGS again. Failing that, it's time to reach for the full-blown RESTORE DATABASE / RECOVER DATABASE.
    – Phill W.
    Jul 10, 2020 at 14:23
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.