Re: DBMS Internals: Transactions vs. Redo Logs

Look at it this way ... 1) A transaction is started 2) A before image is made in the rollback segments 3) Update is applied to in memory copy of block 4) Transaction is closed (committed) 5) Transaction activity is written to redo log 6) Transaction is written from memory to data file 7) Transaction is marked 'clean' 8) If archiving is active, redo log is copied to archive file when full (I'm sure some flames will show up, but it's close & this model has served me well for several years.) Redo logs are multi-file circular buffer ... when one file is full, switches to next, ultimately back to first when last is filled. An archive file is created shortly after a redo log is filled to allow an image of the redo log to be safely tucked away on tape (or elsewhere). If a transaction crashes, or is rolled back, it is restored from the rollback segment. If the CPU crashes, all committed transactions are recovered from the redo log automatically (never seen this to fail yet and I've forced some nasty crashes to test this one). If the media crashes, the latest backup of the files must be restored and, if archiving is used, archives are rolled in to recover to point in time where active redo log can kick in as if CPU crash had occurred. ---- Your objective seems to be to develop a backup strategy for the system. Depending on your recovery requirements (point in time, to recent snapshot or simply operational), you want archiving, file copy or export (in that order). Archiving allows recovery to time of last archive log - depending on size of redo logs and activity, this could be within minutes of an outage. File copy is basically a snapshot of the database (or portion) and allows recovery only to that time. Export does not provide recovery. Instead it provides a snapshot of data to be restored. The difference is internal (eliminates fragmentation, etc but loses rowids). Under no circumstances should an Oracle related file be copied when there is write activity or the potential for write activity. Thus, file copies of tablespace files should only be done when tablespace is offline. For system tablespace, this implies the database is down. If copying entire database (personal recommendation - 1/week minimum as initial plan), make sure you copy the control files as well. Oracle is very careful to ensure that committed transactions are recorded in a recoverable manner. Because of this, I've found that the delays you discuss are non-issues - except when the Operating System cannot write it's file buffers to disk in a timely fashion. (This latter is the reason for the sync command in UNIX & it affects everything, not just Oracle). By the way - do NOT attempt to copy the redo logs directly to tape while the database is up. They are internally marked & can not be brought back in the same manner as the archive log files. That's the reason that the archive log files were made. Hope this helps /Hans