Symptoms of Database Corruption
Database corruption is not always as simple as a “database corrupted” error messages. Unless you are a database administrator, you can only act based upon the symptoms that suggest database corruption. The corruption may be limited to a single table, a data block or a single entry.
What errors and system behaviors are symptoms of database corruption? And what errors and system generated messages look like symptoms of database corruption but are not?
Errors Viewing Data
When you are opening a Microsoft Access table and receive the error "invalid field data type", this can be a symptom of database corruption. When you receive the error "MS Access can't open the table in datasheet view", the error may be due to a corrupted database. Invalid argument errors when trying to view database records can be a symptom of database corruption. If Microsoft Access cannot find the object, this could be due to its corruption.
You may have an incorrect object name or path called out. Notices that the database cannot be opened or errors referencing "unrecognized database format" may be due to corruption.
The "unrecognized database format" error may be due to an incompatible database format such as trying to open a competing database format or early version of the database software you are using. If you cannot read an older database version, back it up before converting it to the newer version. Then try to open it again.
Database Maintenance Warning Signs
With Oracle databases, you can proactively prevent serious problems by periodically repairing and compacting it. When trying to compact a database, the error "could not find the field description" is a possible sign of database corruption. If you receive the notice "Table TempMSysAccessObjects’ already exists" when trying to compact the database, it is probably corrupted.
Errors that the database object does not follow data naming rules though the item follows the appropriate naming convention is another symptom of database corruption. However, you should verify the naming convention and unique identifier format before assuming the problem lies with the database.
Deletion Notices That Imply Database Corruption
If the database provides an information notice that a record is deleted when it is not, the database may be corrupted. If you receive the notice that changes were not successful because they would create duplicate values in the index or primary key when the command clearly would not do this, you can be certain that the database is corrupted.
When the database tables in Microsoft Access show "#DELETED#" although nothing was deleted, the database is corrupted. If you cannot delete objects, this can be a symptom that the database is corrupt.
Errors for Database Corruption
The Oracle error "ORA_11578: ORACLE data block corrupted" means that the data block is corrupted. Using the recover block command allows you to recover the corrupted block without having to recover the entire database.
However, this command does not repair logically corrupted blocks, only physically corrupted data blocks. The Microsoft Access error “the Visual Basic for Applications project in the database is corrupt” message clearly indicates that the database is corrupt.
Errors That Are Not Signs of Database Corruption
The Oracle error "ORA-00376: file 10 cannot be read at this time" means that the datafile or Oracle tablespace is offline. Restoring it will make Oracle tables available again. Oci.dll errors attempting to open an Oracle database may be the result of not having Oracle installed on the computer or a corrupt Oracle installation instead of a corrupt database. TNSNAMES.ora file errors for an Oracle database mean that the tnsnames.ora file is point to a database for which the user does not have access or that is not active.
If the user has conflicting versions of the tnsnames.ora file, they will get an "ORA-12154 error message (could not resolve service name)" error. These users cannot open any Oracle database until there is only one tnsnames.ora file or the same version of the file is kept in multiple locations on the machine.
To test if the Oracle database error is local to a user’s computer such as an incorrect tnsnames.ora file database name, use the Oracle sqlplus application that comes with the Oracle client, generally under "Start", "All Programs", "Oracle orahome92" or the current Oracle database version. This will prompt the user to log into the database.
On the sqlplus sign-in window, leave the user name and password blank while using the database name in the "host string" field. If the tnsnames.ora entry is correct and the database is up and running, you'll get the error message "ORA-0117: invalid username/password; logon denied".
The Oracle message "ORA-12545" means that the Oracle instance cannot communicate with the listener for the Oracle database. The database may be up and running, but the listener service is either stopped or too busy to create a database connection for the user. Try to access the database again. If this fails, notify the database administrator to check and possibly restart the Oracle listener. Error “ORA-12224:TNS: No Listener” also means that the listener is not running, but can be caused by Oracle version incompatibility.
If database connection errors keep reoccurring but the system administrator says the software application is running find, look for a physical cause. Communication failures with a database can be as simple as a loose network connection or failing hard drive.
Steps to Minimize Database Corruption
Lock database entries while they are being edited to minimize the risk of database corruption. If only one user at a time can alter the record, the database does not have to determine which user entry to save or risk becoming corrupted due to simultaneous edits.
One of the simplest solutions to prevent database corruption is saving often. When the database is corrupted, you can return to the most recent backup and quickly rebuild the latest version from archive logs or recreate the last few transactions. Running an Oracle database in archivelog mode makes it easier to recover; the archive log files are used to recreate changes to the database made since the last backup.
Corrupted controlfiles prevent an Oracle database from opening. Creating multiple copies of controlfiles and log files, a process called multiplexing, ensures that at least one version of the file is good and available to open the database if the main control file is corrupted.
Database administrators should review the error logs to find problems early. Then the DBA can fix them instead of letting problems grow until the entire database is inaccessible.
More by this Author
What are data collisions? And how can you prevent data collisions from interfering with your database's operations?
What is PDM or Product Data Management software? How is PDM software used?
Balanced Scorecard includes financial, customer, internal perspectives and "learn and innovate". How is the Balanced Scorecard (BSC) used?