Mir Sayeed Hassan – Oracle Blog

Resize Redo log files in Oracle database 11gr2

 Note:

• We cannot resize the redo log files in Oracle database as we need to drop them & re-create it, Hence its the only way to resize the redo log files
• Therefore database require at least 2 groups of redo log files regardless the no of members & also we cannot drop the current active redo log files so we need to change it to inactive state & then only we can drop from DB
• When you drop the redo log member from DB only the controlfile is updated, so the OS files have to be deleted manually by using the rm command.

First let us check my database name, version & status as shown below:

sys@TESTDB> select INSTANCE_NAME,VERSION,STATUS from v$instance;

INSTANCE_NAME    VERSION           STATUS
---------------- ----------------- --------
sys@TESTDB           11.2.0.4.0        OPEN

Check the redolog files exist & its status

sys@TESTDB> select group#,sequence#,bytes,archived,status from v$log;    --- All its size are 50 MB

GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1      12131   52428800 NO  CURRENT
2      12129   52428800 YES INACTIVE
3      12130   52428800 YES INACTIVE

Location of the redo log files in DB

sys@TESTDB> select member from V$logfile;

MEMBER
----------------------------------------
/u01/app/oracle/oradata/prim/redo03.log
/u01/app/oracle/oradata/prim/redo02.log
/u01/app/oracle/oradata/prim/redo01.log

We need to resize the redo log files, follow the below procedure:

sys@TESTDB> alter database drop logfile group 1;   --------------Can not drop as it's in current status 
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance sys@TESTDB (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01.log'

sys@TESTDB> alter database drop logfile group 2;
Database altered.

sys@TESTDB> alter database drop logfile group 3; ------Its in inactive status but oracle need atleast 2 redo log files, So cant drop

alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance sys@TESTDB
(thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/prim/redo03.log'

Verify after dropping redo log file:

sys@TESTDB> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES  ARC         STATUS
---------- ---------- ----------  --- ----------------
1      12131   52428800  NO    CURRENT
3      12130   52428800  YES   INACTIVE

sys@TESTDB> alter database add logfile group 2  '/u01/app/oracle/oradata/prim/redo2.log' size 100m; --(Its new with 100 MB)
Database altered.

sys@TESTDB> alter database add logfile group 4 '/u01/app/oracle/oradata/prim/redo4.log' size 100m; --(Its new with 100 MB)
Database altered.

sys@TESTDB> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES        ARC           STATUS
---------- ---------- ---------- --- --------------------------------------------
1              12131              52428800       NO             CURRENT
2                  0                104857600       YES            UNUSED
3             12130              52428800        YES            INACTIVE
4                  0               104857600        YES            UNUSED

sys@TESTDB> alter system switch logfile;
System altered.

sys@TESTDB> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES           ARC                        STATUS
---------- ---------- ---------- --- -----------------------------------------------
1               12135               52428800         YES            INACTIVE
2               12136             104857600         YES            INACTIVE
3               12138               52428800          NO             CURRENT
4               12137             104857600          YES           INACTIVE

sys@TESTDB> alter database drop logfile group 1;
Database altered.

Try to perform alter system switch logfile until you reach group 3 as inactive

sys@TESTDB> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2      12151  104857600 YES INACTIVE
         3      12150   52428800 YES INACTIVE
4      12152  104857600 NO  CURRENT

sys@TESTDB> alter database drop logfile group 3;              --- Hence drop the 50 MB group 3
Database altered.

SYS@TESTDB> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2      12151  104857600 YES INACTIVE
4      12152  104857600 NO  CURRENT

sys@TESTDB> select member from V$logfile;

MEMBER
-----------------------------------------
/u01/app/oracle/oradata/prim/redo2.log
/u01/app/oracle/oradata/prim/redo4.log

sys@TESTDB> alter database add logfile group 1 '/u01/app/oracle/oradata/prim/redo1.log' size 100m;  --(Its new with 100 MB)
Database altered.

sys@TESTDB> alter database add logfile group 3 '/u01/app/oracle/oradata/prim/redo3.log' size 100m; --(Its new with 100 MB)
Database altered.

Perform some more alter system switch to get the active & current logfile status

sys@TESTDB> alter system switch logfile;
System altered.
/
/
/

sys@TESTDB> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1      12165  104857600 YES INACTIVE
2      12167  104857600 NO  CURRENT
3      12166  104857600 YES ACTIVE
4      12164  104857600 YES INACTIVE

========Hence resize the redo log files by creating new & drop the existing 50 MB files ==========