0

I'm currently studying oracle 11g,i had this question in mind but never found the answer even in the docs. What happens if log files storage is full?

does the DB stop or block any DML or DDL execution? Does it delete ancient log files and run like nothing happened ?

Thank you !

3
  • @Phil W already gave a very thorough and accurate answer. But I am a bit puzzeled by "studying oracle 11g". While a lot of the fundamentals remain the same, 11g is by now obsolete and fully unsupported. Is there a reason you are studying that particular version, and not the current 19c or 20c?
    – EdStevens
    Sep 4, 2020 at 12:50
  • University program Sep 4, 2020 at 13:10
  • Gotta wonder about a university that teaches out-of-date products. Like taking a course on Windows, and they are using Windows XP.
    – EdStevens
    Sep 4, 2020 at 13:17

1 Answer 1

1

What happens if log files storage is full?

Short answer: The database hangs and will not accept any further changes. Read activity may or may not be able to continue.

Changes (insert, updates, etc.) are written to the Online Redo logs. There are always a number of these arranged as a Circular Buffer and, once one of them is full, the database simply "moves on" to write into the next, "available" one.

The Online Redo Logs that are full are picked up the by the Archiver process, which dumps the redo data into an Archived Redo Log (ArchiveLog) file and clears the OnLine Redo Log, making it available for use again. This usually happens in plenty of time, before the database needs to use that particular Online Redo Log file again.

If the disk containing the Archivelogs fills up, nothing happens - straight away.

The Archiver process will simply wait until it gets enough free space to write the ArchiveLog file and will not do anything else until it can.

Meanwhile, the database continues to fill up the OnLine Redo Logs with changes, periodically moving from one to the next, as they fill up.

The Crunch Point comes when the Archiver is "stuck" and the Online Redo Logs are all full up as well. Now your database simply hangs. It cannot accept any further changes because it has nowhere to record them.
By this time, the Alert log will be full of error messages saying that the Archiver is stuck.

You should only remove ArchiveLog files manually using RMAN.
RMAN knows which files it has to keep hold of so that it can recover your database and so won't get rid of anything you need. Just deleting files by hand, you might.

Better still, you can tell the database to put the ArchiveLog files into the Fast Recovery Area, in which case Oracle database can manage them automatically. You still need to setup and configure RMAN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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