Archives and Logs

Question: What can I do to Prevent Logs from Filling up During a Long Transaction?

Answer: Set LBU_PRESERVE = 1.

In earlier versions of the OnLine engine, users occasionally ran into a problem with getting into a long transaction and filling the log files when rollback occurred. This would result in the "Informix Death Penalty" in which the only way to recover the system was to restore from archives.

Later IDS engines set the LTXHWM and LTXEHWM to lower defaults of 50 and 60, reducing the likelihood of this happening. There is another setting that can provide a second layer of protection if you want a belt-and-suspenders approach to preventing this. Run your engine with LBU_PRESERVE=1 in the $ONCONFIG file. This prevents the last logfile from being used. If you ever get into a potential "death penalty" situation, shut the engine down and reset LBU_PRESERVE = 0 before restarting. If your logfiles are big enough to handle the rollback records, you'll be able to roll back the transaction using this last logfile.

Problem: Point-in-Time Recovery

On my OnLine (5.X) system, someone deleted a table and I'd like to try to recover it by restoring from an archive and rolling forward the logs to a point in time just before the delete. Is there any way of doing this?

Solution: Desperate Measures

If it is absolutely critical that you do this and you have no other options, there is something that you can try that is dangerous and totally unsupported. First, take a full backup of the system because there is about a 50/50 possibility that you will destroy your database.

Then start the restore with someone standing by the tape ready to eject the tape immediately. Get the PID of the restore so that you can kill it if needed. Do a continuous series of “tbtape -1” (you could also try this with ontape). When you get to a point just before where the delete occurred (you need to have an idea when this happened: use tblog or onlog to find the spot), have the tape watcher eject the tape. Force a checkpoint with tbmode -c, and then bump to the next logfile with tbmode -1. Take the engine down and start it back up. When it's up, run tbcheck -cr and tbcheck -cI and tbcheck -cD on the system. If it comes back up at all, do an immediate dump of the table to a flatfile with "UNLOAD TO." If the database shows any signs of corruption or other problems, go back and restore from your good archive. With any luck, the flatfile will be good enough to restore the table.

Although this is an OnLine trick, there may be instances in which you could do the same thing on an IDS system.