Common Causes of Inconsistent Databases

To recover a database from an operating system backup that was made while the installation was running, see Recover an Inconsistent Database.

Changing Ingres installation variables (such as II_SYSTEM, II_DATABASE, II_CHECKPOINT, II_JOURNAL, II_DUMP, or II_WORK) without using proper procedures, causes inconsistency between the information stored in the installation variables and those stored in the database configuration file “aaaaaaaa.cnf”.

Database inconsistency can occur if you move a database, table, application or some other object by using operating system commands rather than the supported Ingres utilities. If the inconsistency is the result of moving a database from another location or installation without using unloaddb, you must remove the database using destroydb, recreate the database using createdb, and repopulate the database using the unloaddb utility. For details, see the Database Administrator Guide.

A database file can become corrupted from hardware or software failures of various kinds. A data file can be inadvertently deleted by hand, but this is rare because only the user who owns the installation can write to the database directories.

If you are in doubt about whether transactions are being recovered, run the logstat utility and examine the “Status” field. It is marked RECOVER if in the recovery state. While a recovery is taking place, for example when restarting after a system failure, the recovery process requires time to read through the transaction log file to back out uncommitted transactions and complete fast-commit transactions. To users, the system appears to hang.

To examine the configuration file of your database, enter the infodb command at the command prompt. You can also use the Database menu in Actian Director or VDBA.

1. Compare the path information for the checkpoint, journal, data and dump locations with that defined for these environment variables and logicals as displayed by the following command:

2. Return the installation logicals to the values displayed to by infodb, if the values have changed. If these values are not the same, the installation logicals have been changed, or the database has been imported from some other Ingres installation.

3. If you need to change the existing values of Ingres installation variables or import a database from another site, you must use the unloaddb utility, as this creates a new, up-to-date configuration file for the database. For a discussion of Ingres environment variable and logicals that cause an inconsistent database if changed after installation is completed, see the chapter Environment Variables and Logicals.

The following are rules that you should keep in mind about the recovery of transactions:

• It takes at least as long to recover aborted transactions as it took to execute them originally.

• The amount of time required for recovery depends on the number of users and transactions, transaction semantics (whether autocommit is set), and the consistency point interval.

• While recovery is proceeding, all users are denied access to databases. Any attempt to connect to a database at this point returns an error such as the following:

• Database inconsistency can occur if a user or the system administrator attempts to “force” entry into the installation by running rcpconfig with the -force_init_log flag (thus erasing the transaction log file) before the recovery system has finished rolling back the uncommitted transactions during recovery.

After a system failure, monitor recovery and always allow it to proceed until the “Recovery Complete” message appears in iircp.log.

By disabling the logging or recovery system, the DBA can temporarily turn off logging for the database to speed bulk loading of data. If logging has been turned off for this database, a NOLOGGING error message appears in the error log file. Typically this message is:

E_DM9050_TRANSACTION_NOLOGGING Database dbname has been updated by a session running with SET NOLOGGING defined.

If the database has become inconsistent, you can check for this error message by typing the following command:

If the NOLOGGING error message appears, logging was disabled on this database. If the NOLOGGING message in the error log was written later than the most recent checkpoint of this database, the database must be restored from the checkpoint. To determine if this is the case, compare the timestamp on the error message in errlog.log with the timestamp in the “checkpoint history” field of the output from the command infodb dbname.

For details on set nologging, see the Database Administrator Guide.

Database inconsistencies can be caused by the use of unsupported hardware configurations on NFS. In systems that include Network File System (NFS) mounts, be aware that Ingres:

• Supports NFS client installation configurations in which the DBMS Server process and data directories on one node are accessed by application programs executing on another.

• Does not support running DBMS servers on one node and accessing data directories on another network node using NFS. The configuration can cause undetected write errors that lead to database inconsistency.

To check your configuration, type mount at the operating system prompt. Make sure that the data directories (II_DATABASE, II_CHECKPOINT, II_JOURNAL and II_LOG_FILE) are not NFS-mounted from a remote node.

For a description of the supported NFS client installation procedures, see the Installation Guide.