Lost a day's worth of data - anyone have any clues?

I know, it's old, but we're using Informix 11.70 for a client. A couple weeks ago, the VM they were on had a memory card that was, I'm told, not seated properly and we had memory errors. Not sure how long. But back on the 9th, we just seemed to lose everything that happened that day. Interesting thing, there's a dbexport of the database nightly. That export had the missing data. So Informix had the data around 11:20pm. At something like 3am, the machine froze. When it was brought back up, the database changes from the previous day were gone. The file system didn't seem to suffer any such loss. Plain Windows files were there. This is a non-logging database. Nobody dumped the database and restored a previous copy. Nor did they do some sort of all-system restore because, like I said, normal Windows files created that day were still there. Best I can think to explain it is that checkpoints aren't happening. Changes were written to the cache, but dirty pages weren't being flushed to disk. Maybe the page cleaners suffered because of the memory problem and weren't working? But because the cache was there, the dbexport that night was able to write the database updates (still in cache) to disk. But once the machine was restarted, that memory was gone. Any thoughts as to what happened? And any ways to monitor this to prevent it from happening again? If it is the checkpoints, would a manual "onmode -c" every so often throughout the day be insurance? Or would that try to employ the existing page cleaners (which didn't seem to be working) and not help one bit? I'm not a DBA. Primarily a developer. Had the Informix DBA class 30 years ago. I usually just install it and it runs well. Using the dbexport that did contain all of the changes, I was able to create some programs to help load much of the missing data back. Big problem because of things like serial fields. After the data was lost, and it reverted to a previous state, Informix started reusing serial numbers that had already been assigned, so just loading the same data didn't work. But my primary concern is not data recovery, it's preventing this from happening again. The memory in the machine was re-seated and the hardware guy tells me that tests seem to say it's OK now. But just in case...

I'd check the Informix server's message log: was it properly checkpointing, every couple minutes, over that period that's missing now? If not, is this instance configured with AUTO_CKPTS on? -> if so, that's a known problem, fixed in later versions. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Hi, many things probably lead to this fatal loss of data. It seems to me that all activity was in the cache and did not trigger a checkpoint. The database typically recovers from the last checkpoint. (yes, you could trigger a checkpoint with onmode -c) After that, logical logs are applied, which would be empty in your case, since the database is set up with no logging. Firstly, use at least buffered logging for the database. That will generate the logs, keeping track on the changes between checkpoints. In this setup (application probably has no concept of transaction handling), each statement will trigger a transaction internally. Second, back up the logs to a separate machine regularly (you should automate that using alarm program, exporting the logs in a local directory is supported. Backup is needed in order to release the logs on disk. Without a log backup, the database might run into a freeze situation, because logs are full). In case your application does not change that much data, you can trigger a logical log backup with onmode -l by a periodical job. Third, check your onconfig for the checkpoint interval where the DB triggers checkpoints by time. (in our setups, this would be typically at least after 300 sec). This can be done with every type of 11.70 Informix. Fourth (optional, leads to a complete mirrored version of the database): set up a HDR secondary on a separate machine with separate power (optimally in a different location) This should be doable (depending on the version (Workgroup, Innovator, Express....) There are some pretty easy to use documentations available how to do this. I understand you are not really a DB admin, but backup of data can be done with ontape very easily. (you would need that for setting up the HDR secondary anyway). A separate dbexport of course does not harm ... But a binary backup and logical logs can give you a nearly lossless recovery. There are plenty of people in this group who might help setting up this (quite simple at least from the perspective of an experienced DB admin) scenario, which basically needs no real permanent attention. The only thing which is not quite that common would be a Windows machine as a server. Most people would prefer a Linux/Unix setup, but the main commands are identical. Best,

Joel: You have several problems that combined to cause the issue you experienced: The database is NON-LOGGED. No transactional database should EVER be non-logged! With no logging of transactions the engine could not recover from the crash. Neither rolling forward to the point of the last log records flushed to disk nor rolling back incomplete transactions. That means that in addition to data loss you may have experienced inconsistent data. Only the dbexport saved you. Turning on logging, and I strongly recommend using UNBUFFERED LOG to best minimize the possibility of data loss. You are running, as Andreas pointed out, a version that has a bug that can prevent or delay auto-checkpointing. You should have CKPTINTVL set to between 300 and 600 seconds depending on your transaction volume (I am a big fan of 300 myself and for my clients). I would strongly recommend disabling AUTO_CKPT (ie set to 0) as even without the bug that Andreas. Setting AUTO_CKPT on tends to result in checkpoints being delayed during parts of the processing day and too frequent during other parts which can affect performance. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------