Types of failure that can occur in Oracle Database.

There are various types of failure can occur is oracle database. Not all types of failure require DBA interventions. Only two type of failure require DBA intervention in order to recover get lost data are a)media failure, and b)user errors .

The next section I will try to discuss the different types of failure can occur in oracle database.

A)Statement Failure:
--------------------------
Whenever a statement fails, the server process executing the statement will detect the problem and roll back the statement. Remember that a statement might attempt to update many rows and fail part way through execution; all the rows that were updated before the failure will have their changes
reversed through use of undo. This will happen automatically.

If the statement is part of a multi statement transaction, all the statements that have already succeeded will remain intact, but uncommitted.

There are four common causes of statement failure:
(i)invalid data,
(ii)insufficient privileges,
(iii)space allocation problems, and
(iv)logic errors.
Examples:
---------
(i)Invalid Data
----------------
SQL> insert into test_stmt_failure values(1);
insert into test_stmt_failure values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (ARJU.SYS_C008711) violated

(ii)Insufficient Privileges
------------------------------
SQL> create table test(a number);
create table test(a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

(iii)Space Allocation Problems
-------------------------------
SQL> create table big_tab (a char(2000)) storage (initial 10M) tablespace test_tbs;
create table big_tab (a char(2000)) storage (initial 10M) tablespace test_tbs
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TEST_TBS

(iv)Logic Errors:
-------------------
SQL> declare a number;
2 begin select col1 into a from test_stmt_failure;
3 end;
4 /
declare a number;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 2

(B)Process Failure:
--------------------
A process failure is a failure in a user process accessing Oracle, i.e., an abnormal disconnection or process termination. The failed user process cannot continue work, although Oracle and other user processes can. If the user process fails while modifying the database, Oracle background processes undo the effects of uncommitted transactions.

(C)Network Failure:
-----------------------
The three points is considered in case of network failures, They are listeners, network interface cards, and routes.

(D)Instance Failure:
---------------------------
Oracle instance is composed of SGA and background processes. Whenever either or both of these fails then instance failure occurs and database discontinue to function.

Instance failure can result from
(i)Hardware problem such as a power outage, or
(ii)A software problem such as an operating system crash.
(iii)Issuing SHUTDOWN ABORT command.

When an instance fails, Oracle does not write the data in the buffers of the SGA to the datafiles.

(E)User Errors:
-------------------
When a user mistakenly delete/update/insert the data of a table and committed that data then that type of errors is called user errors.

(F)Media Failures:
-------------------
Media failure is a physical problem that arises when Oracle tries to write or read a file that is required to operate the database. A common example is a disk head crash that causes the loss of all data on a disk drive. Media failure occur also when physically a disk is damaged.

Related Documents

Flashback Database
Oracle Flashback query
Flashback Features
Flashback Table And Flashback Drop