Urgent - Archive logs genrating too frequent

[Sureshy]

Oracle 8.1.6
SCO Unixware 7

Hi Freinds,

Since Dec 01 as of around 12:30am my archive logs have switched into overdrive. A log is being generated every 2/3 minutes when previously they were being generated every 10/20 minutes. This is causing a space issue problem.

I need to find out why suddenly there is such an increase.
The tablespaces are not in backup mode.
There are no major deletes.
Only 70 connected users currently.
At 12:30 am there would have been hardly anyone connected.
Database is about 3Gb in size and not growing rapidly.

Archive logs are 10Mb
Currently generating around 2 -300Mb of archive logs an hour!

Checked al;ert log and got :
Failure to extend rollback segment RBS15 because of 1650 condition. Gotr this message for RBS 15 and 14 about 4 times.

The database has been bounced to no avail.

Can anyone please suggest what the problem could be or give me a plan of attack to discover where all the redo log generation is coming from ?

Thanks in advance

Suresh

[Sureshy]

Hi Friends,

found that 1650 condition was due to insufficient space on RBS tablespace. I have increased tablespace size to resolve.

Could this be the reason that more archive logs where being generated ? I thought that Oracle would return an error and the statment which aquired thr rollback segment would not complete. This did not happen.

I looked at v$waitstat and got

undo header 6
undo block 22

I guess this means I should created more rolback segments, although, I already have 25 for a max of 120 users...

Any help on this would be appreciated.

Thanks

Suresh

[sambavan]

You might want to check the redolog size and the log switches.

Sam

[Sureshy]

Hi Sam,

redo log size is 10Mb
sometimes getting 5 - 7 log switches a minute (50 - 70MB)
sometimes 1 a minute.

Database is not growing or shrinking in size at an unusual rate.

check v$waitstat and an getting
undo header 48
undo block 859

could this have something to do with the amount of redo ?
How?

I believe I already have enough rollback segments (25 for 100 users)

checking v$rollstat xacts is either 0 or 1
transactions_per_rollback_segments set to 4
optimal is set.

[sambavan]

The log switches seems to be too frequent. Oracle advises you to have the log switch every 30min so, you would require to increase your redo log size appropriately. This would inturn reduce the archive logs that gets generated.

Sam

[Sureshy]

Hi Sam,

Thanks. . am going to increase redo log size.

However this does not resolve the investigation into why the amount of redo log information generated has increased so dramatically overnight.

I am going to use Logmnr to detect what type of redo log info is being stored.

Thanks for your help.

Suresh

[julian]

Originally posted by sambavan
The log switches seems to be too frequent. Oracle advises you to have the log switch every 30min so

The better rule is to have about 20-30 log switches a day.