Muneer's Database Administration Blog

Oracle DBA Knowledge Portal

How to change the Redo Log File size in Oracle Database

Dear Friends,

Yesterday we found and realized that there has been too much log switching in one of our databases and it was impacting the database performance. This excessive log switching was happening because our Redo Log files were not enough large as per the the database activity.

We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.

We decided to increase the Redo Log size to 100MB.

The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.

We have the following Log Groups and members:

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

And the status of the Log Groups is:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1         11   52428800          1 YES       ACTIVE                                                 1156732 27-JAN-11

Here we see that the Group# 1 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:

For this, execute a checkpoint:

SQL> alter system checkpoint;

System altered.

Now again check the status of the Redo Groups:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1         11   52428800          1 YES       INACTIVE                                               1156732 27-JAN-11

The group 2 is now Inactive. Hence we can proceed with dropping this group:

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)
ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'

What went wrong ???

It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.

To overcome this issue, we need to add one more Redo group to the database.

Execute the following step:

SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

… and the status of the Groups:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1         11   52428800          1 YES       INACTIVE                                               1156732 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

The status of the new Group is UNUSED because it has never been used.

Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.

SQL> alter database drop logfile group 2;

Database altered.

Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.

Now add the Redo Group 2 back to the database with changed Redo size:

SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1          0  104857600          1 YES       UNUSED                                                       0
 3          1          0  104857600          1 YES       UNUSED                                                       0

Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 YES       ACTIVE                                                 1156736 27-JAN-11
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

Still the Grpoup is in Active status. Issue a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 YES       INACTIVE                                               1156736 27-JAN-11
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

Now we can drop the Redo Group# 1.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO

Delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo01.log’ from OS.

Now we need to add this group back to the database with 100MB Redo Log file:

SQL> alter database add logfile group 1 '/app01/oratest/oradata/BOTI/BOTI/redo01.log' size 100M;

Database altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1          0  104857600          1 YES       UNUSED                                                       0
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

Here we can see that all the Redo Groups are now showing 100MB size (column BYTES).

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

Though it’s suggested that you should keep 3 groups, if you want you can drop the Redo Group 3 which we added for this exercise.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1          0  104857600          1 YES       UNUSED                                                       0
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         14  104857600          1 NO        CURRENT                                                1157584 27-JAN-11
 2          1         13  104857600          1 YES       ACTIVE                                                 1157376 27-JAN-11

Congratulations ! You have changed the Redo Log file size from 50M to 100M.

Hope this helps.

References:

MOS Note: Example of How To Resize the Online Redo Logfiles – [ID 1035935.6]

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/RLSZ/Default.aspx

About these ads

7 responses to “How to change the Redo Log File size in Oracle Database

  1. Ted May 26, 2011 at 4:48 AM

    Thank you, Muneer.
    Can you go into some additional detail about excessive redo log switching? What about redo log space requests? What is too much?
    Thanks!

  2. Amar July 30, 2012 at 10:09 AM

    Thanks Muneer……..It helps a lot.

  3. Mahrukh Khan December 25, 2012 at 6:22 AM

    Thank you very much. It helped me a lot

  4. Abhishek February 14, 2013 at 3:13 PM

    Thanks man .It helped me a lot.

  5. mahesh April 27, 2013 at 11:21 PM

    Thanks a lot….It save my time for production activity..
    Mahesh

  6. lokesh jain May 1, 2013 at 7:16 PM

    THANX DEAR SIR
    LOKESH

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 62 other followers

%d bloggers like this: