An interesting Oracle-Error case I encountered was about losing all redo log files.
The whole investigation and restore process took 12 hours.
Our server was restored from a Windows backup, but no redo log files were restored.
So redo log files do not exist, and no database archived logs backup were available either.
The database version was 11gR2.
Our redo logs inside the DB in v$log:
redo 1 inactive
redo 2 current
redo 3 inactive
I cannot bring the database to OPEN mode because it can’t find the logfiles of the redo logs in the expected path.
ORA-00313 open failed for members of log group 2 of thread 1
Since in the specific case, we did not care about losing the previous redo logs, I decided to drop them. I created 3 new redo log groups (4,5,6), and I dropped the 1,3 groups.
I cannot drop the redo log group 2 because it needs to switch to inactive status first.
Also, the below commands were NOT working:
alter system switch logfile;
alter system checkpoint;
The database is in MOUNT mode (it needs to be in OPEN mode to execute the commands).
I tried the below command:
alter database open resetlogs;
Obviously it failed with error, and it did not work.
I tried to open the database in restricted mode to switch logfiles:
shutdown immediate
startup restricted
(error: ORA-01041: internal error. hostdef extension doesn’t exist) -> seems to be an oracle bug.
http://www.dba-oracle.com/t_ora_01041_internal_error_hostdef_extension_doesnt_exist.htm
I changed the sqlnet.ora file to overcome it : SQLNET.AUTHENTICATION_SERVICES = (NONE), but did not work.
So, I worked around it with the below steps:
close the sqlplus
restart the OracleDB from windows Services and connect again
(so the database starts automatically but only in MOUNT mode. I could not find any other way to start it except in MOUNT status).
I tried:
ALTER DATABASE OPEN FORCE;
( I could not open it. When the command ran, it was losing connection to DB.
ORA-12154: TNS: could not resolve the connect identifier specified – although nothing else changed – and so I needed to do the above workaround again)
My solution -> recover the DB based on the SCN of the Current Redo Log
select a.first_change#, a.status, b.member from v$log a, v$logfile b where a.group#=b.group# and a.status=’current’;
shutdown immediate;
startup mount
recover database until change «My_SCN_Number_From_Above»;
alter database open resetlogs;
Written by Nikos Minaidis
12/2020
Nikos Minaidis 