I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file is written before it continues.

How does Oracle behave in situations where a transaction overflows the redo logs and the database is in NOARCHIVELOG mode?

  • I'll add that if you care about your data and are using an Oracle database, there really is no point in running in NOARCHIVELOG mode
    – Philᵀᴹ
    Feb 25 '15 at 23:19
  • This reply is in response to Phil's comment above. Well, i disagree, if you have the right recovery strategy it may make sense to use noarchivelog. In fact I was told of a production critical db being used in financial sector that uses noarchivelog mode. They do have extensively studied the db and have enough redolog groups assigned so they dont get overwritten. not suggesting everyone should run and do this, but this might be a specific case.
    – Raj
    Feb 26 '15 at 16:46

Strictly speaking, you cannot overflow the redo logs, because they are endlessly reused in a circular manner. When in the ARCHIVELOG mode, however, a log file cannot be reused until it's archived, so there can be a situation where you would have to wait for archiving if you fill redo logs faster than they are archived.

In the NOARCHIVELOG mode there is no such restriction, so Oracle will just keep rewriting log files one after another in a circular manner.

In either case there is also a wait during log file switch because the database checkpoint must be performed before the next log is overwritten.

This is fully described in the administration manual, by the way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.