Skip to Main Content
  • Questions
  • resizing redo log and sizing log_buffers.


Question and Answer

Tom Kyte

Thanks for the question, B en .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: September 16, 2008 - 12:11 pm UTC


Viewed 10K+ times! This question is

You Asked

What is the easiest way to resize REDO logs! (without

These are the REDO logs
group 1 ('/oradb02/RIMSDB/redo1a.dbf',
'/oradb03/RIMSDB/redo1b.dbf') size 32m ,
group 2 ('/oradb02/RIMSDB/redo2a.dbf',
'/oradb03/RIMSDB/redo2b.dbf') size 32m ,
group 3 ('/oradb02/RIMSDB/redo3a.dbf',
'/oradb03/RIMSDB/redo3b.dbf') size 32m ,
group 4 ('/oradb02/RIMSDB/redo4a.dbf',
'/oradb03/RIMSDB/redo4b.dbf') size 32m ,
group 5 ('/oradb02/RIMSDB/redo5a.dbf',
'/oradb03/RIMSDB/redo5b.dbf') size 32m ,
group 6 ('/oradb02/RIMSDB/redo6a.dbf',
'/oradb03/RIMSDB/redo6b.dbf') size 32m ;

I need to make them larger (96M).

Large (60Gb) production OLTP system what size should the
log_buffer be, currently log_buffer = 3145728 (should I make it
larger when I resize the REDO's?)

and Tom said...

thats a trick question. First of all -- redo log files are NOT datafiles (hence, that command won't work) and second of all -- you cannot resize redo log files.

You must drop each group and then recreate it. That is the only way.

The log_buffer automagically empties:

o every three seconds
o when you commit
o when it is 1/3 full

So, unless you have

o concurrent transactions whose total changes are 1meg AND
o they take less then three seconds from start to finish


o single transactions that take less then three seconds and generate 1 or more meg of redo

you don't need to do anything with the log buffers. 3meg is pretty huge - its a function of the number of concurrent transactions and the amount of data they can change. Since the log buffers are flushed with each commit as well -- if you are in a situation where lots of commits are frequently happening (eg: you do >1 TPS) having a large log buffer is not productive (waste of memory). Resize your log buffer with that in mind.

followup to comment

I'm glad to supply more info but in this case -- there is no command to RESIZE a log file. As I said, you must DROP and CREATE them:

alter database drop logfile '.......';

alter database add logfile ....;

</code> <code>

for the full syntax.


  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment


You're assuming a level of knowledge some of us don't yet have

Jean Therese, June 27, 2001 - 10:36 am UTC

What is the command for resizing? I gathered from the response what needs to be done, but still don't know how to do it.

This is why we're here - to learn all these necessary things we don't know. Thanks for the volume of info.

RBS: deleting last valid member of a group?

Aivar, June 20, 2003 - 10:52 pm UTC

Hi Tom.

My question is a bit off topic, I am stuck with a sentence from </code> <code>for OCP exams.
It's regarding rbs management: "You cannot drop the last valid member of a group". Can you explain this, does it make sense?


Tom Kyte
June 21, 2003 - 10:31 am UTC

it has nothing to do with RBS

it sounds like they are talking about your online redo logs, you have N groups of redo, each with M members. You can drop members of a group.

</code> <code>

Re: cannot drop the last valid member

Aivar, June 22, 2003 - 6:48 pm UTC

Hi Tom,

Sorry, yes this is regarding redo! But they still have this at </code> <code>
It says "You cannot drop the last valid member of a group".
I think this just means you can't drop all members of a redo log file group that contains valid data. You have to use 'alter database drop logfile' and get rid of whole group instead.


Tom Kyte
June 22, 2003 - 9:25 pm UTC

read the link I provided, it basically says exactly the same thing. It is the real documentation (and it is all there for you..)

resizing redo log and sizing log_buffers

Rahul Chaudhari, June 24, 2003 - 7:34 am UTC

Actually we can't resize the redo logfiles we have to drop the redo logfile and then recreate the redo logfile again with the new size what-ever we want. as the log buffers are flushed with each commit, so this option also wont work properly for this case. u'll have to recreate the logfile again and drop the first one.

Tom Kyte
June 25, 2003 - 8:56 am UTC


it is noted above that you cannot resize log files, but I don't understand your "as the log buffers are flushed ... so this option won't work"

what option?
what wont work?

Dataguard redolog

Marcio Portes, November 26, 2006 - 12:33 am UTC

I have to move off my redologs from a filesystem to another. Currently, we are using Dataguard set to MaxAvailability thus the standby redologs are playing as well. I don't want to move the standby redos, just the online redos.


Should I consider anything else to create/drop my redolog files?


Tom Kyte
November 26, 2006 - 9:31 am UTC

</code> <code>

I can say this - you should minimally have one more group on the standby than the primary, but that is mostly for performance reasons (eg: you don't have parity between standby and production typically, standby just uses it logs in a circular fashion just like production - lgwr is transmitting the bits over the network, it is not an "online redo => standby redo" transfer, it is a lgwr write to standby transfer).

It will likely be "no big deal", you just add new logs on the new device and then switch to them and then drop the old.

What about resizing logfile while standby is on dataguard

Ashish, April 26, 2008 - 4:16 am UTC

Hi Tom,
What about resizing logfile while standby is on dataguard and fast start failover("Log writer sync affirm")?

I am assuming to accomplish this, we need to first create new standby log files on standby, which match the size of new redo logs to be created on primary
Tom Kyte
April 28, 2008 - 12:53 pm UTC

show me how to resize a log file first.

resize redo logs on primary and standby database

A reader, August 22, 2008 - 1:50 pm UTC

I know how to resize the redo logs without standby database. However, with standby running, I'm not sure the procudures.

For example:
1) Do I need to cancel Redo Apply before I change the log files on primary database?
2) Do I need to set standby_file_management to MANUAL on both primary and standby database?

Tom Kyte
August 26, 2008 - 7:37 pm UTC

trick question, you do not resize redo logs.


it is all documented.

In my opinion the documented way does not work.

Thomas Rieder, September 11, 2008 - 5:03 am UTC

SQL> ALTER DATABASE DROP LOGFILE '/u01/oradata/PLIFLEX01S2/redo_g1m1PLIFLEX01S2.dbf' ;                                                                

SQL> alter database recover managed standby database cancel ;                                                                                         

Database altered.

SQL> alter system set standby_file_management=MANUAL ;                                                                                                

System altered.

SQL>  select * from v$log;                                                                                                                            

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1      29456  262144000          1 YES CLEARING            3.2299E+10 11-SEP-08
         4          1      29457  262144000          1 YES CLEARING            3.2299E+10 11-SEP-08
         3          1      29457  262144000          1 YES CLEARING_CURRENT    3.2299E+10 11-SEP-08
         2          1      29455  262144000          1 YES CLEARING            3.2299E+10 11-SEP-08

ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance PLIFLEX01S2 (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oradata/PLIFLEX01S2/redo_g1m1PLIFLEX01S2.dbf'

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 PLIFLEX01S2 (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oradata/PLIFLEX01S2/redo_g1m1PLIFLEX01S2.dbf'

Oracle states the redolog on the physical standby as clearing. So they should be empty. But it claims, that the files are needed for for recovery.

The dg config is in the maxavailability mode.

to performe a checkpoint is impossible at standby site since the db is not open. Even a restart or the standby db does not change the situation.

A possible solution might be descriped at metalink Note:395146.1 but I am scared, that this solution may corrupt the filesize specified in the controlfile.

Tom Kyte
September 16, 2008 - 12:11 pm UTC

what documented way, you say the documented way doesn't work but you do not refer us to any documentation.

How to drop and resize an online redo log on the standby databse

Luis Figueira, September 16, 2008 - 12:11 pm UTC

The previous review have almost everything right, even the comment about the metalink note:395146.1. Indeed it's ratter confusing.

The way I use to drop and recreate the online redo logs on the standby, when they are with status CLEARING or CLEARING_CURRENT it's simply to issue an "alter database clear logfile group n;" on the STANDBY.

After clearing the group you can then drop it, and recreate the group with the new size.
Then issue a log switch on the primary database.

This will change the status for the standby online redo group with status "CLEARING_CURRENT" to "CLEARING"
Issue the "alter database clear logfile ..." for the group with status "CLEARING", drop the group, recreate it and issue new log switch on the primary.

Do this for all groups and should be ok.

Obviously, before do any of this you will need to stop the recovery process on the standby database and also temporarily set the standby_file_management to manual. Also, if you are using a Data Guard Broker configuration you need to disable the configuration first.

Hope this helps.


Resizing Redo Log File

Karthickumar Pillaiyarsamy, December 30, 2010 - 3:18 am UTC

> First of all, you can't resizing the redo log files.

> If you want resize the redo log file, you should drop the current log file.

> At the time of drop the redo log file you got error, because till the time archeiver did'n write the redo log file.

> First you should switch the log file using this command


> Again you got the error(Crash Recovery) while you drop the redo log file.


> Now you can drop the redo log file.

> Then create new file with new size.

> Even you have more members in the group, all the members are concurrently, like a multiplexing.