MISSING ACTIVE & CUURENT ONLINE REDOLOG FILES

(NOTE:- ALL ARCHIVE ARE AVAILABLE IF ELSE FOLLOW below (3) STEPS COMMAND)

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          5   52428800          1 YES ACTIVE                  500371 25-MAY-12
         2          1          6   52428800          1 NO  CURRENT                 500436 25-MAY-12
         3          1          4   52428800          1 YES ACTIVE                  500317 25-MAY-12

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-------------------------------delete manual 2 & 3 logfile


SQL> startup    
ORACLE instance started.

Total System Global Area  364904448 bytes
Fixed Size                  1219448 bytes
Variable Size             109053064 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/orcl/redo01.log'


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          5   52428800          1 YES INACTIVE                500371 25-MAY-12
         3          1          4   52428800          1 YES INACTIVE                500317 25-MAY-12
         2          1          6   52428800          1 NO  CURRENT                 500436 25-MAY-12

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/orcl/redo02.log'


SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/orcl/redo02.log'


SQL> recover database until cancel;
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80       2800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       4600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       4250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       4975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       4250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       5850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       3450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       5000                    20
      7839 KING       PRESIDENT            17-NOV-81       6000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       4500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       3100                    20
      7900 JAMES      CLERK           7698 03-DEC-81       3950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       5000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       2300                    10

========================================================================

3) Redo log is CURRENT (DB was shut down cleanly)

If the CURRENT redo log is lost and if the DB is closed consistently, OPEN RESETLOGS can be issued directly without any transaction loss.
 It is advisable to take a full backup of DB immediately after the STARTUP.


RUN
 { SHUTDOWN IMMEDIATE;
 STARTUP MOUNT;
 set until sequence 26 thread 1;
 ALLOCATE CHANNEL ch1 TYPE Disk;
 RESTORE DATABASE;
 RECOVER DATABASE;
 ALTER DATABASE OPEN RESETLOGS;
 }

or
SQL> startup mount;
SQL> recover database until cancel;
SQL> recover database until cancel;
SQL> alter database open resetlogs;


4) Redo log is CURRENT (DB was not shut down cleanly)

When a current redo log is lost, the transactions in the log file are also lost before making to archived logs. Since a DB startup can no more perform a crash recovery (since all the now-available online log files are not sufficient to startup the DB in consistent state), an incomplete media recovery is the only option. We will need to restore the DB from a previous backup and restore to the point just before the lost redo log file. The DB will need to be opened in RESETLOGS mode. There is some transaction loss in this scenario.

RMAN> RESTORE CONTROLFILE FROM '<backup tag location>';

RMAN> ALTER DATABASE MOUNT;

RMAN> RESTORE DATABASE;
 RMAN> RECOVER DATABASE UNTIL TIME "to_date('MAR 05 2009 19:00:00','MON DD YYYY HH24:MI:SS')";

 RMAN> ALTER DATABASE OPEN RESETLOGS;