Categories of Failures in Oracle Database

Today we are going to have look on Categories of Failures in Oracle Database.  This information helps to DBA solve the issue as soon as possible.

Statement Failure

Statement failure transpires when there is a logical failure in the handling of a statement in an Oracle program. If a statement failure occurs, then the Oracle software or operating system returns an error.

A statement failure usually requires no recovery steps: Oracle automatically corrects for statement failure by rolling back any effects of the statement and returning control to the application. The user can simply execute the statement again after the problem indicated by the error message is corrected.

For example, if insufficient extents are allocated, then the DBA needs to allocate more extents so that the user’s statement can execute.

User Process Failure

The uncommon termination of a user session is categorized as a user process failure; any uncommitted transaction must be cleaned up. The PMON (process monitor) background process periodically checks all user processes to ensure that the session is still connected. If the PMON finds a disconnected session, it rolls back the uncommitted transaction and releases all locks held by the disconnected process.

Causes for user process failures typically fall into one of these categories:

1. A user closes their SQL*Plus window without logging out.
2. The workstation reboots suddenly before the application can be closed.
3. The application program causes an exception and closes before the application can be terminated normally.
4. A user process times out and Oracle disconnects the session.
5. A small percentage of user process failures is generally no cause for concern unless it becomes chronic; it may be a sign that user education is lacking—for example, training users to terminate the application gracefully before shutting down their workstation.

Network Failure:

Depending on the locations of your workstation and your server, getting from your workstation to the server over the network might involve a number of hops: you might traverse several local switches and WAN routers to get to the database. From a network perspective, this configuration provides a number of points where failure can occur. These types of failures are called network failures.

In addition to hardware failures between the server and client, a listener process on the Oracle server can fail or the network card on the server itself can fail. To guard against these kinds of failures, you can provide redundant network paths from your clients to the server

When your system uses networks such as local area networks and phone lines to connect client workstations to database servers or to connect several database servers to form a distributed database system, network failures such as aborted phone connections or network communication software failures can interrupt the normal operation of a database system.

For example:

1. A network failure can interrupt normal execution of a client application and cause a process fails to occur. In this case, the Oracle background process PMON detects and resolves the aborted server process for the disconnected user process, as described in the previous section.
2. A network failure can interrupt the two-phase commit of a distributed transaction. After the network problem is corrected, the Oracle background process RECO of each involved database automatically resolves any distributed transactions not yet resolved at all nodes of the distributed database system.

User Error Failure

Even if all your redundant hardware is at peak performance, and your users have been trained to disconnect from their Oracle sessions properly, users can still inadvertently delete or modify data in tables or drop an index. This is known as a user error failure. Although these operations succeed from a statement point of view, they might not be logically correct: the DROP TABLE command worked fine, but you really didn’t want to drop that table!

If data was inadvertently deleted from a table, and not yet committed, a ROLLBACK statement will undo the damage. If a COMMIT has already been performed, you have a number of options at your disposal, such as using data in the undo tablespace for a Flashback Query or using data in the archived and online redo logs with the LogMiner utility, available as a command-line or GUI interface.

You can recover a dropped table using Oracle’s recycle bin functionality: a dropped table is stored in a special structure in the tablespace and is available for retrieval as long as the space occupied by the table in the tablespace is not needed for new objects. Even if the table is no longer in the tablespace’s recycle bin, depending on the criticality of the dropped table, you can use either tablespace point in time recovery (TSPITR) or Flashback Database Recovery to recover the table, taking into consideration the potential data loss for other objects stored in the same tablespace for TSPITR or in the database if you use Flashback Database Recovery.
If the inadvertent changes are limited to a small number of tables that have few or no inter-dependencies with other database objects, Flashback Table functionality is most likely the right tool to bring back the table to a point of time in the past.

Instance Failure

An instance failure occurs when the instance shuts down without synchronizing all the database files to the same system change number (SCN), requiring a recovery operation the next time the instance is started. Many of the reasons for an instance failure are out of your direct control; in these situations, you can minimize the impact of these failures by tuning instance recovery.

Media Failure

Another type of failure that is somewhat out of your control is media failure. A media failure is any type of failure that results in the loss of one or more database files: datafiles, control files or redo log files.

Although the loss of other database-related files such as an init.ora file or a server parameter file (SPFILE) is of great concern, Oracle Corporation does not consider it a media failure.

The database file can be lost or corrupted for a number of reasons:

1. Failure of a disk drive
2. Failure of a disk controller
3. Inadvertent deletion or corruption of a database file

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp