How to change the size of Online Redologs?

If you deal with Oracle Databases for years on a daily basis like I do, you sometimes forget that things that seem elementary to you ain’t necessarily so for others, especially newbies in the Oracle realm. I noticed that recently when I saw some discussions in OTN about redo logfiles and their sizes.

When I go back in time about 12 years, that’s where I was the first time responsible for an Oracle Database (version 7, but some things never change). I inherited that Database from the previous DBA who did the setup, creating quite small log groups of a couple 100 KBs in size – which was not uncommon these days,  because Oracle templates mentioned that size as “medium”, if I recall it right.

I got log switches almost every 10 seconds or faster, but didn’t think about it since nobody complained and everything seemed to run fine. Then I attended an Oracle University course and I learned that this was way too many log switches in that short time and that I should increase the log files. Believe me, that was an exciting thing to do for me those days! I sat there, fingers crossed (mentally, because I had to type in the commands) and did it veeeery carefuly on our production database then.

Some things to consider with changing the size of online redologs:

  • You do it for performance reasons – mostly because your original files are too small and you get log switches too often
  • It is an Online Operation – no end user will even notice about it – apart from maybe better performance afterwards
  • You will not put your Database at risk with it – if you do it right :-)

Let me give you an example:

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 1          1 INACTIVE                 50
 2          1 INACTIVE                 50
 3          1 CURRENT                  50


SQL> select group#,member from v$logfile;

 GROUP# MEMBER
---------- ------------------------------------------------------------
 3 /u01/app/oracle/oradata/orcl/redo03.log
 2 /u01/app/oracle/oradata/orcl/redo02.log
 1 /u01/app/oracle/oradata/orcl/redo01.log

This is my configuration as done by the DBCA. Now I want to get 100m sized logfiles and I want them mirrored for security reason. First, I create new log groups of the desired size:

SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g4m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g4m2.rdo')
     size 100m;

Database altered.

SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g5m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g5m2.rdo')
     size 100m;

Database altered.
SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g6m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g6m2.rdo')
     size 100m;

Database altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 1          1 INACTIVE                 50
 2          1 INACTIVE                 50
 3          1 CURRENT                  50
 4          2 UNUSED                  100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

6 rows selected.

I like the above naming convention, because I can determine by the name easily what member of what group I see and because the suffix rdo is not so easy misinterpreted as a mere ascii textfile that can be deleted if space is needed – believe me, that has happend many times and made the DBA of that site curse and suffer :-)

Now I am going to drop the small files – with SQL commands first!

SQL> alter database drop logfile group 1;

Database altered.

That was easy, wasn’t it? Notice that the OS file is not gone yet  – we have to delete them manually (if they are not OMF). All groups that have the status INACTIVE can be dropped that way.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 2          1 INACTIVE                 50
 3          1 CURRENT                  50
 4          2 UNUSED                  100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

SQL> alter database drop logfile group 2;

Database altered.

If the status is CURRENT or ACTIVE, it won’t be possible to drop the group – you can do nothing wrong here!

SQL>  alter database drop logfile group 3;
 alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

CURRENT means that LGWR is actually using this group to write the redo protocol into. If a log switch happens, the status can become ACTIVE or INACTIVE:

SQL> alter system switch logfile;

System altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 3          1 ACTIVE                   50
 4          2 CURRENT                 100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

ACTIVE means, the checkpoint associated to the redo protocol in that group is not yet finished. It is in other words still needed for a possible instance recovery and cant be dropped therefore. But we can force the checkpoint:

SQL> alter system checkpoint;

System altered.
SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 3          1 INACTIVE                 50
 4          2 CURRENT                 100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

SQL> alter database drop logfile group 3;

Database altered.

Now I am finished with my SQL commands and I can continue with deleting the files of the small groups from the OS. The instance was OPEN all the time and no user was in any way concerned by this whole action. By the way, I mirrored the log groups to protect my database against really serious problems that would arise otherways, if a whole log group gets lost – for example because someone deletes a file from the OS.

About these ads

,

  1. #1 by Don Sharp on January 20, 2010 - 21:52

    Thanks…good write up. When you say you mirrored the log groups, did you do this at the OS level? They all look like they are on the same mount point. It is a good idea to put members of the same group on different mount points.

  2. #2 by Uwe Hesse on January 21, 2010 - 08:49

    Don,
    the mirroring in the example is done with Oracle methods – we have two files each making up one log group.
    Frankly, I did the example on my notebook with one hard disk in, so mount points etc. are not that much relevant for that setup. Else you are right: It would be sensible to put different members of one group on different devices.
    Even with some kind of hardware mirroring, I would recommend to mirror also with Oracle methods as in the example, to protect the groups against human errors.

  3. #3 by Hemant K Chitale on January 22, 2010 - 09:51

    “You will not put your Database at risk with it – if you do it right”
    and
    “Notice that the OS file is not gone yet – we have to delete them manually”

    *unless* you delete the *wrong* file manually.
    Been there, done that !

  4. #4 by Uwe Hesse on January 22, 2010 - 10:03

    Hemant, thank you for pointing that out! That was indeed the reason why I said “if you do it right” :-)
    Last week I had an inhouse course for Oracle DBA beginners. We talked about increasing redo logs, and one student asked, whether he could just delete the old too small files from the OS – without dropping them with SQL first. Not really a good idea…

  5. #5 by Surachart Opun on January 22, 2010 - 19:08

    Thank You for good article.

    I’d like to know your idea.
    We should 2 members in each redo group and should be located on a different disk, to protect the group from loosing all of its members in the event a disk.

    If we use only one PATH. What is advantage of 2 members?

    ‘/u01/app/oracle/oradata/orcl/redo_g6m1.rdo’, ‘/u01/app/oracle/oradata/orcl/redo_g6m2.rdo’

  6. #6 by Uwe Hesse on January 22, 2010 - 19:39

    Surachart,
    as I have mentioned already to Don, on my notebook where I did the example, there is only one hard disk – if it crashes, everything is lost. With realistic hardware, we would of course mirror across different devices. Even with only one hard disk and all files in one PATH, there is still some sense in mirroring like I did: Protection against human error, resp. people who incidentally delete one file, for example.

  7. #7 by Surachart Opun on January 22, 2010 - 19:50

    To Uwe Hesse,

    Thank You for your mention. I always make 2 members.

    But I find some application products(some companies), they created database one member on each redo log group. and I think I should add redo member.

    -)

  8. #8 by imrankhakwani on August 13, 2011 - 18:31

    Excellent article. Just used it on my production 2 node RAC database.

    Would like to add a few things here in the context of RAC:
    1. Use this query to show group, thread (RAC node) and member
    select group#,thread#,members,status,bytes/1024/1024 as mb from v$log;

    2. Run this command on both nodes to create new log groups and log files (My DB is on OMF)
    alter database add logfile;

  9. #9 by Halim on October 20, 2011 - 09:02

    nice

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 2,428 other followers

%d bloggers like this: