Extend size of redo log files in oracle

Check status of redo logs

SQL> set line 800 pages 1000;
SQL> col member for a90;
SQL> col name for a110;
SQL> col IS_RECOVERY_DEST_FILE for a21;
SQL> select * from v$logfile;

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

Check the size of redo logs

SQL> select group#,thread#,bytes/1024/1024 mb,archived,status, members from v$log;
GROUP# THREAD# MB ARC STATUS MEMBERS
1 1 50 NO INACTIVE 1
2 1 50 NO INACTIVE 1
3 1 50 NO CURRENT 1

Size 50MB is too small, It’s can not meet requirement of massive updation and business. So I decide to extend size to 200MB

STEPS

Delete redo log whose status is INACTIVE. and recreate the redo log which is in same GROUP. such GROUP 1.

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add logfile thread 1 group 1 size 200M;
Database altered.

SQL> select group#,thread#,bytes/1024/1024 mb,archived,status, members from v$log;
GROUP# THREAD# MB ARC STATUS MEMBERS
1 1 200 YES UNUSED 1
2 1 50 NO INACTIVE 1
3 1 50 NO CURRENT 1

It’s absolutely that the size of group 1 changed to 200MB.

Delete redo log grop 2,recreating the redo log which is in GROUP 2.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile thread 1 group 2 size 200M;
Database altered.

SQL> select group#,thread#,bytes/1024/1024 mb,archived,status, members from v$log;
GROUP# THREAD# MB ARC STATUS MEMBERS
1 1 200 YES UNUSED 1
2 1 200 YES UNUSED 1
3 1 50 NO CURRENT 1

The size of group 2 changed to 200MB.

Switch redo logs

We need to switch redo logs, Because status log group 3 is CURRENT, it can not be deleted,

SQL> alter system switch logfile;
System altered.

SQL> select group#,thread#,bytes/1024/1024mb, archived, status, members from v$log;
GROUP# THREAD# MB ARC STATUS MEMBERS
1 1 200 NO CURRENT 1
2 1 200 YES UNUSED 1
3 1 50 NO ACTIVE 1

Make checkpoint

This operation can flush data buffer to disk, in other words, it can change status of group 3 from ACTIVE to INACTIVE)

SQL> alter system checkpoint;
System altered.

SQL> select group#,thread#,bytes/1024/1024 mb,archived, status, members from v$log;
GROUP# THREAD# MB ARC STATUS MEMBERS
1 1 200 NO CURRENT 1
2 1 200 YES UNUSED 1
3 1 50 NO INACTIVE 1

So the status of GROUP3 changed to “INACTIVE” , deleting redo log group 3 and recreating the redo log which is in GROUP 3.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile thread 1 group 3 size 200M;
Database altered.

SQL> select group#,thread#,bytes/1024/1024 mb,archived, status, members from v$log;
GROUP# THREAD# MB ARC STATUS MEMBERS
1 1 200 NO CURRENT 1
2 1 200 YES UNUSED 1
3 1 200 YES UNUSED 1

At this point. size of all reddo logs changed to 200MB

Attachment:
How to add redo log and specify it’s thread, group and path.

SQL>alter database add logfile thread x group x (‘/path../redo0x.log’) size xxxM;