Re: Is it really necessary to mirror the physical log?

"Art S. Kagel" <kagel@bloomberg.net> offerred: +Truth be told the result of Logical Recovery without Physical Recovery +is the same if all logical log records are available. This is +mathematically provable. I have thought about this in the past (although I'm not sure how it is mathematically proveable). I believe you are wrong that the end result would be the same. Think of this scenario: A checkpoint occurs. Immediately after, you insert a row into a table in a transaction. A page for that table receives the row. The transaction is committed. LRU cleaning kicks off and that page is flushed out to disk. A crash occurs and the system goes offline without a checkpoint. Start the system back up with (theoretically) physical recovery turned off. So you locate the last checkpoint record and start rolling forward transactions from that point on. You read the insert transaction record, so you insert that record into the table. But the record is already there - without the physical restore to "undo" the insert done after the checkpoint. So if there is a primary key on the table, the insert fails and thus logical recovery fails. If there is no primary key, then you have duplicate records in the table, which is certainly not what you wanted. With physical recovery on as normal, the physical resore assures that any pages modified in shared memory since the last checkpoint reflect their exact state before any transactions were applied against them. So our page from above will be restored and that newly inserted row is no longer there. The replayed transaction inserts the row again, without the presence of a duplicate record. You can extend the same logic to the case of a deleted row. The key to understanding the need for physical recovery during fast recovery is realizing that pages modified in shared memory could be flushed to disk in between checkpoints. Without restoring those flushed pages to their original state, logial recovery couldn't possible recreate a database in a correct state. As regards mirroring, since the rootdbs and any other dbspace that contains logical or physical logs are ALL NECESSARY for online to stay operational, if you are going to mirror one, you better mirror all three; the loss of any of these will result in an unstartable database server. Dave -- Dave Kosenko davek@summitdata.com Director of Training Services (732) 469-4070 Summit Data Group (an Informix Authorized Education Center)