Archive Log Interval

[mooks]

Hi,

There is a archival log being generated every 3 mins, is it too fast? How to slow down the generation of the archive logs? Thanks!

[hnagia]

enlarge your redo log files in size

[mooks]

HI,

What is the bext interval time for the logs to be generated? How do I increase the size of the redo logs without shutting down the database? Thanks!

[julian]

Originally posted by mooks
HI,

What is the bext interval time for the logs to be generated? How do I increase the size of the redo logs without shutting down the database? Thanks!

1. There is no best interval but for busy systems every half an hours seems normal to me.

2. Create new log groups (size 4M-10M for a .log file is best possible choice), then "ALTER SYSTEM SWITCH LOGFILE" as many times as necessary in order to "bite" the news log groups. Then drop the old ones.

[mooks]

Can I dop all the old redo log group while the db is running? As long as the status is not ACTIVE then I can drop them, am i right? Then those archive logs earlier on before the log groups are dropped will be useless already, am I right?

How do i view the members in the group? When I remove the logfile group, I will need to remove the members physically from OS level, am i right?

[Edited by mooks on 09-12-2001 at 04:06 AM]

[julian]

You must have at least 3 redo groups in order to recreate logs. Note that some DBAs use only 2, i.e., the minimum. If that is the case create a 3rd group:

ALTER DATABASE ADD LOGFILE GROUP 'file' SIZE 5M;

Then:

SHUTDOWN and STARTUP RESTRICT MOUNT

Then if the database is in archivelog mode ARCHIVE LOG ALL;

loop for inactive:

Drop an INACTIVE group ALTER DATABASE DROP LOGFILE GROUP ;

Then recreate it ALTER DATABASE ADD LOGFILE GROUP 'file' SIZE 5M REUSE;

end loop; :-)

Then ALTER DATABASE OPEN;

Then ALTER SYSTEM SWITCH LOGFILE;

Then SHUTDOWN, possible BACKUP, startup.

Isn't there in your office somebody who could assist you with all that? R U the only DBA?

[mooks]

Is there any ways that I can do it without shutting down the database? Can I just :

1. create 3 new logfile groups
2. switch the logfile a few times
3. drop the old inactive logfile groups
4. remove members from OS level

[Sureshy]

Hi...

I think this answers your question...

Assuming you wish to add 2 new groups of 2 members each and each logfile is
going to be 10 meg is size, try this :


code:
select max(group) from v$logfile;

This gets the current highest group number - assume 3 for the following.

code:
alter database add logfile group 4
'new_logfile_4a.log' size 10m,
'new_logfile_4b.log' size 10m;

alter database add logfile group 5
'new_logfile_5a.log' size 10m
'new_logfile_5b.log' size 10m;

If you want to add an extra logfile member to each group :

code:
alter database add logfile member
'new_logfile_4c' to group 4;

alter database add logfile member
'new_logfile_5c' to group 5;

Next you can get rid of the old ones :

First find out which is current :

code:
Select group# from v$log where status = 'CURRENT';

This tells you the curent active group number - you can get rid of the old ones which are not current as follows :

code:
alter database drop logfile group N;

Repeat until all old ones are dropped - except for the current one of course, which you can't drop !

Then, if a new logfile group is not current :

code:
alter system switch logfile;

And check the current group number again - it should be one of the new ones and you can drop the old group again.

There you have it - an instance with new bigger logfiles. BUT, if the database is not in archivelog mode, all the redo in the old logs is gone ( so take a backup. Also, because you have changed the database structure,
you will need :

code:
alter database backup controlfile to trace;

I presume that if a recovery is required and archivelog mode is on, and one of the old redo logs (as archived) is required, Oracle will handle it no problems at all and simply askfor/use the smaller copy of the old logfiles.

[mooks]

Thanks! That was what I have intended to do! I hope that it is not wrong! After dropping the logfile group, I can remove the members manually from the OS level right??

[julian]

After dropping the logfile group, I can remove the members manually from the OS level right??

Right. In case they are cooked files of course :-)