Oracle 8 redo Log file resizing

[Marty Metras]

HI,
I need to add or resize the redo log files. I need to do this because with with some of the heave I/O jobs they are not getting writen out to the Archive logs before going through the rotation and the Database stopps and the other transactions wait for it to catch. A few more log groups will solve this problem.
The Controlfile already has MAXLOGFILES=16 and I have 16. I need to add some logfiles or resize them.
I do not see a way to do this with our recreating the controlfile.
Did I miss something?
Right now this is a minor issue. I want to fix it before my users notice.
Marty

[Steven E. Protter]

To add logfiles, you'd need to apparently generate new control files.

Sounds like you want to do that during the weekend or in some planned maintenance window.

A resize to larger size seems like something you might want to do instead. A few lines if sql that I'm sure will be thoughtfully provided.

SEP

[Nicolas Dumeige · Accepted solution]

Hello Marty

The DBA on our site has increased the size of our RedoLog on live instance (Oracle 9.2.0.4.).

Check metalink

Cheers

Nicolas

For instance :
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=603781.999


This is the answer given by support on the same kind of question :

We recommend that the database be shutdown and restarted in restricted mode before resizing the online redo logs, but it is not mandatory. However, I do recommend doing this during a non-peak time.

1. If the database is in archivelog mode, force all filled redo log groups to be archived.

ARCHIVE LOG ALL

2. Find out which is the current redo log group.

SELECT GROUP#, STATUS FROM V$LOG;

One of the groups will have status 'CURRENT'. That will be the last one to be recreated. At least one of the groups should have status 'INACTIVE'. If not, repeat the above query until that is the case.

3. Pick one of the inactive redo groups and drop it.

ALTER DATABASE DROP LOGFILE GROUP ;

4. Recreate that redo log group with the desired size and in the desired location.

ALTER DATABASE ADD LOGFILE GROUP
'file1' SIZE REUSE;

where 'file1' is the full path name of the redo log file.
If you are mirroring your redo log files, the statement would be:

ALTER DATABASE ADD LOGFILE GROUP
('file1', ..., 'fileN') SIZE REUSE;

5. Repeat steps 3 and 4 for all inactive redo log groups.

6. Force a log switch to make the current redo log group inactive.

ALTER SYSTEM SWITCH LOGFILE;

7. Check the status of that redo group to make sure it is inactive.

SELECT STATUS FROM V$LOG
WHERE GROUP# = ;

When the status shows as 'INACTIVE', do steps 3 and 4.

If the database is in archivelog mode, you should check both the redo group's status and whether it has already been archived:

SELECT ARCHIVED, STATUS FROM V$LOG
WHERE GROUP# = ;

When both the status shows as 'INACTIVE' and archived is 'YES', do steps 3 and 4.

[Bill Thorsteinson]

You can increase the size whenever the logfile is not active or waiting for archiving.

I write a script that will drop and recreate the logfiles one by one. Use the REUSE parameter on the file to prevent file exists errors. You will have to force a logfile switch to replace the current logfile.

Wait until it has been archived. In your case you could replace 8 logfiles then do the other 8 when the logfiles have rotated into the new files.

You can do this with the database online and
your users won't notice.

[Marty Metras]

Hi Steve,
I guess I going to pull a weekend. I was looking of a quick out the I have recreate controlfiles once in a while when I when the structure does not match the Standby database. I was looking for an easier way out.
Thanks for you input.
Marty

[Fred Ruffet]

Solution depends on how many groups and members you have. You can't resize. You can only create new. You probably have to drop some before creating a new one if you have reached max. You must find a redo log group that is not in use (with access to dictionnary):
select GROUP#,STATUS from v$log;
will tell you wich group you might drop.
Then recreate it bigger.

You might want to look before at v$logfile to see what files you will need to rm after operation.

[Marty Metras]

Hay guys. Thanks!
I forgot I could do that. I've been doing that for years with tables when they get all fraged up. Da! I never thought of just dropping the group. Hay, I the DBA here. Must be spring fever setting in.
That is a very easy fix.
Thanks for waking me up.
Marty

[Marty Metras]

Thanks Guys,
It is a done Deal.

Marty

[Hein van den Heuvel]

I know you are all done and relatively happy, but IMHO you stil need to explain how it can be that the archiving process fell so far behind.

You may want to put some numbers on the back of an enveloppe to see whether this situation is reasonable. How many MB/sec to the redo? How large are (were!) the redo?

I suspect that file placement over controllers/spindles is a critical factor here.

Worst case would be to have archive, and all redo on one (set of) spindles. That would drive you to be IO/sec limited due to thrashing: the active redo will pull the disk heads to one side 'all the time', and archive write and old redo read would yank the heads to other places.

Ideal case is archive on one set, and redos alternating between two (or three) other sets. This way, as you finish writing to one redo, it will switch to writing on the other where the first will start the reading to copy to the archive. All sets would have near sequential IO patterns, minimal seek time waste, the running at maximum mb/seq.

Personally I do not believe in lots of little REDOs at all. I just run (my benchmarks, not production) with 2 gigabyte sized redos and switch when I feel it is time to do so, nor at csome arbetrary time when they are full. That way I minimize and control the resulting checkpoint activity.
Admittedly, production systems have different parameters to deal with, such as keeping the archive logs to manageable chunks. What is manageable these days? You tell me! 500mb?

If you must stay with smaller files, do still alternate device:
- group 1 100MB on drive(s) A
- group 2 100MB on drive(s) B
- group 3 100MB on drive(s) A
- group 4 100MB on drive(s) B
- ...

fwiw,
Hein.