Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, I will talk about how to resize redo log files in Oracle.
1. We list the redo logs.
1 2 3 4 5 6 7 8 | SQL> select g.group#,g.member,l.bytes/1024/1024 "Size_MB",l.status from v$log l,v$logfile g where l.group# = g.group# order by 1,2; GROUP#|MEMBER |Size_MB |STATUS -----|-----------------------------------|----------|---------- 1|/u01/app/oracle/testdb/redo01.log | 50|INACTIVE 2|/u01/app/oracle/testdb/redo02.log | 50|CURRENT 3|/u01/app/oracle/testdb/redo03.log | 50|INACTIVE 4|/u01/app/oracle/testdb/redo04.log | 50|UNUSED |
2. Currently redolog sizes are 50MB. In order to increase the redolog size to 100MB, we first drop the INACTIVE groups.
1 2 | SQL> alter database drop logfile group 1; SQL> alter database drop logfile group 3; |
3. We also delete the Redo Log files through the operating system.
1 | testdb /u01/app/oracle/testdb> rm redo01.log redo03.log |
4. After the INACTIVE groups are dropped, we list them again.
1 2 3 4 5 6 | SQL> select g.group#,g.member,l.bytes/1024/1024 "Size_MB",l.status from v$log l,v$logfile g where l.group# = g.group# order by 1,2; GROUP#|MEMBER |Size_MB |STATUS -----|-----------------------------------|----------|---------- 2|/u01/app/oracle/testdb/redo02.log | 50|CURRENT 4|/u01/app/oracle/testdb/redo04.log | 50|UNUSED |
5. We increase the size of the dropped redo log files to 100MB.
1 2 | SQL> alter database add logfile group 1 '/u01/app/oracle/testdb/redo01.log' size 100M; SQL> alter database add logfile group 3 '/u01/app/oracle/testdb/redo03.log' size 100M; |
It is listed again.
1 2 3 4 5 6 7 8 | SQL> select g.group#,g.member,l.bytes/1024/1024 "Size_MB",l.status from v$log l,v$logfile g where l.group# = g.group# order by 1,2; GROUP#|MEMBER |Size_MB |STATUS -----|-----------------------------------|----------|---------- 1|/u01/app/oracle/testdb/redo01.log | 100|UNUSED 2|/u01/app/oracle/testdb/redo02.log | 50|CURRENT 3|/u01/app/oracle/testdb/redo03.log | 100|UNUSED 4|/u01/app/oracle/testdb/redo04.log | 50|UNUSED |
6. We make a log switch to activate the groups in UNUSED state.
1 2 3 4 5 6 7 8 9 | SQL> alter system switch logfile; SQL> select g.group#,g.member,l.bytes/1024/1024 "Size_MB",l.status from v$log l,v$logfile g where l.group# = g.group# order by 1,2; GROUP#|MEMBER |Size_MB |STATUS -----|-----------------------------------|----------|---------- 1|/u01/app/oracle/testdb/redo01.log | 100|ACTIVE 2|/u01/app/oracle/testdb/redo02.log | 50|ACTIVE 3|/u01/app/oracle/testdb/redo03.log | 100|ACTIVE 4|/u01/app/oracle/testdb/redo04.log | 50|CURRENT |
