19 replies to this topic

[Vol Yip]

Concerning Autoextend, I normally will disallow autoextend for Tablespaces (Autoextend for tables will be fine) and force DBA to do regular sizing check as a DBA job. Autoextend for tablespaces could be quite dangerous where it will blow off the file system and casues the entire system down.

Regards,

Vol

[Joseph Bove]

Neil,

All the traces in udump are sufficiently old - therefore not a factor with what happened.

In regards to autoextend, I'll double check with the site admin. I'm 90% sure that we're only dealing with autoextending tables. (Thanks for the comment, Vol.)

Redo logs are typically resetting multiple times a day. During high activity, it can be as often as once an hour. However, in general, it's more like every 5 hours or so. If this is too often, then I can certainly recommend tuning and other measures.

Thanks,

Joseph

[Neil Hunter]

It could be part of the problem, but like i said, a dba who knows what they doing should be able to sort this out easy

Try this (pimped from Metalink)

SVRMGR> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE

Or medium sites have 10mb redo logs, large sites anywhere between 20mb and 60mb

[Richard Bassett]

Joseph,

I followed this up and I found some of my old reference materials that say "the database can freeze [...] whenever LGWR is forced to wait for an inactive redo log group to become available for reuse".

I also found some recommendations I once collated for 24x7 Oracle systems that strongly suggest such systems should have "extra" monitoring in place to detect failures in the redo log archiving process - precisely because of its impact on database availability.

Is there any way you can find out if the automated redo log archiving had likely failed when you had your problem? Maybe from timestamps on the archived copies at the archive destination?

Incidentally, for very-high-throughput systems, archiving via a network or comms link can be an issue because the database may be able to create online logs (at local-disk bandwidth) faster than they can be archived. That's very unlikely to be the case with 'normal' applications though.

Also, FWIW, I never used to set production systems up with less than five online log groups.


Regs, Richard

[Larry Siemer]

Joseph,

I followed this up and I found some of my old reference materials that say "the database can freeze [...] whenever LGWR is forced to wait for an inactive redo log group to become available for reuse".

I also found some recommendations I once collated for 24x7 Oracle systems that strongly suggest such systems should have "extra" monitoring in place to detect failures in the redo log archiving process - precisely because of its impact on database availability.

Is there any way you can find out if the automated redo log archiving had likely failed when you had your problem? Maybe from timestamps on the archived copies at the archive destination?

Incidentally, for very-high-throughput systems, archiving via a network or comms link can be an issue because the database may be able to create online logs (at local-disk bandwidth) faster than they can be archived. That's very unlikely to be the case with 'normal' applications though.

Also, FWIW, I never used to set production systems up with less than five online log groups.


Regs, Richard

Joseph,

When all of the redo log groups are currently being archived, you should see a message similar to the following in the alert log:

Thread 1 cannot allocate new log, sequence 23611

Once the archiving process is done with one of the log groups the database should resume normal operations.

Larry