Wednesday, July 4, 2012

Oracle Archive Logging

[Original PostBasically, any change that happens in the database is first captured in a memory structure called the Log Buffer. This memory structure exists inside the Oracle Instance. The Log Buffer normally has a small footprint (somewhere in the neighborhood of 1MB). Information in the Log Buffer memory is flushed to the Redo Logs by the background process LGWR under these curcumstances:
a) every 3 seconds
b) on a commit
c) when the Log Buffer becomes 1/3 full
d) on a checkpoint

Anytime a) b) c) or d) occurs in the database, the information from the Log Buffer is written to the current Red Log by LGWR. Redo Logs are actual physical files residing on the OS. When a Redo Log becomes full, a Log File Switch occurs and a pointer is set to start writing Log Buffer information to the next Redo Log in line. You can run your database with only 2 Redo Logs, but Oracle (and common sense) recommends at least 3. 
So, after this Log File Switch, you have a Redo Log that is full and a pointer to the next Relo Log. The Redo Log that is full needs to be archived (saved somewhere else). So now, a background process called ARCH will get a nudge from LGWR saying "hey, got a redo log that needs to be archived" and ARCH will pick it up and convert it to an Archived Log file and save it in the location specified by your init.ora parameter setting called log_archive_dest_1 (or in yor flash_recovery_area, depending).
So now that the previous Redo Log has been archived, it can be overwritten by LGWR when necessary (e.g., Redo Logs are written to in a round-robin fashion and when Redo Log #3 fills up, the pointer goes back around to Redo Log #1. So if you're in archivelog mode and redo log #1 hasn't been completely archived by ARCH yet, and LGWR needs to write to Redo Log #1, then your database "hangs" until that Redo Log #1 is freed up to be written to again).

So, what is the advantage of having Archived Logs? Say for example you expereince severe corruption or a database crash that required you to restore some datafiles from 7 hours ago. If you have all the archived logs from that pint in time (7 hours ago) up until the oment of the crash, you can apply (or "roll forward") all the changes contained in those archived logs against the restored datafiles. Basically this replays all the changes in the database over the past 7 hours. After recovering the last archived log, Oracle will then look to roll forward even more by using the online redo logs. If those online redo logs contain changes necessary, Oracle will apply those changes also. Basically, you can recover from a serious error all the way up to just before the error occurred. Minimal data loss is the advantage here. you can't do this when you're not in archivelog mode, because all the changes over the past 7 hours are lost because the redo logs just keep overwriting themselves and all the changes are lost between the time of your last backup and the time of the crash.
As the mantra goes . . . if you don't care if your database loses data, then run in noarchivelog mode. If you care about your data and don't want to lose it, then run the database in archivelog mode.

No comments:

´