Database Validation and Recovery

Database Validation
Database Recovery

Database Validation

Sometimes, databases get corrupted. Under certain circumstances, you are advised to validate the database to check for corruption. The times you would check are:

When an application receives a database corrupt error message.
When a backup fails to complete without errors.
If an application aborts rather than shutting down cleanly.
On demand - when the SYSDBA decides to check the database.

Note

Database validation requires that you have exclusive access to the database. To prevent other users from accessing the database while you validate it, use the gfix -shut command to shutdown the database.

When a database is validated the following checks are made and corrected by default:

Orphan pages are returned to free space. This updates the database.
Pages that have been misallocated are reported.
Corrupt data structures are reported.

There are options to perform further, more intensive, validation and these are discussed below.

Default Validation

The command to carry out default database validation is:

gfix -v[alidate] database_name

This command validates the database and makes updates to it when any orphan pages are found. An orphan page is one which was allocated for use by a transaction that subsequently failed, for example, when the application aborted. In this case, committed data is safe but uncommitted data will have been rolled back. The page appears to have been allocated for use, but is unused.

This option updates the database and fixes any corrupted structures.

Full Validation

By default, validation works at page level. If no need to go deeper and validate at the record level as well, the command to do this is:

gfix -v[alidate] -full database_name

using this option will validate, report and update at both page and record level. Any corrupted structures etc will be fixed.

Read-only Validation

As explained above, a validation of a database will actually validate and update the database structures to, hopefully, return the database to a working state. However, you may not want this to happen and in this case, you would perform a read only validation which simply reports any problem areas and does not make any changes to the database.

To carry out a read only validation, simply supply the -n[o_update] option to whichever command line you are using for the validation. To perform a full validation, at record and page level, but in reporting mode only, use the following command:

gfix -v[alidate] -full -n[o_update] database_name

On the other hand, to stay at page level validation only, the command would be:

gfix -v[alidate] -n[o_update] database_name

Ignore Checksum Errors

Checksums are used to ensure that data in a page is valid. If the checksum no longer matches up, then it is possible that a database corruption has occurred. You can run a validation against a database, but ignore the checksums using the -i[gnore] option.

This option can be combined with the -n[o_update] option described above and applies to both full and default validations. So, to perform a full validation and ignore checksums on a database, but reporting errors only, use the following command:

gfix -v[alidate] -full -i[gnore] -n[o_update] database_name

Alternatively, to carry out a page level validation, ignoring checksum errors but updating the database structures to repair it, the command would be:

gfix -v[alidate] -i[gnore] database_name

Ignoring checksums would allow a corrupted database to be validated (unless you specify the -n[o_update] option) but it is unlikely that the recovered data would be usable, if at all, present.

Database Recovery

If the database validation described above produces no output then the database structures can be assumed to be valid. However, in the event that errors are reported, you may have to repair the database before it can be used again.

Recover a Corrupt Database

The option required to fix a corrupted database is the gfix -m[end] command. However, it cannot fix all problems and may result in a loss of data. It all depends on the level of corruption detected. The command is:

gfix -m[end] database_name

This causes the corruptions in data records to be ignored. While this sounds like a good thing, it is not. Subsequent database actions (such as taking a backup) will not include the corrupted records, leading to data loss.

Important

The best way to avoid data loss is to make sure that you have enough regular backups of your database and to regularly carry out test restorations. There is no point taking backups every night, for example, if they cannot be used when required. Test always and frequently.

Equally, when attempting to recover a potentially corrupted database, always work with a copy of the main database file and never with the original. Using the -mend option can lead to silent deletions of data because gfix doesn't care about internal database constraints like foreign keys etc, the -mend option simply says to gfix "go ahead and clean out anything you don't like".