Jump to content


Photo
- - - - -

Waitng for database - file - full function


19 replies to this topic

#16 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 23 July 2004 - 08:31 AM

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

#17 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 23 July 2004 - 02:00 PM

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

#18 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 23 July 2004 - 04:01 PM

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

#19 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 23 July 2004 - 05:45 PM

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
Nothing's as simple as you think

#20 Larry Siemer

Larry Siemer

    Member

  • Members
  • PipPip
  • 17 posts
  • Gender:Male
  • Location:Cincinnati, United States

Posted 26 July 2004 - 01:01 PM

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



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users