What is the Logical Corruption and how to resolve it?
There are two different types of corruption possibilities in database. Those are called as physical corruption and logical corruption. Logical or data corruption is irregularity inside the data, resulting in inaccuracy and violation of critical business rules. For instance, there may be phantom or orphaned rows, that is, child rows without parents, causing them to infringe referential integrity within the database. Fascinatingly, logical corruption may not necessarily be reported as an Oracle error. For an example, when orphaned rows exist, Oracle may not reject such rows due to referential integrity being defined, but disabled those rows. However, it is still believed a form of corruption due to basic business rules and application dependencies being violated and queries possible returning incorrect results. Such corruption may have reasons such as the following.
Application bugs like data-load programs allowing corrupt data to be loaded or loading valid data in a wrong fashion, introducing logical corruption. Such bugs may result from faulted design, as well as inconsistent coding.
User or administrator errors like accidental table remove or truncations and so on.
Incomplete recovery like inconsistent point-in-time recovery mechanism such as segment point-in-time recovery where recovered segment is out of sync with the other segments in the schema, tablespace point-in-time recovery where the segments in the recovered tablespace are out of sync with the segments in other tablespace.
Corrupt segments, such as indexes, that don’t have a corresponding entry for all rows in the table.
Infrequently, Oracle bugs those allowing duplicate rows to creep in, due to a corrupt unique index or errors noticed when using a specific Oracle option, such as the parallel query option or cost-based optimizer.
Identifying and resolving logical errors may need good knowledge of the application. One needs to look at the various schema and the inter-relationships among them, the status of database constraints during batch runs, database options being utilized, frequency of segments being analyzed, hints used in SQL statement for example, the INDEX_DESC or INDEX_ASC hint returns incorrect rows, view definitions, privileges, and the SQL statements themselves for verifying functional accuracy. Also in case data is being acquired superficially for loading via batch operations, then the source data and the sequence of segments being loaded need to be examined. If the source data is very large, then the large file may be sliced and examined individually, either entirely or by random sampling. The sequence of tables being loaded may be important, such that tables required to satisfy referential integrity may have to be loaded first.
For resolving logical corruption of metadata, you need to consult Oracle Support first and provide them full details of corruption including trace files and errors.
Sometime this kind of logical corruption we can remove using RMAN block level recovery or setting of Event of errorstack. Sometimes if we execute catalog and catproc sql files then both files rebuild data dictionary database and repair corrupted. But without guiding of Oracle support staff do not perform any task otherwise you would loose your data.
When you want to make strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would be enjoy more advance topics from our partner resource.