How to resize or re-create online redo log files in oracle

In oracle 10g by default there are three groups having one online redo log file of 50 MB in each group. It is recommended to re-size them before going to use oracle 10g in production environment. In this article I will explain how to resize redo log files from 50 MB to 250 MB. Please follow the steps give below.

Step 1 SQL> select a.group#, a.member, b.bytes/1024/1024 mb from v$logfile a, v$log b where a.group# = b.group#;

GROUP#     MEMBER                                                       MB
3                   /u01/app/oracle/oradata/ORCL/redo03.log        50
2                  /u01/app/oracle/oradata/ORCL/redo02.log         50
1                 /u01/app/oracle/oradata/ORCL/redo01.log          50

This query will show current group with redo log members and their size.

Step 2 Make the last redo log CURRENT one

To find which group is current at this moment use following query

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                    CURRENT
2                    INACTIVE
3                    INACTIVE
Now as you can see that the first group is marked as current but we need to make group 3 as current. So force group 3 to become current one by switching log file. To switch log file use following query.

SQL> alter system switch logfile;

GROUP#         STATUS
---------- ----------------
1                     INACTIVE
2                    CURRENT
3                    INACTIVE

SQL> alter system switch logfile;

GROUP#        STATUS
---------- ----------------
1                     INACTIVE
2                     INACTIVE
3                     CURRENT

Step 3 Drop the first online redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:

SQL> alter database drop logfile group 1;

Database altered.

Note:

As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group.However, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 1 thread 1: ''

Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Step 4 You need to re-create dropped online redo log group with different size. Use the following query to achieve this.

SQL> alter database add logfile group 1 (
2 '/u01/app/oracle/oradata/ORCL/redo01.log',
3 '/u01/app/oracle/oradata/ORCL/redo01.log') size 250m reuse;

Database altered.

Step 5 Force another log switch

After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" group:

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                     UNUSED
2                     INACTIVE
3                     CURRENT

SQL> alter system switch logfile;

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                      CURRENT
2                      INACTIVE
3                      ACTIVE

Step 6 # Loop back to Step 3 until all logs are rebuilt

After re-creating an online redo log group, continue to re-create (or resize) all online redo log groups until all of them are rebuilt.

I hope this article helps you in recreating and resizing the redo log files.