Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 !
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.
