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,