Using IBFirstAid to Diagnose and Repair a Corrupt Database
IBFirstAid for Firebird is an automatic tool that can open a Firebird database as a file and identify damaged page, record, BLOB and index structures. Because it does not have to "connect" to a database as a client, it can offer hope in cases where a database is damaged in ways that the gfix validation and mend routines cannot fix on their own.
IBFirstAid is a commercial product made by the IBSurgeon Team. A free version is available, called IBFirstAid Diagnostician that can be used to diagnose in detail what the corruption is and determine whether the repair routines in the commercial version can fix it.
In this discussion, we describe the full, paid version. The free tool works exactly the same, up to the point where Repair could begin.
The product also comes in separate versions for Firebird and InterBase databases. It is the Firebird version we discuss here.
Current versions of the product run in a 32-bit Windows environment, including compatibility mode on Windows 64-bit platforms and some 32-bit Wine environments on Linux.
An IBFirstAid session has three main parts:
- Opening the database
- Running the diagnostic routine
- Running the repair routine
IBFirstAID does not fix minor corruptions or refresh the state of the database. After any structural repairs are complete, additional steps will be required, using the command line tools, to end up with a completely fresh and sparkling, working database that is ready to return into production.
The first thing IBFirstAid has to do is open the database file and build an internal map of the corrupted database. It will use this map for the subsequent operations as it walks through the structures of the database.
It may take IBFirstAid some time to open your database if the database is large, the computer is slow and/or the page size is small. As an indication, on a 2GHz Pentium IV or equivalent with 512Mb RAM, you could expect IBFirstAid to open a database in about 60 seconds with:
- a 3Gb database with a page size of 4096 bytes
- a 500Mb database with page size of 1024 bytes
Before you try to open the database with IBFirstAid, take these two preparatory steps:
If Firebird is running, stop the service.
Make a file copy of the database in a separate, safe location.
Remember to copy all of the files of a multi-file database!
Open the database:
- Click Database > Open
- Click the File or the Multiple files icon, as appropriate
Find and select the database file(s):
Using the File dialog that appears, locate and select the database file and click Add File.
If you are using the interface for a multi-file database, continue adding files in this manner until you have all of them in your list.
If you make a mistake and need to remove a file from the list, select it and click Delete, or click Clear All to clear all entries from the list.
When the required files are selected, click OK and you are ready to begin the diagnosis.
When you are ready to start the diagnosis, you should see something similar to this:
Once all the database files are loaded in the list, IBFirstAid is ready to begin diagnosing the structures of the database and identifying those that are damaged. Tables and indexes will be checked. Tables are more important, since they contain system and user data. However, the index checks are useful, too, for assessing the situation.
The software will produce logs which can be saved to text files.
If you are using the full version of the software, you can skip this step altogether, if you wish, and go straight to the Repair step, which repeats the Diagnosis routine anyway.
Start the diagnosis
To start the diagnosis, simply click the red checkmark icon in the toolbar. The log window beneath it will proceed to fill up with lines of information as IBFirstAid walks its way through the structures and a progress bar at the bottom indicates how far the process still has to go.
Diagnosis could take several minutes. As an estimate, it will take about 90 seconds for each gigabyte on a Pentium IV or equivalent box with 1GB of RAM.
This screenshot gives a sampling of what you would see if the diagnosis is able to proceed. Use the scrollbar to view the entire contents.
At this point, even if corruption was found and reported, nothing has been written to the database.
Check the detailed log
Once the diagnosis completes, the two last lines in the report window will show a summary of errors counted followed by ---------Finished diagnose---------. Now, you can click on the Detailed Log icon in the toolbar to access both the summary view ("Important messages") and the detailed log:
By clicking the appropriate button in this log form, you can save either or both logs to a file on your hard disk.
It will do no harm to save whatever logs are available, whenever new logs are created. If it turns out that you have errors that cannot be repaired by automated tools, the logs can provide vital information to pass to database recovery experts.
During the analysis, the log messages might stop after one that says "Cannot build internal database image. Database seems to be dead." Unfortunately, it means the database is too damaged to be analysed or repaired by automatic tools. The user interface provides links and instructions for getting further help.
If the diagnostics indicate a dead database, do not try to proceed to the Repair step. However, do not close the application. Follow the instructions above for capturing the log into a text file which can be sent on to experts to figure out what else might be possible to recover the database.
If you have the paid version of IBFirstAid, that includes the Repair tools, you can now proceed to repair the database. Be patient! Repairing will take approximately 60 to 120 seconds per gigabyte, or longer if the application needs to recreate a lot of pages.
- Click the Repair icon or
- Choose Database > Repair from the menu bar
At this point, a modal dialog box appears to remind you to make sure you have backup file copies of your database files:
If you are certain you have the backup copies, just press OK. Otherwise, press Cancel and make the required backup copies.
Run the repair process
During the repair process, IBFirstAID checks the low-level physical consistency of tables and their constraints. Where possible, it fixes relationships, recreates system pages and disables wrong linkages.
You can watch the details of the repair process in the log window. However, there is no need to watch them carefully, since the logs will be available on completion to review and save to files, as previously described for the Diagnostics logs.
When the message bar above the log window indicates ..! ..! .. Finished repair, Repair has finished its work.
On completion of the Repair task, it now remains to use the standard command-line tools gfix and gbak to clean up the loose ends.
Once IBFirstAid Repair has finished its work, you are ready to proceed with the final clean-up steps that will make the repaired database production-ready.
This section expands on the instructions that appear on an information and contact page that is displayed when you click on the Final Steps button beneath the log window after Repair completes.
Save logs and exit IBFirstAid
Before exiting from the IBFirstAid program, go to the Detailed Log pages and save the logs produced by Repair, in case they might be needed for a later review of the damage that was and was not repaired.
Once Repair is finished and you have saved the logs, it is safe to close the program window in the normal ways.
Restart the Firebird service
If you followed the earlier instructions to shut down the Firebird service, then restart it now.
Open a Windows command shell window
Click the Start button and choose Run.. > cmd.exe ) and cd to the bin directory of the Firebird installation.
Run a full validation and repair routine using the command-line tools gfix and gbak
Only the SYSDBA (or equivalent) or the database owner can run a validation. Refer to How To Analyse and Repair a Corrupted Database for the exact steps and order required to accomplish this task.
Because it is still possible that your database has damage of a kind that only a professional recovery expert can help with, make sure you keep the output from this command for possible future reference.
On some versions of Windows you can pipe the standard output directly to a text file by appending the ">" symbol to your gfix call, followed by a fully-qualified path to a file name.
Alternatively, by clicking and holding the left button of your mouse, you can highlight blocks of output in the shell and press the Enter key to copy it to the clipboard. From there, you can paste it into a text file manually.
Test the recovered database
Now, all that remains is to connect to your freshly-restored database with isql or your favourite query tool and check it out with some typical queries.
If all is well, you can go ahead and put the freshly recovered database on-line, renaming it if necessary.
If the outcome is less than you hoped - a database that is still being reported as corrupt - then zip up all your logs and command-line output and contact the IBSurgeon support folk directly. The better the information you provide, the more likely it is that you can get a quick answer.
As a minimum, you should attach:
- A short description of the problems you encountered
- The Diagnostics and/or Repair logs from IBFirstAid
- Your firebird.log file, zipped
- Output from gfix and gbak, if possible