Resize the Redo log file.

We can not resize the redo log file. We must drop and recreate redo log file. When we want to drop redo log file it must be in INACTIVE, if redo log file status are not INACTIVE we have to change the status after dropped from database it will update in control file and we need to remove operating system file manually.

Forcing Checkpoint : Forcing a checkpoint are ensure that all changed in the database buffer cache are written in datafile by using ALTER SYSTEM CHECKPOINT GLOBAL;

[oracle@OEL64BN1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 3 16:08:18 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdbda
Connected.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  49752428800 YES INACTIVE
 2  49652428800 YES INACTIVE
 3  49852428800 NO  CURRENT
SQL>  alter system checkpoint global ;
System altered.
SQL> select sum(bytes)/1024/1024 file_size, group# group_number from v$log group by GROUP#;
 FILE_SIZE GROUP_NUMBER
---------- ------------
50      1
50      2
50      3
SQL> col member format a35
col member format a65
set lines 130
set pages 100
select group#,type, member from v$logfile;SQL> SQL> SQL> SQL>
    GROUP# TYPE    MEMBER
---------- ------- -----------------------------------------------------------------
 3 ONLINE  /u01/app/oracle/oradata/orcln1/redo03.log
 2 ONLINE  /u01/app/oracle/oradata/orcln1/redo02.log
 1 ONLINE  /u01/app/oracle/oradata/orcln1/redo01.log
3 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> !
[oracle@OEL64BN1 ~]$ rm /u01/app/oracle/oradata/orcln1/redo01.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcln1/redo01.log' SIZE 1G;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> exit    !
[oracle@OEL64BN1 ~]$ exitrm /u01/app/oracle/oradata/orcln1/redo01.log.log2.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1    0 1073741824 YES UNUSED
 3  49852428800 NO  CURRENT
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 '/u01/app/oracle/oradata/orcln1/redo02.log' SIZE 1G;
Database altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1    0 1073741824 YES UNUSED
 2    0 1073741824 YES UNUSED
 3  49852428800 NO  CURRENT
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 NO  CURRENT
 2    0 1073741824 YES UNUSED
 3  498 52428800 YES ACTIVE
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 NO  CURRENT
 3  49852428800 YES INACTIVE
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> !
[oracle@OEL64BN1 ~]$ rm /u01/app/oracle/oradata/orcln1/redo02.log.log3.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/app/oracle/oradata/orcln1/redo03.log' SIZE 1G;
Database altered.
SQL>  SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 NO  CURRENT
 3    0 1073741824 YES UNUSED
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 YES INACTIVE
 3  501 1073741824 NO  CURRENT
SQL> select sum(bytes)/1024/1024 file_size, group# group_number from v$log group by GROUP#;
 FILE_SIZE GROUP_NUMBER
---------- ------------
      1024      1
      1024      2
      1024      3