12c recover from one redolog file lost

SQL> select group#, member, status from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
         3
E:\APP\ORACLE12C\ORADATA\CDB\REDO03.LOG


         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02.LOG


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------

         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
INVALID

         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02A.LOG
INVALID

         3

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
E:\APP\ORACLE12C\ORADATA\CDB\REDO03A.LOG
INVALID


6 rows selected.
 SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 INACTIVE
         3          2 CURRENT
 

Delete one redolog file from group 1 with SO commands.

SQL> alter system switch logfile;

System altered.

1. Verify what logfile is missing

alert.log content:

Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Wed Jul 01 08:53:12 2015
Thread 1 advanced to log sequence 1264 (LGWR switch)
  Current log# 1 seq# 1264 mem# 0: E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 CURRENT
         2          2 INACTIVE
         3          2 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 ACTIVE
         2          2 CURRENT
         3          2 ACTIVE

2. Archive the redo log group’s contents if it's the case (database in ARCHIVELOG mode); if you clear this redo log group before archiving it, you must back up the full database to ensure maximum recoverability of the database in case of the loss of a data file.

SQL> alter system archive log group 1;
alter system archive log group 1
*
ERROR at line 1:
ORA-16013: log 1 sequence# 1264 does not need archiving

3. Make the log group INNACTIVE, with a checkpoint. If you try to CLEAR LOGFILE when the logfile group is ACTIVE or CURRENT, following error will be raised:

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance cdb (thread 1)
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG'
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

Make the log group INNACTIVE, with a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 CURRENT
         3          2 INACTIVE
 

4. Run command CLEAR LOGFILE GROUP to recreate the missing logfile from the group:

SQL> alter database clear logfile group 1;

Database altered.

After the recreation of redo log member, you might see the following error in alert.log:

Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00320: cannot read file header from log 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

In this case you have to recreate the log member:

SQL> alter database drop logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LO
G' ;

Database altered.

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;

Database altered.

Before running the ADD LOGFILE MEMBER command, be sure to delete the dropped logfile member from filesystem with SO commands. If not you will receive following error:

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;
alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG' to
group 1
*
ERROR at line 1:
ORA-00301: error in adding log file 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
- file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists