Maintaining Online Redo Log Files on oracle 11g

Oracle Version: 11g

SQL> set linesize 250
SQL> select * from V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         19   52428800        512          1 YES INACTIVE               1315147 25-DEC-15      1350603 26-DEC-15
         2          1         20   52428800        512          1 YES ACTIVE                 1350603 26-DEC-15      1359188 26-DEC-15
         3          1         21   52428800        512          1 NO  CURRENT                1359188 26-DEC-15   2.8147E+14

SQL> select * from V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
         3         ONLINE  /u01/app/oracle/oradata/prim/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/prim/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/prim/redo01.log            NO


Adding Online Redo Log File Groups.

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 '/u01/app/oracle/oradata/prim/redo05.log'  size 50M;

Adding Online Redo Log File Members.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo1a.log' TO GROUP 1, '/u01/app/oracle/oradata/prim/redo2a.log' TO GROUP 2 ,
'/u01/app/oracle/oradata/prim/redo3a.log' to  GROUP 3 ;

SQL> select * from V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
         3         ONLINE  /u01/app/oracle/oradata/prim/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/prim/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/prim/redo01.log            NO
         1 INVALID ONLINE  /u01/app/oracle/oradata/prim/redo1a.log            NO
         2 INVALID ONLINE  /u01/app/oracle/oradata/prim/redo2a.log            NO
         3 INVALID ONLINE  /u01/app/oracle/oradata/prim/redo3a.log            NO


Dropping Online Redo Log File Groups.
First of all ORACLE will never allow you to drop the current ONLINE redolog file
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 NO  CURRENT
         2 YES INACTIVE
         3 YES INACTIVE


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance prim (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01a.log'

We can drop the redolog groups with STATUS='INACTIVE' . To do so do a log switch which change the status of the redo log group.
SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 NO  CURRENT
         3 YES INACTIVE


SQL> alter database drop logfile group 1;

Database altered.


CURRENT:- Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is
called the current redo log file.
ACTIVE:- Redo log files that are required for instance recovery are called active redo log files
INACTIVE :-Redo log files that are no longer required for instance recovery are called inactive redo log files.
UNUSED:-Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CLEARING:- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT:- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.


Dropping Online Redo Log File Members.
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo3a.log';

Database altered.

Verifying Blocks in Redo Log Files.
SQL> show parameter DB_BLOCK_CHECKSUM;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    string      TYPICAL
SQL> alter system set DB_BLOCK_CHECKSUM=true ;

System altered.

SQL> show parameter DB_BLOCK_CHECKSUM;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    string      TRUE

Clearing a Redo Log File.
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the
ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

This statement overcomes two situations where dropping redo logs is not possible:

1.If there are only two log groups
2.The corrupt redo log file belongs to the current group


If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Database altered.