Re: WHY index can corrupted???

Miyaki wrote: > >>OS : Sun Solaris 2.7 > >>IDS : 7.31.UD5 > >>Recently my database engine was crashed due to the index corruption. The > >>engine was back up after repairing the index by using the oncheck command. I'm > >>wondering why the index can be corrupted? In fact, I just performed the > >>database re-org by using the dbexport-import at 3 months ago. > >>Anybody knows the reason that may cause the index corruption??? > Art S. Kagel answered: > > There are several sources of index corruption generally in a few classes: > > Caused by bugs: > > There was a known BTREE-CLEANER bug that would occassionally corrupt indexes but > > I no longer remember what release(s) are affected. > > Caused by crashes: > > If you use caching controllers and/or an intelligent disk farm there is a real > > risk that a system crash can leave data in the cache. This may or may not be > > flushed to disk eventually and you may or may not want it so flushed. Especially > > prevalent after a controller failure. > > Caused by improper shutdown: > > Often I've found corruption similar to what one normally finds after a system > > crash following a supposedly graceful system shutdown/restart. This is caused > > by the rc files sending a shutdown to the database and returning immediately > > without waiting for the server to actually shutdown. The delayed shutdown can > > be caused by a large number of dirty buffers not yet flushed or applications in > > critical sections for which the server must wait before starting the checkpoint > > preparatory to a shutdown. > > Caused by database logging mode: > > UNLOGGED databases are at the highest risk for index corruption as the engine > > has no way to recover a partially written index update after a crash or improper > > shutdown. These are most likely to suffer corruption caused by the other > > issues. > > BUFFERED LOG databases are still at some risk for corruption, especially if > > there are no active UNBUFFERED LOG databases running in the same instance. This > > is because a BUFFERED LOG database does not force the logical log buffer to > > flush when committing a transaction. UNBUFFERED LOG databases always force a > > logical log buffer flush immediately upon writing a COMMIT record to the logical > > log buffer and so are rarely found to have a corrupted index. If you have a > > mixture of BUFFERED and UNBUFFERED databases you may be OK if the UNBUFFERED > > databases are rather active since the BUFFERED database records will be caught > > up in the flushes forced by the UNBUFFERED database activity. > > Note that having databases that are UNBUFFERED LOG databases offers the > > additional risk that the engine will not be able to restart after a crash > > without tech support intervention. This can be caused when a commit requires > > multiple writes to the logical logs and the commit is partially written to one > > buffer which fills and is flushed, then the final COMMIT is written to the next > > logical log buffer which is not immediately flushed before the system crashes. > > On restart the engine will hang when it encounters the end of the logical log > > in the middle of a partially written commit. > > Art S. Kagel "Michael Mueller" <michael.mueller01@kay-mueller.de> observed: > Hi Art and all, > I don't doubt that some of you have observed index corruption in logged > databases after system crashes. If this was not caused by faulty > hardware or os software that corrupts disk contents directly (index and > data pages, log space, etc.) it should be considered an Informix fast > recovery bug and should be fixed if possible. This should also apply to > buffered logging. > Michael Michael, Art has correctly pointed out that caching controllers may potentially cause corruption. That kind of corruption: - is not due to hardware failure, but to the way the hardware works ordinarily - is not due to the database engine So you have a corruption which is not a bug and is not caused by faulty hardware... I agree that a good controller should have a way to disable its cache if I don't want it (on database disks, for example), but if the manufacturer doesn't give you a way to do that... I'd call that too-smart hardware: it tells the engine that data has been flushed before it has been really, and the db faithfully marks that data as already written. It works as a charm until power outage or overheating force a disk farm stop... You are lucky if it's just an index involved! With Oracle and ordinary datafiles, the problem is even greater. You have OS buffering too. Greater flexibility, but less robustness. It tells you that a datafile needs recover, you give RECOVER DATABASE, and sometimes it's all OK. I wonder what has been done when it succeeds. If datafile was corrupt, how can it recover without physical restore? Why doesn't it attempt that directly by itself? Well, maybe I'm too curious, I like to "look under the hood" to understand... Of course, using cooked files exposes Dynamic Server to the same problem. So that's another possible cause. Summarizing them all: - bugs - crashes with cacheing controllers - improper shutdowns - buffered log or unlogged databases - usage of cooked files instead of raw devices with databases Umberto Quaia